OleDbException: No value given for one or more required parameters

Question

Current issue: when running the asmx file locally to check it's working, the error that keeps coming up is that no value given as can be seen below. I've added in parameters now but the error is still appearing.

However now, I feel that the error isn't in the asmx as people have changed the SQL to run correctly. This is the aspx file where the required information is being passed over. Is it possible that there is an issue with passing the session variables that are set up?

 Dim Secure As New Visa.VISAServices()
    Dim authHeader As New Visa.AuthenticationHeader()

    authHeader.username = "HSBC"
    authHeader.password = "password"
    Secure.AuthenticationHeaderValue = authHeader

    Session("sessionsortnum") = sortnum.Text
    Session("sessionaccount") = account.Text


    'Change type for the sortcode
    Dim sortcode As String = CType(Session.Item("sessionsortnum"), String)
    Dim accountnumber As String = CType(Session.Item("sessionaccount"), String)

    lblVisa.Text = Secure.AuthenticateCardSecure(pin.Text, sortnum.Text, account.Text)

    'NEEDS TO STAY HERE DO NOT MOVE!
    Session("sessioncase") = lblVisa.Text
    Dim selectcase As String = CType(Session.Item("sessioncase"), String)

    'Tests - Being sent to the session var
    testsort.Text = sortcode
    testbank.Text = accountnumber
    testselectc.Text = selectcase

This is how the sessions are being passed from one service that is being connected to and the sortcode, accountnumber and selectcase are all displaying correctly.

This is the file that is calling the asmx:

Private Sub checkbalance(ByVal sender As Object, ByVal e As System.EventArgs)

    Dim sortcode As String = CType(Session.Item("sessionsortnum"), String)
    Dim accountnumber As String = CType(Session.Item("sessionaccount"), String)
    Dim selectcase As String = CType(Session.Item("sessioncase"), String)
    testsort.Text = sortcode
    testbank.Text = accountnumber
    testselectc.Text = selectcase

    Select Case testselectc.Text
        Case "1111111111"
            'Dim lloydsbalance As New Lloyds.
            'screen.Text = lloydsbalance.overdraft(accountnumber)
        Case "2222222222"
            Dim barclaysbalance As New Barclays.Transactions()
            screen.Text = barclaysbalance.overdraft(accountnumber, sortcode)
        Case "3333333333"
            Dim santanderbalance As New Santander.Santander()
            screen.Text = santanderbalance.overdraft(accountnumber, sortcode)
Case "4444444444"
Dim HSBCbalance As New HSBC.HSBC()
screen.Text = HSBCbalance.OverDraft(accountnumber)

        Case "5555555555"
            Dim halifaxbalance As New Halifax.Halifax()
            screen.Text = halifaxbalance.overdraft(accountnumber)
        Case "6666666666"
            Dim natwestbalance As New Natwest.natwest()
            screen.Text = natwestbalance.overdraft(accountnumber, sortcode)

    End Select
End Sub

Original issue: Whenever I run the asmx locally just to check if it is working, I just get an error. I've checked the function I'm invoking, however I can't find the error. I am hoping someone can spot what I have done wrong.

Error:

System.Data.OleDb.OleDbException: No value given for one or more required parameters. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at HSBC.over(Int64 accountnumber) at HSBC.OverDraft(Int64 accountnumber)

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'                                                   Overdraft Call
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

<WebMethod()> Public Function OverDraft(ByVal accountnumber As Long) As String
    Return over(accountnumber)
End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'                                   Overdraft function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'Creates a service web method

Private Function over(ByVal accountnumber As Long) As String
    'Open the connection to the database

    Dim BalanceDr As OleDbDataReader
    Dim connectioncmd As OleDbCommand
    Dim connection As OleDbConnection
    Dim connx As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../database/HSBc.mdb") & ";"
    Dim SQL As String = "SELECT Customer_Overdraft FROM Customer WHERE Account_Number=?"
    Dim overdraft As String
    connection = New OleDbConnection(connx)
    connectioncmd = New OleDbCommand(SQL, connection)
    connection.Open()
    'Create a DataReader that will return information.
    BalanceDr = _
    connectioncmd.ExecuteReader(CommandBehavior.CloseConnection)

    If BalanceDr.Read() Then
        'A row was returned
        overdraft = BalanceDr(0)
    Else
        'A row was not returned
        overdraft = "No Balance For Account Found"
    End If
    BalanceDr.Close()
    Return overdraft
End Function

accountnumber is stored in a session that was brought back from a service created by someone else, I know this is getting passed correctly as on ever page I check that it is the correct one being sent and used.

Anyone have any suggestions? I have also added all the required permissions to the database as well.

Answer 1

Very close; your query:

 "SELECT Customer_Overdraft FROM Customer WHERE Account_Number=?" 

Is a parameterized query; you therefore need to add an OleDbParameter to your command object.

Your function should then be similar to (not near Visual Studio so can't syntax check):

Private Function over(ByVal accountnumber As Long) As String  

   'Open the connection to the database  

    Dim BalanceDr As OleDbDataReader  
    Dim connectioncmd As OleDbCommand  
    Dim connection As OleDbConnection  
    Dim connx As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../database/HSBc.mdb") & ";"  
    Dim SQL As String = "SELECT Customer_Overdraft FROM Customer WHERE Account_Number=?"  
    Dim overdraft As String  
    connection = New OleDbConnection(connx)  
    connectioncmd = New OleDbCommand(SQL, connection) 
    'Add A Parameter!
    connectioncmd.Parameters.AddWithValue("accnumber", accountnumber)

    connection.Open()

    'Create a DataReader that will return information.  
    BalanceDr = _  
    connectioncmd.ExecuteReader(CommandBehavior.CloseConnection)  

    If BalanceDr.Read() Then  
        'A row was returned  
        overdraft = BalanceDr(0)  
    Else  
        'A row was not returned  
        overdraft = "No Balance For Account Found"  
    End If  
    BalanceDr.Close()  
    Return overdraft  
End Function  

I would also suggest you use the using statement. This is especially important as you are not closing your database connection explicitly and are instead relying on the fact that the BalanceDr.Close() will be called. 'Using' will simplify this for you.

Your function would then be similar too:

Private Function over(ByVal accountnumber As Long) As String  

   'Open the connection to the database  

    Dim BalanceDr As OleDbDataReader  
    Dim connectioncmd As OleDbCommand  
    Dim connection As OleDbConnection  
    Dim connx As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../database/HSBc.mdb") & ";"  
    Dim SQL As String = "SELECT Customer_Overdraft FROM Customer WHERE Account_Number=?"  
    Dim overdraft As String  

    Using connection = New OleDbConnection(connx)  
        Using connectioncmd = New OleDbCommand(SQL, connection) 
          'Add A Parameter!
          connectioncmd.Parameters.AddWithValue("accnumber", accountnumber)

          connection.Open()

          'Create a DataReader that will return information.  
          Using BalanceDr = connectioncmd.ExecuteReader()  

            If BalanceDr.Read() Then  
                'A row was returned  
                overdraft = BalanceDr(0)  
            Else  
                'A row was not returned  
                overdraft = "No Balance For Account Found"  
            End If

      End Using 

      End Using
    End Using

    Return overdraft

End Function  

Answer 2

You need to set the command's parameter to your accountnumber that is passed in:

connectioncmd = New OleDbCommand(Sql, connection)
        Dim p As OleDb.OleDbParameter = connectioncmd.CreateParameter()
        p.Value = accoutnumber
        connectioncmd.Parameters.Add(p)