ADODB and VBS

5 Mar 2007 2:18 PM

taid

Hi Guys,
I am feeling really thick and not finding what I am looking for.

I am looking to connect to a SQL Server 2000 Database on the localserver for
the script I am writing and then to perform a series of select statements so
that I can compare the data. does anyone have a link to ADODB/VBS for
dummies or can explain a quick this is how ot connect to the DB and this is
how to Query you data and here is how to access it.

What I have so far is:

Set objectConnection = CreateObject("ADODB.Connection")
objectConnection.Open "Driver={SQL Server};Server=localhost;Database=<db
name>;Trusted_Connection=Yes;"

What do I do from here?

Connecting the the DB

5 Mar 2007 3:35 PM

Richard Mueller [MVP]

taid wrote:

Show quote

> I am feeling really thick and not finding what I am looking for.
>
> I am looking to connect to a SQL Server 2000 Database on the localserver
> for
> the script I am writing and then to perform a series of select statements
> so
> that I can compare the data. does anyone have a link to ADODB/VBS for
> dummies or can explain a quick this is how ot connect to the DB and this
> is
> how to Query you data and here is how to access it.
>
> What I have so far is:
>
> Set objectConnection = CreateObject("ADODB.Connection")
> objectConnection.Open "Driver={SQL Server};Server=localhost;Database=<db
> name>;Trusted_Connection=Yes;"
>
> What do I do from here?

I use code similar to below:
=======
strServer = "MyServer"
strDatabase = "MyDDB"
strInstance = "MyInstance"

' Construct connection string.
strDBConnect = "DRIVER=SQL Server;" _
    & "Trusted_Connection=Yes;" _
    & "DATABASE=" & strDatabase & ";" _
    & "SERVER=" & strServer & "\" & strInstance

' Connect to database.
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.ConnectionString = strDBConnect
adoConnection.Open

' Run query.
Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection
adoRecordset.Source = "SELECT Field1, Field2, Field3 " _
    & "FROM dbo.MyTable " _
    & "WHERE Field_ID = 2 "
        & "AND Status 'P'"
adoRecordset.Open

' Enumerate the recordset.
Do Until adoRecordset.EOF
    strField1 = adoRecordset.Fields("Field1").Value
    strField2 = adoRecordset.Fields("Field2").Value
    strField3 = adoRecordset.Fields("Field3").Value
    Wscript.Echo strField1 & "," & strField2 & "," & strField3
    adoRecordset.MoveNext
Loop
adoRecordset.Close
adoConnection.Close
=========
You can also use Command objects and run statements that do not return
recordsets.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

5 Mar 2007 3:48 PM

taid

Thank you for the responce and that does help. I ended up reading up on the
ADO entries on the MSDN site and figured it out I ended up with code that
looks like

'Setting the connection, command and recordset variables for the SQL interface
Set objectConnection = CreateObject("ADODB.Connection")
Set objectCommand = CreateObject("ADODB.Command")
Set objectRecordSet = CreateObject ("ADODB.RecordSet")

'The connection string to the local DB for ePO using trusted auth
objectConnection.ConnectionString = "Driver={SQL
Server};Server=(local);Database=(db);Trusted_Connection=Yes;"

'Opening the connection
objectConnection.Open

'Connection the commmand object to the connection
set objectCommand.ActiveConnection = objectConnection

'This section of the script queiries SQL for the latest info
objectCommand.CommandText = "Select * FROM dbo.Table WHERE value LIKE value"

set objectRecordSet = objectCommand.Execute

Variable = objectRecordSet.Fields(0).Value

Show quote

"Richard Mueller [MVP]" wrote:

> taid wrote:
>
> > I am feeling really thick and not finding what I am looking for.
> >
> > I am looking to connect to a SQL Server 2000 Database on the localserver
> > for
> > the script I am writing and then to perform a series of select statements
> > so
> > that I can compare the data. does anyone have a link to ADODB/VBS for
> > dummies or can explain a quick this is how ot connect to the DB and this
> > is
> > how to Query you data and here is how to access it.
> >
> > What I have so far is:
> >
> > Set objectConnection = CreateObject("ADODB.Connection")
> > objectConnection.Open "Driver={SQL Server};Server=localhost;Database=<db
> > name>;Trusted_Connection=Yes;"
> >
> > What do I do from here?
>
> I use code similar to below:
> =======
> strServer = "MyServer"
> strDatabase = "MyDDB"
> strInstance = "MyInstance"
>
> ' Construct connection string.
> strDBConnect = "DRIVER=SQL Server;" _
>     & "Trusted_Connection=Yes;" _
>     & "DATABASE=" & strDatabase & ";" _
>     & "SERVER=" & strServer & "\" & strInstance
>
> ' Connect to database.
> Set adoConnection = CreateObject("ADODB.Connection")
> adoConnection.ConnectionString = strDBConnect
> adoConnection.Open
>
> ' Run query.
> Set adoRecordset = CreateObject("ADODB.Recordset")
> adoRecordset.ActiveConnection = adoConnection
> adoRecordset.Source = "SELECT Field1, Field2, Field3 " _
>     & "FROM dbo.MyTable " _
>     & "WHERE Field_ID = 2 "
>         & "AND Status 'P'"
> adoRecordset.Open
>
> ' Enumerate the recordset.
> Do Until adoRecordset.EOF
>     strField1 = adoRecordset.Fields("Field1").Value
>     strField2 = adoRecordset.Fields("Field2").Value
>     strField3 = adoRecordset.Fields("Field3").Value
>     Wscript.Echo strField1 & "," & strField2 & "," & strField3
>     adoRecordset.MoveNext
> Loop
> adoRecordset.Close
> adoConnection.Close
> =========
> You can also use Command objects and run statements that do not return
> recordsets.
>
> --
> Richard Mueller
> Microsoft MVP Scripting and ADSI
> Hilltop Lab - http://www.rlmueller.net
> --
>
>
>