MySQL + VB.NET 2010: Simple Login Code

One of the simplest ways to know on client and server side ( VB.NET and MYSQL database respectively) is to MAKE a login system. With this kind of process, it can achieve connecting, querying and executing from the client side to the database server.

First of all, before performing this simple login with MySQL and vb2010, we obviously need the tools.
Things we might want to make

How to connect VB.net to MySQL database server
Select Query on database
Execute the Query and Process it.

Requirements

Visual Basic 2010 - VB.NET - (Visual Studio 2010)
MySQL Database Server ( Community Release ) [ Download here ] MySQL Workbench.

Before we do the login task, we MUST know some information so that we would have an idea. And it is easier to understand in the following steps.

Connection String
Standard syntax

Server=myServerAddress;Database=myDataBase;User id=myUsername;Password=myPassword;

Here I did a module for MySQL parameters; I named it mysqlmodules but you can create any names

How to add module item?
1. On the Project Menu, click Add Windows Form...
2. And then, choose Module, see screenshot below

You can add declarations here, and it can be accessed in all forms. :)

Module mysqlmodules
Public ConnString As String = "server=localhost;user id=root;password=pass;database=test"
Public CONNECTION As New MySqlConnection(ConnString)
Public datardr As MySqlDataReader
Public sqlstatement As String
Public strSQL As String
Public ds As New DataSet
Public cmd As New MySqlCommand
Public dr As MySqlDataReader
Public sqlconnect As MySqlConnection = New MySqlConnection
End Module

Note: Italic words are user-defined declarations. So you can create your define.
Also, please be informed about LOCALHOST and ROOT names.

This may be a simple explanation. So let's cut into pieces and digest
Localhost - server for the Mysql database to connect 127.0.0.1 ( Your local IP). Since I am only connecting to my local MySQL database, so I just use localhost because it simple means 127.0.0.1 in IP Networking terms.

Root - default user id of MySQL ( you can change it )

Now This is the actual on how it connects to server; you can add it to a Form Load

Imports MySql.Data.MySqlClient 'you really import and need this reference.
Public Class login
'no declaration since it is all declared on module mysqlmodules above
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
sqlconnect.ConnectionString = ConnString
Try If sqlconnect.State = ConnectionState.Closed Then ' sqlconnect.Open() MsgBox("It works") Else sqlconnect.Close() MsgBox("no")
End If Catch ex As Exception ' MsgBox(ex.ToString) ' MsgBox(ex.Message.Length.ToString) 'get some error values and display to the user errornum = ex.Message.Length.ToString If errornum = 63 Then MsgBox("Database Cannot Connect. Please Call the Programmer/Database Expert", MsgBoxStyle.Exclamation) End 'just quit Else MsgBox("oppps... Something bad happen!") End If
End Try End Sub
Private Sub ToolTip1_Popup(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PopupEventArgs) Handles ToolTip1.Popup
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btLoginOK.Click
'userid is public here 'we want it to use in all forms ' userid = Me.txtUserID.Text MsgBox(userid)
End Sub
Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click
End Sub
Private Sub btCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btCancel.Click ' MsgBox("Thank", MsgBoxStyle.Information, "Cancelling...") End End SubEnd Class

-----------------------------------------------------------------------------------------------------------------------------

There are some key points here

sqlconnect.ConnectionString = ConnString

a connection string is set to new mysqlconnection to sqlconnect string

sqlconnect.State = ConnectionState.Closed Then sqlconnect.Open()

In the try catch statement, it has a condition on state... if sqlconnect state is closed then it is not connected to MySql database

Note that if it cannot connect to MySQL server and its database then will catch the error to errnum = 63 and says it doesn't connect to MySQL

So it is how you connect your form (vb.net 2010 and in MySQL)
-----------------------------------------------------------------
Now in your Login Button

Private Sub Login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btLoginOK.Click
Dim isfound As Boolean
isfound = False
Try
strSQL = "SELECT * from users where user_id = '" & Me.txtUserID.Text & "' and password = '" & Me.txtPassword.Text & "'"
cmd = New MySqlCommand(strSQL, CONNECTION)
dr = cmd.ExecuteReader()
Do While dr.Read()
isfound = True
Loop
' you can use If dr.HasRows.ToString = True Then
If isfound = True Then
'MsgBox("Found You....")
'login successful
userid = Me.txtUserID.Text
userpass = Me.txtPassword.Text
Form4.Show()
Me.Hide()
Else
MsgBox("Invalid Username/Password", MsgBoxStyle.Information, "Login")
Me.txtUserID.SelectAll()
Me.txtUserID.Focus()
End If
dr.Close()
cmd.Dispose()
'...................................................
'userid is public here
'we want it to use in all forms
'to put it in status or to query some statements
'Put the textbox value to a variable
'...........................................................
userid = Me.txtUserID.Text
'MsgBox(userid)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub