looking for vbscript SQL and syntax...

Posted on 2004-03-24
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 70 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 60 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

626 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