?
Solved

Trouble with insert query using ExecuteScalar()

Posted on 2013-05-29
9
Medium Priority
?
542 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
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 Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

864 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