Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


looking for vbscript SQL and syntax...

Posted on 2004-03-24
Medium Priority
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") "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\mydatabase.mdb"
SQL = "INSERT INTO mytable (name) VALUES('"& Name &"')"
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.
Question by:SimpsonAngus
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 12

Accepted Solution

kingsfan76 earned 280 total points
ID: 10672485
Set Cnn = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset") "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
set rst = nothing
set Cnn = nothing
LVL 12

Expert Comment

ID: 10672493
>> Conn.Close

I mean Cnn.Close

Author Comment

ID: 10672919
Hi kingsfan76,

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

Assisted Solution

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

That is!!!

--Ben Kam
LVL 12

Expert Comment

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()

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

718 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