Validate:

I have a MS SQL database running with information about users. Password, usernames user levels etc. I have the login information working fine, UserName / UserPassword pulling the data from the database to authenticate. But I'm not sure how to incorporate the UserLevel part. Each person has a different level 0-9, and depending on their level it gives them access to a specific area. Here is the code I have thus far.

Any help would be greatly appreciated, new to the whole asp.net thing.

Windows XP Pro
Web Matrix
MS SQL Server

<--login.aspx--> (Not part of the code)

Sub LoginBtn_Click(Sender As Object, E As EventArgs)
If Page.IsValid Then
Dim userDS As New System.Data.DataSet
userDS = GetUser(UserName.Text, UserPass.Text)
If userDS.Tables(0).Rows.Count = 1 Then
FormsAuthentication.RedirectFromLoginPage(UserName.Text, false)
Else
Msg.Text = "Invalid Username or Password: Please try again"
End If
End If
End Sub

Function GetUser(ByVal userName As String, ByVal userPassword As String) As System.Data.DataSet
Dim connectionString As String = "server='RSA1'; trusted_connection=true; database='users'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT [user_table].[UserName], [user_table].[UserPassword] FROM [user_table] WHE"& _
"RE (([user_table].[UserName] = @dotnet.itags.org.UserName) AND ([user_table].[UserPassword] = @dotnet.itags.org.Us"& _
"erPassword))"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userName.ParameterName = "@dotnet.itags.org.UserName"
dbParam_userName.Value = userName
dbParam_userName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userName)
Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userPassword.ParameterName = "@dotnet.itags.org.UserPassword"
dbParam_userPassword.Value = userPassword
dbParam_userPassword.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userPassword)

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function

Function GetLevel() As System.Data.DataSet
Dim connectionString As String = "server='RSA1'; trusted_connection=true; database='users'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT [user_table].[UserLevel] FROM [user_table] WHERE ([user_table].[UserLevel]"& _
" = 0)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End FunctionLooks like you're on the right track...

To answer your question on how to implement the access levels, I would suggest setting a session variable to store the users level. i.e. Session("Level") = "9"

Then on subsequent pages you can use a select case statement to perform some other action based on the level. i.e.

Select Case Session("Level")
Case "0"
' Show level 0 options
Case "1"
' Show level 1 options
Case "2"
' Show level 2 options
End Select

Also, just wanted to offer another pointer about some of the code...

When declaring object where you are instancing a new object of that type you can use the New Keyword before the object type so your code would look like this:

Instead of this:
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

It could look like this:
Dim dbConnection As New System.Data.IDbConnection(connectionString)

Same with these:
Dim dbCommand As New System.Data.IDbCommand
Dim dataSet As New System.Data.DataSet

And lastly, here's a bit more on how we can succinctly add parameters:

Here's what you have:
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userName.ParameterName = "@.UserName"
dbParam_userName.Value = userName
dbParam_userName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userName)
Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userPassword.ParameterName = "@.UserPassword"
dbParam_userPassword.Value = userPassword
dbParam_userPassword.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userPassword)

Here's another way to write it:
Dim dbCommand As New System.Data.IDbCommand
With dbCommand
.CommandText = queryString
.Connection = dbConnection
.Parameters.Add("@.UserName", userName)
.Parameters.Add("@.UserPassword", userPassword)
End With

I know that less lines of code doesn't mean better code... just wanted to give you another way to look at it.

HTH, intellec