How to get the record ID after insert?

[UGrad]

Hi,
I am new in ASP.NET.
Is there a way to get the identity ID from a SQL table after insert a record?
I have those 3 lines of code I am using to insert user into a table, and this table has a column called ID as its identity column.

Dim comm As New Odbc.OdbcCommand("insert into tbl_User (name) values ('User1')", conn)
comm.CommandType = CommandType.Text
comm.ExecuteNonQuery()

 

[jbenson001]

You need to create a stored procedure and specify an output parameter. in your SP, use @@identity.

EX;
Insert Into <table>

@var = @@Identity

return @var


Then in your .NET code, you can reference that ouput parameter from code(parameters collection)

 

[UGrad]

Will this work if I change my query string to this?

"insert into tbl_User (name) values ('User1');select @@identity;

 

[ca8msm]

I think you can get it to work like that but it's not the best way to go.

If you want to know if it does work though you could always try it...

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[adamroof]

   	Sub faxSave(sender As Object, e As EventArgs)
    	Dim objConn As New SqlConnection(ConnectionString)

    	Dim strSQL As String = "INSERT INTO tblFax Values (@fclient, @ft, @ftn, @fd, @fr, @fp, @fcc, @ftype, @fdetail, @ffn);SELECT @@Identity as 'inserted_Key'"
		Dim insertedKey as Int32
		Dim objComm As New SqlCommand(strSQL)
			objComm.Connection = objConn
			objComm.Parameters.Add("@fdetail", SqlDbType.Ntext).Value = txtFaxDetail.Text
			objComm.Parameters.Add("@ft", SqlDbType.Varchar, 100).Value = checkNull(txtFaxTo.Text)
			objComm.Parameters.Add("@ftn", SqlDbType.Varchar, 200).Value = checkNull(txtFaxNum.Text)
			objComm.Parameters.Add("@ffn", SqlDbType.Varchar, 50).Value = checkNull(txtFromNum.Text)
			objComm.Parameters.Add("@fd", SqlDbType.smalldatetime, 4).Value = checkNull(txtFaxDate.Text)
			objComm.Parameters.Add("@fr", SqlDbType.VarChar, 500).Value = checkNull(txtFaxRE.Text)
			objComm.Parameters.Add("@fp", SqlDbType.Int, 4).Value = checkNull(txtFaxPages.Text)
			objComm.Parameters.Add("@fcc", SqlDbType.Varchar, 500).Value = checkNull(txtFaxCC.Text)
			objComm.Parameters.Add("@ftype", SqlDbType.Int, 4).Value = rbFaxType.SelectedValue
			objComm.Parameters.Add("@fclient", SqlDbType.Int, 4).Value = ddlFaxFrom.SelectedItem.Value
		Try
    		objConn.Open()
    		insertedKey = Ctype(objComm.ExecuteScalar(), int32)
    	Catch ex As Exception
			Dim subStr As String = ex.ToString()
			Dim count As Integer = (subStr.LastIndexOf("#")) - (subStr.IndexOf("#") + 1)
			Dim int1 as String = subStr.SubString(subStr.IndexOf("#") + 1, count)
            message.Text = count & " : " & int1
            Response.Write("<script language='javascript'>alert('" & int1 & "')</script>")
            Exit Sub    		
		Finally
    		objComm.Connection.Close()
		End Try
		BindFaxHist(ddlFaxFrom.SelectedItem.Value)
		print(insertedKey)
   	End Sub

 

[UGrad]

ExecuteScalar?
Cool.. That's a new term for me.
I will do some research on in.

Thanks for all the helps.