Record count with ASP and Access

[jorr1013]

Yes, I'm a newbie. But I'm having a problem I can't figure out.

I'm just trying to get a record count from an Access database and I can't make it work. The error simply says, "An exception occurred: 'Execute' "

The code is below. Any help will be greatly appreciated.

-John

<%@ LANGUAGE="VBSCRIPT" %>
<% OPTION EXPLICIT %>


<%

' Set up variables
Dim dcnDB ' As ADODB.Connection
Dim rsCount ' AS ADODB.Recordset


' assign Values to the variables
Set dcnDB = Server.CreateObject("ADODB.Connection")

' Open the connection to the database
dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\databases\ITSurvey.mdb"
dcnDB.Open

Set rsCount = dcnDB.Execute("SELECT COUNT(*) FROM Input")
Response.Write "There are "&rsCount(0)& " responses."


' Clean-up
dcnDB.Close
Set dcnDB = Nothing

%>

[Angelika]

http://www.programmersresource.com/forum/showthread.php?s=&threadid=46

[jorr1013]

Angelika,

I appreciate the link , but I had already looked at that. I guess I'm dense, but in the examples on that link, there is no place where you define the database you are opening, or its location, or the method (i.e. OLEDB or whatever) used to open it.

Like I said, I'm a real newbie at this, but I just don't get it. Please help me understand.

Thanks.

-John

[Angelika]

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath("DB/DBName.mdb")
Set dcnDB = Server.CreateObject("ADODB.Connection")
dcnDB.open strConn

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

strSQL="SELECT * FROM Input"
RS.Open strSQL,strConn
rsCount = RS.RecordCount
Rs.close

Response.Write "There are " & rsCount & " responses."

Set RS = Nothing
dcnDB.Close
Set dcnDB = Nothing

[jorr1013]

Angelika,

I had to play with it a good bit, but I finally got it to work use a DSN definition. Beats me why it wouldn't work otherwise.

Thank you for your help.

-John

[jsawkang]

hi...

u can use this method oso.

sSQL="SELECT COUNT(*) as ccount from category"
set rec=cn.execute(sSQL)
response.write rec("ccount")

[fyrye]

you must include adovbs.inc in the page where you set your connection.
or map directly to it using metadata

if your lopping through results an easy method of counting other then using "select count(field) as [var] from tbl"
try these methods
in your loop simply set up a variable as rscount = rscount + 1
Before your loop make sure to set it as rscount = 1
or use
rscount = rs.recordcount after yout SQL querry
take note that if your locktype is not set correctly you will return a -1
use these
rs.cursorType = 3
rs.lockType = 3
As well never use * when selecting info from the database. it greatly reduces speed having to go to it once create a directory of all fields with in your table, then pull the information from each field, especially if your only calling 1 field.

[jorr1013]

Fryre and Angelika,

Thanks for the advice. A newbie like me can always use some!

-John