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>";
            }

alt text

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}

What exception are you getting? The details of that message will help a lot!! — Jesse McCulloch
– Jesse McCulloch, Commented Jan 17, 2011 at 21:17
I have edited the question with the exception message at the buttom of the question. @Alex- my connection string is defined at the public partial class at the very top. so i dont have any problem with that. — Selase
– Selase, Commented Jan 17, 2011 at 21:25

marc_s · Accepted Answer · 2011-01-17 21:25:44Z

4

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.

answered Jan 17, 2011 at 21:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

msarchet · Accepted Answer · 2011-01-17 21:19:14Z

0

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.

answered Jan 17, 2011 at 21:19

msarchet

15.3k2 gold badges45 silver badges66 bronze badges

4 Comments

ChrisLively Over a year ago

Note that AddWithValue sends the parameter as a varchar and does no type checking. This might not be desired behavior for all parameters.

msarchet Over a year ago

@Chris Lively, I was not aware of this fact but it makes sense.

Selase Over a year ago

especially the image attribute i guess and i have some integer values as well... So what do you recommend Chris?

Selase Over a year ago

Throws the following exception when i tried it with AddWithValue "+ $exception {"The parameterized query '(@CaseID int,@ExhibitType nvarchar(9),@ExhibitImage varbinary(56' expects the parameter '@SuspectID', which was not supplied."} System.Exception {System.Data.SqlClient.SqlException} "

Collectives™ on Stack Overflow

Insert Query in C# using Parameters

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related