Connection must be valid and opens

Question

Here my real code.when I debug I have error Connection must be valid and opens.

Error on line testNamebox.Text = cmd.ExecuteScalar()

Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            Dim totalResultEng As Int16 = 0
            Dim totalResultFr As Int16 = 0
            Dim time As Int16 = 0
            frmSavingData.Show()
            For i As Integer = 0 To Me.DataGridView1.RowCount - 1
                If Me.DataGridView1.Rows(i).Cells(2).Value = "Polynucléaires neutrophiles" Or Me.DataGridView1.Rows(i).Cells(2).Value = "Polynucléaires éosinophiles" Or _
                    Me.DataGridView1.Rows(i).Cells(2).Value = "Polynucléaires basophiles" Or Me.DataGridView1.Rows(i).Cells(2).Value = "Lymphocytes" Or _
                   Me.DataGridView1.Rows(i).Cells(2).Value = "Monocytes" Then
                    totalResultEng = totalResultEng + Val(Me.DataGridView1.Rows(i).Cells(4).Value)
                    totalResultFr = totalResultFr + Val(Me.DataGridView1.Rows(i).Cells(3).Value)
                    time = time + 1
                End If
            Next
            If totalResultEng <> 100 And time = 5 Then
                MsgBox("Please Check English Resultn Of Test Hg !", MsgBoxStyle.OkOnly, "Total Result")
                Exit Sub
            End If
            If totalResultFr <> 100 And time = 5 Then
                MsgBox("Please Check French Result Of Test Hg !", MsgBoxStyle.OkOnly, "Total Result")
                Exit Sub
            End If
            Dim cmd As MySqlCommand, bold As Integer = 0, checkprint As Int16 = 0
            Dim testNamebox As New RichTextBox
            testNamebox.Width = 500
            testNamebox.Height = 50
            For i As Integer = 0 To Me.DataGridView1.RowCount - 1
                Dim fr_result As String
                Dim eng_result As String
                cmd = New MySqlCommand("select testName from Testdetail where testid =" & Me.DataGridView1.Rows(i).Cells(2).Tag, Setting.cn)
                ' cmd = New MySqlCommand("select testName from Testdetail where testid ='" & Me.DataGridView1.Rows(i).Cells(2).Tag & "'", Setting.cn)
                testNamebox.Text = cmd.ExecuteScalar()
                If testNamebox.Lines.GetUpperBound(0) > 0 Then  'control number of lines in RichTextBox
                    testNamebox.Text = Me.DataGridView1(3, i).Value.ToString
                    If testNamebox.Lines.GetUpperBound(0) > 0 Then
                        If IsNothing(Me.DataGridView1(4, i).Value) Then eng_result = "" Else eng_result = Me.DataGridView1(4, i).Value.ToString
                        If IsNothing(Me.DataGridView1(3, i).Value) Then fr_result = "" Else fr_result = Me.DataGridView1(3, i).Value.ToString
                    Else
                        If IsNothing(Me.DataGridView1(4, i).Value) Then eng_result = "" Else eng_result = "" + vbCrLf + Me.DataGridView1(4, i).Value.ToString
                        If IsNothing(Me.DataGridView1(3, i).Value) Then fr_result = "" Else fr_result = "" + vbCrLf + Me.DataGridView1(3, i).Value.ToString
                    End If
                Else
                    If IsNothing(Me.DataGridView1(4, i).Value) Then eng_result = "" Else eng_result = Me.DataGridView1(4, i).Value.ToString
                    If IsNothing(Me.DataGridView1(3, i).Value) Then fr_result = "" Else fr_result = Me.DataGridView1(3, i).Value.ToString
                End If

                If Me.DataGridView1(7, i).Value = True Then bold = 1
                If Me.DataGridView1(8, i).Value = True Then checkprint = 1
                cmd = New MySqlCommand("Update invoicedetail set EngResult='" & _
                                            eng_result.ToString.Replace("\", "\\").Replace("'", "''") & "', FrResult='" & fr_result.ToString.Replace("\", "\\").Replace("'", "''") & _
                                            "',Bold='" & bold & "',checkprint='" & checkprint & "' where testid='" & Me.DataGridView1.Rows(i).Cells(2).Tag & "' and invoiceid='" & Me.DataGridView1.Rows(i).Cells(0).Tag & "'", Setting.cn)
                cmd.ExecuteNonQuery()
                If Me.DataGridView1.Rows(i).Cells(0).Value <> "" And Me.DataGridView1.Rows(i).Cells(8).Value = False Then
                    cmd = New MySqlCommand("Update invoicedetail set checkprint=0 where  TestID = '" & Me.DataGridView1.Rows(i).Cells(2).Tag & "' and invoiceid='" & Me.DataGridView1.Rows(i).Cells(0).Tag & "'", Setting.cn)
                    cmd.ExecuteNonQuery()
                ElseIf Me.DataGridView1.Rows(i).Cells(0).Value <> "" And Me.DataGridView1.Rows(i).Cells(8).Value = True Then
                    cmd = New MySqlCommand("Update invoicedetail set checkprint=1 where  TestID = '" & Me.DataGridView1.Rows(i).Cells(2).Tag & "' and invoiceid='" & Me.DataGridView1.Rows(i).Cells(0).Tag & "'", Setting.cn)
                    cmd.ExecuteNonQuery()
                End If
                cmd.Dispose()
                bold = 0
                checkprint = 0
                frmSavingData.Refresh()
            Next
            cmd = New MySqlCommand("Update invoicedetail set  EngObservation=" & IIf(txtEnglishObservation.Text <> "", "'" & txtEnglishObservation.Rtf.Replace("'", "''").Replace("\", "\\") & "'", "NULL") & _
                                           ", FrObservation=" & IIf(txtFrenchObservation.Text <> "", "'" & txtFrenchObservation.Rtf.Replace("'", "''").Replace("\", "\\") & "'", "NULL") & _
                                           " where testid='" & TestIDforupdate & "' and invoiceid='" & InvoiceNo & "'", Setting.cn) 'TestID
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            '' MsgBox("Save successed!")
            'Me.Close()
            frmSavingData.Dispose()
        Catch ex As Exception
            frmSavingData.Dispose()
            MsgBox(ex.Message)
        End Try
    End Sub

A title and a big block of code is not how you post a question on this site. Please spend some time in the Help Center to learn how to post an appropriate question. That would include a FULL and CLEAR explanation of the problem along with the RELEVANT code. — jmcilhinney, Oct 07 '19 at 08:42
If you use [`Option Strict On`](https://stackoverflow.com/a/29985039/1115360) then Visual Studio will be able to help you with some aspects of the code. — Andrew Morton, Oct 07 '19 at 10:05
Do not post code as an image. Copy and paste the text or type it in if you must. — Mary, Oct 08 '19 at 10:30
Please tell me that Setting.cn doesn't return an Open connection. If you separated your user interface code from your database code and kept you database objects local to your procedure it would be much easier to see what was wrong. — Mary, Oct 08 '19 at 10:35
`if testNamebox.Text=If textNamebox.Lines.GetUpperBound(0) > 0 Then` What in the world is this line??? This doesn't have a chance of compiling so how could you have gotten any error. Show at least the bones of the real code. — Mary, Oct 08 '19 at 10:42
BTW, both cmd.ExecuteScalar and .Tag return objects. I don't see any conversions or casts. — Mary, Oct 08 '19 at 10:47
What is cmd.ExecuteNonQuery doing. The only command text I see is a Select statement. — Mary, Oct 08 '19 at 10:48

score 1 · Answer 1 · answered Oct 07 '19 at 08:51

It's difficult to tell from your question exactly what the error is that you are having, but here's a guess.

In your code, you have:

cmd = New MySqlCommand("select testName from Testdetail where testid =" & Me.DataGridView1.Rows(i).Cells(2).Tag, Setting.cn)
testNamebox.Text = cmd.ExecuteScalar()

I'm assuming that Setting.cn is your connection string. If so, it looks like you are never opening or closing your connection.

Try this:

    cmd = New MySqlCommand("select testName from Testdetail where testid =" & Me.DataGridView1.Rows(i).Cells(2).Tag, Setting.cn)
    Try
        cmd.Connection.Open()
        testNamebox.Text = cmd.ExecuteScalar()
    Catch ex As Exception
        'Handle Error
        testNamebox.Text = ""
    Finally
        cmd.Connection.Close()
    End Try

I added this line cmd.Connection.Open(), but still have this error. — Vannra Btb, Oct 07 '19 at 08:56
you will still have the error because executescalar is related to the query string which in this solution single quotes are missing for the `Me.DataGridView1.Rows(i).Cells(2).Tag` value — OctaCode, Oct 07 '19 at 11:46

Connection must be valid and opens

1 Answers1