CODEWITHSUNDEEP
vb.netparameters
David
David

Reputation: 11

Adding SQL Parameter fails

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"

Upvotes: 1

Views: 36

Answers (1)

Fabio
Fabio

Reputation: 32445

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

Upvotes: 2

Related Questions