How 2 ADO connect 2 Access w/ ASP?

AnakinVB
07-24-2002, 10:49 AM
Hello
I know how to establish an ADO connection to an access database using visual basic. How do I do the same with ASP/vbscript?
thx,
Anakin

AnakinVB
07-24-2002, 11:07 AM
This is an addendum to my own message above. I have no ODBC icon in my Control Panel.
Is there a way for me to write the code directly into my ASP page?

I am using a file called "cars.mdb"

Here is the error I get now:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/audit/cars.asp, line 7

Rezner
07-24-2002, 11:21 AM
You basically do it the same way, except for the object creation process is a little different. Here's an example:Set adoCon = Server.CreateObject("ADODB.Connection")

'Connect to the Access database (change "yourdb.mdb")
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("yourdb.mdb") & ";" & _
"Persist Security Info=False"

'Carve out a recordset
Set RS = Server.CreateObject("ADODB.Recordset")

'Populate the recordset (use your own strSQL)
RS.Open strSQL, adoCon, 3, 3, 1

'***** ASP operations here

RS.Close
Set RS = Nothing

adoCon.Close
Set adoCon = Nothing
This ASP script would create its own connection to the database. A good practice is to utilize the global.asa file to create an Application level connection to the database though -- depending on how many simultaneous site visitors you expect.

AnakinVB
07-24-2002, 01:06 PM
Rezner said:

"'Populate the recordset (use your own strSQL)
RS.Open strSQL, adoCon, 3, 3, 1"
----------------------

This is where I am crashing.
I don't quite get what you mean by "use your own strSQL - should I being using a SELECT statement here?

This is the VB code I would use:
RS.Open "Select * FROM Cars", CN, adOpenStatic, adLockBatchOptimistic

How do I know what my parameters are in ASP?

thx,
Anakin

Rezner
07-24-2002, 01:27 PM
Your strSQL is going to be "SELECT * FROM Cars".

Me using "adoCon, 3,3,1" is associating the recordset with the active connection and then setting its cursor location (you can spell it out or use the numerical index like me). You don't need to use that combination in particular... there are many combinations. I believe there is a tutorial on cursor locations in the Tutors Corner. I typically use this one though.

AnakinVB
07-24-2002, 01:40 PM
Thx Rezner for getting me this far.
I am getting closer. Now, I'm coming to a halt at line 25 - "
adoCon.open "Cars""

The error message reads:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/audit/cars.asp, line 25


Here is my code:
<%

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("cars.mdb") & ";" & _
"Persist Security Info=False"

Set RS = Server.CreateObject("ADODB.Recordset")

RS.Open "SELECT * FROM Cars", adoCon, 3, 3, 1

'JC ASP operations here

RS.Close
Set RS = Nothing

adoCon.Close
Set adoCon = Nothing

RS="SELECT carName FROM Cars ORDER BY carName"

set adoCon = server.createobject("ADODB.Connection")

adoCon.open "Cars"

set cars=adoCon.execute(RS)

%>



<% do while not cars.eof %>

<%= cars(0) %> <br>



<%cars.movenext

loop%>

<% cars.close %>

I do not understand why I am crashing at:
adoCon.open "Cars"

Can anyone else see my error?

Rezner
07-24-2002, 01:43 PM
You've closed and destroyed both the connection and recordset objects... so you can't use them any more.

I'm just guessing here, but I'd say your code should look more like this:<%

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("cars.mdb") & ";" & _
"Persist Security Info=False"

Set RS = Server.CreateObject("ADODB.Recordset")

RS.Open "SELECT carName FROM Cars ORDER BY carName", adoCon, 3, 3, 1

While NOT RS.EOF
response.write RS("carName") & "<BR>"

RS.Movenext
Wend

'Close and destroy ADODB Recordset object
RS.Close
Set RS = Nothing

'Close and destroy ADODB Connection object
adoCon.Close
Set adoCon = Nothing
%>Note: You can use RS("<<FIELD NAME>>") instead of using the numerical index.

AnakinVB
07-24-2002, 02:32 PM
Thank you, that worked. I will use this code as my educational model when connecting to Access from now on.

Rezner
07-24-2002, 03:18 PM
Rock on. Like I said though, once you get this method down you really should study up on the useage of the global.asa file and the implementation of Application level objects. It will greatly increase the efficiency of your site since you will only need one connection to the database and not multiple instances of it.

Thinker
07-25-2002, 09:10 AM
Here is the error I get now:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
How do I know what my parameters are in ASP?
If you use a connectionstring like Rezner suggested, you won't
need ODBC at all. The only I know of to get the associated
numbers for the enums in a object library is to open VB, add the
proper reference (ADO Object Library) and look in the Object
Browser. You can't use the enums directly in VBScript.

Rezner
07-25-2002, 10:02 AM
Originally posted by Thinker
You can't use the enums directly in VBScript.
If you're using MS InterDev the methods and enums will show up when you envoke the dot operator (even with user built objects).
But, I'm pretty sure that this is the only way to have them listed like this.

Thinker
07-25-2002, 10:09 AM
Good to know. Never liked vis interdev very much but that would
be a good reason to use it.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum