Click here to Skip to main content
15,867,999 members
Articles / Desktop Programming / Windows Forms

VB.NET to MySQL Database Connection

Rate me:
Please Sign up or sign in to vote.
4.52/5 (13 votes)
18 Jun 2007CPOL1 min read 280.5K   76   15
Connecting from VB.NET to a MySQL database.

Introduction

I'm currently developing an inventory management system which caters to multiple warehouses. I will share with you some tips and tricks in the development of this program. The system is still under development.

The database I use is MySQL Server 5.0.x together with the MySQL .NET Connector. Firstly, I'll share a few tricks about connecting to MySQL. Discussions on saving, querying, and other useful tricks will follow later on.

Using the code

To be able to create this program, you must have basic knowledge concerning MySQL 5.0 as well as VB.NET.

Prerequisites

Steps

  1. Install MySQL Server 5.0 and and MySQL .NET Connector.
  2. Import a reference of the MySQL >NET Conenctor in Project Settings.
  3. Go to Settings and type the following settings:
  4. Name         Type      Scope   Value
    myDB ->      String -> User -> DatabaseName
    myServer->   String -> User -> Servername
    myUsername-> String -> User -> DbUserName
    myPassword-> String -> User -> DbPassword
    myPort->     String -> User -> mySQLPort(3306)

  5. Change the values in your MySQL database settings. The purpose of changing the setting is that if the MySQL database settings will change, e.g., the server is migrated, you can easily change the server settings and you won't have to rebuild the project.
  6. Add a Module in your project and name it mdlDataConn6. Paste the following code in your Module:
  7. VB
    Imports MySql.Data.MySqlClient
    
    Module mdlDataConn
        Public conn As New MySqlConnection
    
        Public Sub ConnectDatabase()
    
            Try
                If conn.State = ConnectionState.Closed Then
                    conn.ConnectionString = "DATABASE=" & My.Settings.myDB & ";" _
                      & "SERVER=" & My.Settings.myServer & ";user id=" & My.Settings.myUsername _
                      & ";password=" & My.Settings.myPassword &";port=" & _
                      My.Settings.myPort & ";charset=utf8"
                    conn.Open()
                End If
    
            Catch myerror As Exception
                MessageBox.Show("Error Connecting to the database", "Error Database Server", _
                                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                End
            End Try
        End Sub
    
        Public Sub DisconnectDatabase()
            Try
                conn.Close()
            Catch myerror As MySql.Data.MySqlClient.MySqlException
    
            End Try
        End Sub
    End Module
    'To connect to mySQL Database just call.
    ConnectDatabase
    
    'To close the mySQL Database
    DisconnectDatabase

That's all!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Philippines Philippines
Jayson Pascual is the software developer of the POS Technology Solution located in Philippines. He was more than 4 years in experience developing in business system such as POS, Inventory, Accounting, and etc. He start developing business system at age of 17 using VB6 and MS SQL Server 2000 and current using VB2005 and MySQL 5.0 to reduce cost and maintenance for the SME's Businesses. \

He currently serving SME's in the Philippines and developing easiest as possible business system because most of the employees and employers are first time user in computer.

Comments and Discussions

 
Questionconnection problem Pin
Mark Anthony Suelila9-Sep-16 21:56
Mark Anthony Suelila9-Sep-16 21:56 
Questionplease i need help here Pin
Member 117464859-Jun-15 9:20
Member 117464859-Jun-15 9:20 
Question'&amp' not declared Pin
Beninda5-Dec-13 0:27
Beninda5-Dec-13 0:27 
AnswerRe: '&amp' not declared Pin
Mark_essex27-Dec-13 7:13
Mark_essex27-Dec-13 7:13 
QuestionError (amp;) Pin
ZoZZ8819-Jun-13 14:18
ZoZZ8819-Jun-13 14:18 
QuestionHow enumerated MySQL server instances on the network? Pin
Gregorio Cobá14-Jul-09 19:14
Gregorio Cobá14-Jul-09 19:14 
GeneralHelp! I'm a desperate beginner :( Pin
DarkRaven102415-Nov-07 23:35
DarkRaven102415-Nov-07 23:35 
QuestionSample SQL query codes Pin
M Sopian H17-Sep-07 16:45
M Sopian H17-Sep-07 16:45 
Hi,

Could you please show us some example of SQL Query codes using this MySQL connector. I am new in this VB .NET environment, but, experienced in PHP & C Languages. So, some of the philosphy might understand by me.

my email address : my_offhegoes@yahoo.com

Thanks,
M Sopian H
AnswerRe: Sample SQL query codes Pin
fdchiu24-Sep-10 21:11
fdchiu24-Sep-10 21:11 
QuestionMysql 4.2 Pin
zubinkasad26-Jun-07 21:52
zubinkasad26-Jun-07 21:52 
AnswerRe: Mysql 4.2 Pin
Irwan Hassan27-Jun-07 3:17
Irwan Hassan27-Jun-07 3:17 
GeneralRe: Mysql 4.2 Pin
zubinkasad27-Jun-07 3:36
zubinkasad27-Jun-07 3:36 
GeneralRe: Mysql 4.2 Pin
Irwan Hassan27-Jun-07 15:16
Irwan Hassan27-Jun-07 15:16 
GeneralRe: Mysql 4.2 Pin
zubinkasad27-Jun-07 18:46
zubinkasad27-Jun-07 18:46 
GeneralRe: Mysql 4.2 Pin
Irwan Hassan28-Jun-07 17:52
Irwan Hassan28-Jun-07 17:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.