INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

Public MySQL Connection

Public MySQL Connection

(OP)
Hi, I'm fairly new to VB.net, 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!
Thanks,
Ty

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
Example:

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) >
 Else
   ' --- No Records Returned ---
 Endif 

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,
JRB-Bldr

RE: Public MySQL Connection

(OP)
Thank you both so much for your great help!
-Ty

RE: Public MySQL Connection

(OP)
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 --> VB.net

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

        Try
            cletconn.Open()
            StatColor = Color.Green

            ConnStr = "CLETS Connected"
            cletconn.Close()
        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

        Try
            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 --
            Cmd.Connection.Open()

            GetDataReader = Cmd.ExecuteReader

        Catch ex As Exception
            ' --- Close/Clear Open Objects ---
            GetDataReader = Nothing
            Conn.Close()
            Conn.Dispose()
            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

Quote:

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,
JRB-Bldr



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!

Resources

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