Login Screen

[dctechuser]

I'm in a learning phase of Vb, and I am trying to develop a Login form that will look in the sequel server database and pull the UserName and Password along with role.

I have setup a User table in the Sequel Server 2005 database. The fields are: Username, Password, and Admin.

I have created the windows form with a UserNameTextBox and PasswordTextBox. I have loaded a UserTableAdapter and created a Query called Login:

The Select Statement is setup as:

SELECT COUNT(*) AS Expr1, Admin
FROM Users
WHERE (UserName = @Username) AND (Password = @Password)
GROUP BY Admin

This query runs and gives me the correct record when the UserName and Password parameters are passed.


My Visual Basic coding for the Login button event when clicked:


Try
If CType(Me.UsersTableAdapter.Login(Me.UserNameTextBox.Text, _
Me.PasswordTextBox.Text), Integer) > 0 Then

MessageBox.Show("Welcome")
Else
MsgBox("Invalid UserName and Password.")

End If

Catch ex As Exception
MsgBox(ex.ToString)
End Try

End Sub

I need to capture what is in the Admin field. If the Admin field contains a "1", I want an Admin Menu to load. If Admin field contains a "0", I want a tech form to load after the the OK button is clicked on the Welcome message box. If I can capture what is in the Admin Field, I can write an If statement to do what I want if the AdmintextBox.text = "1" or "0". I just need to be able to reference the field somehow.

Any help getting me in the right direction is appreciated.

dctechuser

 

[RiverGuy]

Try something like this:

YourCommandObject.CommandText = "SELECT SUM(Admin) AS Admin FROM  Users WHERE UserName = @UserName AND Password = @Password"

Dim o As Object = YourCommandObject.ExecuteScalar()
Dim HasAccess As Boolean = False
Dim IsAdmin As Boolean = False

If IsDBNull(o) Then
   'Invalid User
Else
   HasAccess = True
   IsAdmin = (Convert.ToInt32(o) = 1) 
End If

 

[dctechuser]

Riverguy,

I'm not sure I understand all of that. YourCommandObject.Commandtext = Select Statement. I understand what your Select statement is doing, but the other parts I do not.


Can you explain a little more clearly. I am new to this.
Thanks
dcTechuser

 

[RiverGuy]

Your problem might be that you are using a TableAdapter. TableAdapters hide a lot of the data access code from the end user. I've never used one and never intend to. If you want to learn about ADO.Net, you will need to learn about DataSets, DataTables, DataReaders, DataAdapters, Connections and Command Objects. Using TableAdapters will hide all of that from you.

My code assumes that you have a SqlConnection and SqlCommand object. You then pass in a query to get the user data. The .ExecuteScalar() method expects a single row with a single column returned by the query. It then stuffs the value of that into the o object variable.

Now, if the variable is null, then the user is not valid. If o as an integer is 1, then the user is an Admin. If o as in integer is 0, then the user is a valid user, just not an admin.

 

[Sorwen]

There is nothing wrong with what you are doing and it may actually be the best way for your situation. I would make a suggestion that unless multiple people use the same windows login and if your in a network envrioment you use windows login user instead of doing your own Username/Password authentication. You save yourself a host of possible problems. If your admin users do not always need to login as admin then what you do is have a special admin shortcut and send your program a command line string such as -admin or something that will have it start in admin mode where they could enter in a username and password to verify their admin rights. That way you are only dealing with a small group of people that you have to worry about forgotten username/passwords, stolen passwords, etc.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!