locked
Help with username and password validation

    Question

  • Hi There,

    I am very new to VB 2008 Express Edition and have been playing around with it for the past few days. I have create some forms and a database with the following tables. I have gone through all the videos for VB and SQL Server at the Learning Centre. It's after viewing them that gave me the project I want to work on. So please can someone help me.

     

    Forms

    1. Login Page
    2. Main Page
    3. Error Page

    Tables

    1. TblCustomers
    2. TblProducts
    3. TblUsers

    I have a record created in the TblUsers table username field {nchar(10)} "Joe" with password field {nchar(10)} "password".

    I want the login form to validate username and it's corresponding password from the TblUsers table from my database. If credentials incorrect the Error page should display and ok btn should take me back to Login Page. If successful then after closing the login page, the Main page should load up.

     

    Could someone please help me

     

    Thanks in Advance for your help

    Dan 

     

    Friday, December 07, 2007 12:58 PM

Answers

  • Sorry, the threads tend to expand.

     

    Here is an example to check a login

    It is very quick and clean.  And it will eliminate a bunch of designer "junk"

     

     

    if you take a look at the red underlined words:

     

    yourconnectionstring is a setting location created by the designer - ( if you place your cursor after the g and hit backspace, it will bring up intellisense and you can choose your connection string setting)

    users is the table name

    username and password are columns in the table

    nvarchar, 50    is the column datatype and the maximum character length

     

     

    Public Class UserLoginForm

     

        Dim user_connection As SqlClient.SqlConnection

        Dim user_reader As SqlClient.SqlDataReader

        Dim user_command As SqlClient.SqlCommand

        Dim select_string As String = Nothing

     

     

     

        Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click

     

     

            If Me.UsernameTextBox.Text = "" Or Me.PasswordTextBox.Text = "" Then

                MsgBox("Please enter a User Name and a Password")

                Exit Sub

            End If

     

            user_connection = New SqlClient.SqlConnection(My.Settings.YourConnectionString)

            select_string = "Select * From Users Where UserName = @UserName And Password = @Password"

     

            user_command = New SqlClient.SqlCommand(select_string, user_connection)

     

            With user_command

                .Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = Me.UsernameTextBox.Text

                .Parameters.Add("@Password", SqlDbType.NVarChar, 50).Value = Me.PasswordTextBox.Text

            End With

     

            If user_command.Connection.State = ConnectionState.Closed Then

                user_command.Connection.Open()

            End If

     

            user_reader = user_command.ExecuteReader()

     

            If user_reader.HasRows Then

     

                While user_reader.Read

                    user_password = user_reader("Password").ToString

                    access_level = user_reader("AccessLevel").ToString

                End While

     

                MsgBox("Login Successfull")

     

            Else

                MsgBox("Incorrect User Name or Password")

     

            End If

     

                user_reader.Close()

                user_command.Connection.Close()

                user_connection.Close()

     

        End Sub

    Saturday, December 08, 2007 1:25 AM
  • No problem Dan,

     

    sql server express does not allow you to connect to a database on a remote computer.

    you can do it with code but you have to setup the server to allow remote connections and a few other things.

     

    I had attempted to do this a while back and was able to connect but i could not get the user permissions setup properly so i put that on hold after spending somewhere around 6 hours on it.

     

    There are some examples on the web on how to do it but they did not go into the parts that i was having trouble with.

    Pretty soon i will be moving to visual studio 2008 professional so i won't have to worry about that but if i get it all worked out for express then i will post it.

     

    You can use the code above to connect to a database on your local computer though.

     

    Jeff

    Monday, December 10, 2007 2:18 PM

All replies

  • If you are new to programming then those videos although good, have just shown you how to work with the designer.

     

    The problem you face now is do you try to do some workaround using the designer or do you learn to do it in code.

     

    If you are more comfortable with the designer then you can use a hidden form with the datagridview on it and loop through the rows to find a match.

     

    Or you can use a datareader to check it.  This is much faster and cleaner.  But it requires code to do it.

     

    If you do some searching you will find what you need

    see what you can come up and then post back and we can help more

    Friday, December 07, 2007 2:34 PM
  •  

    "This is much faster and cleaner.  But it requires code to do it."

     

    This reflects a sentiment I see a lot of these days. Why are people programming if they don't want to program? I honestly don't understand that.

    Friday, December 07, 2007 4:06 PM
  • Renee,

     

    You made me stop and think here for a minute.

    I really think the problem is the videos.

     

    The only programming i have ever done before vb.net was in basic and then in excel 2000/2003 vba.  There were no wizards or much help in the designer like now.  When i finally made it over to vb.net i knew how to work with the code window and forms but when it came to the databases i thought based on the videos that the designer was the way you had to connect to a database.  Since it was so new to me i really thought you had to use the designer.

     

    I still think that the plan is working for microsoft because when you see how easy the designer will do things for you then you will use express.  And ofcourse it's free.  And once you get so into it and you find you need to do more, then you realize you have to learn code.

     

    This is why i am making my database code generating apps because they will save a lot of time and they will show new developers how the code works together to perform the different commands.

     

    Ok,  it's really for me bacause i get tired of typing the same lines over and over again with different names.  I can be lazy about that.  Cant't I?

     

    Just kidding, i think it will really help the new people out.   And even the more advanced users also.

    Friday, December 07, 2007 6:13 PM
  • From your description it sounds great ! Almost too good to be true.....

     

    I started a .Net program using the designer and to be honest I didn't kknow much about databases. But it was a large project and the database design was anything but solidified. I never found a way to modify a database and have that reflected in the designer. Hence I had to throw away my project and code everytime a Changed the database. After two days of that, i never went back to the designer. I found I needed more right away.

     

    I didn't realize it jeff but not many people design databases from the ground up. Most database designs are static. posiibly the designer is a good thing in such cases but it's absolutely aggravating, almost decimating to have to crumple up your project and throw it away everytime you want to add a field to a database.

     

    More and more, I do see people complaining about having to write code. I can't understand it. I wonder if you looked at the Goto Thread. I found that to e one of the neatest threads because it really required a lot of thinking and "analysis". I put that in double quotes because I don't think people approach problems in the same way I do.

     

    I really like what you've done and how far you've come and I especially like that you outgrew the command/builder binding navigator phase. You don't see the good people using them... Now, if I could only get you into stored procs! Wink

     

     

    Friday, December 07, 2007 6:36 PM
  • Well i am not far off from stored procedures.

     

    As a matter of fact, let me start a new thread about something i found on the matter.

    Friday, December 07, 2007 6:47 PM
  • Dear VB Gurus,

     

    Thank you very much for all your replies. However I am still not any closer to finding a solution for my question. js06 's reply seems to be more favourable for the issue at hand however I don understand what everyone here is talking about, Dont rely too much on the designer especially when it comes to databases. Understood completely. I used to do some programming years ago using PASCAL and COBOL.

     

    Because it's been a while I really want to get started on VB express using the designer because I can go back to the code and have alook around as to what is going on. But before I start working on code I need to start with a good project that wont bore me like it has done in the past everytime started using some "Learn VB in 24 hours" books. This is my project and not someone elses. I know exactly what I want out of it. I'm not trying to read someone else's mind. Hence why I really want to use the designer.

     

    However, If this cannot be done using the designer then obviously I would have to use. code. All the code I look at is either for older VBs or not relavant to what I am trying to achive. I tried some code and found myself trying to figure out what they are trying to achive. So again I have come to a STOP.

     

    If I may be so humble as to ask one of you to post some code in order for me to do this.

    This is it guys this could be my turning point to get back into programming. Hopefully!

    I have wasted so much money and time trying to get back into programming. I have a stack of Learn VB books on my desk that I use as paper weights. 

     

    Thank you in advance

     

    Regards,

    Dan 

     

    Saturday, December 08, 2007 12:50 AM
  • Sorry, the threads tend to expand.

     

    Here is an example to check a login

    It is very quick and clean.  And it will eliminate a bunch of designer "junk"

     

     

    if you take a look at the red underlined words:

     

    yourconnectionstring is a setting location created by the designer - ( if you place your cursor after the g and hit backspace, it will bring up intellisense and you can choose your connection string setting)

    users is the table name

    username and password are columns in the table

    nvarchar, 50    is the column datatype and the maximum character length

     

     

    Public Class UserLoginForm

     

        Dim user_connection As SqlClient.SqlConnection

        Dim user_reader As SqlClient.SqlDataReader

        Dim user_command As SqlClient.SqlCommand

        Dim select_string As String = Nothing

     

     

     

        Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click

     

     

            If Me.UsernameTextBox.Text = "" Or Me.PasswordTextBox.Text = "" Then

                MsgBox("Please enter a User Name and a Password")

                Exit Sub

            End If

     

            user_connection = New SqlClient.SqlConnection(My.Settings.YourConnectionString)

            select_string = "Select * From Users Where UserName = @UserName And Password = @Password"

     

            user_command = New SqlClient.SqlCommand(select_string, user_connection)

     

            With user_command

                .Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = Me.UsernameTextBox.Text

                .Parameters.Add("@Password", SqlDbType.NVarChar, 50).Value = Me.PasswordTextBox.Text

            End With

     

            If user_command.Connection.State = ConnectionState.Closed Then

                user_command.Connection.Open()

            End If

     

            user_reader = user_command.ExecuteReader()

     

            If user_reader.HasRows Then

     

                While user_reader.Read

                    user_password = user_reader("Password").ToString

                    access_level = user_reader("AccessLevel").ToString

                End While

     

                MsgBox("Login Successfull")

     

            Else

                MsgBox("Incorrect User Name or Password")

     

            End If

     

                user_reader.Close()

                user_command.Connection.Close()

                user_connection.Close()

     

        End Sub

    Saturday, December 08, 2007 1:25 AM
  •  

    Dan,

     

    First of all, not everyone here is a guy.

     

    I think you hit on some very sensible questions. REALLY sensible. The designer doesn't produce code... in fact it's almost anti code.

     

    There are tons of good learning projects. Write a taskmanager, a search program or a database centered contacts program. There are tons of good examples ...right here. Many people forget that each forum has a good forum specific search.

     

    Good luck!

     

     

    Saturday, December 08, 2007 1:34 AM
  • Hi js06,

     

    Thank you very much for the code you supplied. I have been playing around with it alot. Eventhough some of the sql statements blow me away it is still a good way for me to study. I do however came to a stop and would really apprecite if you could have alook at this for me.

     

    I did make some changes the are all (marked in red in breackets)

     

    Public Class UserLoginForm

     

    Dim user_connection As SqlClient.SqlConnection

    Dim user_reader As SqlClient.SqlDataReader

    Dim user_command As SqlClient.SqlCommand

    Dim select_string As String = Nothing

    Dim user_password As String (Had to add this)

    Dim access_level As String (Had to add this)

     

     

     

    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click

     

     

     

     

    If Me.UsernameTextBox.Text = "" Or Me.PasswordTextBox.Text = "" Then

    MsgBox("Please enter a User Name and a Password")

    Exit Sub

    End If

     

     

    user_connection = New SqlClient.SqlConnection(My.Settings.MyDatabase_1ConnectionString)

    select_string = "Select * From TblUsers Where UserName = @UserName And Password = @Password" (Changed TblUsers, I did not take the quotes out, because when I did the Select statement had syntax error)

     

    Please excuse me if I look, sound and act dumb. I think this is where I am going wrong. However please go read on below

     

     

    user_command = New SqlClient.SqlCommand(select_string, user_connection)

     

     

    With user_command

    .Parameters.Add("@UserName", SqlDbType.NChar, 10).Value = Me.UsernameTextBox.Text (Changed from NVarChar, 50)

    .Parameters.Add("@Password", SqlDbType.NChar, 10).Value = Me.PasswordTextBox.Text (Changed from NVarChar, 50)

    End With

     

     

    If user_command.Connection.State = ConnectionState.Closed Then

    user_command.Connection.Open()

    End If

     

     

    user_reader = user_command.ExecuteReader() (Here is where the program actually pauses when I enter the login details and press enter)

     

     

    If user_reader.HasRows Then

     

     

    While user_reader.Read

    user_password = user_reader("Password").ToString

    access_level = user_reader("AccessLevel").ToString

    End While

     

     

    MsgBox("Login Successfull")

     

     

    Else

    MsgBox("Incorrect User Name or Password")

     

     

    End If

     

     

    user_reader.Close()

    user_command.Connection.Close()

    user_connection.Close()

     

     

    End Sub

     

     

     

    So there you go that's where I am at. Please could you help me.

     

    Thanks js06

     

    Oh by the way I didnt mean to insult anyone by calling all VB GURUS. By no means does that mean GUYS. It is not gender specific.

     

    Thanks again for all your comments I really will take onboard. I just have to start somewhere.

     

    Thanks Again

    Dan

     

     

     

    Sunday, December 09, 2007 7:42 AM
  •  

     

    "it's not gender specific"

     

    I'm sure Thomas Jefferson meant the same thing when he said, "All men are created equal."

     

    It isn't an accident the erasing the presense of women, by calling everyone guys, happens to coincide with the label used for men.

     

    Sunday, December 09, 2007 8:01 AM
  • Hi 4173dan,

     

    i look to jeff's code that he gave me last last last month?! and i found a username and password validation....

    Here's The Code:

     

    First you need to declare a variable which is a username and password

     

    Dim myuser = Textbox1.Text  <----username

    Dim mypassword = Textbox.Text

     

    Make a New ConnectionString

     

    Dim conn As New Oledb.OledbConnection("Connectionstrings") <-- you can found various connectionstrings that you'll needed for your future project or you forgot what's the connectionstring appropriate to your application.

     

    Make a SQL Command

     

    Dim cmd As New Oledb.OledbCommand("Select Count(username) From Table Where username" ' & myuser & ' " And " ' mypassword ' " ", conn) <--i  forget the exact quotes try to play with them

     

    Open the Connection

     

    Conn.Open

     

    Make a Statement

     

    If cmd.ExecuteScalar = 1 Then

    msgbox("user found")

    else

    msgbox("user not found")

    End If

     

    Close the Connection

     

    conn.Close

     

     

    It's only implies the validation of username and password.... if you want to add some logintype which is administrator or user i think jeff code is more appropriate for this...

    Sunday, December 09, 2007 9:43 AM
  • Dan,

     

    I am not sure what you mean by it pauses.

    Does it just not do anything?

     

    Can you be more specific?

     

    It can take a little bit to connect to your database on the first try

    I just tried my database connection application and it took about 12 seconds to connect the first time.

     

    I finally added a progressbar to it because sometimes it would take longer and it seemed like my app was hung up.

     

    So try it again and give it a little bit or give some more details on what you mean by pausing.

     

    Sunday, December 09, 2007 11:19 PM
  • Hi js06,

    Sorry if I was'nt clear. When I run the Application, the login form appears. Then I login in using the username and password which I had put in the database table earlier. After a few seconds the form code page appears with a yeloow arrow -> pointing at the statement which reads user_command.connection.Open()

    and a little box opens up and says

     

    SqlExeption was unhandled

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

     

    So while that error was on screen I had alook at the actual login form that I entered. The username and password is still displayed and the mouse is at the egg timer. It's not hung, because I can move the mouose and switch between the pages. However nothing happens from there onwards.

     

    Hope that sort of explains it and I know I sound really dumb.

     

    But your help would be much appreciated.

     

    Thanks again

    Dan

     

     

     

    Monday, December 10, 2007 8:52 AM
  • No problem Dan,

     

    sql server express does not allow you to connect to a database on a remote computer.

    you can do it with code but you have to setup the server to allow remote connections and a few other things.

     

    I had attempted to do this a while back and was able to connect but i could not get the user permissions setup properly so i put that on hold after spending somewhere around 6 hours on it.

     

    There are some examples on the web on how to do it but they did not go into the parts that i was having trouble with.

    Pretty soon i will be moving to visual studio 2008 professional so i won't have to worry about that but if i get it all worked out for express then i will post it.

     

    You can use the code above to connect to a database on your local computer though.

     

    Jeff

    Monday, December 10, 2007 2:18 PM
  • Hi Jeff,

     

    Thanks very much for your reply. However I do not have another computer. This is the only computer I have and the database is located in this computer.

     

    Cheers

    Dan

     

    Monday, December 10, 2007 8:12 PM
  • The only time i have had that error was when trying to connect to a server.

     

    You could see if sql server is setup to start automatically

    start menu

    programs

    sql server 2005

    configuration tools

    SQL Server Surface Area Configuration

    click on surface area configuration for services and connections

    make sure the startup type is set to automatic

     

    you might even try stopping and re-starting

     

    After this i don't know what else could be done off-hand 

    You could start a thread just for that.

     

     

    all else fails - restart your computer

    Monday, December 10, 2007 8:59 PM
  •  

    All of you should use this type of thread it is better
    Friday, February 01, 2008 10:51 PM