用只包含数值的行填充Gridview的查询没有给我想要的结果
本文关键字:结果 我想要 查询 填充 包含数 Gridview | 更新日期: 2023-09-27 17:53:13
我上传了一个Excel文件,我将行排序到2个表中,一个表具有有效和可接受的数据,另一个表包含需要编辑的数据以便进行验证。我有3列:姓名,电子邮件和手机我在查询具有有效移动值(数值,不允许字母)的行时遇到麻烦
(向下滚动,直到你到达"我的问题是在下面的查询"下面是我的代码:
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 = "";
}
}
图像包含我得到的结果
GRIDVIEW结果我的问题是为什么我的ValidTable仍然是空的?我的查询语句有问题吗?
你的代码看起来有效,我怀疑你的选择语句可能有问题。创建一个简单的字符串变量,并将其设置为如下命令:
string validExcelCommandText = "SELECT * FROM [" + getExcelSheetName + @"] where ((Email like '%@%.com') and (Name is not null) and (Mobile like '^[[:digit:]]+$'))";
在这行上放置一个断点并运行您的应用程序,取validExcelCommandText
的值并在SQL Server Management Studio中运行查询。如果它不返回任何结果,然后修复你的SQL查询,如果它这样做,然后确保没有逻辑,隐藏你的GridView
控件在页面上或检查在代码后面的绑定。
我把我的查询语句重写为下面的语句,它工作了
string ValidExcelCommandText = @"SELECT * FROM [" + getExcelSheetName + @"] where ((Email like '%@_%.com') and (Name is not null) and not ( ISNUMERIC(Mobile) = 0))";