ASP database question (scripting) (1 Viewer)

[Vince Maskeeper]

Ok, I know enough with ASP/VBscript to be dangerous- but not enought to solve my own problems much of the time.

Back when I started, I wrote some DB interfacing scripts from a few "self help" books on the topic, and had no trouble. In my case, i was doing no DB updating or editing via web, so it was just read/display- which was easy. At soem point, the guy who owns my servers helped me streamline some code, and changed the method by which i was connecting to the database.

So, now I'm trying to write a quick script to update a databse I have (basically is going through the entries, testing a condition and assigning a property to a DB field based on the condition). Unfortunately, since my connection method is different than it was in the book, none of the conditions I try to include in the connection string will work.

I understand that some sort of lock method has to be passed with the connection commands to open the DB for writing/updating, but I can't figure out the syntax to use with my specific connection menthod...

In the book they use something like adOpenOptimistic, adOpenStatic-- but when i try to encorporate these into my string- I get errors that they are undefined variables (when i assumed they were part of the lang). It seems that the connection method I'm using is looking for a different type of argument.

I notice in one script I found online, with a similar syntax of DB connection, they followed theirs with ,2,2- which seemed to be the argument for opening it and being able to update/edit. When I tried to encorporate the ,2,2 intro my connection, I got errors again (although i'm unsure what they were, can try again and post if needed).

Since I am more of a "oh my god, it works" kind of scripting person at this point, not so much a "how do I streamline this to get .22 nonoseconds of performance out of it"-- I'm a bit out of my leugue with tinkering with how the script calls the DB- so any tips would be helpful. I will post below an example of my exact script.

 

[Vince Maskeeper]

@ LANGUAGE="VBSCRIPT"
OPTION EXPLICIT
Response.Buffer = True


Dim objRec
Dim ReturnedInfo
Dim Statement
Dim Thisstate
Dim Location
Dim StrConnect
Dim objConn


'*** Open the database.
Dim dbPath
dbPath = "C:Inetpubwwwrootmusicianassist.comdbpremium.m db"
strConnect= "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath

Statement = "SELECT * FROM Zipcodes ORDER BY State, City"
Set objConn = Server.CreateObject ("ADODB.Connection")
Set ReturnedInfo = Server.CreateObject ("ADODB.Recordset")

objConn.Open strconnect
Set ReturnedInfo = objconn.Execute(Statement)




================================================== =

So, all I want to figure out is what arguements, and where to put them, to let it open this DB so I can use the UPDATE method to assign a value to a field in the database.

I'm sure when I pass the connection striing to the open method that I should be able to add an argument to tell it to lock it for writing, but it just doesn't work with any syntax I've tried.

-Vince

 

[Kevin P]

@ LANGUAGE="VBSCRIPT"
 OPTION EXPLICIT
 Response.Buffer = True
 
 Const adLockReadOnly = 1
 Const adLockPessimistic = 2
 Const adLockOptimistic = 3
 Const adLockBatchOptimistic = 4
 
 Const adOpenForwardOnly = 0
 Const adOpenKeyset = 1
 Const adOpenDynamic = 2
 Const adOpenStatic = 3
 
 Dim objRec
 Dim ReturnedInfo
 Dim Statement
 Dim Thisstate
 Dim Location
 Dim StrConnect
 Dim objConn
 
 
 '*** Open the database.
 Dim dbPath
 dbPath = "C:Inetpubwwwrootmusicianassist.comdbpremium.m db"
 strConnect= "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
 
 Statement = "SELECT * FROM Zipcodes ORDER BY State, City"
 Set objConn = Server.CreateObject ("ADODB.Connection")
 Set ReturnedInfo = Server.CreateObject ("ADODB.Recordset")
 
 objConn.Open strconnect
 ReturnedInfo.Open Statement, objConn, adOpenStatic, adLockOptimistic

The only change is the addition of the Consts for the ADO constants (these will exist already in VB but not VBScript), and the method of opening the recordset in the last line of code.

KJP

 

[Paul McElligott]

You can also set the constants using ADOVBS.INC as an include file, if you have it.

 

[Vince Maskeeper]

Ok, thanks guys- working now.

But here's an insane follow-up question:

I have a database with 70,000+ entries- they are City, State and Zip for the majority of cities in the US. In addition, it has latitude and longitude figures. Using the state and lat/long figures: I have added an additional field which is region.

The script tests each entry and finds the region, and assigns the region to region field in the database (it's basically a giant case statement that looks something like this):

Case "AL" 
 IF Returnedinfo("Latitude") >= 33.15 then
 Location = "Alabama- Northern" 
 ElseIF Returnedinfo("Longitude") < -86.6 then
 Location = "Alabama- Southwestern" 
 ElseIF Returnedinfo("Longitude") >= -86.6 then
 Location = "Alabama- Southeastern" 
 Else 
 Location = "000000000" 
 End If 
 Case "AZ" 
 IF Returnedinfo("Latitude") >= 34.1 then
 Location = "Arizona- Northern" 
 ElseIF Returnedinfo("Latitude") < 34.1 then
 Location = "Arizona- Southern" 
 Else 
 Location = "000000000" 
 End If 
 Case "CA" 
 IF Returnedinfo("Latitude") >= 38.45 then
 Location = "California- Northern" 
 ElseIF Returnedinfo("Latitude")  34.5 and Returnedinfo("Latitude") < 38.45 then
 Location = "California- Central" 
 Else 
 Location = "000000000" 
 End If 
 Case "CO" 
 IF Returnedinfo("Latitude") >= 38.9 then
 Location = "Colorado- Northern" 
 ElseIF Returnedinfo("Latitude") < 38.9 then
 Location = "Colorado- Southern" 
 Else 
 Location = "000000000" 
 End If 
 Case "CT" 
 IF Returnedinfo("Latitude") >= 41.6 then
 Location = "Connecticut- Northern" 
 ElseIF Returnedinfo("Latitude") < 41.6 then
 Location = "Connecticut- Southern" 
 Else 
 Location = "000000000" 
 End If

etc, etc, etc...

The script runs, and I ouput to the screen as it is going, and it works fine. The problem is, when it started the MDB file is about 7 megs. After the script runs, it shows the db file as 290 megs.

All I'm doing is adding 10-20 characters to each listing, for 70,000 listings-- this shouldn't be anywhere close to that amount of data!! I can see it outputting what it's writing to the db directly, and it isn't screwed up (it isn't showing 3000000 characters for region for example). Why would the DB go from 7 megs to 296 megs by just adding a few words??

Start size: 7,458,816 bytes
Finish size: 296,972,288 bytes

-Vince

 

[Kevin P]

Is the region field indexed? Also, you aren't putting trailing spaces or other characters at the end of the string are you?

Also, try compacting the DB after you've loaded the data. It may be heavily fragmented from expanding the record length.

Lastly, for a large DB or one that will be hit by lots of users, you're better off using a more industrial strength database such as SQL Server rather than Access.

 

[Vince Maskeeper]

No trailing spaces in the string. Not sure what is meant by indexed (i'm stumbling my way through)...

I will do the compacting, but have to download it back off the server first (which is going now).

I know SQL is the way to go, right now my backend is real simple and traffic levels are "relatively" low... but if/when growth happens I will migrate (but liked to know how to interface with access for the sake of doing it for small sites.)

-Vince