[thelist] Converting VBScript from Access to SQL

Jeremy Weiss jweiss03 at comcast.net
Tue Nov 19 18:51:01 CST 2002


I'm switching the database powering my site from Access to SQL Server and
I'm having a problem with some of the code.  What I'm trying to do is add
records into a table, then get the id of the record added and it along with
some other values to a second table.  I've read the articles are 4guys for
this and can't seem to get it to work.  Below is some of the code... a bit
long though.

Here's the code that worked with Access (minus a lot of the values):
<snip>
DBPath = Server.MapPath("../Database/agentszips2.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";"
Set RS = Server.CreateObject("ADODB.RecordSet")

RS.Open "agent_tbl", Conn, adOpenKeySet, adLockOptimistic, adCmdTable
RS.AddNew
RS("agent") = agent
RS("phone") = officephone
RS.Update
idOfAddedRecord = RS("id")

RS.Close
Set RS = Nothing
Set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open "zip_tbl", Conn, adOpenKeySet, adLockOptimistic, adCmdTable

zipcode_array = Split(zip,",")

For Each V in zipcode_array
   If V = " " Then Exit For
   RS.AddNew
   RS("zipcode") = LTrim(V)
   RS("agent_tbl_id") = idOfAddedRecord
   RS.Update
Next

RS.Close
conn.close
</snip>

Now, I've played around with "SELECT @@IDENTITY" trying to get that to work,
but haven't managed to get it.  The frustrating part for me is that the code
isn't throwing any errors.  It processes just fine, but when I check the
database the agent_tbl_id field is still blank.  Any help would be greatly
appreciated.

Thanks,
Jeremy






More information about the thelist mailing list