开发者

Insert Query in C# using Parameters

开发者 https://www.devze.com 2023-02-05 00:06 出处:网络
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.

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

Insert Query in C# using Parameters

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}


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.


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消