[samuel17]

Hi all,
i have an assignment, yes im a student, and i need to make a login screen that reads values from a sql database. My problem is that im failing miserably. I have visual studio 2010 and SQL server 2008 R2. I have watched videos on youtube but i ran into issues, i did almost exactly what the example in my book has, failed at it, so i figured when all else fails, go get help from the fellow coders plus i want to do something like this just for a fun summer project too so i really would like to learn this. Any how, i will attach the sql script for the database so anyone can take a look if that helps. Here is the current code i have, which gives me an error at runtime saying "Invalid object 'SportEmployee'".

Imports System.Data.SqlClient

Public Class LoginForm
    Dim cnEmployee As New Data.SqlClient.SqlConnection
    Dim daEmployee As New Data.SqlClient.SqlDataAdapter
    Dim sqlCommand As New Data.SqlClient.SqlCommand
    Dim dsEmployee As New Data.DataSet
    Dim db As New SportMotorsDataSet



    Private Sub LoginForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        frmMenu.Show()
    End Sub

    Private Sub LoginForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        frmMenu.Hide()
    End Sub

    Private Sub LoginBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoginBtn.Click


        'connection string
        cnEmployee.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=EBD;Integrated Security=True"
        sqlCommand.CommandText = "Select * " & _
                                 "From [SportEmployee] " & _
                                 "Where EmployeeUsername= '" & UserNameTextBox.Text & _
                                 "'AND EmployeePassword= '" & PasswordTextBox.Text & "';"
        cnEmployee.Open()
        sqlCommand.Connection = cnEmployee
        daEmployee.SelectCommand = sqlCommand
        daEmployee.Fill(dsEmployee) 'Error highlights this

        Dim count = dsEmployee.Tables(0).Rows.Count

        If count > 0 Then
            MsgBox("You have successfully logged in")
        Else
            MsgBox("You suck at life", MsgBoxStyle.Critical)
            UserNameTextBox.Clear()
            PasswordTextBox.Clear()
            UserNameTextBox.Focus()

        End If

    End Sub
End Class

Can anyone explain what the problem is here? I saw a lot of different ways of doing this but none worked right for me.I appreciate the help all.

Attached File(s)

•  CreateSportMotorsLocal.zip (5.15K)
  Number of downloads: 470

[Ionut]

The error appears because SportEmployee doesn't exist in the database you connected to. From connection string I see EBD as database, but in your script is SportMotors. Check that you use the correct database.

[samuel17]

Hey Ionut,
Long time no talk haha Are you following me? haha just messing around! So much going on, so much im trying to do with a week left, i stupidly copied the wrong connection string. Always a great help man, I will try that, thanks tons!

Sam

[samuel17]

It works! whoop! one other question. When i do a successful login in i have it clear the controls, i newly added that so its not on my code above, but even when i then type in the wrong username and password, it still tells me its correct. It works in the reverse order though, if i use an incorrect login first it says its wrong, then i can change it to a correct login in and it says its right. Do i have to do something so it works from a correct login and then an incorrect login, like something needs to be reset?

Thanks again,
Sam

[Ionut]

I see dsEmployees in declared as global variable, so its scope is on the entire application. After you fill it first time(correct login), it remains filled with a that row. Declare all the variables inside LoginBtn_Click. Through all variables I mean, dataset, dataadapter and if you choose to keep the SqlConnection variable as global, call .Close method before you exit from LoginBtn_Click. You don't need 100 connections to the server from one instance of the application

And, because you are the learning part, I would suggest you to consider using parameterized queries. This way you make a query more secure and limit the exposure to sql injections.

[samuel17]

Youre the man Ionut! I was trying to do a parameterized query because thats the example that my book i have shows but i followed it almost exactly, with exception of the connection and variables and stuff and it didnt work. I used a try/catch method with it and it just kept responding with the catch error so i gave up on that and decided to try to find a different method because i only have a week left, i need all the time i can get. I did bookmark that link though, i will try using that for my project i wanna do this summer.

Thanks,
Sam

[samuel17]

Well im on here still, anyone have any good examples of how to set an item in a database, in this case the EmployeeID, to a variable that can then be used or passed to a different form to be used and the usual that a variable would be used for? Any good examples out there of that?

Thanks again,
Sam

[smohd]

You may create a module and put public variable which ca be used any where in your application

[Ionut]

MSDN is always a great place to look for example. In this case you have to use SqlDataReader and get the information you need.
For the passing variable, i would suggest you to use
1. properties
2. pass the data through the contructor of the new form.

To declare a class variable as public is not a good practice and also "violates" the OOP encapsulation principle.

This post has been edited by Ionut: 14 May 2011 - 02:25 PM

[samuel17]

Hi guys,
Sorry to undo all the help you have given but i decided to go back to the parameter approach and try that and it did work. Here is what i have, and be sure to let me know any thoughts on improving this:

Imports System.Data.SqlClient

Public Class LoginForm
    Dim cnEmployee As New Data.SqlClient.SqlConnection
    Dim sqlCommand As New Data.SqlClient.SqlCommand
    Dim db As New SportMotorsDataSet

    Private Sub LoginForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        frmMenu.Hide()
    End Sub

    Private Sub LoginBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoginBtn.Click
        Dim daEmployee As New Data.SqlClient.SqlDataAdapter
        Dim dsEmployee As New Data.DataSet
        Dim user As String
        Dim pass As String
        Dim VarEmployeeID As Long

        user = UserNameTextBox.Text
        pass = PasswordTextBox.Text

        'connection string
        cnEmployee.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=SportMotors;Integrated Security=True"
        sqlCommand.Connection = cnEmployee
        sqlCommand.CommandText = "Select * " & _
                                 "From SportEmployee " & _
                                 "Where EmployeeUsername=@EmpUser And " & _
                                 "EmployeePassword=@EmpPass;"
        sqlCommand.Parameters.Add(New Data.SqlClient.SqlParameter _
                                  ("EmpUser", UserNameTextBox.Text))

        sqlCommand.Parameters.Add(New Data.SqlClient.SqlParameter _
                                  ("EmpPass", PasswordTextBox.Text))

        
        Try
            cnEmployee.Open()
            VarEmployeeID = CLng(sqlCommand.ExecuteScalar)
            If VarEmployeeID <> 0 Then
                MsgBox("You have successfully logged in")
                Orders.lngEmployeeID = VarEmployeeID
                UserNameTextBox.Clear()
                PasswordTextBox.Clear()
                UserNameTextBox.Focus()
                Me.Dispose()
                Orders.Show()
            Else 
            MsgBox("Incorrect Login!", MsgBoxStyle.Critical)
                UserNameTextBox.Clear()
                PasswordTextBox.Clear()
                UserNameTextBox.Focus()
                Me.Dispose()
            End If
        Catch
            MsgBox("Fail!")
        Finally
            cnEmployee.Close()
        End Try
        cnEmployee.Close()





    End Sub
End Class

Just a note, i didnt look this over for leftover old code from my first try, i just commented out a lot of stuff, i will do so now. Thanks for the help guys!

Sam

[smohd]

Since you have import System.Data.SQLClient, just say:

Dim cnEmployee As New SqlConnection
 

and like that.

You dont need to make repetition of code, instead you can create sub and call it.

[samuel17]

o hooray, another issue. Now that i am not using a dataAdapter and dataset, how do reset it so when a login is incorrect that they can try again? The first incorrect login says "Incorrect Login!" as i wanted it to, but then when i put in a correct login it says "Fail!" which is my catch. It's getting there slowly, thanks for the help guys!

Sam

[smohd]

samuel17, on 15 May 2011 - 05:05 AM, said:

o hooray, another issue. Now that i am not using a dataAdapter and dataset,

What are you using? To do it again just allow user to return to your form again and input value again.
Also you dont need to dispose the form if login failed

This post has been edited by smohd: 14 May 2011 - 04:31 PM

[samuel17]

i did it with a parameter query to do this. When the form comes back up i can enter new values in the textboxes but no matter the value it gives me the value from my catch, I'd like to be able to keep that form open and just reset compared to opening another form, disposing the login form and then making the user have to go back and open the form again. Any ideas?

Sam

[smohd]

I didnt get clear what the problem is, but if you need to give user another chance to try login then in the else block you need only to clear components and then give a message. This will make him to remain to the login form and retry. The code in catch block are executed only if an error occurs in the try block, so if that happens means you need to debug to see if the connection is done correctly.