Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trouble with insert query using ExecuteScalar()

Posted on 2013-05-29
9
Medium Priority
?
535 Views
Last Modified: 2013-06-05
protected void lbtnSubmit_Click(object sender, EventArgs e)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AssetMgmtConnectionString"].ToString()))
            {
                // Create a command object.
                SqlCommand cmd = new SqlCommand();
                SqlCommand cmd1 = new SqlCommand();


                // Assign the connection to the command.
                cmd.Connection = conn;
                cmd1.Connection = conn;


                // Set the command text
                // SQL statement or the name of the stored procedure  
                cmd.CommandText = "INSERT INTO AssetMgmt.dbo.Tracking(UserKey, AssetKey, CarrierID, StatusID, ReasonID, TicketID, Notes, TrackingNum, ShipID) values ('@UserKey', '@AssetKey', '@CarrierID', '@StatusID', '@ReasonID', '@TicketID', 'Notes', '@TrackingNum', '@ShipID')";
                cmd1.CommandText = "INSERT INTO AssetMgmt.dbo.SYS_ShipLog(UserKey, AssetKey, Date ) values ('@UserKey', '@AssetKey', '@Date'); SELECT Scope_Identity();";


                // Set the command type
                // CommandType.Text for ordinary SQL statements;  
                // CommandType.StoredProcedure for stored procedures.
                cmd.CommandType = CommandType.Text;
                cmd1.CommandType = CommandType.Text;




                // Append the parameters.
                cmd.Parameters.Add("@TicketID", SqlDbType.Int).Value = txtTicket.Text;
                cmd.Parameters.Add("@Notes", SqlDbType.NVarChar, 255).Value = txtNotes.Text;
                cmd.Parameters.Add("@UserKey", SqlDbType.Int).Value = ddlUser.SelectedValue;
                cmd.Parameters.Add("@TrackingNum", SqlDbType.NVarChar, 50).Value = txtTrackingNum.Text;
                cmd.Parameters.Add("@CarrierID", SqlDbType.Int).Value = ddlCarrier.SelectedValue;
                cmd.Parameters.Add("@ReasonID", SqlDbType.Int).Value = ddlReason.SelectedValue;
                cmd.Parameters.Add("@StatusID", SqlDbType.Int).Value = ddlStatus.SelectedValue;
                cmd.Parameters.Add("@AssetKey", SqlDbType.Int).Value = ddlAsset.SelectedValue;

                cmd1.Parameters.Add("@UserKey", SqlDbType.Int).Value = ddlUser.SelectedValue;
                cmd1.Parameters.Add("@AssetKey", SqlDbType.NVarChar, 255).Value = ddlAsset.SelectedValue;
                cmd1.Parameters.Add("@Date", SqlDbType.Int).Value = txtDateShip.Text;

                // Open the connection.
                conn.Open();
                int newShipID = Convert.ToInt32(cmd1.ExecuteScalar());
                //string newShipID = cmd1.ExecuteScalar().ToString();
                //int shipID = Convert.ToInt32(newShipID);

                cmd.Parameters.Add("@ShipID", SqlDbType.Int).Value = newShipID;

                // Execute the command.
                cmd.ExecuteNonQuery();
            }


            // Rebind the GridView control to show inserted data.
            BindGridView();


            // Empty the TextBox controls.
            txtTicket.Text = "";
            txtNotes.Text = "";
            txtTrackingNum.Text = "";
            txtDateShip.Text = "";


            // Show the Add button and hiding the Add panel.
            lbtnAdd.Visible = true;
            pnlAdd.Visible = false;
        }
0
Comment
Question by:BigDeer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 3

Author Comment

by:BigDeer
ID: 39206657
Didn't mean to post that soon...

I'm trying to update 2 tables at the same time and grab the PK ID (ShipID) at the right time to append it to the second insert. I have a different type of insert that's less secure that this works for:

SqlCommand shipCmd = new SqlCommand("INSERT INTO AssetMgmt.dbo.SYS_ShipLog(UserKey, AssetKey, Date ) values ('" + ddlUserValue + "', '" + ddlAssetValue + "', '" + txtDateShip.Text + "'); SELECT Scope_Identity();", assetsCon);
assetsCon.Open();
string newShipID = shipCmd.ExecuteScalar().ToString();

Open in new window


Any help appreciated...you can see some of the commented out items I've tried. I keep getting an unable to convert string to int32 error.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39206905
Hi,

What is the value returned?

Giannis
0
 
LVL 8

Expert Comment

by:cubaman_24
ID: 39206980
Hello:
You can not issue an insert statement and call execute scalar in the same call.

You have two choices:
Create an stored procedure an inside it, do the insert an return the new id.
Execute the insert and then query for the new id in different commands.

Best regards.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 3

Author Comment

by:BigDeer
ID: 39207197
No value is returned, it just throws an error complaining about not being able to convert string newShipID = cmd1.ExecuteScalar().ToString(); from a string to an int32.

@cubaman_24 the following command works perfectly for what I'm trying to do:

SqlCommand shipCmd = new SqlCommand("INSERT INTO AssetMgmt.dbo.SYS_ShipLog(UserKey, AssetKey, Date ) values ('" + ddlUserValue + "', '" + ddlAssetValue + "', '" + txtDateShip.Text + "'); SELECT Scope_Identity();", assetsCon);
assetsCon.Open();
string newShipID = shipCmd.ExecuteScalar().ToString();

Open in new window


and the newShipID works as the variable I need for the command that follows it, which is another insert using the variable. If there's a better way to do it, I'm definitely not stuck using this but I need something that's not vulnerable to injection.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39210467
Forgive me if I am wrong, but I suspect you will find that the ExecuteScalar method requires a resultset.  An INSERT statement does not have one.

I would suggest you use the ExecuteNonQuery method instead.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39210473
Ah never mind, I see you have a SELECT statement after all.  Then if you want to use the ExecuteScalar method, then you will have to make the following change:
cmd1.CommandText = "SET NOCOUNT ON; INSERT INTO AssetMgmt.dbo.SYS_ShipLog(UserKey, AssetKey, Date ) values ('@UserKey', '@AssetKey', '@Date'); SELECT Scope_Identity();";
0
 
LVL 3

Accepted Solution

by:
BigDeer earned 0 total points
ID: 39210889
I figured it out...it's because I have the items surrounded by quotes in the value section of the insert causing the data to read as a string instead of an integer. Removed the quotes and it works perfectly.

Thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39212237
Removed the quotes and it works perfectly.
I am afraid that does not make any sense.
0
 
LVL 3

Author Closing Comment

by:BigDeer
ID: 39221479
Figured this one out on my own
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question