用只包含数值的行填充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仍然是空的?我的查询语句有问题吗?

用只包含数值的行填充Gridview的查询没有给我想要的结果

你的代码看起来有效,我怀疑你的选择语句可能有问题。创建一个简单的字符串变量,并将其设置为如下命令:

   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))";