none
No value given for one or more required parameters- error - oledb to Access.

    Question

  • Hi,

    Below is the code i am trying to execute against MSAccess database to return a dataset.

    And i get the error "No value given for one or more required parameters".

    strMSUSSQL = "SELECT [Fee Amount] FROM " + strTable + " WHERE"

    'get the location text.

    Select Case strPOS

    Case "3", "4", "5", "A"

    strLocationTextVal = "SP"

    Case Else

    strLocationTextVal = "GP"

    End Select

    strLocationText = " [Location Text] = '" + strLocationTextVal + "'"

    strSQL = strMSUSSQL + strLocationText

    strTOSVal = " AND [Type Of Service] = '" + strTOS + "'"

    strSQL = strSQL + strTOSVal

    strProc = " AND [Procedure Code] = '" + strProcCD + "'"

    strSQL = strSQL + strProc

    objDA = New OleDbDataAdapter(strSQL, strAccessCnstr)

    objDS = New DataSet

    objDA.Fill(objDS)

    If Not objDS Is Nothing Then

    If objDS.Tables.Count > 0 Then

    MsgBox(objDS.Tables(0).Rows.Count)

    End If

    End If

    I also tried using oledbcommand object with parameters, but the parameters doesnt seem to apply, the query was returning the entire table. Here is that code.

    objCmd = New OleDbCommand()

    With objCmd

    .Connection = objConn

    strMSUSSQL = "SELECT [Fee Amount] FROM " + strTable + " WHERE"

    .CommandText = strMSUSSQL

    .Parameters.Add("[Location Text]", OleDbType.VarChar)

    .Parameters(0).Value = strLocationTextVal

    .Parameters.Add("[Type Of Service]", OleDbType.VarChar)

    .Parameters(1).Value = strTOS

    .Parameters.Add("[Procedure Code]", OleDbType.VarChar)

    .Parameters(2).Value = strProcCD

    .Parameters.Add("[Begin Date]", OleDbType.Date)

    .Parameters(3).Value = "#7/1/2003#"

    oledr = .ExecuteReader()

    End With

    Any help would be greatly appreciated.

    Thanks.

    Wednesday, July 05, 2006 5:15 PM

Answers

  • Yes, that error message has nothing to do with the usage of a reserved words. The use of reserved words will generate a syntax error.

    When using OLEDB with ADO.NET named parameters are unsupported. Instead, remove the parameter names from your SQL statement and replace each with a question mark. In addition, you can remove the parameter names when adding parameters to the Parameter collection. Just make certain to add them to the collection in the order which corresponds to their order in the SQL statement.

    As I mentioned in my previous post, you may want to dump out the resulting SQL statement (strSQL) and post it here so we can take a look at it.

    Monday, July 10, 2006 4:53 PM

All replies

  • At top where in the Code do you assign the value to strTable?  The message you recieved is usally because one of the fields you are querying is not in the table.


    You are doing parameters wrong at the bottom.  It should be like this:

    strMSUSSQL = "SELECT [Fee Amount] FROM " + strTable + " WHERE [Location Text] = @LocationText"

    .CommandText = strMSUSSQL

    .Parameters.Add("@LocationText", OleDbType.VarChar)

    .Parameters(0).Value = strLocationTextVal


    Last bit of advice is to avoid spaces in column names, because it makes you have use [] which is a pain IMO.


    Wednesday, July 05, 2006 5:29 PM

  • The error indicates that there are one or more column names that cannot be resolved (not present in the table specified). I would dump out the resulting SQL statement when the error occurs and verify that it is correct and that all the columns are present in the specified table.
    Wednesday, July 05, 2006 5:57 PM
  • It could be also in a case if one of the fields or table name is a reserved word. In this case you need to put the names into square brackets insid of your SQL statement
    Thursday, July 06, 2006 2:27 AM
    Moderator
  • Thanks for all your inputs.

    I changed the code based on your suggestions as below.

    strMSUSSQL = "SELECT [Fee Amount] FROM " + strTable + " WHERE"

    strLocationText = " [Location Text] = @LocationText"

    strTOSVal = "[Type Of Service] = @TypeOfService"

    strProc = "[Procedure Code] = @ProcedureCode"

    strSQL = strMSUSSQL + strLocationText + " AND " + strTOSVal + " AND " + strProc

    objCmd = Nothing

    objCmd = New OleDbCommand()

    With objCmd

    .Connection = objConn

    .CommandText = strSQL

    .Parameters.Add("@LocationText", OleDbType.VarChar)

    .Parameters(0).Value = strLocationTextVal

    .Parameters.Add("@TypeOfService", OleDbType.VarChar)

    .Parameters(1).Value = strTOS

    .Parameters.Add("@ProcedureCode", OleDbType.VarChar)

    .Parameters(2).Value = strProcCD

    oledr = .ExecuteReader()

    If oledr.HasRows Then

    While oledr.Read()

    If Not IsDBNull(oledr.Item(0)) Then

    GetPrevailingFee = CDec(oledr.Item(0))

    'Exit Function

    End If

    End While

    End If

    End With

    - As you can see, i did put all the field names with spaces in [] and also changed the way parameters are added. But i still get the same error message "one or More Required Parameters missing",

    Any more suggestions, please?

    Thanks.

     

    Monday, July 10, 2006 1:52 PM
  • Yes, that error message has nothing to do with the usage of a reserved words. The use of reserved words will generate a syntax error.

    When using OLEDB with ADO.NET named parameters are unsupported. Instead, remove the parameter names from your SQL statement and replace each with a question mark. In addition, you can remove the parameter names when adding parameters to the Parameter collection. Just make certain to add them to the collection in the order which corresponds to their order in the SQL statement.

    As I mentioned in my previous post, you may want to dump out the resulting SQL statement (strSQL) and post it here so we can take a look at it.

    Monday, July 10, 2006 4:53 PM
  • I also get this error as I try to use the new VB.NET 2005 binding navigator control with an access database.  the odd this is that this works on the same database in a different solution.  Is there a file or registry entry someplace where I may need to delete a value in order to reset the connection?  that's all I can think of.  I've included a few lines from the exception detail information:

    System.Data.OleDb.OleDbException was unhandled
      ErrorCode=-2147217904
      Message="No value given for one or more required parameters."
      Source="Microsoft JET Database Engine"
      StackTrace:
           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 System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

    These are all part of the underlying control logic used by the binding navigator.

    Wednesday, October 04, 2006 11:29 PM

  • Can you post the SQL command that is executing when the error occurs? I would assume it's probably your insert or update command.
    Thursday, October 05, 2006 12:25 PM
  • Hi kbradl1

    I fall into the same situation. Your answer also help me to solve the problem.

    Thanks

    Chaiwat

    Wednesday, June 05, 2013 7:22 AM