Connection must be valid and open VB.net error using vb.net

Question

This is my code for read data from EXCEL file using ODBC driver and write in MySql Database.

Public Class WebForm3
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim MySqlCmd = New SqlCommand()

        ' Dim dialog As New System.Windows.Forms.OpenFileDialog()
        'Dim dialog As New OpenFileDialog()
        'dialog.Filter = "Excel files |*.xls;*.xlsx"
        'dialog.InitialDirectory = "C:\"
        'dialog.Title = "Select file for import"
        'If dialog.ShowDialog() = DialogResult.OK Then
        Try
            Dim dt As DataTable
            Dim buff0 As String
            Dim buff1 As String
            Dim buff2 As String

            dt = ImportExceltoDatatable("C:\\Book1.xls")

            For i = 0 To dt.Rows.Count - 1
                buff0 = dt.Rows(i)(0)
                buff1 = dt.Rows(i)(1)
                buff2 = dt.Rows(i)(2)

                Dim connStr As String = "server=localhost;user=root;database=ajaxsamples;port=3306;password=innoera;"
                Dim connMysql As MySqlConnection = New MySqlConnection(connStr)
                Dim sql As String = "INSERT INTO ajaxsamples.customers  VALUES('" & buff0 & "','" & buff1 & "','" & buff2 & "')"
                Dim cmd As MySqlCommand = New MySqlCommand(sql, connMysql)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                connMysql.Close()
            Next
        Catch ex As Exception
            MsgBox(Err.Description, MsgBoxStyle.Critical)
        End Try

        'End If
    End Sub


    Public Shared Function ImportExceltoDatatable(filepath As String) As DataTable
        ' string sqlquery= "Select * From [SheetName$] Where YourCondition";
        Dim dt As New DataTable
        Try
            Dim ds As New DataSet()
            Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties=""Excel 12.0;HDR=YES;"""
            Dim con As New OleDbConnection(constring & "")

            con.Open()
            Dim myTableName = con.GetSchema("Tables").Rows(0)("TABLE_NAME")
            Dim sqlquery As String = String.Format("SELECT * FROM [{0}]", myTableName)
            'Dim myTableName = con.GetSchema("Tables").Rows(0)("TABLE_NAME")
            'Dim sqlquery As String = String.Format("SELECT * FROM Sheet1$") ' "Select * From " & myTableName

            Dim da As New OleDbDataAdapter(sqlquery, con)
            da.Fill(ds)

            dt = ds.Tables(0)
            Return dt
        Catch ex As Exception
            MsgBox(Err.Description, MsgBoxStyle.Critical)
            Return dt
        End Try
    End Function

End Class

I got this error,

"Connection must be valid and open "

whats wrong in code? I am newbie for VB. Any help would be appreciated.

SQL Injection vulnerabilities: never add external values to a SQL command with string concatenation. Always use parameters. — Richard

Nimitz E. Nimitz E. · Accepted Answer · 2016-07-26T21:35:06

You forgot to open your connection.

Try
    connMysql = New MySqlConnection
    connMysql.ConnectionString = connStr
    connMysql.Open() 'You forgot to open your connection
    sql = "SELECT * FROM users"

    cmd = New MySqlCommand(sql, connMysql)
    cmd.ExecuteNonQuery()
    cmd.Dispose()
Catch ex As Exception
    'your error code here
Finally
    connMysql.Close() 'close your connection
End Try

Connection must be valid and open VB.net error using vb.net

1 Answers