none
Pulling data from SQL Server using RecordSet object in VBScript

    Question

  • Hello, I am kinda new to VBScript, but have used C# to pull data from a SQL Server db using DataTables and Reader, etc, however I am at a loss as to how to accomplish this in VBScript. I have a variable, which the user specifies, that I wish to use as a parameter in a SQL string, much like the following: "SELECT x FROM y WHERE x = '@param'" The result will then be stored in a string variable and I wiill do what I have to do with it. Can someone help me set this up. Sorry if it's a silly question, I'm just very new to VB. BTW, this is for a VB Script :/ Thanks! -Serg
    Sunday, May 15, 2011 4:30 AM

Answers

  • This is the answer to my question. 

     

    ' enforces variable names and declarations

    option explicit

     

    Dim Connection

    Dim Recordset

    Dim SQL

    Dim Server

    Dim field

     

    'declare the SQL statement that will query the database

    SQL = "SELECT ClusterAssignment FROM dbo.Studies WHERE Study_ID = '18054'"

     

    'create an instance of the ADO connection and recordset objects

    Set Connection = CreateObject("ADODB.Connection")

    Set Recordset = CreateObject("ADODB.Recordset")

     

    'open the connection to the database

    Connection.Open "DSN=x;UID=u;PWD=p;Database=db"

     

    'Open the recordset object executing the SQL statement and return records 

    Recordset.Open SQL,Connection

     

    'first of all determine whether there are any records 

    If Recordset.EOF Then 

    wscript.echo "There are no records to retrieve; Check that you have the correct job number."

    Else 

    'if there are records then loop through the fields 

    Do While NOT Recordset.Eof   

     

    field = Recordset("ClusterAssignment")

     

    if field <> "" then

    wscript.echo field

    end if

     

    Recordset.MoveNext     

    Loop

    End If

     

    'close the connection and recordset objects to free up resources

    Recordset.Close

    Set Recordset=nothing

    Connection.Close

    Set Connection=nothing

    Monday, May 16, 2011 7:17 PM

All replies

  • There are several VBScript examples in the Script Repository like this one.
    Uros Calakovic
    Monday, May 16, 2011 5:12 PM
  • This is the answer to my question. 

     

    ' enforces variable names and declarations

    option explicit

     

    Dim Connection

    Dim Recordset

    Dim SQL

    Dim Server

    Dim field

     

    'declare the SQL statement that will query the database

    SQL = "SELECT ClusterAssignment FROM dbo.Studies WHERE Study_ID = '18054'"

     

    'create an instance of the ADO connection and recordset objects

    Set Connection = CreateObject("ADODB.Connection")

    Set Recordset = CreateObject("ADODB.Recordset")

     

    'open the connection to the database

    Connection.Open "DSN=x;UID=u;PWD=p;Database=db"

     

    'Open the recordset object executing the SQL statement and return records 

    Recordset.Open SQL,Connection

     

    'first of all determine whether there are any records 

    If Recordset.EOF Then 

    wscript.echo "There are no records to retrieve; Check that you have the correct job number."

    Else 

    'if there are records then loop through the fields 

    Do While NOT Recordset.Eof   

     

    field = Recordset("ClusterAssignment")

     

    if field <> "" then

    wscript.echo field

    end if

     

    Recordset.MoveNext     

    Loop

    End If

     

    'close the connection and recordset objects to free up resources

    Recordset.Close

    Set Recordset=nothing

    Connection.Close

    Set Connection=nothing

    Monday, May 16, 2011 7:17 PM