System.Data.OleDb.OleDbException: No value given for one ore more required parameters in ASP.Net

Last Reply on Aug 03, 2017 04:04 AM By zameer

Posted on Aug 02, 2017 01:52 AM

    In the access database I have added one more column and I called this column iCounter with type Number. Then in put this in my code:

 

 

 Using con As New OleDbConnection(ConfigurationManager.ConnectionStrings("TestLocal").ConnectionString)
            Dim cmd As New OleDbCommand("SELECT [tblWorkOrderItem].WorkOrderItemID,[tblWorkOrderItem].bffGrade AS Item,[tblWorkOrderItem].Quantity AS Qty, [tblWorkOrderItem].Description, [tblWorkOrderItem].Note, [iCounter] FROM (tblWorkOrderItem INNER JOIN tblWorkOrder ON tblWorkOrderItem.WorkOrderID = tblWorkOrder.WorkOrderID) WHERE [tblWorkOrderItem].WorkOrderID=@ID", con) With {.CommandType = CommandType.Text}
            Dim myParamCollection As OleDbParameterCollection = cmd.Parameters
            Dim myParam As OleDbParameter = myParamCollection.Add(New OleDbParameter("ID", OleDbType.Integer))
            myParam.Value = Convert.ToInt16(Session("ID"))
            ' Dim myICounter As OleDbParameter = myParamCollection.Add(New OleDbParameter("iCounter", OleDbType.Integer))
            ' myICounter.Value = 0
            Dim selectResults As New DataSet()
            Dim ole_DBAdapter As New OleDbDataAdapter(cmd)
            ole_DBAdapter.Fill(selectResults)
            con.Open()
            If cmd.ExecuteReader.HasRows Then
                GridView1.DataSource = selectResults
                GridView1.DataBind()
            End If
            con.Close()
        End Using

Problem if I add any column into the table and then put this new column in the query I get this error message:

 

System.Data.OleDb.OleDbException: 'No value given for one ore more required parameters." 

 

However this is not to be placed in a Where condition this is just a column to be added into the table. If I remove this from the SQL query it is fine. But I need it because I want to be able to put some log string in the column.  How do I go about resolving this issue?

 

Thanks in advance.

Posted on Aug 02, 2017 02:25 AM

Hi xboxown,

Please Change your sql select query with below query.

SQL

SELECT [tblWorkOrderItem].WorkOrderItemID,[tblWorkOrderItem].bffGrade AS Item,[tblWorkOrderItem].Quantity AS Qty,[tblWorkOrderItem].Description,[tblWorkOrderItem].Note,[tblWorkOrder].[iCounter] FROM tblWorkOrderItem INNER JOIN tblWorkOrder ON tblWorkOrderItem.WorkOrderID = tblWorkOrder.WorkOrderID WHERE [tblWorkOrderItem].WorkOrderID=@ID

 


Posted on Aug 02, 2017 10:56 AM Modified on on Aug 02, 2017 11:09 AM
zameer says:

Hi xboxown,

Please Change your sql select query with below query.

SQL

1
SELECT [tblWorkOrderItem].WorkOrderItemID,[tblWorkOrderItem].bffGrade AS Item,[tblWorkOrderItem].Quantity AS Qty,[tblWorkOrderItem].Description,[tblWorkOrderItem].Note,[tblWorkOrder].[iCounter] FROM tblWorkOrderItem INNER JOIN tblWorkOrder ON tblWorkOrderItem.WorkOrderID = tblWorkOrder.WorkOrderID WHERE [tblWorkOrderItem].WorkOrderID=@ID

 

 I did 100% exactly as you said and I still got the error message:

 

Error message

 

Continuation of the string

 

 

Even this failed

 

 

SELECT [tblWorkOrderItem].[WorkOrderItemID],[tblWorkOrderItem].[bffGrade] AS Item,[tblWorkOrderItem].[Quantity] AS Qty,[tblWorkOrderItem].[Description],[tblWorkOrderItem].[Note],[tblWorkOrder].[iCounter] FROM [tblWorkOrderItem] INNER JOIN [tblWorkOrder] ON [tblWorkOrderItem].[WorkOrderID] = [tblWorkOrder].[WorkOrderID] WHERE [tblWorkOrderItem].[WorkOrderID]

 


Posted on Aug 03, 2017 04:04 AM

Check with the below code.

Using con As New OleDbConnection(ConfigurationManager.ConnectionStrings("TestLocal").ConnectionString)
    Dim cmd As New OleDbCommand("SELECT [tblWorkOrderItem].WorkOrderItemID,[tblWorkOrderItem].bffGrade AS Item,[tblWorkOrderItem].Quantity AS Qty, [tblWorkOrderItem].Description, [tblWorkOrderItem].Note, [iCounter] FROM (tblWorkOrderItem INNER JOIN tblWorkOrder ON tblWorkOrderItem.WorkOrderID = tblWorkOrder.WorkOrderID) WHERE [tblWorkOrderItem].WorkOrderID=@ID", con)
    cmd.CommandType = CommandType.Text
    Dim myParamCollection As OleDbParameterCollection = cmd.Parameters
    Dim myParam As OleDbParameter = myParamCollection.Add(New OleDbParameter("@ID", OleDbType.[Integer]))
    myParam.Value = Convert.ToInt16(Session("ID"))
    ' Dim myICounter As OleDbParameter = myParamCollection.Add(New OleDbParameter("iCounter", OleDbType.Integer))
    ' myICounter.Val
    con.Open() 
    Dim selectResults As New DataSet()
    Dim ole_DBAdapter As New OleDbDataAdapter(cmd)
    ole_DBAdapter.Fill(selectResults)
    GridView1.DataSource = selectResults
    GridView1.DataBind()
    con.Close()
End Using

I have checked with the code and is working in my side.