Solved

Trouble with insert query using ExecuteScalar()

Posted on 2013-05-29
9
496 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now