vb6 ADODB TSQL procedure call quit working after database migration
- by phill
This code was once working on sql server 2005. Now isolated in a visual basic 6 sub routine using ADODB to connect to a sql server 2008 database it throws an error saying:
"Login failed for user 'admin' "
I have since verified the connection string does work if i replace the body of this sub with the alternative code below this sub. When I run the small program with the button, it stops where it is marked below the asterisk line. Any ideas? thanks in advance.
Private Sub Command1_Click()
Dim cSQLConn As New ADODB.Connection
Dim cmdGetInvoices As New ADODB.Command
Dim myRs As New ADODB.Recordset
Dim dStartDateIn As Date
dStartDateIn = "2010/05/01"
cSQLConn.ConnectionString = "Provider=sqloledb;" _
& "SERVER=NET-BRAIN;" _
& "Database=DB_app;" _
& "User Id=admin;" _
& "Password=mudslinger;"
cSQLConn.Open
cmdGetInvoices.CommandTimeout = 0
sProc = "GetUnconvertedInvoices"
'On Error GoTo GetUnconvertedInvoices_Err
With cmdGetInvoices
.CommandType = adCmdStoredProc
.CommandText = "_sp_cwm5_GetUnCvtdInv"
.Name = "_sp_cwm5_GetUnCvtdInv"
Set oParm1 = .CreateParameter("@StartDate", adDate, adParamInput)
.Parameters.Append oParm1
oParm1.Value = dStartDateIn
.ActiveConnection = cSQLConn
End With
With myRs
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.CursorType = adOpenKeyset
'.CursorType = adOpenStatic
.CacheSize = 5000
'***************************Debug stops here
.Open cmdGetInvoices
End With
If myRs.State = adStateOpen Then
Set GetUnconvertedInvoices = myRs
Else
Set GetUnconvertedInvoices = Nothing
End If
End Sub
Here is the code which validates the connection string is working.
Dim cSQLConn As New ADODB.Connection
Dim cmdGetInvoices As New ADODB.Command
Dim myRs As New ADODB.Recordset
cSQLConn.ConnectionString = "Provider=sqloledb;" _
& "SERVER=NET-BRAIN;" _
& "Database=DB_app;" _
& "User Id=admin;" _
& "Password=mudslinger;"
cSQLConn.Open
cmdGetInvoices.CommandTimeout = 0
sProc = "GetUnconvertedInvoices"
With cmdGetInvoices
.ActiveConnection = cSQLConn
.CommandText = "SELECT top 5 * FROM tarInvoice;"
.CommandType = adCmdText
End With
With myRs
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'.CursorType = adOpenKeyset
.CursorType = adOpenStatic
'.CacheSize = 5000
.Open cmdGetInvoices
End With
If myRs.EOF = False Then
myRs.MoveFirst
Do
MsgBox "Record " & myRs.AbsolutePosition & " " & _
myRs.Fields(0).Name & "=" & myRs.Fields(0) & " " & _
myRs.Fields(1).Name & "=" & myRs.Fields(1)
myRs.MoveNext
Loop Until myRs.EOF = True
End If
