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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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