CODEWITHSUNDEEP
sql-servervb.netsql-server-2008stored-procedures
Srinivasan
Srinivasan

Reputation: 303

Calling SQL Server stored procedure from vb.net 2008 error

I am trying to execute a SQL Server stored procedure from vb.net 2008. But I get the error

Procedure or function 'Sp_Messages_Display' expects parameter '@MsgSno', which was not supplied.

Here is my code

Public Function Load(ByVal CnnStr As String, ByVal MsgSno As Long, ByVal Msg_Status As eMsgStatus) As ADODB.Recordset
    Dim Cnn As ADODB.Connection
    Dim Com As ADODB.Command
    Cnn = New ADODB.Connection
    Cnn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
    Cnn.ConnectionString = CnnStr
    Cnn.Open()
    Com = New ADODB.Command
    Com.ActiveConnection = Cnn

    With Com
        .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
        .CommandText = "Sp_Messages_Display"
        .CreateParameter("@MsgSno", ADODB.DataTypeEnum.adBigInt, ADODB.ParameterDirectionEnum.adParamInput, 4, MsgSno)
        .CreateParameter("@Msg_Status", ADODB.DataTypeEnum.adSmallInt, ADODB.ParameterDirectionEnum.adParamInput, 4, Msg_Status)
        Load = .Execute(RecordsAffected)
        SqlError = Err.Description
    End With

    If Not Load.EOF Then
        With Me
            .MsgSno = Load.Fields("MsgSno").Value
        End With
    End If
    Com.ActiveConnection = Nothing
    Cnn = Nothing
End Function

Please help me where I am wrong. Thanks in advance

Upvotes: 0

Views: 798

Answers (2)

Stefano d'Antonio
Stefano d'Antonio

Reputation: 6152

You need to add parameters to the Command, the CreateParameter only creates a new instance without adding it to the collection of the Command:

With Com
    CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
    .CommandText = "Sp_Messages_Display"
    .Parameters.Add("@MsgSno", ADODB.DataTypeEnum.adBigInt, ADODB.ParameterDirectionEnum.adParamInput, 4, MsgSno)
    .Parameters.Add("@Msg_Status", ADODB.DataTypeEnum.adSmallInt, ADODB.ParameterDirectionEnum.adParamInput, 4, Msg_Status)
    Load = .Execute(RecordsAffected)
    SqlError = Err.Description
End With

I now believe you are not using VB.NET, but VB, in that case this is the way to go from MSDN:

ccmd.parameters.Append ccmd.CreateParameter(, adInteger, adParamReturnValue, , NULL)   ' return value
ccmd.parameters.Append ccmd.CreateParameter("InParam", adVarChar, adParamInput, 20, "hello world")   ' input parameter
ccmd.parameters.Append ccmd.CreateParameter("OutParam", adVarChar, adParamOuput, 20, NULL)   ' output parameter

Upvotes: 2

Richard
Richard

Reputation: 108975

From the documentation to CreateParameter:

This method does not automatically append the Parameter object to the Parameters collection of a Command object.

You need to add the parameter you have created to the collection.

Upvotes: 1

Related Questions