2 Replies - 28042 Views - Last Post: 29 August 2007 - 12:10 PM Rate Topic: -----

#1 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

How to obtain Scope_Identity without using a stored procedure

Post icon  Posted 29 August 2007 - 11:09 AM

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



Is This A Good Question/Topic? 0
  • +

Replies To: How to obtain Scope_Identity without using a stored procedure

#2 Jayman   User is offline

  • Student of Life
  • member icon

Reputation: 423
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: How to obtain Scope_Identity without using a stored procedure

Posted 29 August 2007 - 11:58 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: How to obtain Scope_Identity without using a stored procedure

Posted 29 August 2007 - 12:10 PM

Worked like a charm, thank you very much!
Was This Post Helpful? 1

Page 1 of 1