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

 

[haxxani]

can you add queries to add, delete, update (database) and search (by id and or by name)

it will be a great help. thanks for the nice tutorial

 

[JuggaloBrotha]

can you add queries to add, delete, update (database) and search (by id and or by name)

it will be a great help. thanks for the nice tutorial

If you put all the database stuff in an xsd file you can use designers to create the datatables and their adapters

 

[spawn23]

nice thnks