locked
No value given for one or more required parameters OleDBException RRS feed

  • Question

  • User1979939751 posted

    Hi,

    I'm having this error and any of the post I found so far did not help me find an answer.
    I'm trying to update a record into Access database. The connection string is correct, so is the sql statement. My main problem is that the name of the field i have to update vary depending on the month the user selected. My table has fields named as the 12 months. A user has to make a contribution and depending on the period, I have to update his record.

    So I declare the month name as variable (TheMonth = MonthName(i); i =1 to 12) and the contribution as variable (TheAmount) and I put this:

    Dim ContributionUpdate As OleDbCommand = New OleDbCommand(MySqlStatement, MyConnectionString)
    ContributionUpdate.Parameters.Add(New OleDbParameter("@'" & TheMonth & "'", TheAmount))
    ContributionUpdate.ExecuteNonQuery()

    MySqlStatement is an update query against my database

    When I execute, I'm getting the message "No value given for one or more required parameters" and "In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user."


    How can I solve this and then isn't possible to use a variable for the field name in the command parameters?

    Thanks 

    Friday, January 21, 2011 1:09 AM

Answers

  • User1992938117 posted

    ContributionUpdate.Parameters.Add(New OleDbParameter("@'" & TheMonth & "'", TheAmount))

    why not you are using like below:

    Dim ConnString As String = Utils.GetConnString()
    Dim SqlString As String = "Update Contacts Set FirstName = ?, LastName = ?"
    Using conn As New OleDbConnection(ConnString)
      Using cmd As New OleDbCommand(SqlString, conn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
        cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
        conn.Open()
        cmd.ExecuteNonQuery()
      End Using
    End Using


    Source:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 21, 2011 4:03 AM
  • User1867929564 posted

    Either create dynamic sql .If mdb do not support dynamic sql then
    create if else condition.

    and atleast show one hard coded update statment for particular Member

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 21, 2011 7:16 AM
  • User-1199946673 posted

    Dim MySqlStatement As String = "UPDATE MyTable SET MyTable.'" & TheMonth & "' = ? "

    Remove the single quotes:

    Dim MySqlStatement As String = "UPDATE MyTable SET MyTable." & TheMonth & " = ? "

    But I think the underlying problem is the database design. Instead of using 12 month columns in you table, I would rather use another table where you can store 12 records for each month


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 21, 2011 9:31 AM
  • User1979939751 posted

    I did correct my query by removing the ' around TheMonth and VarYear and it works. The query now looks like this

    Dim MySqlStatement As String = "UPDATE MyTable SET MyTable." & TheMonth & " = ? "
    MySqlStatement = MySqlStatement & "WHERE ((MyTable.emailadr) = '" & VarEmail & "') AND ((MyTable.TheYear) = " & VarYear & ");"

    The field TheYear was receiving numbers.

    Thanks for all your help 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 21, 2011 9:52 AM

All replies

  • User1992938117 posted

    ContributionUpdate.Parameters.Add(New OleDbParameter("@'" & TheMonth & "'", TheAmount))

    why not you are using like below:

    Dim ConnString As String = Utils.GetConnString()
    Dim SqlString As String = "Update Contacts Set FirstName = ?, LastName = ?"
    Using conn As New OleDbConnection(ConnString)
      Using cmd As New OleDbCommand(SqlString, conn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
        cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
        conn.Open()
        cmd.ExecuteNonQuery()
      End Using
    End Using


    Source:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 21, 2011 4:03 AM
  • User1867929564 posted

    You mean to say that this month,you will update
    update table set January=""
    next month it may be Feb and so on.

    write dynamic sql
    or create if else if condition
    If monthname=jan then
    update.........
    else if monthname=feb then
    update...........

    Explain ur requirment again.


    Friday, January 21, 2011 5:41 AM
  • User1979939751 posted

    You mean to say that this month,you will update
    update table set January=""
    next month it may be Feb and so on
     

     

    No. This month I will update the same table but the fiedl January for some members, and the field Febuary for others. Next month, I will update the same table, but the field January for some membes, the field Febuary for others and maybe the field March. The field varies.

     

    Thanks

    Friday, January 21, 2011 7:08 AM
  • User1979939751 posted

  • Dim ConnString As String = Utils.GetConnString()   
  • Dim SqlString As String = "Update Contacts Set FirstName = ?, LastName = ?"  
  • Using conn As New OleDbConnection(ConnString)   
  •   Using cmd As New OleDbCommand(SqlString, conn)   
  •     cmd.CommandType = CommandType.Text   
  •     cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)   
  •     cmd.Parameters.AddWithValue("LastName", txtLastName.Text)   
  •     conn.Open()   
  •     cmd.ExecuteNonQuery()   
  •   End Using   
  • End Using 

 

Ok I can do that. But where you hard coded cmd.Parameters.AddWithValue("LastName", txtLastName.Text), I need "LastName" to vary. It could be January, Febuary or March.

Friday, January 21, 2011 7:11 AM
  • User1867929564 posted

    Either create dynamic sql .If mdb do not support dynamic sql then
    create if else condition.

    and atleast show one hard coded update statment for particular Member

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 21, 2011 7:16 AM
  • User1979939751 posted

    Here is the code I'm using. The variable email (VarEmail) comes from another table where the users information are.

    Dim VarYear As Integer = Year(RegistrationDate)
    Dim TheMonth As String = DropDownListTheMonth.SelectedItem.Text
    Dim TheAmount as String = Trim(TextBoxTheAmount.Text)
    Dim MySqlStatement As String = "UPDATE MyTable SET MyTable.'" & TheMonth & "' = ? "
    MySqlStatement = MySqlStatement & "WHERE ((MyTable.emailadr) = '" & VarEmail & "') AND ((MyTable.TheYear) = '" & VarYear & "');"
    Dim ContributionUpdate As OleDbCommand = New OleDbCommand(MySqlStatement, MyConnectionString)
    ContributionUpdate.Parameters.Add(New OleDbParameter("@" & TheMonth, TheAmount))
    ContributionUpdate.ExecuteNonQuery()

    The error is pointing ContributionUpdate.ExecuteNonQuery()

      

    Friday, January 21, 2011 9:14 AM
  • User-1199946673 posted

    Dim MySqlStatement As String = "UPDATE MyTable SET MyTable.'" & TheMonth & "' = ? "

    Remove the single quotes:

    Dim MySqlStatement As String = "UPDATE MyTable SET MyTable." & TheMonth & " = ? "

    But I think the underlying problem is the database design. Instead of using 12 month columns in you table, I would rather use another table where you can store 12 records for each month


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 21, 2011 9:31 AM
  • User1979939751 posted

    I did correct my query by removing the ' around TheMonth and VarYear and it works. The query now looks like this

    Dim MySqlStatement As String = "UPDATE MyTable SET MyTable." & TheMonth & " = ? "
    MySqlStatement = MySqlStatement & "WHERE ((MyTable.emailadr) = '" & VarEmail & "') AND ((MyTable.TheYear) = " & VarYear & ");"

    The field TheYear was receiving numbers.

    Thanks for all your help 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 21, 2011 9:52 AM
  • User1979939751 posted

    Instead of using 12 month columns in you table, I would rather use another table where you can store 12 records for each month
     

     

    What do you mean by 12 records for each month? There are hundreds of users and the number of users vary from month to month.

    The project if for a community members. New members come while other members left. What was done is to create a table e.g. Mytable to record the monthly contribution. Each user/member has 12 fields (the month), the fields year and email, ... In addition, the table has other fields which are not involved in this particular process. If you have any suggestion about the database design, please let me kow.

    Thanks

    Friday, January 21, 2011 10:04 AM
  • User-1199946673 posted

    Each user/member has 12 fields (the month), the fields year
     

    Create 1 record for each month (+ year) for every user/member in a seperate table:

    Table User:

    UserID
    Name
    Email
    ...

    Table Contribution

    ContributionID
    UserID
    Month
    Year
    Amount
    ...

    Sunday, January 23, 2011 2:32 PM