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

    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
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

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

    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
  •  



Click Here to Expand Forum to Full Width