Accessing "all" declared variables in Class1.vb to many forms of a solution in vb.net

Question

After a series of downloading connectors && some plugins, I finally managed to connect xampp to VB.Net. Though I found it very time-consuming/hassle everytime I put a connection string to each form that requires SQL insert and select statements So I came up with the idea of creating a Class1.vb and place there all the connection strings to xampp. So that everytime I created I new form, all I have to do is to import Class1.vb to each form so I could access the Dim conn As New MySqlConnection which as I have mentioned is declared on Class1.vb

Though, somehow I get connected with MYSQL most of the variables including one that bears the name of the dbtable cannot be access. Let me show you what's inside my Class1.vb

Public Class Class1
Dim conn As New MySqlConnection
Dim command As MySqlCommand
Dim reader As MySqlDataReader

Public Sub mysqlConnect()

If Not conn Is Nothing Then conn.Close()
    conn.ConnectionString = String.Format('connectionString for Dbname,server,uid,pword)
    Try
        conn.Open()

        MessageBox.Show("Connected!")
    Catch x As Exception
        MsgBox(x.Message)
    End Try
    conn.Close()
end sub
end class

Inside Form1.vb

Public Class Form1
Dim newCon as New Class1   


Private Sub Form2_Load
newCon.mysqlConnect()
'Note: mysqlConnect() is a function declared in Class1.vb


Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        conn.Open()
    Catch x As Exception
    End Try
    Dim command As New MySqlCommand(String.Format("INSERT STATEMENT"))
    command.ExecuteNonQuery()
    conn.Close()
    End Sub

But the variables I indicate on Button1_Click aren't working. It's as if it cannot read some functions of the Public Sub Connect() Though, as I have mentioned earlier, the Msgbox("Connected") did work which means that I am totally connected with my DB.

Any ideas, my friends?

Your comments are appreciated. Thanks in advance.

can I see connect from class1? or inseat of connect should be mysqlConnect() — Claudius, Mar 23 '16 at 11:55
conn is closed right after you display "Connected!". Also, conn is a variable of Class1 but you seem to be using an other variable called conn inside Button1. — the_lotus, Mar 23 '16 at 11:59
oh! Sorry I mistyped the mysqlConnect to connect() in newCon it must be `newCon.mysqlConnect()` I'll edit it. Thanks sir. @Claudius — MoonPrincess, Mar 23 '16 at 11:59
may wee part where "some functions of the Public Sub Connect()" — Claudius, Mar 23 '16 at 12:03
@the_lotus its the same `conn` am trying to open in `button1` since as you've said, Sir, I have closed it right after `connected!` I've tried to comment the conn.close() but still has no effect. — MoonPrincess, Mar 23 '16 at 12:03
@Claudius the `database table` sir. Like it said it is connected with `xampp` though it can't save the `insert statement` coz the conn.open() is in redline. So I guess that's the culprit. But I wonder why the `conn.open()` is on redline if it is connected? That's where am having trouble. — MoonPrincess, Mar 23 '16 at 12:06
It's recommended to open a new MySqlConnection before each query and close it right after. I wouldn't suggest have a global conn object. You could have a shared string that has the connection string in instead. — the_lotus, Mar 23 '16 at 12:06
@the_lotus you mean, the glocal object not possible, sir? :( and I have no choice but to open a new msqlconnection each time I create new form? how sad. — MoonPrincess, Mar 23 '16 at 12:22
@MoonPrincess it is possible, just [not recommended](http://stackoverflow.com/questions/4439409/open-close-sqlconnection-or-keep-open). — the_lotus, Mar 23 '16 at 12:26
@the_lotus I see, connection leaks... Thanks for the link and advice sir. — MoonPrincess, Mar 23 '16 at 12:39

score 0 · Answer 1 · answered Mar 23 '16 at 12:43

Two common solutions are: 1) Change your Class1 to have a "Factory method" which will give you an open connection. 2) Wrap your connection inside of your class

Example 1:

Public Class Class1
Dim conn As New MySqlConnection
Dim command As MySqlCommand
Dim reader As MySqlDataReader


Public Shared Function mysqlConnect() AS MySqlConnection

    If Not conn Is Nothing Then conn.Close()
    conn.ConnectionString = String.Format('connectionString for Dbname,server,uid,pword')
    Try
        conn.Open()

        ''MessageBox.Show("Connected!")
    Catch x As Exception
        MsgBox(x.Message)
    End Try
    ''conn.Close() ''This must be done by your calling function now
    ''btw, if you forget, it may cause connection leaks, which are evil
    Return conn
End function
End class

Example 2:

Public Class Class1
Public conn As MySqlConnection
Dim command As MySqlCommand
Dim reader As MySqlDataReader

''open the connection in the constructor
Sub New()
   conn = New MySqlConnection
   conn.ConnectionString = String.Format('connectionString for Dbname,server,uid,pword')
    Try
        conn.Open()

        ''MessageBox.Show("Connected!")
    Catch x As Exception
        MsgBox(x.Message)
    End Try
End Sub

''close the connection in the destructor
Protected Overrides Sub Finalize()
    conn.Close() ''automatically runs when this class is garbage collected
    conn.Dispose()
End Sub
End class

Accessing "all" declared variables in Class1.vb to many forms of a solution in vb.net

1 Answers1