Insert Query in C# using Parameters

Question

Am trying to insert several columns into my database using the following insert query from C# but it throws the exception somehow somewhere and my guess is there are no values provided for insertion. i just want to confirm that and find out how i can fix the insert statement. i have a picture below that displays what is passed into the parameters at runtime. i used a break point to get this info.

need your expertise at this one...thanks

if (Page.IsValid)
        {
            DateTime exhibitDate = DateTime.Now;
            int caseid = Convert.ToInt32(CaseIDDropDownList.SelectedItem.Text);
            string exhibittype = exhibitTypeTextBox.Text.ToString();
            string storedloc = storedLocationTextBox.Text.ToString();
            string offid = DropDownList1.SelectedItem.Text.ToString();
            Stream imgStream = exhibitImageFileUpload.PostedFile.InputStream;
            int imgLen = exhibitImageFileUpload.PostedFile.ContentLength;                
            byte[] imgBinaryData = new byte[imgLen];
            int n = imgStream.Read(imgBinaryData,0,imgLen);
            try
            {
                SqlConnection connections = new SqlConnection(strConn);
                SqlCommand command = new SqlCommand("INSERT INTO Exhibits (CaseID, ExhibitType, ExhibitImage, DateReceived, StoredLocation, InvestigationStatus, OfficerID, SuspectID, InvestigatorID, ManagerID, AdminID ) VALUES (@CaseID, @ExhibitType, @ExhibitImage, @DateReceived, @StoredLocation, @InvestigationStatus, @OfficerID, @SuspectID, @InvestigatorID, @ManagerID, @AdminID)", connections);

                SqlParameter param0 = new SqlParameter("@CaseID", SqlDbType.Int);
                param0.Value = caseid;
                command.Parameters.Add(param0);

                SqlParameter param1 = new SqlParameter("@ExhibitType", SqlDbType.NText);
                param1.Value = exhibittype;
                command.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter("@ExhibitImage", SqlDbType.Image);
                param2.Value = imgBinaryData;
                command.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter("@DateReceived", SqlDbType.SmallDateTime);
                param3.Value = exhibitDate;
                command.Parameters.Add(param3);

                SqlParameter param4 = new SqlParameter("@StoredLocation", SqlDbType.NText);
                param4.Value = storedloc;
                command.Parameters.Add(param4);

                SqlParameter param5 = new SqlParameter("@InvestigationStatus", SqlDbType.VarChar, 50);
                param5.Value = "";
                command.Parameters.Add(param5);

                SqlParameter param6 = new SqlParameter("@OfficerID", SqlDbType.NChar, 10);
                param6.Value = offid;
                command.Parameters.Add(param6);

                SqlParameter param7 = new SqlParameter("@SuspectID", SqlDbType.NChar, 10);
                param7.Value = null;
                command.Parameters.Add(param7);

                SqlParameter param8 = new SqlParameter("@InvestigatorID", SqlDbType.NChar, 10);
                param8.Value = null;
                command.Parameters.Add(param8);

                SqlParameter param9 = new SqlParameter("@ManagerID", SqlDbType.NChar, 10);
                param9.Value = null;
                command.Parameters.Add(param9);

                SqlParameter param10 = new SqlParameter("@AdminID", SqlDbType.NChar, 10);
                param10.Value = adminID;
                command.Parameters.Add(param10);

                connections.Open();
                int numRowsAffected = command.ExecuteNonQuery();
                connections.Close();

                if (numRowsAffected != 0)
                {
                    Response.Write("<BR>Rows Inserted successfully");
                    CaseIDDropDownList.ClearSelection();
                    exhibitTypeTextBox.Text = null;
                    storedLocationTextBox.Text = null;
                    DropDownList1.ClearSelection();
                }
                else
                {
                    Response.Write("<BR>An error occurred uploading the image");
                }
            }
            catch (Exception ex)
            {
                string script = "<script>alert('" + ex.Message + "');</script>";
            }

the exception is as follows

• $exception {"The parameterized query '(@CaseID int,@ExhibitType ntext,@ExhibitImage image,@DateReceive' expects the parameter '@SuspectID', which was not supplied."} System.Exception {System.Data.SqlClient.SqlException}

Answer 1

If you want to pass in NULL for your database / parameter type, you need to use DBNull.Value like this:

SqlParameter param9 = new SqlParameter("@ManagerID", SqlDbType.NChar, 10);
param9.Value = DBNull.Value;
command.Parameters.Add(param9);

Do this wherever you're setting something to null right now, and I'm pretty sure it'll work just fine. Everything is looking okay.

Answer 2

You can do this much easier, try it as such:

if (Page.IsValid)
        {
            DateTime exhibitDate = DateTime.Now;
            int caseid = Convert.ToInt32(CaseIDDropDownList.SelectedItem.Text);
            string exhibittype = exhibitTypeTextBox.Text.ToString();
            string storedloc = storedLocationTextBox.Text.ToString();
            string offid = DropDownList1.SelectedItem.Text.ToString();
            Stream imgStream = exhibitImageFileUpload.PostedFile.InputStream;
            int imgLen = exhibitImageFileUpload.PostedFile.ContentLength;                
            byte[] imgBinaryData = new byte[imgLen];
            int n = imgStream.Read(imgBinaryData,0,imgLen);
            try
            {
                SqlConnection connections = new SqlConnection(strConn);
                SqlCommand command = new SqlCommand("INSERT INTO Exhibits (CaseID, ExhibitType, ExhibitImage, DateReceived, StoredLocation, InvestigationStatus, OfficerID, SuspectID, InvestigatorID, ManagerID, AdminID ) VALUES (@CaseID, @ExhibitType, @ExhibitImage, @DateReceived, @StoredLocation, @InvestigationStatus, @OfficerID, @SuspectID, @InvestigatorID, @ManagerID, @AdminID)", connections);
                command.Parameters.AddWithValue("@CaseID", caseid);
                //and so on for your 10 parameters




                connections.Open();
                int numRowsAffected = command.ExecuteNonQuery();
                connections.Close();

                if (numRowsAffected != 0)
                {
                    Response.Write("<BR>Rows Inserted successfully");
                    CaseIDDropDownList.ClearSelection();
                    exhibitTypeTextBox.Text = null;
                    storedLocationTextBox.Text = null;
                    DropDownList1.ClearSelection();
                }
                else
                {
                    Response.Write("<BR>An error occurred uploading the image");
                }
            }
            catch (Exception ex)
            {
                string script = "<script>alert('" + ex.Message + "');</script>";


               }
}

I'm not sure if this will actually fix what is happening without knowing the exact exception. If you could provide the actual exception that would help a lot.