30 September 2012

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.
  1. Visual Basic 2010 - VB.NET - (Visual Studio 2010)
  2. 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 ( Your local IP). Since I am only connecting to my local MySQL database, so I just use localhost because it simple means 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
            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
' 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
                MsgBox("Invalid Username/Password", MsgBoxStyle.Information, "Login")
            End If
            '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
        Catch ex As Exception
        End Try
    End Sub

I hope you will find it more useful. 

Featured Offers: