none
Error caliing Sybase SP from VB.net RRS feed

  • Question

  • Hi,

     

    When calling a sybase SP from VB.Net I am getting error "No value given for one or more required parameters".

    I hv only 2 input params as fromDt and ToDate, and need data back in Dataset.

     

    Pls look the code below. Anybody can help ????

     

    Even same SP I am not able to call thur datareader as well

     

    Try

     

    Dim oleCommand As New OleDbCommand()

    Dim oleParam1 As New OleDbParameter()

    Dim oleParam2 As New OleDbParameter()

    Dim oleAdapator As New OleDbDataAdapter() '(sSQL, SybaseConn)

    Dim newDataSet As New DataSet()

    Dim newDT As New DataTable("Monthly")

     

    oleCommand.Connection = SybaseConn

    oleCommand.CommandType = CommandType.StoredProcedure

    oleCommand.CommandText = "{Call ProductDB..QBSMonthlyProcess (?,?)}"

    oleAdapator.SelectCommand = oleCommand

     

    oleParam1.ParameterName = "@FromDate"

    oleParam1.DbType = DbType.Date

    oleParam1.Direction = ParameterDirection.Input

    oleParam1.Value = Trim(frmDate) 'CType(Trim(frmDate), DateTime).Date

    'oleParam1.Size = 10

    oleCommand.Parameters.Add(oleParam1)

     

    oleParam2.ParameterName = "@ToDate"

    oleParam2.DbType = DbType.Date

    oleParam2.Direction = ParameterDirection.Input

    oleParam2.Value = Trim(toDate) 'CType(Trim(toDate), DateTime).Date

    'oleParam2.Size = 10

    oleCommand.Parameters.Add(oleParam2)

    oleAdapator.MissingMappingAction = MissingMappingAction.Passthrough

    oleAdapator.MissingSchemaAction = MissingSchemaAction.Ignore

    oleAdapator.Fill(newDT)

    RetrieveSybaseDataInDataSetWthParam = newDataSet

    Catch ErrorHandler As Exception

    Err.Raise(Err.Number, Err.Source, Err.Description)

    End

    Finally

    End Try

     

    TIA,

    Amit

    Thursday, December 20, 2007 6:33 PM

Answers

  • You need to specify only name of the stored procedure for the CommandText, without using Call and without specifying parameters. You also need to convert your date strings into dates. Your code should look like

     

    Dim oleCommand As New OleDbCommand()

    Dim oleParam1 As New OleDbParameter()

    Dim oleParam2 As New OleDbParameter()

    Dim oleAdapator As New OleDbDataAdapter() '(sSQL, SybaseConn)

    Dim newDataSet As New DataSet()

    Dim newDT As New DataTable("Monthly")

     

    oleCommand.Connection = SybaseConn

    oleCommand.CommandType = CommandType.StoredProcedure

    oleCommand.CommandText = "ProductDB..QBSMonthlyProcess"

    oleAdapator.SelectCommand = oleCommand

     

    oleParam1.ParameterName = "@FromDate"

    oleParam1.DbType = DbType.Date

    oleParam1.Direction = ParameterDirection.Input

    oleParam1.Value = Convert.ToDateTime(Trim(frmDate))

    oleCommand.Parameters.Add(oleParam1)

     

    oleParam2.ParameterName = "@ToDate"

    oleParam2.DbType = DbType.Date

    oleParam2.Direction = ParameterDirection.Input

    oleParam2.Value = Convert.ToDateTime(Trim(toDate))

    oleCommand.Parameters.Add(oleParam2)

     

    oleAdapator.Fill(newDT)

     

     

    Friday, December 21, 2007 2:28 AM
    Moderator