Solved

looking for vbscript SQL and syntax...

Posted on 2004-03-24
5
449 Views
Last Modified: 2008-03-17
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.
0
Comment
Question by:SimpsonAngus
  • 3
5 Comments
 
LVL 12

Accepted Solution

by:
kingsfan76 earned 70 total points
ID: 10672485
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
 
LVL 12

Expert Comment

by:kingsfan76
ID: 10672493
>> Conn.Close

I mean Cnn.Close
0
 

Author Comment

by:SimpsonAngus
ID: 10672919
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
 
LVL 1

Assisted Solution

by:benkam
benkam earned 60 total points
ID: 10673063
foo_string = "this is a string of " & Cstr(foo)

That is!!!

--Ben Kam
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 10673381
>>> 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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question