?
Solved

Trouble with insert query using ExecuteScalar()

Posted on 2013-05-29
9
Medium Priority
?
514 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 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