none
Fill: SelectCommand.Connection property has not been initialized.

    Question

  • All, 

    I'm new to this page and I'm learning to use MySQL as my DB through VB.NET2010. When my App tries to connect to the DB it gives this error:

    Fill: SelectCommand.Connection property has not been initialized.

    Here is the code:

    Imports MySql.Data.MySqlClient
    Imports System
    
    Public Class DatabaseFunctions
    
        Shared connect As MySqlConnection
    
        Public Shared Sub CreateConnection()
    
            Dim connect As MySqlConnection
            connect = New MySqlConnection()
            connect.ConnectionString = "server=localhost;user id=root;Password=M3g5;database=memoria"
            connect.Open()
    
        End Sub
    
        Private Shared Sub CheckConnection()
    
            If connect Is Nothing OrElse connect.State = ConnectionState.Closed Then
                CreateConnection()
            End If
    
        End Sub
    
        Public Shared Function GetDataReader(ByVal SQL As String) As MySqlDataReader
    
            CheckConnection()
            Dim cmd As New MySqlCommand(SQL, connect)
            Dim dr As MySqlDataReader
            dr = cmd.ExecuteReader
            Return dr
    
        End Function
    
        Public Shared Function GetDataTable(ByVal SQL As String) As DataTable
    
            CheckConnection()
            Dim cmd As New MySqlCommand(SQL, connect)
            Dim table As New DataTable
            Dim da As New MySqlDataAdapter(cmd)
    
            da.Fill(table)
            Return table
    
        End Function
    
        Public Shared Sub ExecuteQuery(ByVal SQL As String)
    
            CheckConnection()
            Dim cmd As New MySqlCommand(SQL, connect)
            cmd.ExecuteNonQuery()
    
        End Sub
    End Class
    

    Thank you in advance.

    Wednesday, March 13, 2013 11:52 AM

Answers

  • In your GetDataTable Function Execute non query is not called

    In your ExecuteQuery Function does not do anything with the query.
    • Edited by Greg Colborne Wednesday, March 13, 2013 11:56 AM
    • Marked as answer by theValkyrie Wednesday, March 13, 2013 12:18 PM
    Wednesday, March 13, 2013 11:55 AM
  • Remove in this code the 

    Dim connect, now you create beside the global one also a local one which goes out of scope at the end of the method.

     Public Shared Sub CreateConnection()
    
            Dim connect As MySqlConnection
            connect = New MySqlConnection()
            connect.ConnectionString = "server=localhost;user id=root;Password=M3g5;database=memoria"
            connect.Open()
    
        End Sub


    Success
    Cor

    • Marked as answer by theValkyrie Wednesday, March 13, 2013 12:18 PM
    Wednesday, March 13, 2013 12:12 PM

All replies

  • In your GetDataTable Function Execute non query is not called

    In your ExecuteQuery Function does not do anything with the query.
    • Edited by Greg Colborne Wednesday, March 13, 2013 11:56 AM
    • Marked as answer by theValkyrie Wednesday, March 13, 2013 12:18 PM
    Wednesday, March 13, 2013 11:55 AM
  • Thanks for the quick response.. I added it to the code, as you can tell I am a complete newbie when it comes to database. 

    Here's what it looks like now.

    Imports MySql.Data.MySqlClient
    Imports System
    
    Public Class DatabaseFunctions
    
        Shared connect As MySqlConnection
    
        Public Shared Sub CreateConnection()
    
            Dim connect As MySqlConnection
            connect = New MySqlConnection()
            connect.ConnectionString = "server=localhost;user id=root;Password=M3g5;database=memoria"
            connect.Open()
    
        End Sub
    
        Private Shared Sub CheckConnection()
    
            If connect Is Nothing OrElse connect.State = ConnectionState.Closed Then
                CreateConnection()
            End If
    
        End Sub
    
        Public Shared Function GetDataReader(ByVal SQL As String) As MySqlDataReader
    
            CheckConnection()
            Dim cmd As New MySqlCommand(SQL, connect)
            Dim dr As MySqlDataReader
            dr = cmd.ExecuteReader
            Return dr
    
        End Function
    
        Public Shared Function GetDataTable(ByVal SQL As String) As DataTable
    
            CheckConnection()
            Dim cmd As New MySqlCommand(SQL, connect)
            cmd.ExecuteNonQuery()
            Dim table As New DataTable
            Dim da As New MySqlDataAdapter(cmd)
    
            da.Fill(table)
            Return table
    
        End Function
    
        Public Shared Sub ExecuteQuery(ByVal SQL As String)
    
            CheckConnection()
            Dim cmd As New MySqlCommand(SQL, connect)
            cmd.ExecuteNonQuery()
    
        End Sub
    End Class
    

    Have I added it in the correct line? 

    The first error went away and now the error is : Connection must be valid and open

    Wednesday, March 13, 2013 12:11 PM
  • Remove in this code the 

    Dim connect, now you create beside the global one also a local one which goes out of scope at the end of the method.

     Public Shared Sub CreateConnection()
    
            Dim connect As MySqlConnection
            connect = New MySqlConnection()
            connect.ConnectionString = "server=localhost;user id=root;Password=M3g5;database=memoria"
            connect.Open()
    
        End Sub


    Success
    Cor

    • Marked as answer by theValkyrie Wednesday, March 13, 2013 12:18 PM
    Wednesday, March 13, 2013 12:12 PM
  • Removed.

    Here's what it is now:

    Imports MySql.Data.MySqlClient
    Imports System
    
    Public Class DatabaseFunctions
    
        Shared connect As MySqlConnection
    
        Public Shared Sub CreateConnection()
    
            connect = New MySqlConnection()
            connect.ConnectionString = "server=localhost;user id=root;Password=M3g5;database=memoria"
            connect.Open()
    
        End Sub
    
        Private Shared Sub CheckConnection()
    
            If connect Is Nothing OrElse connect.State = ConnectionState.Closed Then
                CreateConnection()
            End If
    
        End Sub
    
        Public Shared Function GetDataReader(ByVal SQL As String) As MySqlDataReader
    
            CheckConnection()
            Dim cmd As New MySqlCommand(SQL, connect)
            Dim dr As MySqlDataReader
            dr = cmd.ExecuteReader
            Return dr
    
        End Function
    
        Public Shared Function GetDataTable(ByVal SQL As String) As DataTable
    
            CheckConnection()
            Dim cmd As New MySqlCommand(SQL, connect)
            cmd.ExecuteNonQuery()
            Dim table As New DataTable
            Dim da As New MySqlDataAdapter(cmd)
    
            da.Fill(table)
            Return table
    
        End Function
    
        Public Shared Sub ExecuteQuery(ByVal SQL As String)
    
            CheckConnection()
            Dim cmd As New MySqlCommand(SQL, connect)
            cmd.ExecuteNonQuery()
    
        End Sub
    End Class
    Wednesday, March 13, 2013 12:17 PM
  • What cor said!

    Just to put it in practical terms the connection object only has meaning inside the sub.

    If your going to have shared functions you can not have a global variable as cor sudjested thou.

    You need to turn the sub into a Function as mysqlConnection and return Connect
    like this:
     Public Shared Function CreateConnection() As MySqlConnection
    
            Dim connect As MySqlConnection
            connect = New MySqlConnection()
            connect.ConnectionString = "server=localhost;user id=root;Password=M3g5;database=memoria"
            connect.Open()
    
            return Connect
    
        End Sub

    And you have to change GetTableData to use that connection
        Public Shared Function GetDataTable(ByVal SQL As String) As DataTable
    
            Dim Connect as mysqlconnection = CheckConnection()
            Dim cmd As New MySqlCommand(SQL, connect)
            cmd.ExecuteNonQuery()
            Dim table As New DataTable
            Dim da As New MySqlDataAdapter(cmd)
    
            da.Fill(table)
            Return table
    
        End Function


    Wednesday, March 13, 2013 12:18 PM