Question ExcuteReader Error

[Dhagz03]

Good day asking for help.

I'm building a POS System and I always get the error of

ExecuteReader requires an open available Connection. The connection's current state is closed.

Scenario when I scan more than 3 the error is always show.

How to fix error?

My Code :

Private conString As String = "Provider=Microsoft.ace.oledb.12.0;data source= dzre.accdb"

Private Sub txt_barcode_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txt_barcode.KeyPress
    If e.KeyChar = ChrW(Keys.Enter) Then
        Dim scannedValue As String = txt_barcode.Text.Trim()
        If Not String.IsNullOrEmpty(scannedValue) Then
            InsertScannedValue(scannedValue)
            txt_barcode.Clear()
            count_total_price()
            load_pos()
        End If
        e.Handled = True
    End If
End Sub

Private Sub InsertScannedValue(value As String)

    Dim str As String = "Insert into invoice_sale (is_invoice, is_item_name, is_item_qty, is_item_price, is_item_unit, is_item_id, is_item_date) values(@d0, @d1, @d2, @d3, @d4, @d5, @d6)"
    Using connection As New OleDbConnection(conString)
        Using cmd As New OleDbCommand(str, connection)
            cmd.Parameters.AddWithValue("@d0", lbl_invoice.Text)
            cmd.Parameters.AddWithValue("@d1", TextBox3.Text)
            cmd.Parameters.AddWithValue("@d2", TextBox1.Text)
            cmd.Parameters.AddWithValue("@d3", TextBox4.Text)
            cmd.Parameters.AddWithValue("@d4", TextBox5.Text)
            cmd.Parameters.AddWithValue("@d5", TextBox2.Text)
            cmd.Parameters.AddWithValue("@d6", lbl_date.Text)
            Try
                connection.Open()
                Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
                If rowsAffected > 0 Then
                    lblStatus.Text = "Data inserted successfully"
                Else
                    lblStatus.Text = "Failed  to insert data"
                End If

            Catch ex As Exception
                lblStatus.Text = "Error: " & ex.Message
            Finally
                If connection.State = ConnectionState.Open Then
                    connection.Close()
                End If
            End Try
        End Using
    End Using
End Sub

Thank you and advance.

 

[jmcilhinney]

Please be sure to format code snippets. Unformatted code is too hard to read.

 

[jmcilhinney]

Are you sure that it's that code that throws the exception? You're not calling ExecuteReader there and I don't see any code that would display a message box, so I suspect it's actually happening elsewhere.

 

[jmcilhinney]

By the way, your Finally block is pointless because you are creating the connection with a Using statement, so it will be implicitly closed at the end of the block. Also, you don't have to nest multiple Using blocks like that. A single Using statement can create multiple objects. You just need to separate them with a comma, e.g.

Using connection As New OleDbConnection(connectionString),
      command As New OleDbCommand(sql, connection)
    '...'
End Using

Finally, it's very bad that your TextBoxes have the default, generic names. ALWAYS provide descriptive names for controls and everything else. It would be very easy to use the wrong one in the wrong place and not even know it.

 

[Dhagz03]

How do I fix the error?

 

[jmcilhinney]

I don't know. I need more information in order to be able to help you. That's why I asked you a question. Are you going to answer it?

Have you actually debugged your code? If not, do so now, and do so before posting in future. Step through the code and find out exactly where the exception is thrown and the application state at the time. If you're calling ExecuteReader on a command then you can look at the Connection of that command before the call and see if it has a State of Closed. If it does, you can then step through the code line by line before that to see if and where the connection is opened and/or closed.

 

[Dhagz03]

Data connection:
Private conString As String = "Provider=Microsoft.ace.oledb.12.0;data source= dzre.accdb"

The scenario is
Once I scan the barcode of an item
It will save to the database invoice_sale

So I put the code in Keypress

Private Sub txt_barcode_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txt_barcode.KeyPress
If e.KeyChar = ChrW(Keys.Enter) Then
Dim scannedValue As String = txt_barcode.Text.Trim()
If Not String.IsNullOrEmpty(scannedValue) Then
InsertScannedValue(scannedValue)
txt_barcode.Clear()
End If
e.Handled = True
End If
End Sub


And I created Search function . When the Barcode = item_barcode
Display the value in textboxes
txt_product.Text = dr("item_name")
txt_qty.Text = dr("item_qty")
txt_price.Text = dr("item_price")
txt_unit.Text = dr("item_unit")
Database: tbl_item


Then I created "InsertScannedValue" sub to save the scanned data.

Private Sub InsertScannedValue(value As String)

Dim str As String = "Insert into invoice_sale (is_invoice, is_item_name, is_item_qty, is_item_price, is_item_unit, is_item_id, is_item_date) values(@d0, @d1, @d2, @d3, @d4, @d5, @d6)"
Using connection As New OleDbConnection(conString)
Using cmd As New OleDbCommand(str, connection)
cmd.Parameters.AddWithValue("@d0", lbl_invoice.Text)
cmd.Parameters.AddWithValue("@d1", txt_product.Text)
cmd.Parameters.AddWithValue("@d2", txt_qty.Text)
cmd.Parameters.AddWithValue("@d3", txt_price.Text)
cmd.Parameters.AddWithValue("@d4", txt_unit.Text)
cmd.Parameters.AddWithValue("@d5", txt_barcode.Text)
cmd.Parameters.AddWithValue("@d6", lbl_date.Text)
Try
connection.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
If rowsAffected > 0 Then
lblStatus.Text = "Data inserted successfully"
Else
lblStatus.Text = "Failed to insert data"
End If

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Using
End Using

End Sub

 

[jmcilhinney]

I specifically asked you to format your code snippets and you have failed to do so again. If you're not going to help us help you, you make it less likely that we will help you. Also, you just posted all the same code as before but with a few comments. Some more detail is good but you still haven't answered my questions or indicated anything you determined by debugging your code. I suspect that's because you haven't debugged the code, which is a problem in itself. You need to debug your code so, if you don't know how, you should stop what you're doing and learn how now. Even if you still need our help, we need information to work with so you need to debug your code to get that information.