ODBC Connection Syntax in VBScript

[princessk1]

I am trying to connect to a SQL Server DB through an ODBC connection in vbscript. I am having trouble with the syntax. Does anyone have some sample code relating to this? Thanks!

 

[AbdulSami]

My Dear,

I didn't try it in VBScript but doing it in VB6.

Following is the code. Make sure that ,

1. You have define ODBC DSN. In following example it is 'pubs'.
2. You should check Microsoft Data Objects 2.x Library in Project Referecnes. (I am using 2.6).

I hope it may give you some hint to do it in Vb Script.

Best of Luck!

Abdul Sami

Private Sub Form_Load()
Dim attrec As New ADODB.Recordset, a As Integer
a = 0
attrec.Open &quot;select * from attendance where attendance.ecode = '0032' and attendance.date >= '01-apr-01' and attendance.date <= '30-apr-02'&quot;, &quot;DSN=pubs;UID=sa;database=master&quot;, adOpenStatic, adLockBatchOptimistic
Do While Not attrec.EOF
Debug.Print attrec.Fields(0)
attrec.MoveNext
a = attrec.RecordCount
a = a + 1
If a = 1000 Then
Exit Do
End If
Loop
attrec.Close
End Sub

 

[Tweir]

Here are some different connection strings coming from my ASP test page:

strconn = &quot;Provider=SQLOLEDB;Data Source=YourSQLServerServerName;Initial Catalog=YourSQLDBNAME;&quot;
strconn = strconn & &quot;User ID=webuser;Password=webuser;APP=ASP Script&quot;

'OLE DB w/IP IT WORKS!!!!
strconn1 = &quot;Provider=SQLOLEDB;Data Source=xxx.xxx.x.xx,1433;Network Library=DBNETLIB;Initial Catalog=YourSQLDBNAME;&quot;
strconn1 = strconn1 & &quot;User ID=webuser;Password=webuser;APP=ASP Script&quot;

'ODBC IT WORKS!!!!
strconn2 =&quot;Driver={SQL Server};Server=YourSQLServerServerName;DATABASE=YourSQLDBNAME;uid=webuser;pwd=webuser;APP=ASP Script&quot;

'ODBC DSN IT WORKS!!!
strconn4 =&quot;DSN=DSNName;uid=webuser;pwd=webuser;DATABASE=YourSQLDBNAME;APP=ASP Script&quot;

'From MS site IT WORKS!!!!
strconn6 = &quot;Provider=SQLOLEDB;Password=webuser;User ID=webuser;Initial Catalog=YourSQLDBNAME;Data Source=YourSQLServerServerName;Network Library=DBNETLIB;APP=ASP Script&quot;


'Open the connection here:
Set cn = Server.CreateObject(&quot;ADODB.Connection&quot
cn.ConnectionString = strconn6
cn.Open
_______________________________
One of those strconns should work for you.