ASP.Net \ C# SQL Parameters DBNull-CodePudding

Am struggling to use SQL parameters for C# \ ASP.Net, not used C# with ASP.Net much so quite new to it, I have a web page with a dropdown box (drpdSerType) and a text box (txtRNumSer) and a button, the button code is below...

If I don't use the parameters as seen the commented code then it works fine...

            try
            {
            SqlConnection con = new SqlConnection();
            SqlDataAdapter sda = new SqlDataAdapter();
            DataTable dt = new DataTable();
            con.ConnectionString = "Server=server1;Database=db1;Integrated Security=True";
            sda.SelectCommand.Parameters.AddWithValue("@I", drpdSerType.SelectedValue.ToString());
            sda.SelectCommand.Parameters.AddWithValue("@S", txtRNumSer.Text);
            sda = new SqlDataAdapter("SELECT Number, Type, Qty, Date, num, ReceiptID FROM IssuedRN WHERE @I = @S", con);
            //This line works but no secure
            //sda = new SqlDataAdapter("SELECT Number, Type, Qty, Date, num, ReceiptID FROM IssuedRN WHERE "   drpdSerType.SelectedValue.ToString()   " = '"   txtRNumSer.Text   "'", con);
            sda.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();

I get the following error; System.NullReferenceException: 'Object reference not set to an instance of an object.' Related to line

sda.SelectCommand.Parameters.AddWithValue("@I", drpdSerType.SelectedValue.ToString());

I have also followed the guidance here; https://csharp-station.com/Tutorial/AdoDotNet/Lesson06

Thanks for any help on this, I have tried many different ways to use parameters but all seem to do this or give the above error.

Thanks Karl

CodePudding user response：

you have a bug

"SELECT Number, Type, Qty, Date, num, ReceiptID FROM IssuedRN WHERE @I = @S"

WHERE @I = @S" what is this?

and fix the rest

con.ConnectionString = "Server=server1;Database=db1;Integrated Security=True";

var sqlString="....fixed sql";
var cmd = new SqlCommand(sqlSting, con);

var value= drpdSerType.SelectedValue;
if(value == null)
        cmd.Parameters.AddWithValue("@I", DBNull.Value);
  else
        cmd.Parameters.AddWithValue("@I", value.ToString());

cmd.Parameters.AddWithValue("@S", txtRNumSer.Text);

 sda = new SqlDataAdapter();
sda.SelectCommand=cmd;
 sda.Fill(dt);
 .....

CodePudding user response：

I would always strong datatype the parms

eg:

sda.SelectCommand.Parameters.Add("@I",SqlDbType.NVarChar).Value = drpdSerType.SelectedValue.ToString());

or if it a nummber, then use:

sda.SelectCommand.Parameters.Add("@I",SqlDbType.int).Value = drpdSerType.SelectedValue.ToString());

The main issue you have to deal with? Can the combo box be blank? In other words, when you load up teh grid, and set the actual value of the combo, you often have to translate null into a blank (empty string). And when sending back to database, you have to translate back from "" to null.

Worse yet? If the combo is data driven, then do you add a extra blank row to the combo to handle the above. Since if the list/data for the combo does not have a blank row choice then you can NOT shove into the combo a blank choice if it don't have one!! (but the database row driving the grid may well certainly have a null for that choice - so you have to ensure the combo drop allows this.