Solved

Trouble with insert query using ExecuteScalar()

Posted on 2013-05-29
9
497 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

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.

Question has a verified solution.

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

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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
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.

806 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