Connecting a MySql database to VB.Net Application [.Net Control]-VBForums
Results 1 to 7 of 7

Thread: Connecting a MySql database to VB.Net Application [.Net Control]

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2010
    Location
    Colombo
    Posts
    23

    Connecting a MySql database to VB.Net Application [.Net Control]

    Hii again,

    Its all with .Net to Vb now, however think you gonna like this.
    Things required:

    MySql .Net Connector

    Make a new host with a mysql database, then create the following database,
    make new database any name and create table called: test
    create 3 columns : 1: ID 2: Name 3: Age
    ID sholud be auto incrementing

    OK
    1) Make a new form, the usual things, and make a reference to the just downloaded control.

    2) Rename your main control to frmMain for the sake of ma code

    3) Add a new data grid and rename it to dataGrid

    4) Now add a new class and rename to clsConnect add the following code.

    Code:
    Imports MySql.Data.MySqlClient
    Public Class clsConnect
        Dim dataAdap As MySqlDataAdapter
        Dim cmdBuild As MySqlCommandBuilder
        Public data As DataTable
        Dim conn As New MySqlConnection
        Dim connected As Boolean = False
        Sub connect()
            Dim name As String = "your databse name"
            Dim server As String = "your server address"
            Dim user As String = "your databse username"
            Dim pwd As String = "your db pwd"
    
    
            If Not conn Is Nothing Then conn.Close()
            conn.ConnectionString = String.Format("server={0}; user id={1}; password={2}; database={3}; pooling=false", server, user, pwd, name)
            Try
                conn.Open()
                MsgBox("connected")
    
            Catch ex As Exception
                MsgBox("cannot connect")
            End Try
        End Sub
        Sub search(ByVal str As String)
            Try
                If connected Then
                    data = New DataTable
                    dataAdap = New MySqlDataAdapter("SELECT * FROM test WHERE Name LIKE '%" & str & "%'", conn)
    
                    cmdBuild = New MySqlCommandBuilder(dataAdap)
                    dataAdap.Fill(data)
                    frmMain.dataGrid.DataSource = data
                End If
            Catch ex As Exception
    
            End Try
    
        End Sub
        Sub View()
            If frmMain.connected Then
                Try
                    data = New DataTable
    
                    dataAdap = New MySqlDataAdapter("SELECT * FROM test", conn)
                    cmdBuild = New MySqlCommandBuilder(dataAdap)
    
                    dataAdap.Fill(data)
    
                Catch ex As Exception
                    MsgBox("Error COnnecting To The Database!")
                End Try
                frmMain.dataGrid.DataSource = data
    
            End If
        End Sub
        Sub addRecord(ByVal name As String, ByVal age As String)
            If frmMain.connected Then
                Try
                    Dim cmd As New MySqlCommand(String.Format("INSERT INTO `test` ( `ID` , `Name` , `Age`  )VALUES (NULL , '{0}', '{1}')", name, age), conn)
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox("Your Record Added Successfully!", MsgBoxStyle.Information, "Successful!")
                End Try
            End If
        End Sub
        Sub editRecord(ByVal id As String, ByVal name As String, ByVal age As String)
            Try
    
                Dim cmd As New MySqlCommand(String.Format("UPDATE `test` SET `Name` = '{0}', `Age` = '{1}' WHERE `test`.`ID` ={2} LIMIT 1 ;", name, age, id), conn)
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox("Your Record Added Successfully!", MsgBoxStyle.Information, "Successful!")
            End Try
        End Sub
     
    End Class
    5) using the code

    first make an instance of the class on your global variables.

    Code:
    Public connector As New clsConnect
    add the following to your form load
    Code:
    Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
            connector.connect()
            dataGrid.DataSource = connector.data
        
        End Sub
    Now the config is done, lets use it

    6) Loading the data on the databse to your datagrid:

    This code always load the databse contents to your dataGrid
    Code:
    connector.View()
    7) Making a search query and filling the dataGrid with the results:
    Code:
    connector.search("your search string")
    8) Adding a new record to the databse:
    Code:
    connector.addRecord("ManZzup", "15000")
    9) Editting a record on the databse:
    Code:
    frmMain.connector.editRecord("1", "ManZzup", "16000")
    10) Thats all!

    You can change the code if you know a bit of .net and mqSql
    And please report bugs and issues.

  2. #2
    PowerPoster formlesstree4's Avatar
    Join Date
    Jun 2008
    Location
    On the Internet
    Posts
    3,249

    Re: Connecting a MySql database to VB.Net Application [.Net Control]

    This is a codebank entry.

  3. #3
    Fanatic Member newprogram's Avatar
    Join Date
    Apr 2006
    Location
    in your basement
    Posts
    769

    Re: Connecting a MySql database to VB.Net Application [.Net Control]

    sweet!!!! thank you
    Live life to the fullest!!

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Sep 2010
    Location
    Colombo
    Posts
    23

    Re: Connecting a MySql database to VB.Net Application [.Net Control]

    ahh how can i move this?
    ::Its All About Knowing:::
    ::::Zorums:::::

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,977

    Re: Connecting a MySql database to VB.Net Application [.Net Control]

    Quote Originally Posted by ManZzup View Post
    ahh how can i move this?
    Click the Report icon on your post, which sends a message to all mods, and ask them to move it to the VB.NET CodeBank forum.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,230

    Re: Connecting a MySql database to VB.Net Application [.Net Control]

    Thread moved from the 'VB.Net' forum to the 'CodeBank VB.Net' forum

    (thanks for letting us know folks )

  7. #7
    Member
    Join Date
    Mar 2011
    Posts
    56

    Re: Connecting a MySql database to VB.Net Application [.Net Control]

    Look, everytime when i searching something with connector.search it anyways found that string what i wrote, but that string does not exist in my database table. My codes:
    Code:
    Sub search(ByVal str As String)
            Try
                If connected Then
                    data = New DataTable
                    dataAdap = New MySqlDataAdapter("SELECT * FROM accounts WHERE login ='" & str & "'", conn)
                    cmdBuild = New MySqlCommandBuilder(dataAdap)
                    dataAdap.Fill(data)
                    MsgBox("Account found!", MsgBoxStyle.Information)
                End If
            Catch ex As Exception
    
            End Try
        End Sub
    And in main form on button click:
    Code:
    connector.search(TextBox1.Text)
    How i can fix this problem?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.