Check null and emptry string in a cell while retrieving the excel data

Question

Error : Additional information: Syntax error (missing operator) in query expression '[SKU N#] !='''.

 if (getFileExtension.ToLower() == ".xls")
                            {
                                conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + getFileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;;IMEX=1\""; ;
                            }
                            else if (getFileExtension.ToLower() == ".xlsx")
                            {
                                conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + getFileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;;IMEX=1\"";
                            }
                            OleDbConnection con = new OleDbConnection(conString);
                            if (con.State == ConnectionState.Closed) con.Open();
                            System.Data.DataTable ExcelSheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                            string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();

                            switch (UpdateProductViewModel.ProductDataType)
                            {
                                case 1:
                                    query = "SELECT * FROM [" + SpreadSheetName + "] WHERE [SKU N#] !='' AND [SKU N#] NOT LIKE '%P'";
                                    break;
                                case 2:
                                    query = "SELECT * FROM [" + SpreadSheetName + "] WHERE [SKU N#] !='' AND [SKU N#] LIKE '%P'";
                                    break;
                                case 3:
                                    query = "SELECT * FROM [" + SpreadSheetName + "] WHERE [SKU N#] !=''";
                                    break;
                            }

When I remove the WHERE [SKU N#] !='' this line from where it works fine. what I am doing wrong here ?

Try `WHERE NOT [SKU N#] = ''` instead. – Keyur PATEL Sep 19 '16 at 10:22 — Keyur PATEL, Sep 19 '16 at 10:22
@KeyurPATEL Is '!=' NOT a valid operator? – sachin Sep 19 '16 at 10:23 — sachin, Sep 19 '16 at 10:23
@KeyurPATEL thanks for answer. Its working. thanks a lot. – Mannan Bahelim Sep 19 '16 at 10:27 — Mannan Bahelim, Sep 19 '16 at 10:27

score 1 · Accepted Answer · answered Sep 19 '16 at 10:39

I think the issue is that your column has NULL values and not just blank strings.

No value can ever be equal to (or not equal to) NULL because NULL has no value.

WHERE NOT [SKU N#] = '' can work but is not probably the best way to check for NULL values.

I would suggest using WHERE [SKU N#] IS NOT NULL AND [SKU N#] != ''

Check null and emptry string in a cell while retrieving the excel data

1 Answers1