Adding SQL Parameter fails

Question

This is a new concept for me and I can't quite see what's causing the error I'm attempting to populate a datagridview control from a single field in an Access database (from a combo and Text box source). Using literals works, but not with the parameter.

Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Backend & ";Persist Security Info=False;")
    Dim command As New OleDbCommand("Select Year from tblTest ", conn)
    Dim criteria As New List(Of String)

    If Not cboYear.Text = String.Empty Then
        criteria.Add("Year = @Year")
        command.Parameters.AddWithValue("@Year", cboYear.Text)
    End If

    If criteria.Count > 0 Then
        command.CommandText &= " WHERE " & String.Join(" AND ", criteria)
    End If

    Dim UserQuery As New OleDbDataAdapter(command.CommandText, conn)
    Dim UserRet As New DataTable
    UserQuery.Fill(UserRet)

    With frmDGV.DataGridView2
        .DataSource = UserRet
    End With

    frmDGV.DataGridView2.Visible = True
    frmDGV.Show()

Trying to Fill the datatable shows exception 'No value given for one or more required parameters.'

The value of command.CommandText at that point is "Select Year from tblTest WHERE Year = @Year"

Looks like VB. Add the language-appropriate tag to your question. — PM 77-1
– PM 77-1, Commented May 31, 2015 at 1:47

Fabio · Accepted Answer · 2015-05-31 07:09:45Z

2

Your instance of OleDbDataAdapter was created using two parameters query text and connection.

Dim UserQuery As New OleDbDataAdapter(command.CommandText, conn)

In this case DataAdapter doesn't know about parameters at all.

Instead use constructor with paremeter of type OleDbCommand.

Dim UserQuery As New OleDbDataAdapter(command)

In your code instance of OleDbCommand already associated with connection

edited May 31, 2015 at 7:09

answered May 31, 2015 at 7:04

Fabio

32.5k4 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding SQL Parameter fails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related