[webwired]

Hi guys, me again... I know with this question you're all probably thinking, use a store procedure,... but I haven't learned how to do that yet and time is running out... So, I was wondering if someone could please tell me how I can retrieve the Scope_Identity for the Identity field of my inline sql insert? I found one reference to it on my research, but it was sort of vague... It said...

Quote

You do not need to use SP in this case. All you need to do is to execute
SELECT SCOPE_IDENTITY() statement right after you inserted record. Main rule
is to execute it against same connection which application uses to insert
record, otherwise you will not get proper value

But since Insert is an ExecuteNonQuery and Select is not, I'm a bit confused on how to make this work with my code... Code to follow...

	Public Sub InsertMyData(ByVal myConnString As String, _
							ByVal customer_bus_name As String, _
							ByVal customer_address As String, _
							ByVal customer_city As String, _
							ByVal customer_state As String, _
							ByVal customer_zip As String, _
							ByVal customer_taxid As String, _
							ByVal customer_discount_rate As String)
		Dim myInsertQuery As String = "INSERT INTO customers VALUES( '" & customer_bus_name & "', '" & customer_address & "', '" & _
				customer_city & "', '" & customer_state & "', '" & customer_zip & "', '" & customer_taxid & "', '" & customer_discount_rate & "' )"
		Dim myConnection As New SqlConnection(myConnString)
		Dim myCommand As New SqlCommand(myInsertQuery, myConnection)
		Dim retvalue As Integer
		myConnection.Open()
		retvalue = myCommand.ExecuteNonQuery()
		myConnection.Close()
	End Sub

[Jayman]

Once you've executed the Insert Command or in this case the ExecuteNonQuery, change the CommandText property of the SQLCommand object to the new Query. Then execute the ExecuteScalar method to retrieve the value from the SELECT statement. You can resuse a command object as often as you like, just need to change the CommandText property and execute using the proper method.

		Dim myInsertQuery As String = "INSERT INTO customers VALUES( '" & customer_bus_name & "', '" & customer_address & "', '" & _
				customer_city & "', '" & customer_state & "', '" & customer_zip & "', '" & customer_taxid & "', '" & customer_discount_rate & "' )"
		Dim myConnection As New SqlConnection(myConnString)
		Dim myCommand As New SqlCommand(myInsertQuery, myConnection)
		Dim retvalue As Integer
		myConnection.Open()
		retvalue = myCommand.ExecuteNonQuery()

		myCommand.CommandText = "SELECT SCOPE_IDENTITY() statement"
		valueReturned = myCommand..ExecuteScalar()

		myConnection.Close()
	End Sub

[webwired]

Worked like a charm, thank you very much!