Monday, December 2, 2019

Connecting Visual Studio 2008 to MySQL Database using ODBC Connector

Step 1: Install MySQL Database like MySQL Server 5.0
Step 2: Install ODBC Connector like Connector ODBC 5.1
Step 3: Create a project in Visual Studio

Project Type: Visual Basic -> Windows
Project Template: Windows Forms Application


Step 4: Click My Project on your Solution Explorer
Step 5: Click on Reference Tab


Step 6: Click on Add 
Step 7: Click on COM Tab
Step 8: Look for Microsoft ActiveX Data Object 2.7 Library or higher version
Step 9: Click OK

Step 10: On your Project Explorer right click on your project name and mouse over Add
Step 11: Add Module

Step 12: Add the following code on the very top of other code

Imports ADODB

Step 13: Add the following code just below the Module Module 1

Public conn As New ADODB.Connection
Public rs As New Recordset
Public lvi As ListViewItem

Step 14: Add the following code just below Public lvi As ListViewItem

Public Sub Connection()
        On Error GoTo Err
        If conn.State = ObjectStateEnum.adStateOpen Then conn.Close()
            conn.ConnectionString = "Driver=MySQL ODBC 5.1 Driver;Server=Localhost;Port=3306;Database=name_of_my_database;UID=mysqluser;PWD=mysqlpassword;Option=3;"
           conn.ConnectionTimeout = 5
           conn.CursorLocation = CursorLocationEnum.adUseClient
           conn.Open()
           Exit Sub
Err:
        MsgBox(Err.Description, MsgBoxStyle.Information + MsgBoxStyle.Critical)
    End Sub

Note: Driver may depend on the driver you have installed. Server may vary if the mysql database is installed on your machine or to the other computer, if it is the second then use the other computer's IP Address instead of Localhost but be sure you have enabled Allow Remote Connection during the installation of your MySQL Database. Port may also vary if what port number you have used during the installation of MySQL Database, Database name may vary on the created database in your MySQL Database Server, as well as the UID which uses "root" as the default user when you install MySQL and the PWD or password you set up during the MySQL installation while Option = 3 should be kept as is. Lastly there is no restriction on the number of ConnectionTimeout so you decide. THIS IS YOUR CONNECTION SCRIPT TO CONNECT VB.net to MySQL

Step 15: Add the following code after the End Sub of Public Sub Connection()

Public Sub OpenRecord(ByVal sql As String)
        On Error GoTo Err
        If rs.State = ObjectStateEnum.adStateOpen Then rs.Close()
        rs.Open(sql, conn, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic)
        Exit Sub
Err:
        MsgBox(Err.Description, MsgBoxStyle.Information)
    End Sub

Note: sql comes from your parameter while conn comes from the variable declaration on top.

Step 16: Go back to your design and double click on your form and add this code just below the Public Class Form1

Dim isEdit As Boolean = False

Step 17: On your Form_Load() add the following lines

Connection() 'this will call the connection script
getAllUsers("") 'this will retrieve all previous data from users table.

Note: Be sure you have created a database in your MySQL and should have at least one table like "users" with fields userid int type, username and password text type

Step 17: Double click on your CLEAR button and add this lines inside

TextBox1.Clear()
TextBox2.Clear()
TextBox3.Clear()
getAllUsers("")

Step 18: Go back to your design and double click on your SAVE Button and add this lines

 If Trim(TextBox1.Text) <> "" Or Trim(TextBox2.Text) <> "" Or Trim(TextBox3.Text) <> "" Then
            OpenRecord("Select * from user Where userid = '" & TextBox1.Text & "'")
            With rs
                If isEdit = False Then 'add new record
                    .AddNew()
                    .Fields("userid").Value = TextBox1.Text
                    .Fields("username").Value = TextBox2.Text
                    .Fields("password").Value = TextBox3.Text
                    .Update()
                    MsgBox("New user added.", MsgBoxStyle.Information)
                Else 'edit existing record
                    .Fields("username").Value = TextBox2.Text
                    .Fields("password").Value = TextBox3.Text
                    .Update()
                    MsgBox("Information has been edited.", MsgBoxStyle.Information)
                    TextBox1.Enabled = True
                End If
            End With
        Else
            MsgBox("Incomplete Fields. Please try again.", MsgBoxStyle.Information)
        End If
        Button2_Click(sender, e)
        getAllUsers("")

Step 19: Go back to your design and double click on button EDIT and add this lines

If ListView1.Items.Count = 0 Then Exit Sub
If ListView1.SelectedItems.Count = 0 Then Exit Sub

TextBox1.Text = ListView1.SelectedItems.Item(0).SubItems(0).Text
TextBox2.Text = ListView1.SelectedItems.Item(0).SubItems(1).Text
TextBox3.Text = ListView1.SelectedItems.Item(0).SubItems(2).Text
TextBox1.Enabled = False
 isEdit = True


Step 20: Go back to your design and double click on DELETE button and add this lines

If ListView1.Items.Count = 0 Then Exit Sub
If ListView1.SelectedItems.Count = 0 Then Exit Sub

If MsgBox("This action will delete the selected user. Do want to continue?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then

      OpenRecord("Delete from user where userid = '" &   ListView1.SelectedItems.Item(0).SubItems(0).Text & "'")
      getAllUsers("")
      MsgBox("Selected user has been deleted.", MsgBoxStyle.Information)
End If

Step 21: Go back to your design and double click on Textbox for searching and add this lines

Private Sub TextBox4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox4.TextChanged
        getAllUsers(TextBox4.Text)
End Sub

Step 22: Add this code at the very end of the script but do not go beyond the "End Class" for your code may not run successfully.

Public Sub getAllUsers(ByVal str As String)
        ListView1.Items.Clear()

        OpenRecord("Select * from user Where username like '" & str & "%'")
        With rs
            While Not .EOF
                lvi = ListView1.Items.Add(.Fields("userid").Value)
                lvi.SubItems.Add(.Fields("username").Value)
                lvi.SubItems.Add(.Fields("password").Value)
                .MoveNext()
            End While
        End With
    End Sub




No comments:

Post a Comment

A REVIEW ON CONNIE DABATE’S MURDER CASE: Fitbit One Wearable

T he Author   ROSITO D. ORQUESTA MSIT Student at Jose Rizal Memorial State University-Dapitan Campus OIC-ICT Dean, Eastern Mindanao College ...