ASP database question (scripting)

Discussion in 'Computers' started by Vince Maskeeper, Apr 10, 2003.

  1. Vince Maskeeper

    Vince Maskeeper Producer

    Joined:
    Jan 18, 1999
    Messages:
    6,499
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  2. Vince Maskeeper

    Vince Maskeeper Producer

    Joined:
    Jan 18, 1999
    Messages:
    6,499
    Likes Received:
    0
    Trophy Points:
    0
    @ 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
     
  3. Kevin P

    Kevin P Screenwriter

    Joined:
    Jan 18, 1999
    Messages:
    1,439
    Likes Received:
    0
    Trophy Points:
    0
    Code:
    @ 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
     
  4. Paul McElligott

    Paul McElligott Cinematographer

    Joined:
    Jul 2, 2002
    Messages:
    2,598
    Likes Received:
    0
    Trophy Points:
    0
    Real Name:
    Paul McElligott
    You can also set the constants using ADOVBS.INC as an include file, if you have it.
     
  5. Vince Maskeeper

    Vince Maskeeper Producer

    Joined:
    Jan 18, 1999
    Messages:
    6,499
    Likes Received:
    0
    Trophy Points:
    0
    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):

    Code:
    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
     
  6. Kevin P

    Kevin P Screenwriter

    Joined:
    Jan 18, 1999
    Messages:
    1,439
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  7. Vince Maskeeper

    Vince Maskeeper Producer

    Joined:
    Jan 18, 1999
    Messages:
    6,499
    Likes Received:
    0
    Trophy Points:
    0
    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
     

Share This Page