Login Form with added dataset

[DimMeAsLost]

All,
This is a great site with alot of information. My background is mostly Access VBA and I am having to learn VB.NET. I am trying to recreate an Access 2007 application I wrote into VB.Net. We are looking at using a hosted SQL Server so we do not have to worry about remote offices or replication. I am using VS 2008 and SS2008.

My issue is that I need to create a login form in VB that with look at SQL Server DB and return a dataset record matching the username and password. There are many tutorials on here but do not match what I am looking for.

I have a stored procedure with 2 parameters (@prmUser and @prmPassword) named spLogin.

I need to call the sp and pass the parameters to SQL Server DB and return the complete row of data that is matched from the button_click event on my form.

In Access, I would have a form that would have the returned data and I would reference that information throughout the application by using the 'forms!frmName!txtValue' to populate forms, reports, and fields that would capture the user that created the record. What is the best practice for this?

Next when the login credentials are correct then I need another form to open, which is my MDI form, if not correct then msgbox to try again.

Thank you all in advance for any help

Mark

 

[demausdauth]

Right click your project and go to Properties. On the Application tab near the bottom of the tab is a button "View Application Events", click this. It will open the Application events and add to your project.

Create, if you haven't already, a Login form. I made a simple one with 2 textboxes and 2 buttons (Login,Cancel).

Login form code

Public Class frmLogin

    Dim dtLoggedInRowInformation As DataTable


    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.DialogResult = Windows.Forms.DialogResult.Cancel
        Me.Close()
    End Sub

    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click

        If Not String.IsNullOrEmpty(txtUserName.Text) AndAlso Not String.IsNullOrEmpty(txtPassword.Text) Then

            If Not IsLoggedIn(txtUserName.Text, txtPassword.Text) Then
                MessageBox.Show("Username/Password was not correct")

                'clear textboxes
                txtPassword.Text = ""
                txtUserName.Text = ""

            Else
                ' login was successful
                Me.DialogResult = Windows.Forms.DialogResult.OK
                Me.Close()

            End If

        Else
            MessageBox.Show("Username/Password cannot be blank")
        End If

    End Sub

    Private Function IsLoggedIn(ByVal PassUserName As String, ByVal PassPassword As String) As Boolean

        'this datatable will be available on this form 
        dtLoggedInRowInformation = New DataTable()

        Using sqlCon As New SqlClient.SqlConnection("MyConnectionString")

            If Not sqlCon.State = ConnectionState.Open Then sqlCon.Open()

            Using sqlCmd As New SqlClient.SqlCommand("MyStoredProcedureName", sqlCon)
                sqlCmd.CommandType = CommandType.StoredProcedure

                'Make parameters
                sqlCmd.Parameters.Add("@prmUser", SqlDbType.VarChar, 20)
                sqlCmd.Parameters.Add("@prmPassword", SqlDbType.VarChar, 20)

                'Fill values
                sqlCmd.Parameters("@prmUser").Value = PassUserName
                sqlCmd.Parameters("@prmPassword").Value = PassPassword


                dtLoggedInRowInformation.Load(sqlCmd.ExecuteReader())

            End Using
        End Using

        If dtLoggedInRowInformation.Rows.Count > 0 Then
            Return True
        End If

        Return False

    End Function
End Class

Then open the ApplicationEvents class and from the left hand dropdown choose "MyApplication Events" and from the right hand dropdown choose Startup.

startup event

        Private Sub MyApplication_Startup(ByVal sender As Object, ByVal e As Microsoft.VisualBasic.ApplicationServices.StartupEventArgs) Handles Me.Startup

            Dim LoginForm As New frmLogin

             If Not LoginForm.ShowDialog() = DialogResult.OK Then
                'If e.cancel is true then the application ends otherwise loads normally
                e.Cancel = True
            End If
        End Sub

Note I did not have a database available to run this against, however it is the same way that I have setup another login form. Also there is no error trapping in this code, something for you to add.

 

[JohnH]

Dim LoginForm As New frmLogin

If Not LoginForm.ShowDialog()

This form instance must be disposed manually by you, see explanation in remarks section here Form.ShowDialog Method (System.Windows.Forms).
An alternative is to not create another form instance and instead just use the default one, see for example How to: Access a Form.

 

[demausdauth]

Thanks JohnH

 

[DimMeAsLost]

Thanks so much for you help. I had to go out of town for meetings and had no time to work on this. I will try it out this evening and report back. Thanks again.

 

[DimMeAsLost]

I have created everything as requested.

When I debug, the frmLogon form opens. I enter username and password, and then the login form opens again.

I guess my next question is shouldnt my form called frmMain be the next to open after succesful login?

Second, how can I access and call the dataset?

Thanks for your help

 

[DimMeAsLost]

OK, so I can access the data in the datatable that was created, however by looking at the code you had written, the SqlDataReader has not been closed, therefore I cant do any other actions to the database until it is closed. Which in turn the datatable loses its data.


I need to rethink the process a little.

1. Applications opens to frmLogin
2. User logs in (successful next step 3 / unsuccessful user retries)
3. frmLogin closes, dataset row returned
4. frmInformation has textboxes for each column of the row returned
5. frmInformation textboxes are populated with the data returned
6. frmInformation is hidden
7. Connection is closed
8. frmMain (MDI) is opened


Thanks again for all of your help

 

[demausdauth]

Ok, first the login form reopening after login -- is probably because you have the startup form set to the login form. You want to change this to either frmInformation or frmMain, which ever makes the most sense to you. Because, within the StartUp Event we are interrupting the applicaiton process before that startup form, doing the login, and then allowing it to either continue with starting up or telling the application that it doesn't startup.

Second, the datatable dtLoggedInRowInformation is declared as a form level variable, it will only be available to this form. Once it is closed the the information is disposed of. Which leaves you a couple choices: 1) Pass the datatable information back, 2) make the datatable an application level global variable, 3) re-get the information when you need it.

Personally I try to avoid making global variables, I have seen them cause too many problems. You can pass the datatable information back through a property, or re-query the database, the latter being what I would recommend.

I have a last question, why fill the frmInformation then hide it right away? What is the purpose behind frmInformation?

 

[DimMeAsLost]

Thank you for your reply. I will note the change to the startup form and see if this helps me.

The main reason I want the data is because all the user information is in that row of data. Their user level, address, phone, fax, extension, cell, division, etc.

I use this information to populate reports, records are save with user name and location so we can sort records by individuals, the list goes on. I wanted to be able to reference by stating =forms!frmInfo!textbox1.text

 

[JuggaloBrotha]

forms!frmInfo!textbox1.text

Good ol' VBA syntax in the modern world. I used to feel your pain.

 

[demausdauth]

It would be better to create a specific class to store the information related to the user and then use that through out the application. This way you can keep the data typed correctly at all times, by this I am assuming that you would load each value from the dataset into a textbox and then get the information as you needed it. Any numerical values would be stored as strings in textboxes, and have to be converted to do math. Whereas keeping the values typed as decimals, integers, etc.. in a separate class you wouldn't need to do type conversions to use them.

Now I know I said that I try not to use global application variables but in this case you may want to.

 

[demausdauth]

So how does this figure in to what you are trying to do? Here is a quick and simple mock up.

I created a new module and gave it the name modUser, then I added a public class to the module.

Imports System.Data
Imports System.Data.SqlClient


Module modUser

    Friend ThisAppUser As MyAppUserInformation

    Public Class MyAppUserInformation

        Private mName As String = String.Empty
        Private mAddress1 As String = String.Empty
        Private mAddress2 As String = String.Empty
        Private mAddress3 As String = String.Empty
        Private mAddress4 As String = String.Empty
        Private mCity As String = String.Empty
        Private mStateProv As String = String.Empty
        Private mPostalCode As String = String.Empty
        Private mHourlyPay As Decimal = 0D

        'Properties
        Public ReadOnly Property Name() As String
            Get
                Return mName
            End Get
        End Property

        Public ReadOnly Property Address1() As String
            Get
                Return mAddress1
            End Get
        End Property

        Public ReadOnly Property IsValidUser() As Boolean
            Get
                'the not reverses the result from IsNullOrEmpty, True becomes False, and False becomes True
                ' if mName is String.Empty, IsNullOrEmpty returns True, and viceversa
                Return (Not String.IsNullOrEmpty(mName))
            End Get
        End Property




        'cosntructors
        Public Sub New(ByVal UserName As String, ByVal Password As String)

            Dim dtUser As New DataTable("User")

            If LogIn(UserName, Password, dtUser) Then

                'continue to set all the other values from the datatable
                mName = dtUser.Rows(0)("username").ToString()
                mAddress1 = dtUser.Rows(0)("Address1").ToString()
                mAddress2 = dtUser.Rows(0)("Address2").ToString()
                mAddress3 = dtUser.Rows(0)("Address3").ToString()
                mAddress4 = dtUser.Rows(0)("Address4").ToString()
                mCity = dtUser.Rows(0)("City").ToString()
                mStateProv = dtUser.Rows(0)("StateProv").ToString()
                mPostalCode = dtUser.Rows(0)("PostalCode").ToString()

                If dtUser.Rows(0)("HourlyPay") IsNot DBNull.Value Then
                    Dim TempHourly As String = dtUser.Rows(0)("HourlyPay").ToString()
                    If Not String.IsNullOrEmpty(TempHourly) Then
                        mHourlyPay = Convert.ToDecimal(dtUser.Rows(0)("HourlyPay").ToString())
                    Else
                        mHourlyPay = 0D
                    End If

                Else
                    mHourlyPay = 0D
                End If

            End If


        End Sub


        'Private Methods
        Private Function LogIn(ByVal PassUserName As String, ByVal PassPassword As String, ByRef dtPassUserInformation As DataTable) As Boolean
            'this datatable will be available on this form 
            Using sqlCon As New SqlClient.SqlConnection("MyConnectionString")

                If Not sqlCon.State = ConnectionState.Open Then sqlCon.Open()

                Using sqlCmd As New SqlClient.SqlCommand("MyStoredProcedureName", sqlCon)
                    sqlCmd.CommandType = CommandType.StoredProcedure

                    'Make parameters
                    sqlCmd.Parameters.Add("@prmUser", SqlDbType.VarChar, 20)
                    sqlCmd.Parameters.Add("@prmPassword", SqlDbType.VarChar, 20)

                    'Fill values
                    sqlCmd.Parameters("@prmUser").Value = PassUserName
                    sqlCmd.Parameters("@prmPassword").Value = PassPassword


                    dtPassUserInformation.Load(sqlCmd.ExecuteReader())

                End Using
            End Using

            If dtPassUserInformation.Rows.Count > 0 Then
                Return True
            End If

            Return False

        End Function
    End Class

End Module

How do we use this?

Login form

Public Class frmLogin

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.DialogResult = Windows.Forms.DialogResult.Cancel
        Me.Close()
    End Sub

    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click

        If Not String.IsNullOrEmpty(txtUserName.Text) AndAlso Not String.IsNullOrEmpty(txtPassword.Text) Then

            modUser.ThisAppUser = new modUser.MyAppUserInformation(txtUserName.Text, txtPassword.Text)

            If modUser.ThisAppUser.IsValidUser Then
                ' login was successful
                Me.DialogResult = Windows.Forms.DialogResult.OK
                Me.Close()

            Else
                MessageBox.Show("Username/Password was not correct")

                'clear textboxes
                txtPassword.Text = ""
                txtUserName.Text = ""


            End If

        Else
            MessageBox.Show("Username/Password cannot be blank")
        End If

    End Sub   
End Class

Then on any other form that you need to get information on you would do this

txtName.Text = modUser.ThisAppUser.Name
txtAddressLine1.Text = modUser.ThisAppUser.Address1
etc....

The class allows you make the information available and keep it together, and gives you a single place to make changes to information concerning the user. Also this way there is not a form on the UI thread waiting to cause potential problems, perhaps in trying to update information.

Incidentally I used readonly properties, meaning that the information can only be read and not updated in the class.

 

[DimMeAsLost]

Looks like this is what I am needing. I will be trying this out and see if it works for me. Thank you so much for your time and efforts in helping me in my quest.

 

[DimMeAsLost]

So let me follow what you have:

1. Under the Public Class I need to declare each item I want returned as 'Private'

2. Under the 'Properties, I need to Get and Return each item I have listed in the Public Class.

3. Under 'Constructors, I need to follow the syntax you have for each item to list its datatype.

4. I do not have an 'Hourly Pay' field, but you gave me a demonstration on how to convert the data type from string to decimal. (just making sure)


Again, thanks for all of your help. Much appreciated!!!!!

 

[demausdauth]

1. Under the Public Class I need to declare each item I want returned as 'Private'

You could declare each data member as public and directly access them, however I prefer to use Private data members and Properties to access them. Utilizing properties to access data members gives you more control over the data. One of my main reasons is you get the chance to validate data assigned (in the Set) and to validate data before returning it (in the Get).

2. Under the 'Properties, I need to Get and Return each item I have listed in the Public Class.

I made the example properties readonly. This was because the information to be stored in the class was internally queried and set. You may decide that you want to allow the Address lines to be changed, then you would want to make the properties read and write. That is something that only you will know.

'This property allows you to set and get information from the class.
        Public Property City() As String
            Get

            End Get
            Set(ByVal value As String)

            End Set
        End Property
'This property allows you to only set information to the class.
        Public WriteOnly Property Address1() As String
            Set(ByVal value As String)
                  If Not String.IsNullOrEmpty(value) Then
                       mAddress1 = value
                  Else
                        mAddress1 = "Default Value" 
                  End If
            End Set
        End Property
'This property allow you to only read information from the class.
        Public ReadOnly Property Address2() As String
            Get
                 Return mAddress2
            End Get          
        End Property

3. Under 'Constructors, I need to follow the syntax you have for each item to list its datatype.

I think a provided a fairly decent model to follow, but I wouldn't say you need to write the code the same as I did, that you can determine on your own. Checking for the DBNull.Value I think is pretty important, even when dealing with String types. Essentially you are transferring the data from the datatable to the class.

4. I do not have an 'Hourly Pay' field, but you gave me a demonstration on how to convert the data type from string to decimal. (just making sure)

That's correct, but it's not the only way to do it. As with all things programming related there are as many ways to do it as there are programmers. There is the Decimal.TryParse() method and various others. If you think that the field could be null then I would make sure to check for the DBNull.Value first even if it is a string value in the db.