locked
IsNull Column RRS feed

  • Question

  • User-1499457942 posted

    hi

      I want to check For IsNull in Criteria Column. If it is Null then it should be replaced by ''

    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT Category,Name,Item,Criteria,,Name+Criteria as Id FROM [" + sheet1 + "]", cnnExcel))

    SqlBulkCopy objbulkInsert = new SqlBulkCopy(con);

    objbulkInsert.DestinationTableName = "[Test]";
    objbulkInsert.WriteToServer(dtExcelData);

    Thanks

    Monday, December 17, 2018 7:17 AM

Answers

  • User-893317190 posted

    Hi JagjitSingh,

    Since you are using ado to retrieve data from excel, syntax of your query may differ from  sql query.

    I suggest you could operate on the datatable.

    Below is my code.

     DataTable dtExcelData = new DataTable();
    
    
                   
                    dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                new DataColumn("Name", typeof(string)),
                new DataColumn("Salary",typeof(decimal)) });
                   
                    dtExcelData.Columns["Id"].AutoIncrement = true;
                    dtExcelData.Columns["Id"].AutoIncrementSeed = 1;
                    dtExcelData.Columns["Id"].AutoIncrementStep = 1;
    
                    //connection.Close();
                    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT    Name,salary  FROM [" + sheet2 + "]", connection))
                    {
                        oda.Fill(dtExcelData);
    
                        foreach (DataRow item in dtExcelData.Rows)
                        {
                            if(item["Name"] == DBNull.Value)
                            {
                                item["Name"] = ""; // to column of type string
                            }
                            if(item["Salary"]== DBNull.Value)
                            {
                                item["Salary"] = 0;// to column of type int
                            }
                            
                        }
                       
    
                    }
                    string consString = ConfigurationManager.ConnectionStrings["UserItemDb3"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(consString))
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                            
                            sqlBulkCopy.DestinationTableName = "dbo.salary";
                             table
                            sqlBulkCopy.ColumnMappings.Add(0, 0);
    
                            sqlBulkCopy.ColumnMappings.Add(1, 1);
                            sqlBulkCopy.ColumnMappings.Add(2, 2);
                           
                            con.Open();
                           
                            sqlBulkCopy.WriteToServer(dtExcelData);
                            con.Close();
                        }
                    }

    My excel

    Id		Salary		name
    1				
    3				abc
    5		1500		abd
    6		1200		ouuo
    7		1000		luv
    

    The result.

    id	name	salary
    1		0
    2	abc	0
    3	abd	1500
    4	ouuo	1200
    5	luv	1000

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 18, 2018 2:58 AM

All replies

  • User1080785583 posted

    Here is an example site to do such, https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-2017.

    ISNULL(Criteria, '')

    Monday, December 17, 2018 6:29 PM
  • User-893317190 posted

    Hi JagjitSingh,

    Since you are using ado to retrieve data from excel, syntax of your query may differ from  sql query.

    I suggest you could operate on the datatable.

    Below is my code.

     DataTable dtExcelData = new DataTable();
    
    
                   
                    dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                new DataColumn("Name", typeof(string)),
                new DataColumn("Salary",typeof(decimal)) });
                   
                    dtExcelData.Columns["Id"].AutoIncrement = true;
                    dtExcelData.Columns["Id"].AutoIncrementSeed = 1;
                    dtExcelData.Columns["Id"].AutoIncrementStep = 1;
    
                    //connection.Close();
                    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT    Name,salary  FROM [" + sheet2 + "]", connection))
                    {
                        oda.Fill(dtExcelData);
    
                        foreach (DataRow item in dtExcelData.Rows)
                        {
                            if(item["Name"] == DBNull.Value)
                            {
                                item["Name"] = ""; // to column of type string
                            }
                            if(item["Salary"]== DBNull.Value)
                            {
                                item["Salary"] = 0;// to column of type int
                            }
                            
                        }
                       
    
                    }
                    string consString = ConfigurationManager.ConnectionStrings["UserItemDb3"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(consString))
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                            
                            sqlBulkCopy.DestinationTableName = "dbo.salary";
                             table
                            sqlBulkCopy.ColumnMappings.Add(0, 0);
    
                            sqlBulkCopy.ColumnMappings.Add(1, 1);
                            sqlBulkCopy.ColumnMappings.Add(2, 2);
                           
                            con.Open();
                           
                            sqlBulkCopy.WriteToServer(dtExcelData);
                            con.Close();
                        }
                    }

    My excel

    Id		Salary		name
    1				
    3				abc
    5		1500		abd
    6		1200		ouuo
    7		1000		luv
    

    The result.

    id	name	salary
    1		0
    2	abc	0
    3	abd	1500
    4	ouuo	1200
    5	luv	1000

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 18, 2018 2:58 AM