[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