VBScript- SQL Query of MS SQL 7 Database 
Author Message
 VBScript- SQL Query of MS SQL 7 Database

I'm trying to write a script that will query a MS SQL 7 Database.
All I want is to execute a "count" on the number of entires in the database,
and return it so I can display it via wscript.echo.
If anyone knows how to get that , please drop me an email back...
Thanks
Steven Santoni

So far I have this...

' Script Purpose:
'  Execute SQL query on database
'Script Created: 05-21-02

Option Explicit

WScript.Echo "Script Starting"
WScript.Echo " "

'Variables for SQL connection
Dim connection
Dim sConnectionString

'create connection
Set connection = WScript.CreateObject("ADODB.Connection")
WScript.Echo "Connection Object Created  : " & err.number

 'make connection
 Dim sconnectmsqasql
 sconnectmsqasql = "driver={SQL Server};" & _
                              "server=MYSERVERNAME;" & _
                              "database=MyDatabase"
 connection.ConnectionString = sconnectmsqasql

 'open connection
 connection.Open
 WScript.Echo "Connection Made   : " & Err.number

 Dim cmd
 Set cmd = CreateObject("ADODB.Command")
 WScript.Echo "Command object created  : " & err.number
 Set cmd.ActiveConnection = connection   'this is key

 Dim strSQLText
 'strSQLText = "INSERT INTO Email VALUES " & "(" & "'" & DateSent & "')"
 strSQLText = "SELECT count (*) From Tablel where DateField = '5-22-2002'"

 cmd.CommandText = strSQLText
 dim results
 set results = cmd.execute
 WScript.Echo "Errors " & err.number
 '---- how can I get the 'results' *******!!!!!!

 'cmd.execute
 WScript.Echo "Command Executed!   : " & err.number
 'close connection
 connection.Close
 WScript.Echo "Connection Closed   : " & err.number

 '*********
 '*********    End SQL Processing
 '*********

'Cleanup
Set connection = Nothing

WScript.Echo "Script Complete"



Mon, 08 Nov 2004 01:46:33 GMT  
 VBScript- SQL Query of MS SQL 7 Database

Quote:
> I'm trying to write a script that will query a MS SQL 7 Database.
> All I want is to execute a "count" on the number of entires in the
database,
> and return it so I can display it via wscript.echo.
> If anyone knows how to get that , please drop me an email back...

Replies to the group, not email. Sorry.

The count function is an aggregate query. In this instance it'll return a
single row recordset. So something like this will work:

============================================

set cnn=createobject("ADODB.Connection")

cnn.open
"Provider=SQLOLEDB;server=yourserver;database=yourdb;uid=testuser;password=p
wd"

set rs=cnn.execute("select count(*) from queue")

if rs.eof and rs.bof then

 wscript.echo "Error: No records"

else

 wscript.echo rs.fields(0)

end if

============================================

Iain



Mon, 08 Nov 2004 04:22:20 GMT  
 VBScript- SQL Query of MS SQL 7 Database
Thanks for the reply... I found that just dumping in part of your code...
after this line worked like a champ...

Thanks for the help...  Now I can go about getting this thing fine tuned...
Steve

New code is...

WScript.Echo "Command Executed!   : " & err.number    'existing

 'new
 if results.eof and results.bof then
 wscript.echo "Error: No records"
 else
 wscript.echo results.fields(0)
  end if
'code continues...


Quote:
> I'm trying to write a script that will query a MS SQL 7 Database.
> All I want is to execute a "count" on the number of entires in the
database,
> and return it so I can display it via wscript.echo.
> If anyone knows how to get that , please drop me an email back...
> Thanks
> Steven Santoni

> So far I have this...

> ' Script Purpose:
> '  Execute SQL query on database
> 'Script Created: 05-21-02

> Option Explicit

> WScript.Echo "Script Starting"
> WScript.Echo " "

> 'Variables for SQL connection
> Dim connection
> Dim sConnectionString

> 'create connection
> Set connection = WScript.CreateObject("ADODB.Connection")
> WScript.Echo "Connection Object Created  : " & err.number

>  'make connection
>  Dim sconnectmsqasql
>  sconnectmsqasql = "driver={SQL Server};" & _
>                               "server=MYSERVERNAME;" & _
>                               "database=MyDatabase"
>  connection.ConnectionString = sconnectmsqasql

>  'open connection
>  connection.Open
>  WScript.Echo "Connection Made   : " & Err.number

>  Dim cmd
>  Set cmd = CreateObject("ADODB.Command")
>  WScript.Echo "Command object created  : " & err.number
>  Set cmd.ActiveConnection = connection   'this is key

>  Dim strSQLText
>  'strSQLText = "INSERT INTO Email VALUES " & "(" & "'" & DateSent & "')"
>  strSQLText = "SELECT count (*) From Tablel where DateField = '5-22-2002'"

>  cmd.CommandText = strSQLText
>  dim results
>  set results = cmd.execute
>  WScript.Echo "Errors " & err.number
>  '---- how can I get the 'results' *******!!!!!!

>  'cmd.execute
>  WScript.Echo "Command Executed!   : " & err.number
>  'close connection
>  connection.Close
>  WScript.Echo "Connection Closed   : " & err.number

>  '*********
>  '*********    End SQL Processing
>  '*********

> 'Cleanup
> Set connection = Nothing

> WScript.Echo "Script Complete"



Mon, 08 Nov 2004 20:12:11 GMT  
 VBScript- SQL Query of MS SQL 7 Database
rs.count                        Will give you a count of the records within
the query..

- Chris Allen


Quote:


> > I'm trying to write a script that will query a MS SQL 7 Database.
> > All I want is to execute a "count" on the number of entires in the
> database,
> > and return it so I can display it via wscript.echo.
> > If anyone knows how to get that , please drop me an email back...

> Replies to the group, not email. Sorry.

> The count function is an aggregate query. In this instance it'll return a
> single row recordset. So something like this will work:

> ============================================

> set cnn=createobject("ADODB.Connection")

> cnn.open

"Provider=SQLOLEDB;server=yourserver;database=yourdb;uid=testuser;password=p

- Show quoted text -

Quote:
> wd"

> set rs=cnn.execute("select count(*) from queue")

> if rs.eof and rs.bof then

>  wscript.echo "Error: No records"

> else

>  wscript.echo rs.fields(0)

> end if

> ============================================

> Iain



Sat, 13 Nov 2004 12:38:16 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. MS Access and SQL 7.0 databases with VBScript

2. Using Vbscript and SQL-DMO to connect to SQL Server and run a T-SQL script

3. Q: MS SQL queries -> ANSI SQL

4. Q: MS SQL queries -> ANSI SQL

5. Query an access database via SQL in VB6

6. Hyperlink To Query SQL 7 database?

7. Interdev/scripting/MS SQL Database

8. access to MS sql database with WSH ??

9. Converting MS SQL to MS SQL CE

10. Complicated Record Handling on MS Sql server and MS Accesses Databases

11. vbscript and SQL Query using 'LIKE'

12. SQL queries, ASP and VBScript

 

 
Powered by phpBB® Forum Software