[thelist] Converting VBScript from Access to SQL

Wade Armstrong wade_lists at runstrong.com
Tue Nov 19 19:31:01 CST 2002


on 11/19/02 4:49 PM, Jeremy Weiss at jweiss03 at comcast.net wrote:

> I'm switching the database powering my site from Access to SQL Server and
> I'm having a problem with some of the code.
> 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")

> Now, I've played around with "SELECT @@IDENTITY" trying to get that to work,
> but haven't managed to get it.

SQL Server doesn't inherently require "SELECT @@IDENTITY" - it's only a new,
more efficient way to do things. Thanks to ADO, all you have to do to make
the same code work with SQL Server is change the connection string to to:
(should all be on one line)
Driver={SQL
Server};Server=XX.XX.XX.XX;Database=database;UID=username;PWD=password

Then your code should work as it is.

Once you've got that working, come back and we can go over doing inserts and
updates the quick way, with straight SQL, instead of using Recordsets.

Wade





More information about the thelist mailing list