looking for vbscript SQL and syntax...

I have a working code for inserting a new record in to my access database.

Set Cnn = Server.CreateObject("ADODB.Connection")
Cnn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\mydatabase.mdb"
SQL = "INSERT INTO mytable (name) VALUES('"& Name &"')"
Cnn.execute(SQL)
set Cnn = nothing

What is the SQL and syntax for returning the total number of records from mytable? Please give me a complete working code.
SimpsonAngusAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kingsfan76Connect With a Mentor Commented:
Set Cnn = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset")
Cnn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\mydatabase.mdb"
SQL = "SELECT COUNT(*) AS NumOfRec FROM mytable"
set rst = Cnn.execute(SQL)
if not rst.EOF then
   response.write rst.Fields("NumOfRec")
end if
rst.Close
Conn.Close
set rst = nothing
set Cnn = nothing
0
 
kingsfan76Commented:
>> Conn.Close

I mean Cnn.Close
0
 
SimpsonAngusAuthor Commented:
Hi kingsfan76,

Thanks!
Would you mind telling how to convert NumOfRec to string?
i.e. foo = rst.Fields("NumOfRec")   'foo is numeric at this moment
     foo_string = "this is a string of " & foo   <--- must change foo from numeric to string
 
0
 
benkamConnect With a Mentor Commented:
foo_string = "this is a string of " & Cstr(foo)

That is!!!

--Ben Kam
0
 
kingsfan76Commented:
>>> foo_string = "this is a string of " & foo  

i think if you do that it will automatically convert it to string.  you don't really need the cstr()
0
All Courses

From novice to tech pro — start learning today.