fetching a single row 1

[Mdiaz]

I am an avid ASP programer, but I seem to be having problems getting thing done with asp.net. My first asp.net page is a login page where I check to see if the user exists, then if he exists I need to fetch data from two tables so that I can set some session variables. I can't seem to do it. I have several books and have searched this site but still no clue. Maybe I'm hard headed...

Sub LoginBtn_Click(Sender As Object, E As EventArgs)
               If Page.IsValid Then
                   Dim userDS As New System.Data.DataSet
                   userDS = MyQueryMethod(UserName.Text, UserPass.Text)
               
    
                       If userDS.Tables(0).Rows.Count = 1 Then
                                'Here I need to fetch user and agency data from tables and set session variables.
                                'like - DeptNum, full name, and permissions user has
                                'then set the session variables like ... session("administartor") = "Yes"
                                
                             
                             
                              Msg.Text = "Credentials Verified! Welcome."
                       Else
                           Msg.Text = "Invalid Credentials: Please try again"
                       End If
               End If
           End Sub
    
    
           Function MyQueryMethod(ByVal userLogin As String, ByVal pass As String) As System.Data.DataSet
               Dim connectionString As String = "server='111.11.111.11'; user id='someuserID'; password='SomePassword'; Database='coolDB'"
               Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
    
               Dim queryString As String = "SELECT * FROM [Agency], [Users] WHERE (([Users].[UserLogin] = @UserLogin"& _
       ") AND ([Users].[Pass] = @Pass)) AND ([Users].[DeptID] = [Agency].[DeptID])"
               Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
    
               sqlCommand.Parameters.Add("@UserLogin", System.Data.SqlDbType.NVarChar).Value = userLogin
               sqlCommand.Parameters.Add("@Pass", System.Data.SqlDbType.NVarChar).Value = pass
    
               Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)
               Dim dataSet As System.Data.DataSet = New System.Data.DataSet
               dataAdapter.Fill(dataSet)
               
               Return dataSet
           End Function

 

[digiduck]

for setting the session variables from the data in the table try...

If userDS.tables(0).rows.count = 1 then
   session("DeptNum") = userDS.tables(0).rows(0)("DeptNum")
   session("FullName") = userDS.tables(0).rows(0)("FullName")
......
......

If there is any chance for the data in those fields to be empty be sure and add a check in there like this to avoid an error.

   if not userDS.tables(0).rows(0)("DeptNum") is dbnull then session("DeptNum") = userDS.tables(0).rows(0)("DeptNum")

Gone looking for myself. Should I arrive before I'm back, keep me here.

 

[Mdiaz]

DigiDuck,

Thanks for the help.

It worked fine.

Mike Diaz...