c# fetching database into SQL Server datatable

Question

I have a problem with this code... I don't have the slightest clue for what is happening...

When I run this code in Visual Studio, I get an error saying:

System.InvalidCastException: 'Object cannot be cast from DBNull to other types.'

And here's the code:

conn.Open();

SqlCommand com = new SqlCommand("update lend set date_back=convert(datetime2, getdate(), 102) where client_name ='" + comboBox1.Text + "'", conn);

SqlDataAdapter da = new SqlDataAdapter("Declare @startdate smalldatetime declare @enddate smalldatetime set @startdate = (select date_lended from dbo.lend where client_name = '" + comboBox1.Text + "') set @enddate = (select date_back from dbo.lend where client_name = '" + comboBox1.Text + "') SELECT DATEDIFF(DAY, @startdate+2, @enddate)as timepassedd", conn);

DataTable dt = new DataTable();
da.Fill(dt);

foreach (DataRow DR in dt.Rows)
{
    int date;
    date = Convert.ToInt32(DR["timepassedd"]);

    if (date > 0)
    {
        com = new SqlCommand("DELETE lend WHERE client_name ='"+comboBox1.Text+"'" +
                             "UPDATE book_list set book_stock = book_stock  1 WHERE book_name ='" + comboBox1.Text + "'",conn);
        com.ExecuteNonQuery();

        MessageBox.Show("You Returned the book " + date + " Days Late!" +
                            "please pay the fee to the front desk");

        UserPanel u = new UserPanel();
        u.Show();

        this.Hide();
    }
    else if (date <= 0)
    {
        com = new SqlCommand("DELETE lend WHERE client_name ='" + comboBox1.Text + "'" +
                             "UPDATE book_list set book_stock = book_stock  1 WHERE book_name ='" + comboBox1.Text + "'", conn);
        com.ExecuteNonQuery();

        MessageBox.Show("You returned the book " + date + " Days Late!" +
                            "please pay the fee to the front desk");

        UserPanel u = new UserPanel();
        u.Show();

        this.Hide();
    }
}

conn.Close();

Thanks in advance

You have allowed nulls in your database table, it can't convert null values, don't allow null values or use a datareader and check each bit of data that could be null with 'IsDBNull' and if it isn't null display other a default value — Daniel Loudon, Dec 09 '17 at 09:02
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](https://xkcd.com/327/) — marc_s, Dec 09 '17 at 10:35

score 0 · Answer 1 · answered Dec 09 '17 at 09:53

Run code below the answer will be obvious :

                string sql1 = "update lend set date_back=convert(datetime2, getdate(), 102) where client_name ='" + comboBox1.Text + "'";
                Console.WriteLine(sql1);
                Console.ReadLine();
                SqlCommand com = new SqlCommand(sql1, conn);
                string sql2 = "Declare @startdate smalldatetime declare @enddate smalldatetime set @startdate = (select date_lended from dbo.lend where client_name = '" + comboBox1.Text + "') set @enddate = (select date_back from dbo.lend where client_name = '" + comboBox1.Text + "') SELECT DATEDIFF(DAY, @startdate+2, @enddate)as timepassedd"
                Console.ReadLine();
                SqlDataAdapter da = new SqlDataAdapter(sql2, conn);

Rickless · Accepted Answer · 2017-12-09T10:06:40.377

        conn.Open();
        SqlCommand com = new SqlCommand("update lend set date_back=convert(datetime2, getdate(), 102) where client_name ='" + comboBox1.Text + "'", conn);
        SqlDataAdapter da = new SqlDataAdapter("Declare @startdate smalldatetime declare @enddate smalldatetime set @startdate = (select date_lended from dbo.lend where client_name = '" + comboBox1.Text + "') set @enddate = (select date_back from dbo.lend where client_name = '" + comboBox1.Text + "') SELECT DATEDIFF(DAY, @startdate+2, @enddate)as timepassedd", conn);
        DataSet ds = new DataSet();
        da.Fill(ds);
        if(ds.Tables.Count == 1)
        {
            if(ds.Tables[0].Rows.Count > 0)
            {
                foreach(DataRow dr in ds.Tables[0].Rows)
                {
                    if(dr.ItemArray.Length > 0)
                    {
                        if(dr["timepassedd"] != DBNull.Value)
                        {
                            int date;
                            date = Convert.ToInt32(Dr["timepassedd"]);
                            if (date > 0)
                            {
                                com = new SqlCommand("delete lend where client_name ='" + comboBox1.Text + "'" +
                                    "UPDATE book_list set book_stock = book_stock  1 WHERE book_name ='" + comboBox1.Text + "'", conn);
                                com.ExecuteNonQuery();
                                MessageBox.Show("You Returned the book " + date + " Days Late!" +
                                    "please pay the fee to the front desk");
                                UserPanel u = new UserPanel();
                                u.Show();
                                this.Hide();
                            }
                            else if (date <= 0)
                            {
                                com = new SqlCommand("delete lend where client_name ='" + comboBox1.Text + "'" +
                                    "UPDATE book_list set book_stock = book_stock  1 WHERE book_name ='" + comboBox1.Text + "'", conn);
                                com.ExecuteNonQuery();
                                MessageBox.Show("You Returned the book " + date + " Days Late!" +
                                    "please pay the fee to the front desk");
                                UserPanel u = new UserPanel();
                                u.Show();
                                this.Hide();
                            }
                        }
                    }
                }
            }
        }
        conn.Close();

You should check for DBNull value, if the column is null-able in the database a DBNull.Value will be returned instead! So you should check this column for such a value before dealing with it
Also, you shouldn't accept values from the user input and directly injecting them into the SQL query! ADO.Net. Has something called Sql Parameters it can be found in the property Parameters in the SqlCommand class. You should use this property to add values from user as parameters to the query
For Example, client name can be added like this:

    com.Parameters.Add(new SqlParameter("client_name", comboBox1.Text));

Now you tell the Sql Command that the value is actually presented in the SqlParameters collection like this:

SqlCommand com = new SqlCommand("update lend set date_back=convert(datetime2, getdate(), 102) where client_name ='@client_name'", conn);

Take a look at this question Why do we always prefer using parameters in the SQL statements?
This is a MSDN reference for the Parameters property

Thanks!! its now running properly, thank you my friend :) – Chun Chun Maru Dec 11 '17 at 10:23 — Chun Chun Maru, Dec 11 '17 at 10:23

score 0 · Answer 3 · answered Dec 09 '17 at 09:55

set @startdate = (select date_lended from dbo.lend where client_name = '" + comboBox1.Text + "')
set @enddate = (select date_back from dbo.lend where client_name = '" + comboBox1.Text + "')

a) vulnerable to unexpected behaviour if comboBox1.Text was ever to contain ' characters

b) both will result in @startdate or @enddate being NULL if there are no records matching what's selected in the combobox, which then produces a NULL value for '@datepassedd', hence your error when trying to convert to an int.

c) you don't need to query the database twice, you can just do

select DATEDIFF(DAY, date_lended+2, date_back) as timepassedd FROM dbo.lend where client_name = @clientName

(@clientName can then be added a as parameter)

In this case, there will be no rows returned if no records match the name. BTW there was never any need to use a DataTable and iterate through the records, as your query as it is now only ever returns a single row.

c# fetching database into SQL Server datatable

3 Answers3