Link to home
Start Free TrialLog in
Avatar of nhorsley
nhorsley

asked on

Defining Sesssion Values from sql db

I'm trying to pull together all the session values from a user registration table so I can use those values in various forms.

When a user who is already registered tries to login, the following code works but I can't figure out how to add additional fields as session values.

This is the login code:

<%@ Page Language="VB" %>

<script runat="server">

    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 Credentials: Please try again or " + "<a href='NewUsers.aspx'>register a new user</a>"
    End If
    End If
    End Sub
   
   
        Function GetUser(ByVal username As String, ByVal password As String) As System.Data.DataSet
            Dim connectionString As String = "server='(local)'; trusted_connection=true; database='BCA'"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
   
            Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[username] = @username) AND ([Users"& _
    "].[password] = @password))"
            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_password As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
            dbParam_password.ParameterName = "@password"
            dbParam_password.Value = password
            dbParam_password.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_password)
   
            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

</script>
__________________________

Presently I am obtaining the session value for the username by getting it from the login form by using this code on the login page where the user enters the username and password:
<%Session("Username") = Request.Form("Username")%>

I also want to obtain a Session("Email") and a Session("Company") values but those values are only available from the Registration table named Users.  I see that I'm creating a select query to that table that should give me access to those values but I can't figure out how to make the values available so that I can use them as session values.

Using the code above, will you show me how I do that?

Thank you.
Avatar of daffodils
daffodils

So the select query returns User details from the database and you want to access these values and store them in a Session variable.. am I reading this right.

You can access the values from the DataSet (if you have a typed dataset) as:
Dim str As String
str = myDataSet.<table>(rownum).column

If you are using an untyped dataset.. then this would work...
Dim s As String
s = CType(myDataSet.Tables("Users").Rows(0).Item("Email"), String)

Something like this .. would give you the value of column "Email" from the 'first' row of the "Users" table.
For typed dataset....     Session("email") = myDataSet.Users(0).Email
For untyped dataset....  Session("email") = CType(myDataSet.Tables("Users").Rows(0).Item("Email"), String)

Dim connectionString As String = "server='(local)'; trusted_connection=true; database='BCA'"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
   
            Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[username] = @username) AND ([Users"& _
    "].[password] = @password))"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
            Dim dbReader As System.Data.SqlClient.SqlDataReader
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection
            dbConnection.Open()
            dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
            dbReader.Read()
            Session("Email") = dbReader("Email")
            Session("Company") = dbReader("Company")
            Whatever else you need:

Regards,

Aeros
Oh.. I just went thru your code again.. you have an untyped dataset..
that option would be your answer
Avatar of nhorsley

ASKER

I've tried to implement the suggestions but I am confused about where I would put the untyped dataset code:

Session("email") = CType(myDataSet.Tables("Users").Rows(0).Item("Email"), String)

I realize this is harder than I thought it would be so I raised the points.
Just before you return the dataset..

dataAdapter.Fill(dataSet)
--> here
Session("email") = CType(myDataSet.Tables("Users").Rows(0).Item("Email"), String)
Return dataSet

It is not overtly complicated.. you are retrieving the value "Email" from the required column in the first row of your table.. and just type setting it as string.

And wherever you want to retrieve it .. just remember to check it is not null and then typeset it again.. Session storage is like a key-value pair - some sorta hashtable.. it stores data against a key and does not remember the type.
I made the change and got this error:

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 45:             Dim dataSet As System.Data.DataSet = New System.Data.DataSet
Line 46:             dataAdapter.Fill(dataSet)
Line 47:           Session("email") = CType(dataSet.Tables("Users").Rows(0).Item("Email"), String)
Line 48:
Line 49:             Return dataSet
 

Source File: C:\Inetpub\wwwroot\memberarea\login.aspx    Line: 47

Try
Session("Email") = dbReader("Email")

or

Session("Email") = CStr(dbReader("Email")

Regards,

Aeros
Oh.. I need someone with VB.NET experience here.. I know this works in C# as

Session["email"] = (string) myDS.Tables["Users"].Rows[0]["Email"];

You need the VB.NET equivalent..

I submitted a new question to VB.net section.
About how to convert that line of code.
Okay.. have you tested the Sql query.. does it return any rows??

If you are not planning on updating any data in the table.. it might be a much light-weight  option to use a datareader.. and it is easier to access data in a reader.
The login process works fine as it is.  And I don't know how to test the sql query.  I used to know in asp but I don't know how in asp.net.
I meant testing it in SQL Query Analyzer... from SQL Server tools.
When I tried to use the following code:

Dim connectionString As String = "server='(local)'; trusted_connection=true; database='BCA'"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
   
            Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[username] =" & Session("Username")
            Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
            Dim dbReader As System.Data.SqlClient.SqlDataReader
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection
            dbConnection.Open()
            dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
            dbReader.Read()
            Session("Email") = CStr(dbReader("Email"))
            Session("Company") = CStr(dbReader("Company"))

I got this error:

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30451: Name 'dbReader' is not declared.

Source Error:

 

Line 45:             Dim dataSet As System.Data.DataSet = New System.Data.DataSet
Line 46:             dataAdapter.Fill(dataSet)
Line 47:               Session("Email") = CStr(dbReader("Email")
Line 48:
Line 49:
 

Source File: C:\Inetpub\wwwroot\memberarea\login.aspx    Line: 47

Ok, I tested it and the query works.
VB.NET Conversion
Session("email") = CType(myDS.Tables("Users").Rows(0)("Email"), String)
I used your VB code and it threw this error:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 45:             Dim dataSet As System.Data.DataSet = New System.Data.DataSet
Line 46:             dataAdapter.Fill(dataSet)
Line 47:               Session("email") = CType(dataSet.Tables("Users").Rows(0)("Email"), String)
Line 48:
Line 49:             Return dataSet
 

Source File: C:\Inetpub\wwwroot\memberarea\login.aspx    Line: 47

thats the way  daffodils  wanted I suggested something like this:

dbReader.Read()
            Session("Email") = dbReader("Email")
            Session("Company") = dbReader("Company")
            Whatever else you need:


or

Session("Email") = dbReader("Email")

or

Session("Email") = CStr(dbReader("Email")

Regards,

Aeros
As a workaround you could always bind the session variables to an invisiible label, and then pull the value for your sql from the invisible label:

Private Sub BindUserData()
        Me.lblSessionID.Text = CStr(Session("SessionID"))
        Me.lblShippingMethod.Text = CStr(Session("ShippingMethod"))
        Me.txtFirstName.Text = CStr(Session("FirstName"))
        Me.txtLastName.Text = CStr(Session("LastName"))
        Me.txtAddress.Text = CStr(Session("Address"))
        Me.txtLocation.Text = CStr(Session("Location"))
        Me.txtCity.Text = CStr(Session("City"))
        Me.txtState.Text = CStr(Session("State"))
        Me.txtZipCode.Text = CStr(Session("ZipCode"))
        Me.txtPhoneNumber.Text = CStr(Session("PhoneNumber"))
        Me.lblCountry.Text = CStr(Session("Country"))
    End Sub
basically.. this error means that it cannot find that value.. "Email"
But when you tested the Query... with the same username etc..  it was returning a row with the Email value ?
Wait.. this is what you need to do.. you need to add the name of the data table when filling the data set

dataAdapter.Fill(dataSet, "Users")  --> LOOK HERE
Session("email") = CType(dataSet.Tables("Users").Rows(0)("Email"), String)
Return dataSet
Daffodils, I tried adding Users and it threw this error:

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30057: Too many arguments to 'Public Overridable Function Fill(dataSet As System.Data.DataSet) As Integer'.

Source Error:

 

Line 44:             dataAdapter.SelectCommand = dbCommand
Line 45:             Dim dataSet As System.Data.DataSet = New System.Data.DataSet
Line 46:             dataAdapter.Fill(dataSet,"Users")
Line 47:              Session("email") = CType(dataSet.Tables("Users").Rows(0)("Email"), String)
Line 48:
 

Source File: C:\Inetpub\wwwroot\memberarea\login.aspx    Line: 46
you  could always use datables instead:

cmd.CommandText = "SELECT * FROM CustomerTracking WHERE SessionID = '" & Me.lblTrackingID.Text & "'"
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Dim da As New ByteFX.Data.MySqlClient.MySqlDataAdapter
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn("ProductName", GetType(String)))
            dt.Columns.Add(New DataColumn("Quantity", GetType(String)))
            dt.Columns.Add(New DataColumn("UnitPrice", GetType(String)))
            dt.Columns.Add(New DataColumn("SKU", GetType(String)))
            Dim datar As DataRow
            While dr.Read
                datar = dt.NewRow
                datar(0) = dr(0)
                datar(1) = dr(1)
                datar(2) = dr(2)
                datar(3) = dr(3)
                dt.Rows.Add(datar)
            End While
            dr.Close()
            cnn.Close()
            cmd.Dispose()
            cnn.Dispose()

Regards,

Aeros
ahhh...heres what you need:

Private Sub LoadProductData()
        Dim cnn As New ByteFX.Data.MySqlClient.MySqlConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New ByteFX.Data.MySqlClient.MySqlCommand
        Dim da As New ByteFX.Data.MySqlClient.MySqlDataAdapter(cmd)
        Dim ds As New DataSet
        Dim Query As String
        Query = "SELECT * FROM Products ORDER BY ItemName ASC"
        cmd.CommandText = Query
        cmd.Connection = cnn
        cnn.Open()
        da.SelectCommand = cmd
        da.Fill(ds, "Products")
        dg.DataSource = ds.Tables("Products").DefaultView
        dg.DataKeyField = "ProductID"
        dg.DataBind()
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
    End Sub
AerosSaga, when I run you code in the onclick event for the login page, I get this error:

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30451: Name 'CommandBehavior' is not declared.

Source Error:

 

Line 20:             dbCommand.Connection = dbConnection
Line 21:             dbConnection.Open()
Line 22:             dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
Line 23:             dbReader.Read()
Line 24:             Session("Email") = dbReader("Email")
 

Source File: C:\Inetpub\wwwroot\memberarea\login.aspx    Line: 22



ok, try system.data.commandbehavior
Untyped datasets are a real pain... okay..

dataset.Tables.Add("Users") --> HERE
dataAdapter.Fill(dataSet, "Users")  --> AND HERE
Session("email") = CType(dataSet.Tables("Users").Rows(0)("Email"), String)
Return dataSet




It threw the "Too many arguments to 'Public Overridable Function Fill(dataSet As System.Data.DataSet) As Integer'.

I'm going to call it a day.  Thank you for trying.  I'll work on it again tomorrow.


ASKER CERTIFIED SOLUTION
Avatar of daffodils
daffodils

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial