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 Subi 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