How to return COUNT(*) and store it in a Variable for further usage in VBscript

Question

I am new to Vbscript (3 days to be precise),

I am connecting my script to SQL Server and playing around with databases. I have connected successfully

Now here's what I want to do:

I want to store the value of SELECT COUNT(*) FROM TABLE_A in a variable to be used in VBscript.

This SQL query returns the number of rows in table and it is an integer, but how do I return it and save in a variable?

I tried this:

Dim VARX
SET VARX = connection.execute("SELECT COUNT(*) FROM TABLE_A")

So now VARX should contain the number of rows of TABLE_A.

But this is a wrong way I know. And of course It posts an error "TYPE Mismatch:". Please guide me!

Community · Accepted Answer · 2017-05-23 10:32:33Z

3

Try something like this :

Dim rs, varx
SET rs = connection.execute("SELECT COUNT(*) FROM TABLE_A")
varx = rs(0).value

Not my field of expertise actually, here are some references :

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Oct 19, 2014 at 7:14

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ekkehard.Horner Over a year ago

The second Set is wrong. The value returned is a simple number, not an object.

har07 Over a year ago

@Anantvaibhav you're welcome. BTW, why unmarked this answer? (is it the updated code didn't work for you?)

Anant Vaibhav Over a year ago

@har07, As I said, It worked with some modifications.

Anant Vaibhav · Accepted Answer · 2014-10-20 07:07:07Z

0

Here is the final code which worked:

...
Set Recordset=CreateObject("ADODB.Recordset")
ConnString="DRIVER={SQL Server};SERVER=PCX\SQLEXPRESS;UID=sa;PWD=password;DATABASE=testdb"

Dim SQL_Rows, NoOfRows

SQL_Rows = "SELECT Count (*) from Table_A"
Recordset.open SQL_Rows,ConnString
NoOfRows = Recordset(0).value
Recordset.close

MsgBox NoOfRows

Now Message Box shows the number of rows in table_A

answered Oct 20, 2014 at 7:07

Anant Vaibhav

3653 gold badges8 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to return COUNT(*) and store it in a Variable for further usage in VBscript

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related