TimJordanVBS
Tue Jan 20 05:16:18 CST 2004
JIM.H. <anonymous@discussions.microsoft.com> wrote in message news:<174B30B5-AFC0-4523-A2ED-2E02E6EF802B@microsoft.com>...
> Hello,
> I have a ODBS defined for SQL server. How can I connect and retrieve data from a table in VBScript. Is there any examples.
> Thanks.
> Jim.
Hi Jim,
below is a code example to get you started. It uses ADO, which isn't
the only way, but it's the only way I know! We connect to a database
using an odbc source, username and password, select some rows from a
table and then loop
through the rows. Note, there is no error checking in this code.
Regards, Tim.
Dim MyODBC
Dim MyUsername
Dim MyPassword
Dim strConn
Dim objADODBConn
Dim objRecordSet
Dim strSQL
Dim arrRecords
Dim intRowNum
Dim ThisCol1
Dim ThisCol2
Dim ThisCol3
MyODBC = "test"
MyUsername = "sa"
MyPassword = ""
Set objADODBConn = CreateObject("ADODB.Connection")
strConn="DSN=" & MyODBC & ";UID=" & MyUsername & ";PWD=" & MyPassword
& ";"
objADODBConn.Open(strConn)
Set objRecordSet=CreateObject("ADODB.RecordSet")
strSQL = "select Col1, Col2, Col3 from MyTable where Col1 = 'X';"
objRecordSet.Open strSQL,objADODBConn
If objRecordSet.EOF And objRecordSet.BOF Then
'No rows were selected
MsgBox "No Rows met the criteria"
Else
arrRecords=objRecordSet.GetRows
'arrRecords now contains the results from the select.
'we can step through each row and access each column of each row
like this
For intRowNum=0 to UBound(arrRecords,2)
ThisCol1 = arrRecords(0,intRowNum)
ThisCol2 = arrRecords(1,intRowNum)
ThisCol3 = arrRecords(2,intRowNum)
'Do something interesting with ThisCol1,ThisCol2,ThisCol3
MsgBox "Row " & intRowNum+1 & " of " _
& UBound(arrRecords,2)+1 & vbCrLf & "Col1 = " & _
ThisCol1 & ", Col2 = " & ThisCol2 & ", Col3 = " & ThisCol3
Next
End If
objRecordSet.Close
objADODBConn.Close
Set objRecordSet = Nothing
Set objADODBConn = Nothing