Database Driven Login form with Admin backend (Image Heavy)

[formlesstree4]

Well, I asked around here, got a partial answer that really helped out, but I decided to write an official How To on a database driven login form. I'll include the Administration Backend to go along with it soon.

Dial-Up users should not use this thread, it is image heavy

So, here we go:

Well, let's start by opening Visual Basic 2008.

1. File ---> New Project, or press Control + N
2. Create a Windows Form Application and give it whatever name you want.

Now that the project has been created, you have a form in front of you, that's empty.

What you are going to do, is create two text boxes, two labels, two command buttons, and two radio buttons, and organize them how you want to.
Or you can do it how I did it inside the demonstration.

Rename the labels, radio buttons, and buttons to something else, like shown.

Now, that was the GUI created for the login form. Let's create the database.
Right click WindowsApplication1 (or whatever you called the project), and hit add item. Then select Local database, and hit ok.
This should pop up

Put whatever database name you want, in this example, I will use Database1 as the name. Now, press finish, and you can see your database has been added to your project.
Now, we need to create the tables for the login form. Double click your newly created database (Database1.sdf in the solution explorer) and a Database Explorer should pop up at the bottom of Visual Basic 2008.

Right click 'Tables', and hit Create Tables. A screen should pop up. Configure it to what's shown in the image below.

Press Ok, and we have a table created. Yay! Now, let's populate it with information to test the login system, which we will do later on.
Right click the 'Users' table and hit show table data. There should be two rows with (Null) inside both. This means there is no data in the table. We are going to fix that.

Add some information like shown.

There, now the table has some information for the form to process. One problem, the form doesn't know how to read the table.
So, we need some rather simple coding. Trust me, its so easy (not). You are going to love (hate) this as much as I do (not).

First, go back to your login form, and double click the Login button. This is the hard one here. You are now inside the code editor. Now, go to the line right below where the Public Class is, and put these lines in:

	Dim con As SqlCeConnection = New SqlCeConnection("Data Source=Database1.sdf") 'This here, declares where the database is located
	Dim Clear As Double
	Dim myDA As SqlCeDataAdapter
	Dim myDataSet As DataSet
	Dim dt As New DataTable()

So now, your form should have this code.

As you can see, there are a few errors with the code. You need to import the SQL Server information
Go above the Public Class line and paste this line in:

Imports System.Data.SqlServerCe

So now, it should look like this:

Good! No errors, but we still can't read the database. Well, let's fix that.

Go to the button 1 click event. This will tell the form we want to login, but of course, we need some authentication. So paste this code in:

		con.Open()
		Try
			Dim cmd As SqlCeDataAdapter = New SqlCeDataAdapter("SELECT Username,Password FROM users WHERE Username ='" & TextBox1.Text & "' AND Password ='" & TextBox2.Text & "'", con)
			cmd.Fill(dt)
			If dt.Rows.Count = 0 Then
				MessageBox.Show("Login failed, please try again.")
				cmd = Nothing
				dt.Clear()
			ElseIf dt.Rows.Count = 1 Then
				MessageBox.Show("Login worked...")
				cmd = Nothing
				dt.Clear()
			End If
		Catch ex As Exception

		End Try
		If con.State <> ConnectionState.Closed Then
			con.Close()
		End If

Now, lets break this down.

con.Open()

This opens the connection to the database.

Dim cmd As SqlCeDataAdapter = New SqlCeDataAdapter("SELECT Username,Password FROM users WHERE Username ='" & TextBox1.Text & "' AND Password ='" & TextBox2.Text & "'", con)

Possibly the most complex line here. This line states that the word 'cmd' is a data adapter. It will connect to the database with the query listed. That query will 'SELECT' a username and password from the database, the username being textbox1.text, and the password being textbox2.text. The reason con is at the end is telling the query to use the connection labeled by con.

cmd.Fill(dt)

If you remember, dt means datatable, or thats the variable we defined earlier as DataTable. What this does, is take the results and dumps it into an imaginary table that you can't see.

If dt.Rows.Count = 0 Then

If there are no matching rows, then perform the next lines, which say that you didn't login.

cmd = Nothing
dt.Clear()

These two lines clear the DataTable and the Connection information. This is to prevent multiple things building up, and can cause a big mess.....not fun.

ElseIf dt.Rows.Count = 1 Then

This is the exact opposite of above. It says if there is a matching row. I tried just using Else, and the program threw a hissy fit. Enough said.

You already know what the cmd = nothing and dt.clear() mean so we'll skip it.

Catch ex As Exception

Simply catches any errors.
The last lines close the connection.


Phew! That was a lot to process! Now, lets actually test it.

Press that Green arrow at the top of Visual Basic, and the form will load up.

Type in whatever username and password you had added in earlier. You should get this:

If you don't try again, or edit your database information.

You can goof around, and do a GUI modification to something like this:

The 2nd part to this tutorial is here

 

[formlesstree4]

Adding Administration Backend To Database Login form (Image Heavy)

This is the administration addon to my previous tutorial for a login form. If you haven't done that yet, please do so before coming and doing this, as you may get very confused very fast.
It is here

Now, this too is image heavy, cause i like to be descriptive as well as showing how things work, so here we go.


Open WindowsApplication1 (the login form you created in my last tutorial), and create a new form.




This is our administration form..yay! Now, make a tab control component, and using the Dock feature in properties, make it fill across the screen.



Now, under TabPage1, make two text boxes, 3 buttons and 2 labels. Arrange those as you want to.



Now, we are going to make a DataGridView component. Ignore any pesky popups that occur, and Dock it to the right hand side of the form. Also, update the names as shown.



Under TabPage2, do the exact same thing, also filling in the names




Now, under the users tab, double click add, or anything to get to the code editor. Go above the Public Class line, and put the following two lines:

Imports System.Data.SqlServerCe
Imports System.Data.Sql

It should look like this now



Now, something new in the administration backend is MD5 Encryption. This will enable you to encrypt strings.
Go below the Public Class line and add this function:

    Function hashtext(ByVal strToHash As String) As String
        Dim md5Obj As New Security.Cryptography.MD5CryptoServiceProvider
        Dim bytesToHash() As Byte = System.Text.Encoding.ASCII.GetBytes(strToHash)
        bytesToHash = md5Obj.ComputeHash(bytesToHash)
        Dim strResult As String = ""
        For Each b As Byte In bytesToHash
            strResult += b.ToString("x2")
        Next
        Return strResult
    End Function

When this function is called, it will hash the string....very useful, but only in MD5 hash. Later on, I will show you how to replace this with other forms of encryption.

Now, your code editor should look something like this.




So, the encryption function is in, but we will create a few Public Sub's, to make life a lot easier. These will hold the calls needed to modify the database and view whats inside of them. This first Public Sub is to reveal the user data and have it print out into that DataGridView Component we just created. Cool huh!

    Public Sub UserData()
        Dim con As SqlCeConnection = New SqlCeConnection("Data Source=Database1.sdf") 
        Dim cmd As SqlCeCommand
        Dim myDA As SqlCeDataAdapter
        Dim myDataSet As DataSet

        con.Open()
        cmd = New SqlCeCommand("Select * From users", con)
        myDA = New SqlCeDataAdapter(cmd)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "users")
        DataGridView1.DataSource = myDataSet.Tables("users").DefaultView
    End Sub

That is placed right under the Encryption Function. Now, the regular users are shown, but what about the Administrators? Don't they deserve a spot? So, the code is almost the exact same, but there are a few differences...have a look.

    Public Sub AdminData()
        Dim con As SqlCeConnection = New SqlCeConnection("Data Source=Database1.sdf") 'This here, declares where the database is located
        Dim cmd As SqlCeCommand
        Dim myDA As SqlCeDataAdapter
        Dim myDataSet As DataSet
        con.Open()
        cmd = New SqlCeCommand("Select * From Administrators", con)
        myDA = New SqlCeDataAdapter(cmd)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "Administrators")
        DataGridView2.DataSource = myDataSet.Tables("Administrators").DefaultView
    End Sub

So, now we have both DataGridView's covered, and both will display the correct information.

This is how the code editor screen should look

(Last image by the way)


OK, one more thing to do. You need to create an form load event. If you kept the name as form2, then use this code:

    Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

If you didn't, just change Form2 to the name of the form. In this load event, we will call those two Public Subs we created earlier. In Form2_Load, add these two lines:

UserData()
Admindata()

That will call those two subs and execute them.

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

Now, we can start creating the button stuff. Under Button1, add this code:

        Dim con As SqlCeConnection = New SqlCeConnection("Data Source=Database1.sdf")
        Dim cmd As SqlCeCommand
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        cmd = New SqlCeCommand("INSERT INTO users(Username, Password) VALUES('" & textbox1.Text & "','" & hashtext(textbox2.Text) & "')", con)
        cmd.ExecuteNonQuery()
        UserData()

Let's break this down nicely

Dim con As SqlCeConnection = New SqlCeConnection("Data Source=Database1.sdf")

This tells us where the database is located

Dim cmd As SqlCeCommand

This defines what word launches the SqlCeCommand function.

        If con.State = ConnectionState.Closed Then
            con.Open()
        End If

Opens the connection to the database if it's closed. If it isn't closed, it won't do anything.

        cmd = New SqlCeCommand("INSERT INTO users(Username, Password) VALUES('" & textbox1.Text & "','" & hashtext(textbox2.Text) & "')", con)

This is the actual sql statement that will be executed. It will insert the Username from the textbox1, and the MD5 hash of textbox2 into the database. Textbox2 is hashed, because it is the password.

        cmd.ExecuteNonQuery()

This will Execute the query.

UserData()

This reloads the DataGridView to show the changes. It removes the need for a timer.


Jesus, that was just for the Adding. This gets better and better as we go along!

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

Now it's time for the deletion button

For button2, this is the code used:

        Dim con As SqlCeConnection = New SqlCeConnection("Data Source=Database1.sdf")
        Dim cmd As SqlCeCommand
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        cmd = New SqlCeCommand("DELETE FROM users WHERE Username='" & textbox1.Text & "'", con)
        cmd.ExecuteNonQuery()
        UserData()

No need to break it down, cause you already know what it does.

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

Now, the final one, updating users.

        Dim con As SqlCeConnection = New SqlCeConnection("Data Source=Database1.sdf")
        Dim cmd As SqlCeCommand
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        cmd = New SqlCeCommand("UPDATE users SET password='" & hashtext(textbox2.Text) & "' WHERE Username='" & textbox1.Text & "'", con)
        cmd.ExecuteNonQuery()
        UserData()

The above code will change their password.

Phew! Only half way done.

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

Now, lets start the Administration Editing area.


For Adding Administrators, I decided to double MD5 the password, for improved security. You can do whatever, increase or decrease, but that's how i decided to do it.

This is to add administrators:

        Dim con As SqlCeConnection = New SqlCeConnection("Data Source=Database1.sdf")
        Dim cmd As SqlCeCommand
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        cmd = New SqlCeCommand("INSERT INTO Administrators(Login, Password) VALUES('" & textbox3.Text & "','" & hashtext(hashtext(textbox4.Text)) & "')", con)
        cmd.ExecuteNonQuery()
        AdminData()

The only difference is the last line, which updates the Admin datagrid, not the user one

-----------------------------------------------------------------------------------------------------------------------------------------------------------
Now deleting them:

        Dim con As SqlCeConnection = New SqlCeConnection("Data Source=data\Database1.sdf")
        Dim cmd As SqlCeCommand
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        cmd = New SqlCeCommand("DELETE FROM Administrators WHERE Login='" & TextBox3.Text & "'", con)
        cmd.ExecuteNonQuery()
        AdminData()

-----------------------------------------------------------------------------------------------------------------------------------------------------------
And Finally, updating their passwords.

        Dim con As SqlCeConnection = New SqlCeConnection("Data Source=data\Database1.sdf")
        Dim cmd As SqlCeCommand
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        cmd = New SqlCeCommand("UPDATE Administrators SET Password='" & hashtext(hashtext(TextBox4.Text)) & "' WHERE Login='" & TextBox3.Text & "'", con)
        cmd.ExecuteNonQuery()
        AdminData()

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


Now, we need to make the login form determine whether or not you want to login as admin or not.
Those two little buttons added do just the thing

Load up form1.vb and paste this in:

    Private Sub RadioButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles RadioButton1.Click
        If RadioButton2.Checked = True Then
            RadioButton2.Checked = False And RadioButton1.Checked = True
        End If
    End Sub

    Private Sub RadioButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles RadioButton2.Click
        If RadioButton1.Checked = True Then
            RadioButton1.Checked = False And RadioButton2.Checked = True
        End If
    End Sub

These two subs make sure that neither button can be selected at the same time.
Add the encryption function to form1:

    Function hashtext(ByVal strToHash As String) As String
        Dim md5Obj As New Security.Cryptography.MD5CryptoServiceProvider
        Dim bytesToHash() As Byte = System.Text.Encoding.ASCII.GetBytes(strToHash)
        bytesToHash = md5Obj.ComputeHash(bytesToHash)
        Dim strResult As String = ""
        For Each b As Byte In bytesToHash
            strResult += b.ToString("x2")
        Next
        Return strResult
    End Function

Now, find button1 form event. Replace it with this:

        If RadioButton2.Checked = True Then
            con.Open()
            Try
                Dim cmd As SqlCeDataAdapter = New SqlCeDataAdapter("SELECT Username,Password FROM users WHERE Username ='" & TextBox1.Text & "' AND Password ='" & hashtext(TextBox2.Text) & "'", con)
                cmd.Fill(dt)
                If dt.Rows.Count = 0 Then
                    MessageBox.Show("Login failed, please try again.")
                    cmd = Nothing
                    dt.Clear()
                ElseIf dt.Rows.Count = 1 Then
                    MessageBox.Show("Login worked...")
                    cmd = Nothing
                    dt.Clear()
                End If
            Catch ex As Exception

            End Try
            If con.State <> ConnectionState.Closed Then
                con.Close()
            End If
        ElseIf RadioButton1.Checked = True Then
            con.Open()
            Try
                Dim cmd As SqlCeDataAdapter = New SqlCeDataAdapter("SELECT Login,Password FROM Administrators WHERE Login ='" & TextBox1.Text & "' AND Password ='" & hashtext(hashtext(TextBox2.Text)) & "'", con)
                cmd.Fill(dt)
                If dt.Rows.Count = 0 Then
                    MessageBox.Show("Login failed, please try again.")
                    cmd = Nothing
                    dt.Clear()
                ElseIf dt.Rows.Count = 1 Then
                    Form2.Show()
                    cmd = Nothing
                    dt.Clear()
                End If
            Catch ex As Exception

            End Try
            If con.State <> ConnectionState.Closed Then
                con.Close()
            End If
        End If

Now that that is done, you need to add the table Administrators to the database, with the rows Login and Password. Once you do that, make sure the user login password is encrypted once, and that you add an administrator with a double encrypted password.

There you go! The administration backend integration is concluded. Any questions, just PM me.

 

[drew4663]

Very good tutorial.

I haven't gone through the admin side yet but I followed everything to the letter and it worked great. You actually explained things instead of just showing. Very kind of you to put this up for the community.

 

[formlesstree4]

I'm glad you like it!

 

[InertiaM]

Great tutorial but your queries really should be parameterized for security purposes.

 

[daveofgv]

Now, we are going to make a DataGridView component. Ignore any pesky popups that occur, and Dock it to the right hand side of the form. Also, update the names as shown.

Under TabPage2, do the exact same thing, also filling in the names

I have just a quick question..... These pics did not appear on the thread.
You have such an awsome thread and I like the pic idea, but can't see these.

Anyway to provide them?

daveofgv

 

[formlesstree4]

Great tutorial but your queries really should be parameterized for security purposes.

This is just a tutorial, I leave it up to the programmer using the HowTo to parameterize his/her queries themselves.

I have just a quick question..... These pics did not appear on the thread.
You have such an awsome thread and I like the pic idea, but can't see these.

Anyway to provide them?

daveofgv

I'm sorry they aren't there, ImageShack must have deleted them. I don't have the original images anymore, but I can attempt to format it the same way I did.

 

[daveofgv]

Also, update the names as shown

May I then ask what you ment by this.

That was my main question about the images.

Thanks for your reply and great thread.

daveofgv

 

[formlesstree4]

May I then ask what you ment by this.

That was my main question about the images.

Thanks for your reply and great thread.

daveofgv

I'll get a screenshot soon, it will take me a bit, as I'm busy at the moment.
But you'll need the image to understand what I was saying.

 

[drew4663]

InertiaM - Maybe you should consider making a tutorial on how to parameterize queries so we can build further upon this tutorial. That would include encrypted registration as well I presume?

 

[MattP]

InertiaM - Maybe you should consider making a tutorial on how to parameterize queries so we can build further upon this tutorial. That would include encrypted registration as well I presume?

There's already an excellent thread on this topic: The Ins and OUTs of Parameterized Queries... - Visual Basic .NET Forums

 

[InertiaM]

InertiaM - Maybe you should consider making a tutorial on how to parameterize queries so we can build further upon this tutorial. That would include encrypted registration as well I presume?

The link to the tutorial is in my signature (and has been for a long time )

 

[zka123]

hi guys
i am stuck when i get to

"Now, we are going to make a DataGridView component. Ignore any pesky popups that occur, and Dock it to the right hand side of the form. Also, update the names as shown."

because the image is not their can someone help me please.

 

[formlesstree4]

The names as shown are from the previous controls you created. You can make your own 'names' for them.

 

[zka123]

The names as shown are from the previous controls you created. You can make your own 'names' for them.

Thanks for your reply.