insert into sql and get scope identity

i need to get the scope identity once i insert a new row into sql. I'm not sure how to get the id of the newly created row. see my code below.
protected void loadCRSQL()
    {      
        string tempCRSON = lblDocN.Text.Remove(0, 2);
        int crSON = Convert.ToInt32(tempCRSON);
        string crBLN = txtbxBLog.Text.ToString();
        string crCC = txtbxCC.Text.ToString();    

        SqlConnection dbConnection = new SqlConnection(connectionString);
        
        string insertCRString = "Insert into cR ([crSON],[crBLN], ";
        insertCRString += "[crCC]) ";
        insertCRString += "VALUES (@crSON,@crBLN,@crCC) ";
	insertCRString += "select @id = SCOPE_IDENTITY()"; 
        //sql command
        SqlCommand dbCommand = new SqlCommand();
        dbCommand.CommandText = insertCRString;
        dbCommand.Connection = dbConnection;
        //
        dbCommand.Parameters.Add(new SqlParameter("@crSON", SqlDbType.Int));
        dbCommand.Parameters["@crSON"].Value = crSON;
        dbCommand.Parameters.Add(new SqlParameter("@crBLN", SqlDbType.VarChar, 25));
        dbCommand.Parameters["@crBLN"].Value = crBLN;
        dbCommand.Parameters.Add(new SqlParameter("@crCC", SqlDbType.VarChar, 25));
        dbCommand.Parameters["@crCC"].Value = crCC;
        
        dbCommand.CommandTimeout = 60;

        //
        try
        {
            dbConnection.Open();
            dbCommand.ExecuteNonQuery();
            prepCRDSQL(int iIdentity); //how do I get the scope_identity to pass it to another sql insert?
        }
        catch (SqlException ex)
        {
            writeError(ex.Message.ToString());
            sendErrorEmail(ex.Message.ToString());
            Response.Redirect("error.aspx");
        }
        finally
        {
            //close db conn
            dbConnection.Close();
        }
    }

Open in new window

vicominAsked:
Who is Participating?
 
HugoHiaslCommented:
if you ude ms sql you could query

select @@identity

with a command after the insert.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Alfred A.Commented:
0
 
Om PrakashCommented:


dbCommand.Parameters.Add(new SqlParameter("@crSON", SqlDbType.Int)); 
dbCommand.Parameters["@crSON"].Value = crSON; 
dbCommand.Parameters.Add(new SqlParameter("@crBLN", SqlDbType.VarChar, 25)); 
dbCommand.Parameters["@crBLN"].Value = crBLN; 
dbCommand.Parameters.Add(new SqlParameter("@crCC", SqlDbType.VarChar, 25)); 
dbCommand.Parameters["@crCC"].Value = crCC; 

SqlParameter id = new SqlParameter("@id", SqlDbType.Int);            
id.Direction = ParameterDirection.Output;            
dbCommand.Parameters.Add(id);            

dbConnection.Open(); 
dbCommand.ExecuteNonQuery(); 
id = (int)id.Value;

Open in new window

0
 
RoySharpCommented:
As you haven't said which db you use here's the answer for MySQL

select last_insert_id() from tableName;

This is per-connection so you get the id for your last insert statement.
0
 
vicominAuthor Commented:
using sql server 2005. so is the code below a good solution?
protected void loadCRSQL()
    {      
        string tempCRSON = lblDocN.Text.Remove(0, 2);
        int crSON = Convert.ToInt32(tempCRSON);
        string crBLN = txtbxBLog.Text.ToString();
        string crCC = txtbxCC.Text.ToString();    

            int iScopeIdentity = 0;

        SqlConnection dbConnection = new SqlConnection(connectionString);
        
        string insertCRString = "Insert into cR ([crSON],[crBLN], ";
        insertCRString += "[crCC]) ";
        insertCRString += "VALUES (@crSON,@crBLN,@crCC) ";
	insertCRString += "SET @crScopeId = SCOPE_IDENTITY()"; 
        //sql command
        SqlCommand dbCommand = new SqlCommand();
        dbCommand.CommandText = insertCRString;
        dbCommand.Connection = dbConnection;
        //
        dbCommand.Parameters.Add(new SqlParameter("@crSON", SqlDbType.Int));
        dbCommand.Parameters["@crSON"].Value = crSON;
        dbCommand.Parameters.Add(new SqlParameter("@crBLN", SqlDbType.VarChar, 25));
        dbCommand.Parameters["@crBLN"].Value = crBLN;
        dbCommand.Parameters.Add(new SqlParameter("@crCC", SqlDbType.VarChar, 25));
        dbCommand.Parameters["@crCC"].Value = crCC;

        SqlParameter scopeIdParameter = new SqlParameter("@crScopeId", SqlDbType.Int);
        scopeIdParameter.Direction = ParameterDirection.Output;
        dbCommand.Parameters.Add(scopeIdParameter);

        
        dbCommand.CommandTimeout = 60;

        //
        try
        {
            dbConnection.Open();
            dbCommand.ExecuteNonQuery();
	    iScopeIdentity = (int)scopeIdParameter.Value;
            prepCRDSQL(iScopeIdentity); //how do I get the scope_identity to pass it to another sql insert?
        }
        catch (SqlException ex)
        {
            writeError(ex.Message.ToString());
            sendErrorEmail(ex.Message.ToString());
            Response.Redirect("error.aspx");
        }
        finally
        {
            //close db conn
            dbConnection.Close();
        }
    }

Open in new window

0
 
Alfred A.Commented:
Hi,

Did you tried to run it?  That was my approach in one of my app before and it worked and I just followed the instructions in the very first link I provided.
0
 
Alfred A.Commented:
Hi,

Oh.  You assigned the points already.  I didn't noticed that.  Thank you. :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.