vb.netaccess-data-projectaccessdatasource

Exception thrown when executing query


I have vb.net app form, it contains id as combobox. after click on edit buton & chose an other id, i found error Like the picture below.so what to do?

Private Sub searchparfum()
    Dim dt As New DataTable
    Dim ds As New DataSet
    ds.Tables.Add(dt)
    Dim da As New OleDbDataAdapter("select num from parfum", MaConnection)
    da.Fill(dt)
    Dim r As DataRow
    cmb_parfum.AutoCompleteCustomSource.Clear()
    For Each r In dt.Rows
        cmb_parfum.AutoCompleteCustomSource.Add(r.Item(0).ToString)
    Next
    cmb_parfum.AutoCompleteMode = AutoCompleteMode.SuggestAppend
    cmb_parfum.AutoCompleteSource = AutoCompleteSource.CustomSource
End Sub

Private Sub cmb_parfum_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_parfum.SelectedIndexChanged
    qry = "select * from parfum where num ='" & (cmb_parfum.Text) & "' "
    cmd = New OleDbCommand(qry, MaConnection)
    cmd.ExecuteNonQuery()
    dr = cmd.ExecuteReader
    If dr.Read Then

        txt_num.Text = dr("num")
        lab_nom.Text = dr("nom")
        lab_prix.Text = dr("prix")
        stock_par.Text = dr("stock")
        txt_ventes.Text = dr("ventes")
        photo.Text = dr("photo")

    End If
End Sub

enter image description here


Solution

  • Database object like commands and connection should be declared in the method where they are used so they can be properly disposed. Using...End Using blocks do this for you even if there is an error.

    You would not use cmd.ExecuteNonQuery() for a Select statement. That is used only for Update, Insert or Delete commands.

    You don't want the connection to remain open while you update the user interface. So, we load a data table which does not require the connection to remain open while we use the data. The reader does require an open connection.

    Is photo really a string type?

    Private ConStr As String = "Your connection string"
    
    Private Sub cmb_parfum_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_parfum.SelectedIndexChanged
        Dim dt As New DataTable
        Dim qry = "select * from parfum where num = @Parfum;"
        Using cn As New OleDbConnection(ConStr),
                cmd As New OleDbCommand(qry, cn)
            cmd.Parameters.Add("@Parfum", OleDbType.VarWChar).Value = cmb_parfum.Text
            cn.Open()
            Using dr = cmd.ExecuteReader
                dt.Load(dr)
            End Using
        End Using 'connection is closed and disposed and command is disposed
        If dt.Rows.Count > 0 Then
            txt_num.Text = dt(0)("num").ToString
            lab_nom.Text = dt(0)("nom").ToString
            lab_prix.Text = dt(0)("prix").ToString
            stock_par.Text = dt(0)("stock").ToString
            txt_ventes.Text = dt(0)("ventes").ToString
            photo.Text = dt(0)("photo").ToString
        End If
    End Sub