Some help executing a store procedure

[AT76]

I'm having trouble with the execution of the store proc below. I keep getting error 3146. The error started when we upgraded SQL Server to SQL2000.

Below is the old code:

Private Sub Form_Load()

Dim MyMonth As Integer
Me.AIM_FORECAST.Visible = False

bFrmLoaded = False

Set wrkODBC = CreateWorkspace("ODBCWorkspace", _
"", "", dbUseODBC)

Set conMaterialsDB = wrkODBC.OpenConnection("", _
dbDriverNoPrompt, , _
"ODBC;DATABASE=Materials;FileDSN=//na.paccar.com/ppdren/publicfiles/MatSys/dsns/PLDB_SQL.dsn;Trusted_Connection=Yes")

strPartNumber = Forms![FRM_Main]![ITMID]

Set qryNotes = conMaterialsDB.CreateQueryDef("", "execute pc_select_fcst_notes '" & strPartNumber & "'")
Set rstNotes = qryNotes.OpenRecordset(dbOpenSnapshot)

If rstNotes.EOF = False Then

Set ctrl = Me.CB_FCST_Notes
ctrl.RowSourceType = "Value List"
rstNotes.MoveLast
rstNotes.MoveFirst
iCount = rstNotes.RecordCount

If (iCount > 1) Then
strFirstNote = Format(rstNotes!FCSTDTE, "Short Date") & " " & Mid(rstNotes!PLNRNAME, 11) & " - " & Trim(rstNotes!FCSTNOTE)
ctrl.SetFocus
ctrl = strFirstNote
rstNotes.MoveNext

While (rstNotes.EOF = False)
strNotes = strNotes & " " & Format(rstNotes!FCSTDTE, "Short Date") & " " & Mid(rstNotes!PLNRNAME, 11) & " - " & Trim(rstNotes!FCSTNOTE) & ";"
rstNotes.MoveNext
Wend
ctrl.RowSource = strNotes
End If

If (iCount = 1) Then
strFirstNote = Format(rstNotes!FCSTDTE, "Short Date") & " " & Mid(rstNotes!PLNRNAME, 11) & " - " & Trim(rstNotes!FCSTNOTE)
ctrl.SetFocus
ctrl = strFirstNote
End If

End If
iCount = 0
wrkODBC.Close
Set wrkODBC = Nothing
Me.Command256.SetFocus

End Sub




I believe the error comes from the way its trying to connect to SQL and SQL2000. From what I have read in the web I need to change my connection to ADODB. I'm having some trouble with my connection. I keep getting error 91 (Object variable or With block variable not set). The line of code where it fails is in bold. Here's what I have so far:


Private Sub Form_Load()

Dim connString As String
Dim MyMonth As Integer
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

connString = "Driver={SQL Server};Server=NAME;Database=NAME;Trusted_Connection=yes;"

cn.ConnectionString = connString
cn.Open

Me.AIM_FORECAST.Visible = False
bFrmLoaded = False

strPartNumber = Forms![FRM_Main]![ITMID]

Set qryNotes = conMaterialsDB.CreateQueryDef("", "execute pc_select_fcst_notes '" & strPartNumber & "'")
Set rstNotes = qryNotes.OpenRecordset(dbOpenSnapshot)

If rstNotes.EOF = False Then

Set ctrl = Me.CB_FCST_Notes
ctrl.RowSourceType = "Value List"
rstNotes.MoveLast
rstNotes.MoveFirst
iCount = rstNotes.RecordCount

If (iCount > 1) Then
strFirstNote = Format(rstNotes!FCSTDTE, "Short Date") & " " & Mid(rstNotes!PLNRNAME, 11) & " - " & Trim(rstNotes!FCSTNOTE)
ctrl.SetFocus
ctrl = strFirstNote
rstNotes.MoveNext

While (rstNotes.EOF = False)
strNotes = strNotes & " " & Format(rstNotes!FCSTDTE, "Short Date") & " " & Mid(rstNotes!PLNRNAME, 11) & " - " & Trim(rstNotes!FCSTNOTE) & ";"
rstNotes.MoveNext
Wend
ctrl.RowSource = strNotes
End If

If (iCount = 1) Then
strFirstNote = Format(rstNotes!FCSTDTE, "Short Date") & " " & Mid(rstNotes!PLNRNAME, 11) & " - " & Trim(rstNotes!FCSTNOTE)
ctrl.SetFocus
ctrl = strFirstNote
End If

End If
iCount = 0
cn.Close
Me.Command256.SetFocus

End Sub


Can someone point out how to fix this? Thank you in advance for your help!!!

 

[AT76]

Ok,

Here's what I have worked so far. I'm not sure how to pass this parameter (" & strPartNumber & "). I've attached the code below. Thank you!


Private Sub Form_Load()

Dim connString As String
Dim MyMonth As Integer
Dim cn As New ADODB.Connection
Dim objCom As ADODB.Command
Dim objPara As ADODB.Parameter
Dim rstNotes As ADODB.Recordset

Set cn = New ADODB.Connection
Set objCom = New ADODB.Command

connString = "Driver={SQL Server};Server=NAME;Database=NAME;Trusted_Connection=yes;"

cn.ConnectionString = connString
cn.Open

Me.AIM_FORECAST.Visible = False
bFrmLoaded = False

strPartNumber = Forms![FRM_Main]![ITMID]

With objCom
.CommandText = "execute pc_select_fcst_notes" 'Name of the stored procedure
.CommandType = adCmdStoredProc 'Type : stored procedure
.Parameters.Append .CreateParameter(" & strPartNumber & ")
.ActiveConnection = cn.connString
Set rstNotes = cn.Execute(.CommandText)
End With

' Set qryNotes = conMaterialsDB.CreateQueryDef("", "execute pc_select_fcst_notes '" & strPartNumber & "'")
' Set rstNotes = qryNotes.OpenRecordset(dbOpenSnapshot)

If rstNotes.EOF = False Then

Set ctrl = Me.CB_FCST_Notes
ctrl.RowSourceType = "Value List"
rstNotes.MoveLast
rstNotes.MoveFirst
iCount = rstNotes.RecordCount

If (iCount > 1) Then
strFirstNote = Format(rstNotes!FCSTDTE, "Short Date") & " " & Mid(rstNotes!PLNRNAME, 11) & " - " & Trim(rstNotes!FCSTNOTE)
ctrl.SetFocus
ctrl = strFirstNote
rstNotes.MoveNext

While (rstNotes.EOF = False)
strNotes = strNotes & " " & Format(rstNotes!FCSTDTE, "Short Date") & " " & Mid(rstNotes!PLNRNAME, 11) & " - " & Trim(rstNotes!FCSTNOTE) & ";"
rstNotes.MoveNext
Wend
ctrl.RowSource = strNotes
End If

If (iCount = 1) Then
strFirstNote = Format(rstNotes!FCSTDTE, "Short Date") & " " & Mid(rstNotes!PLNRNAME, 11) & " - " & Trim(rstNotes!FCSTNOTE)
ctrl.SetFocus
ctrl = strFirstNote
End If

End If
iCount = 0
cn.Close
Me.Command256.SetFocus

End Sub