Adding to a table, replace value

[apex82]

I am adding a new record to a table name System.

At the moment I am selecting a value from a dropdown list (populated by the BusinessID value from a database)and adding a value to a text box.

BusinessID is linked to the System table, which is the table that I’m adding to.

This works perfectly for me:

<%

str_SQL = "INSERT INTO System ([System Code], [BusinessID])" 
str_SQL = str_SQL & " VALUES "
str_SQL =str_SQL & "('" & Request.Form("SystemCode") & "', '" & Request.Form("Business") & "')"

Set obj_RS1 = obj_CN.Execute(str_sql, adBoolean)

%>

What I would like to do is rather than display the meaningless BusinessID display the [Business Code].

Does anyone know how I change this?

Thank you.

 

[RiverGuy]

Add a

SELECT SCOPE_IDENTITY() to the end of your SQL code.

 

[apex82]

Do you mean like so?

str_SQL = "INSERT INTO System ([System Code], [BusinessID]) SELECT SCOPE_IDENTITY()";

 

[RiverGuy]

No, this is what I meant:

str_SQL = "INSERT INTO System ([System Code], [BusinessID])"
str_SQL = str_SQL & " VALUES "
str_SQL =str_SQL & "('" & Request.Form("SystemCode") & "', '" & Request.Form("Business") & "')[b]; SELECT SCOPE_IDENTITY()[/b]"

But I think I was confused. I don't think you have an Identity column. If your problem is in displaying a different value from your recordset, then your question might be better suited for the VBScript forum or whatever language you are using for your web page.

 

[apex82]

[Business Code] is a column from the dbo.Business table of which BusinessID is the primary key.

Is it not possible to pull through the [BusinessID] using the [Business Code] as part of the code I posted in the initial thread?