Retrieve Identity Column Value After Inserting Record 1

[apex82]

I have the following code, which inserts into a database a record with a primary key named System_ID.

For i = 0 to Ubound(val)
if len(val(i))=2 then
str_SQL = "INSERT INTO System ([BusinessID], [System Code], [System Description])"
str_SQL = str_SQL & " VALUES "
str_SQL = str_SQL & "('" & first_part & "', '" & val(i) & "', '"  & "System Code " & val(i) & " for Business " & second_part  & "')"
else
Response.Redirect("index.asp?a=Only two character inputs can be accepted&p=" & input &"")
end if

What I’d like to do is after it has inserted take the value for the System_ID and insert a record in a new table so something like this?:

str_SQL = "INSERT INTO System_Text ([SystemID])" 
str_SQL = str_SQL & " VALUES "
str_SQL = str_SQL & "('" & NewID &  "')"

Can anyone help me?

Thanks.

 

[gmmastros]

What type of database are you using? SQL Server, Access, Oracle, MySQL, etc....

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

If this is a SQL Server database (as suggested in other questions you have asked), I would recommend you create a stored procedure in your database. This will allow you to insert rows in to both tables within the stored procedure. Your ASP code would simply call the one stored procedure to do both tasks.

Open SQL Server Management Studio, open a query and select your database. Then copy/paste the code below.

Create Procedure InsertSystem
	@BusinessId [!]VarChar(20)[/!],
	@SystemCode [!]VarChar(20)[/!],
	@SystemDescription [!]VarChar(20)[/!]
As 
SET NOCOUNT ON

Declare @SystemId Int

Insert into [System](BusinessId, [System Code],[System Description])
Value (@BusinessId, @SystemCode, @SystemDescription)

Select @SystemID = Scope_Identity()

Insert Into System_Text(SystemId) Values(@SystemId)

Select @SystemId As SystemId

The part highlighted in red will have to change to match the data type of your columns.

After you make that change, press F5 to run the code. This will create a stored procedure in your database. Once created, you can re-use it as often as you'd like.

Then change your ASP code to call the stored procedure. Something like this....

For i = 0 to Ubound(val)
  if len(val(i))=2 then
    str_SQL = "InsertSystem '" & first_part & "', '" & val(i) & "', '"  & "System Code " & val(i) & " for Business " & second_part  & "'"
  else
    Response.Redirect("index.asp?a=Only two character inputs can be accepted&p=" & input &"")
  end if

The real trick here is Scope_Identity(). This will return the value assigned by the database for the identity column.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[apex82]

Works perfect! Massive thanks George!

 

[apex82]

I didn't test this completely.

It works perfect when adding only one record. When I try to add more than one it gives the error below:

Microsoft OLE DB Provider for SQL Server
-1
Incorrect syntax near 'InsertScheme'.

Can I change the procedure to accomodate adding more than one?

Thanks.

 

[apex82]

I didn't test this completely.

It works perfect when adding only one record. When I try to add more than one it gives the error below:

Can I change the procedure to accomodate adding more than one?

Sorry. Please ignore this. It is working perfect!