Link to home
Start Free TrialLog in
Avatar of myndwire
myndwire

asked on

How to create a login form for sql server

I'm new to vb but have some experience with sql server. I'm trying to learn to build sql server 2005 front end apps with vb.net 2008. I would like to create a login form for my startup form and store the username/password in variables that I can use throughout the app so the user only has to enter them once. I will be using sql server authentication since I may potenially have clients accessing the database from offices not connected to the domain where the sql server resides. I tried to find a tutorial or sample app but cant seem to find anything. Can anyone help or point me in the right direction?
Avatar of townsma
townsma
Flag of Indonesia image

If you wish to use SQL authentication, it is quite easy, on a normal form get the usename and password, then use the username and password to connect to the database. Just use it in the connection string.
Try this links:
http://download.microsoft.com/download/c/2/3/c2318968-80aa-43de-a755-9c0763a2dca8/VB101SamplesAll.msi


This link talks about vb and mysql connection, BUT it really helps, you can apply it in MS sql if you have truly understand the Sql basics connections.

http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-1


Good Luck!
:)
Hi there,

There are a couple of things you may want to consider when building such an application. Firstly you would be best to implement a seperate class to handle all your database transactions, such that you dont have to repeat code constantly. With regards to your logon requirments. All you would have to do is specify your SQL database username/password in the login string. Of course that being said it is also good to keep this information encrypted. As such storing any connection string in your app.config is stored in plain text so might want to look at other ways of ensuring that it is kept safe.

I have uploaded the example to ee-stuff and you can find it here
https://filedb.experts-exchange.com/incoming/ee-stuff/7264-LoginExample.zip

I have also attached the code fromt he frmLogin so that other experts may add additional information to the question

Regards
Steve
Imports DataAccess
Imports System.Data.SqlClient
 
Public Class frmLogin
 
    Sub New()
 
        ' This call is required by the Windows Form Designer.
        InitializeComponent()
 
        ' Add any initialization after the InitializeComponent() call.
 
        'You only need to specify the connection string once to the data access layer
        DataAccess.DAL.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=Example;Persist Security Info=True;User ID=ExampleUser;Password=password"
 
    End Sub
 
    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        'Wrap the login attempt to catch any errors that may occur
        Try
            'The parameters hold the username and password that are to be passed to the database for authentication
            'The database contains 1 table named 'Users' with 2 columns 'Username' and 'Password'
            'The datareader provides a read only method of returning data and is the quickest way of returning data from a table
            'The data access layer provided can be used in similar ways to retreive datatables and dataset and also can use stored procedures
 
 
            'Declare the parameters to hold username and password
            Dim Parameters(1) As SqlParameter
            'Set peram 0 to lookup against the 'Username' field with the text from the username textbox
            Parameters(0) = DAL.CreateParameter("@Username", DbType.String, txUsername.Text)
            'Similarly use peram 1 to lookup the 'Password' field with the text from the password textbox
            Parameters(1) = DAL.CreateParameter("@Password", DbType.String, txPassword.Text)
            'Create the sql statement that will do the actual look of the user in the database
            Dim SQL As String = "SELECT * FROM Users WHERE Username=@Username AND Password=@Password"
            'Create a new datareader instance and execute the sql statement to try and lookup and authenticate the user
            Dim Reader As SqlDataReader = DAL.ExecuteReader(CommandType.Text, SQL, Parameters)
 
            'If the reader has any rows there a user matching the username and password has been found
            If Reader.HasRows Then
                While Reader.Read
                    MsgBox("User: " & Reader.Item("Username") & " authenticated.")
                    'Do additional login stuff here such as show next form
                End While
            Else
                'Else if there is nor eturned rows then there is no user matching the details provided
                MsgBox("Incorrect Username or Password")
            End If
 
        Catch ex As Exception
            'Display any error that occurs
            MsgBox("An error occured" & vbCrLf & "Error details: " & ex.ToString)
        End Try
 
        'Other info that you should look into...
        'Store passwords in the database as encrypted string and encyrpt the password field to match the encrypted field before doing the username check.
 
    End Sub
 
End Class

Open in new window

Another bit of advice is you may also want to shift the logic side of things to another layer in the project and adopt a tiered architecture such that your interfact/presentation layer is seperate from any logic or direct interactions with the database. This will allow for much easier changes to be made to the application throughout its lifetime.
Avatar of myndwire
myndwire

ASKER

Thanks for all the quick replies. They definately got me going in the right path. One thing I am a little confused about..do I need to declare some kind of global variable that stores the values from the text boxes where the user enters their user name and password and the refer to those variables in the connection string? That was my initial line of thinking, but as I said, I'm a beginner at this so I wasnt sure how to implement.
Are you trying to authenticate the user agains an account created for them in SQL Management studio to actually access the database or a user record within the table?

If it is a user account to defined by management studio just dot he following with the connection string provided above..

You dont have to declare the username and password in the same class these could be shared fields or properties anywhere in the project and can be used like this.. Bearing in mind with the DAL provided in my solution you only need to set the connection string once.

Regards
Steve
    Public Username As String = ""
    Public Password As String = ""
    Sub New()
 
        ' This call is required by the Windows Form Designer.
        InitializeComponent()
 
        ' Add any initialization after the InitializeComponent() call.
 
        'You only need to specify the connection string once to the data access layer
        Me.Username = Me.txPassword.Text
        Me.Password = Me.txPassword.Text
 
        DataAccess.DAL.ConnectionString = String.Format("Data Source=.\SQLEXPRESS;Initial Catalog=Example;Persist Security Info=True;User ID={0};Password=(1)", Username, Password)
 
    End Sub

Open in new window

Yes you have to initialize a global variable if you want the variable to be access in the other form, and declare as public.


As I have said, follow this link and read the codes, and you will notice what do to or not.

This link talks about vb and mysql connection, BUT it really helps, you can apply it in MS sql if you have truly understand the Sql basics connections.

http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-1


Good Luck!
:)
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial