NullReferenceException error while assigning connection string to MySQL connection property of a structure

Question

So here's the thing, I have two sets of Functions that's supposed to work similarly, but the second one uses structures. I'm trying to set up my mysql connection using structures. The first one works, it uses public variables declared in another module. The 2nd one does not, it uses a structure declared public in another module. It always gives me the NullReferenceException when setting the ConnectionString property.

First (Working)

Public Function CheckConnection(server As String, port As String, username As String, password As String) As Boolean
    Try
        sqlConnection.ConnectionString = BuildConnectionString(server, port, username, password)
        sqlConnection.Open()
        Return True
    Catch ex As Exception
        Return False
    Finally
        If Not sqlDataReader Is Nothing Then sqlDataReader.Close()
        If sqlConnection.State = ConnectionState.Open Then sqlConnection.Close()
    End Try        
End Function

Second (Not Working)

Public Function CheckConnection(server As String, port As String, username As String, password As String) As Boolean

    Try
        Dim structQuery As New MyQueryStructure
        With structQuery
            .Server = server
            .Port = port
            .Username = username
            .Password = password
            .Connection.ConnectionString = BuildConnectionString(.Server, .Port, .Username, .Password)
        End With
        structQuery.Connection.Open()
        Return True
    Catch ex As Exception
        GenerateErrorReport(ex)
        Return False
    End Try

End Function

Additional codes & variables

Public Function BuildConnectionString(stringHost As String, stringPort As String, stringUsername As String, stringPassword As String)
    Return "Server=" & stringHost & ";Port=" & stringPort & ";Uid=" & stringUsername & ";Pwd=" & stringPassword & ";Allow User Variables=True;Persist Security Info=true;SslMode=none;"
End Function

Public sqlConnection As New MySqlConnection
Public sqlQuery As String
Public sqlCommand As New MySqlCommand
Public sqlDataReader As MySqlDataReader
Public sqlDataAdapter As New MySqlDataAdapter
Public sqlDataset As New DataSet
Public sqlDataTable As New DataTable
Public sqlServer, sqlPort, sqlUsername, sqlPassword As String

Public Structure MyQueryStructure
    Dim Connection As MySqlConnection
    Dim Command As MySqlCommand
    Dim DataReader As MySqlDataReader
    Dim DataAdapter As MySqlDataAdapter
    Dim DataSet As DataSet
    Dim DataTablee As DataTable
    Dim Line As String
    Dim Server As String
    Dim Port As String
    Dim Username As String
    Dim Password As String
End Structure

I can always go back to the first one. But I kind of want to learn how to use Structures. Also, I need my connection string to be changeable during runtime.

Looks like you never initialize the `.Connection` property on your structure. You can't set the `.ConnectionString` property on a null reference. — David, Aug 24 '18 at 15:26
@David please teach me how to do that with the structures. If I'm not mistaken, the reason the first set worked is because I declared `Public sqlConnection As New MySqlConnection`. Using a structure, how do I initialize it? If my assumption is wrong, please explain. — YinYangKim, Aug 24 '18 at 15:35
You should know that you are using those things quite the opposite of how they are intended. Connections, COmmands, Readers should all be created, used and disposed as needed. There is nothing inherently reusable about them — Ňɏssa Pøngjǣrdenlarp, Aug 24 '18 at 15:45

score 1 · Accepted Answer · answered Aug 24 '18 at 15:35

1

You have problem in your code exactly what the error says. You were trying to use the Connection without initializing it. So initialize your struct variable like this :

With structQuery
    .Server = server
    .Port = port
    .Username = username
    .Password = password
    .Connection = New MySqlConnection(BuildConnectionString(.Server, .Port, .Username, .Password))
End With

answered Aug 24 '18 at 15:35

Md. Suman Kabir

5,243
5
25
43

1

Thank you! I still get confused when it comes to handling nulls and initializing variables properly. To everyone, sorry for wasting your time, but I learned from this. – YinYangKim Aug 24 '18 at 15:42

NullReferenceException error while assigning connection string to MySQL connection property of a structure

1 Answers1