Exception thrown when executing query

Question

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

This is a terrible question for numerous reasons. You obviously have not bothered to take the site tour, despite being prompted, to learn how the site works, which means that you have certainly not spent any time in the Help Centre to learn more. You have posted nothing but code in the question and not even formatted it, then you posted a question in the title. You've also not bothered to enter an image description when prompted and you have posted a picture of text rather than the text, which prevents us copying it if we want to search for it. You've also posted two identical screenshots. — jmcilhinney, Jan 31 '21 at 01:30
A proper question provides a full and clear description in the question itself, which includes exactly what you're trying to achieve, how you're trying to achieve it and what happens when you try. Screenshots should only be for clarification. They should never be the only source of anything. Error messages are text and should be posted as such. The last thing you should do is write the title, which should be a summary of the issue, not the question itself. If you can't be bothered to do what you can to help us, you'll find us less enthusiastic to help you. — jmcilhinney, Jan 31 '21 at 01:32

score 0 · Answer 1 · answered Jan 31 '21 at 09:39

As the error message says, you don't have an open connection to the DB. Add an open and close and maybe wrap it in a Try .. Catch too like this:

    cmd = New OleDbCommand(qry, MaConnection)
    Try
        MaConnection.Open()
        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
    Catch ex As Exception
        ' Handle any exception
    Finally
        MaConnection.Close()
    End Try

Also look into using parameters in your SQL Commands too

score 0 · Accepted Answer · answered Feb 01 '21 at 01:43

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

Exception thrown when executing query

2 Answers2