[thelist] Converting VBScript from Access to SQL

Eric Engelmann eric.engelmann at geonetric.com
Tue Nov 19 23:14:01 CST 2002


Ah, gotcha, thanks.

Two possibilities, with 2 minor comments at the end:

1. I think Access upsizing/import does not set the primary key for you, if i
recall correctly. Make SURE you have the ID field set as primary key in SQL
Server, in addition to Identity. I tried your code and sat there staring at
it trying to figure out why mine worked and yours didn't, and the only way I
could break it was to remove the Primary Key designator from the field in
SQL Server. I guess it kind of makes sense, something about not being able
to hold onto the bookmark on the row you're on without a primary key
specified.

I didn't know that, guess you learn something new every day. :-)

2. If you can't do that, then the brute force method is to requery and move
to the last record:

RS.Update
RS.Requery
RS.MoveLast
idOfAddedRecord = RS("id").Value
Response.Write "item" & idOfAddedRecord

Worked for my test example, even without the PK set in SQL. This has the
potential for a concurrency problem, though, if two people submitted a
millisecond apart and they crossed paths.

Give that a shot.

3. Personal preference #1:

Set db = Server.CreateObject("adodb.connection")
db.Open "DSN=membership;UID=XXXX;PWD=XXXX;DATABASE=membership"
Set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open "agent_tbl", Conn, adOpenKeySet, adLockOptimistic, adCmdTable

Doing these in this sequence is a bit confusing, because I would have
expected at first glance the the RS will utilize the db Connection just by
the way its laid out, but it doesn't.

4. Personal preference #2:

Create an application variable for all of your connstrings, so you can do:

db.Open Application("myDSN")

and in your global.asa:

Application("myDSN") =
"DSN=membership;UID=XXXX;PWD=XXXX;DATABASE=membership"

Its a god-fearing rule around here to never have anything server specific
(paths, connstrings, etc) in any page of code other than global.asa (or in
some cases, a single global include, usually named global.asp)

When you need to move the site, change the DSN name, or anything else, it'll
make your life much, much easier, you change it once and BLAMO its all set
throughout the site.

Good luck, hope that fixes it.

- Eric





More information about the thelist mailing list