ytba92 ytba92 - 5 months ago 10
SQL Question

Query to fill Gridview with rows that contain only numeric values is not giving me the result that I want

I uploaded an Excel file, and I'm sorting the rows into 2 tables, one that has Valid and acceptable data, the other containing data that needs to be edited in order to be validated.
I have 3 columns: Name, Email, and Mobile
I'm having trouble querying the rows with valid Mobile values (numeric values, no letters allowed)

(SCROLL DOWN UNTIL YOU REACH "MY PROBLEM IS IN THE BELOW QUERY"
Here's my code:



protected void UploadBtn_Click(object sender, EventArgs e)
{
string filename = string.Empty;
if(FileUpload1.HasFile)
{

try
{
string[] allowFile = { ".xls", ".xlsx" };
string fileExt = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName);
bool isVaidFile = allowFile.Contains(fileExt);
if (!isVaidFile)
{
MessageLbl.ForeColor = System.Drawing.Color.Red;
MessageLbl.Text = "Must be an Excel file";
}
else
{
int fileSize = FileUpload1.PostedFile.ContentLength;
if (fileSize <= 1048576)
{
filename = Path.GetFileName(Server.MapPath(FileUpload1.FileName));
FileUpload1.PostedFile.SaveAs(Server.MapPath("~/UploadExcel/") + filename);
string filePath = Server.MapPath("~/UploadExcel/") + filename;
OleDbConnection con = null;
if (fileExt == ".xls")
{
con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";");
}
else if (fileExt == ".xlsx")
{
con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";");
}
con.Open();
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString();
OleDbCommand ExcelCommand =
new OleDbCommand(@" SELECT * FROM [" + getExcelSheetName + @"]", con);
OleDbCommand EditExcelCommand =
new OleDbCommand(@" SELECT * FROM [" + getExcelSheetName + @"] where Email not like '%@%.com' or Email is null or Name is null or Mobile is null or Mobile not like '^[[:digit:]]+$'", con);
//
//
// MY PROBLEM IS IN THE BELOW QUERY
//
//
OleDbCommand ValidExcelCommand =
new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"] where ((Email like '%@%.com') and (Name is not null) and (Mobile like '^[[:digit:]]+$'))", con);
OleDbDataAdapter EditAdapter = new OleDbDataAdapter(EditExcelCommand);
OleDbDataAdapter ValidAdapter = new OleDbDataAdapter(ValidExcelCommand);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
DataSet ExcelDataSet = new DataSet();
DataSet EditDataSet = new DataSet();
DataSet ValidDataset = new DataSet();
EditAdapter.Fill(EditDataSet);
ValidAdapter.Fill(ValidDataset);
ExcelAdapter.Fill(ExcelDataSet);
con.Close();
GridView5.DataSource = ValidDataset;
GridView4.DataSource = EditDataSet;
GridView3.DataSource = ExcelDataSet;
GridView3.DataBind();
GridView4.DataBind();
GridView5.DataBind();
}
else
{
MessageLbl.Text = "Attachment file size should not be greater than 1 MB";
}
}
}
catch (Exception ex)
{
MessageLbl.Text = "Error occurred while uploading file. " + ex.Message;
}

}
else
{
MessageLbl.Text = "";
}


}





The image contains the results I'm getting

GRIDVIEW RESULTS

My question is why is my ValidTable remaining empty? is something wrong with my query statement?

Answer

Your code looks valid, I suspect there might be a problem with your select statement.Create a simple string variable and set it equal to the command below:

   string validExcelCommandText = "SELECT * FROM [" + getExcelSheetName + @"] where ((Email like '%@%.com') and (Name is not null) and (Mobile like '^[[:digit:]]+$'))";

Place a break point on this line and run your application, take the value of validExcelCommandText and run the query in SQL Server Management Studio.If it doesn't return any results then fix your SQL query if it does then make sure there is no logic that hides your GridView control on the page or check the bindings in the code behind.