VB.net to mySQL Database Connection

Introduction

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

The database I used was mySQL Server 5.0.x together with mySQL DotNet Connector. Firstly, I'll start to share few tricks in 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.

Prerequisite:
1. mySQL Server 5.0
2. mySQL Dot Net Connector 5.1
3. VB.net Express edition/ VB.net Family

Steps:
1. Install first mySQL Server 5.0 and and mySQL Dot Net Connector.
2. Import the reference of mySQL DotNet Conenctor. in the Project Settings3. Go to Settings and type the following settings:

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)

4. 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 was migrated, you can furthermore easily change server settings and you dont have to rebuild the project.
5. Add Module in your project and name it as mdlDataConn6. Paste the follwing code in your module.

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 

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 9 of 9 (Total in Forum: 9) (Refresh) FirstPrevNext

How enumerated MySQL server instances on the network? Gregorio Cobá 20:14 14 Jul '09   I need to know follow information about to mysql using visual basic 2005 code 1. enumerated the mysql servers installed on the network 2. determinate how many databases each one have and, 3. Tables from each one database have. How do i this process? Thanks. Sign In·View Thread·PermaLink 5.00/5 (1 vote) Help! I'm a desperate beginner :( DarkRaven1024 0:35 16 Nov '07   Hi! I am currently doing my thesis and using MySQL 5 and VB.NET (Visual Studio2005) for creating a system. here's an example: I will insert a new data into my MYSQLdatabase through VB.NET. The moment I click "save" it will insert all the data and a stored procedure created in MySQL will create an ID for the inserted data. I want to know how can I get that specific ID after the data is stored in the database. it's a client server architecture. the database is centralized and there are scenarios where different clients(different PCs) simultaneously insert data to the database, each client should only get the ID created for the data they stored into the database. please help me with this problem...I'm not that experienced in VB.net and MySQL Sign In·View Thread·PermaLink 2.67/5 (3 votes) Sample SQL query codes M Sopian H 17:45 17 Sep '07   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 Sign In·View Thread·PermaLink 2.50/5 (4 votes) Mysql 4.2 zubinkasad 22:52 26 Jun '07   Hi I have been using mysql 4.2, but problem is i can access it in asp but am not able to add it to the database and my database is on redhat linux. Another query as i m also preparing inventory system wanted to know if i can copy one gridview row to another gridview row when user selects the record in first gridview, as soon as they select it it shows up in 2nd gridview on same page and than all those records are stored in database. thanks zk Sign In·View Thread·PermaLink Re: Mysql 4.2 Irwan Hassan 4:17 27 Jun '07   OK, I would like to help you, but I don't understand your question. Did you mean MySQL 4.1.2? I don't know if there are mysql 4.2 available. I'm a VB6 Programmer for past 7 years. Currently I'm slowly moving to VB2005. I'm using MySQL 4.1.12 for more 2 years. Before this I'm using MS Access for most of my developed application. So, you may ask the question to both of us. I will help you, if I understand your question. Sign In·View Thread·PermaLink 2.00/5 (3 votes) Re: Mysql 4.2 zubinkasad 4:36 27 Jun '07   Sorry actually have mysql 3.23.54 version. i am looking for urgent answer on my gridview question, if u can help me with dat wud be helpfull. thanks Sign In·View Thread·PermaLink 1.50/5 (2 votes) Re: Mysql 4.2 Irwan Hassan 16:16 27 Jun '07   OK, no problem. You just tell me what is the purpose of the double gridview. I and lot of programmer her wish to help. We will do our best. Tell some senario about the grid view. Sign In·View Thread·PermaLink 1.00/5 (1 vote) Re: Mysql 4.2 zubinkasad 19:46 27 Jun '07   Ok here i go what i want is there are 2 gridview on same page. lets give name gridview1 and gridview2 i am populating gridview1 by selecting Brand (which is listbox) Category (which is dropdownbox) Product (which is textbox) now selecting all this option i will populate gridview1. There is select link on each gridview1 row Now what i want is on selecting row by clicking on select link that row or record should be displayed in gridview2 likewise user will keep on changing the options and can populate gridview1 with other rows. So gridview2 should hold all the records which are selected by the users. After he finishes it off by selecting the records all the records in gridview2 should be stored in database. I hope i am clear about the problem If you want i can send you snapshot of the page which you can see it, but i dont think there is any link where i can attach snapshot. Thanks for your help zk Sign In·View Thread·PermaLink 3.50/5 (2 votes) Re: Mysql 4.2 Irwan Hassan 18:52 28 Jun '07   OK, now I understand what you want. There are few ways to do it. One of the way I suggest is create a Data Table (DataTable1) and populate the GridView1. Data Table 1 Contain all the product available for the customer to choose from. Create another data table (DataTable2) and select the product the customer already choosen (if exist). When a customer select a row in grid view 1, get the product id and add a new row in grid view 2, which should have the number of items and product price and etc. When user finish choosing the product, you may ask them to save the product choosen or automatically save. The Data Table should be responsible for saving the new items in grid view 2 automatically (if all the insert command and other related command are properly initialized). I had the same senario for one of my project. The project I did in VB6 with MySQL 4.1. The project is about the students. When a student promoted to new class in next year, the teacher should get the student details from previous year for his/her class. So, the teacher should select the record in the flex grid 1 and the student record will be transfered to grid view 2. All the transfered student will saved to his/her class. My email address is irwanhassan@hotmail.com. So all the futher question, you can redirect to the email. I will send a sample screen shot about the solution I told you. Sign In·View Thread·PermaLink 2.25/5 (3 votes)

Last Visit: 10:53 8 Nov '09     Last Update: 10:53 8 Nov '09 1