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
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