Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Public MySQL Connection

Public MySQL Connection

Hi, I'm fairly new to, but I'm curious if it's possible to create a MySQL Connection and make it Public/Global to where multiple Subs/source files could use it. Any solutions would be greatly appreciated!

RE: Public MySQL Connection

Yes it is. Right-click the project in Solution Explorer, then select Add->Module. Give the module a name and click Add. You can the create the connection object there, as Public, and it will be visible to the entire project. When referencing global variables in modules, I like to include the module name (e.g., Module1.Connection1), even though the module name is not required. It just keeps things clear, that the connection is defined in a module.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

RE: Public MySQL Connection

This is just one way of doing this. There are a number of ways to approach what you want.
Generally I create a new VB.Net App_code module containing a Class named:
Public Class DataClass

And within that class I have 3 separate Functions to use anywhere in my code;
1) GetDataSet(ByVal SQLQueryStr as String) ' <-- Returns Records from Database
2) ExecuteNonQuery(ByVal SQLQueryStr as String) ' <-- Runs Updates/Deletes/Etc. not requiring Record(s) Return
3) GetDataReader(ByVal SQLQueryStr As String) ' <-- Used as part of Gridview ObjectDataSource SelectMethod

Each of those contains the appropriate Connect String, but in addition each one is used for different types of SQL Operations against a 'backend' database.

The Functions are then called anywhere within the application

CODE -->

' --- Return a set of Records ---
 Dim ds As DataSet
 Dim dt As DataTable
 ds = DataClass.GetDataSet(<SQL Query String>)
 dt = ds.Tables(0)
 If dt.Rows.Count >= 1 Then
   < do something with the record(s) >
   ' --- No Records Returned ---

That way not only the Connection string is 'seen' as public, but also all the other code necessary to perform the desired operation - which is often used multiple times within a given application.

Good Luck,

RE: Public MySQL Connection

Thank you both so much for your great help!

RE: Public MySQL Connection

Hey guys, I tried that module method. I get an error when compiling. I'm going to post my code any maybe someone can tell me where I'm going wrong? Thanks in advance!

**the variable 'Mnemonic' in this case is defined by a form prior to referencing this module

CODE -->

Imports MySql.Data.MySqlClient

Public Module CLETSConnect

    Dim StatColor As Color
    Dim ConnStr As String

    Public Sub ConnCLET()
        Dim cletconn As MySqlConnection
        cletconn = New MySqlConnection()

        cletconn.ConnectionString = "server=localhost;user id=root;password=rootpw;database=" & Mnemonic

            StatColor = Color.Green

            ConnStr = "CLETS Connected"
        Catch errors As MySqlException
            StatColor = Color.Red

            ConnStr = "CLETS NOT CONNECTED"

        End Try

    End Sub
End Module 

RE: Public MySQL Connection

The following is an example of one of the Connections that I talk about above.
Note that within the calling code, I pass the SQL Query String.

CODE -->

Public Class DataClass
    Public Shared Function GetDataReader(ByVal sql As String) As Data.OleDb.OleDbDataReader
        Dim cDataPath As String = ""
        Dim Conn As New System.Data.OleDb.OleDbConnection
        Dim Cmd As New System.Data.OleDb.OleDbCommand
        Dim reader As System.Data.OleDb.OleDbDataReader = Nothing
        GetDataReader = Nothing

            cDataPath = < UNC and/or fully pathed String defining where to find data >
            ' --- Use cDataPath in the following Connection String ---
            Conn.ConnectionString = < Your specific MySQL Connection String >

            Cmd.Connection = Conn
            Cmd.CommandText = sql  ' --- Passed as Parameter to this Class's Function --

            GetDataReader = Cmd.ExecuteReader

        Catch ex As Exception
            ' --- Close/Clear Open Objects ---
            GetDataReader = Nothing
            HttpContext.Current.Session("sql") = Nothing
            ' --- Write out Error to Log File ---
            ' --- Examine  ex.Message   to understand error specifics ---
            LogError("GetDataReader:" & ex.Message & vbCrLf & ex.Source & ex.StackTrace & vbCrLf & " DataPath: " & cDataPath & vbCrLf & " SQL:" & sql)

        End Try
        HttpContext.Current.Session("sql") = Nothing
    End Function

End Class 

Once the

CODE -->

GetDataSet(<SQL Query String>) 
is executed and no error encountered, it brings back a Data Table ( dt ) which may have Rows in it ((assuming that the SQL Query found something) or not.

Also you indicate


the variable 'Mnemonic' in this case is defined by a form prior to referencing this module
but have you confirmed that it is truly being 'seen' by this Function so as to result in an accurate MySQL Connection String?

Creating a MySQL Connector
MySQL Connection Strings

Good Luck,

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close