hello all,
i have the following VB code which is expected to activate a storred procedure from SQL DB. for some reason i recieve a msg that the procedure does not recieve the inpute parameters.
the code:

Sub Validate_Data()
'check if data is there:
If Cells(5, 6) = "" Then Exit Sub Else

'Locals:
Dim HN As String
Dim EN As String
Dim YN As String
Dim OK As Integer


' retrieve movie parameters:
HN = Cells(6, 5).Value
EN = Cells(7, 5).Value
YN = Cells(8, 5).Value ' should be integer???

' init parameters:
Dim HN_in As ADODB.Parameter 'INPUT
Dim EN_in As ADODB.Parameter 'INPUT
Dim YN_in As ADODB.Parameter 'INPUT
Dim OK_sig As ADODB.Parameter 'OUTPUT

' Command object:
Dim check As New ADODB.command

' Create a connection object. and connect:
Dim ConDB As ADODB.connection
Set ConDB = New ADODB.connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=LENOVO-325FBA33\SQLEXPRESS;INITIAL CATALOG=StagingArena;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
ConDB.Open strConn

' Set Parameters for procedure:
Set check = New ADODB.command
Set HN_in = check.CreateParameter("@MNH", adVarChar, adParamInput, 100)
Set EN_in = check.CreateParameter("@MNE", adVarChar, adParamInput, 100)
Set YN_in = check.CreateParameter("@year", adInteger, adParamInput)
Set OK_sig = check.CreateParameter("@flag", adInteger, adParamOutput)



' Connect and append all parameters to SP:
With check
.ActiveConnection = ConDB
.CommandType = adCmdStoredProc
.CommandText = "SP_Update_Trans"
.Parameters.Append HN_in
.Parameters.Append EN_in
.Parameters.Append YN_in
End With


check.Execute


OK = check.Parameters("@flag")
' execute command:


End Sub

i recieve the following error:
"procedure or function 'SP_Update_Trans' expects '@MNH', which was not supplied

as you can see from the code, it was supplied.

any help will do.
thank you all

#
' Command object:
#
Dim check As New ADODB.command
#

#
' Create a connection object. and connect:
#
Dim ConDB As ADODB.connection
#
Set ConDB = New ADODB.connection
#
Dim strConn As String
#
strConn = "PROVIDER=SQLOLEDB;"
#
strConn = strConn & "DATA SOURCE=LENOVO-325FBA33\SQLEXPRESS;INITIAL CATALOG=StagingArena;"
#
strConn = strConn & " INTEGRATED SECURITY=sspi;"
#
ConDB.Open strConn
#

#
' Set Parameters for procedure:
#
Set check = New ADODB.command

dim check as ADODB.command
set check = new ADODB.command

Looks like your instantiating the command object twice. You can get by using only your first line: that would be late binding.

Or you can do as shown above. That would be early binding.
It's quicker than late binding and is the preferred way.

As far as your error, you might check on the proper inclusion of special characters in strings. Not sure, but it's possible the '@' sign has to be properly inserted in the code. The @ sign has special meaning in certain database engines.

This article has been dead for over six months. Start a new discussion instead.