Solved

Defining Sesssion Values from sql db

Posted on 2004-08-06
30
315 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:nhorsley
  • 11
  • 10
  • 9
30 Comments
 
LVL 8

Expert Comment

by:daffodils
ID: 11737524
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)

0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11737546
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
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11737571
Oh.. I just went thru your code again.. you have an untyped dataset..
that option would be your answer
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11742764
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.
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11743042
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.
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11743241
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

0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11743265
Try
Session("Email") = dbReader("Email")

or

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

Regards,

Aeros
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11743300
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..

0
 
LVL 1

Author Comment

by:nhorsley
ID: 11743350
I submitted a new question to VB.net section.
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11743353
About how to convert that line of code.
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11743370
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.
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11743395
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.
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11743431
I meant testing it in SQL Query Analyzer... from SQL Server tools.
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11743459
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

0
 
LVL 1

Author Comment

by:nhorsley
ID: 11743475
Ok, I tested it and the query works.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 17

Expert Comment

by:AerosSaga
ID: 11743498
VB.NET Conversion
Session("email") = CType(myDS.Tables("Users").Rows(0)("Email"), String)
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11743604
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

0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11743617
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
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11743645
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
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11743686
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 ?
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11743731
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
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11743894
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
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11743908
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
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11743910
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
0
 
LVL 1

Author Comment

by:nhorsley
ID: 11743921
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



0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11743932
ok, try system.data.commandbehavior
0
 
LVL 8

Expert Comment

by:daffodils
ID: 11743974
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




0
 
LVL 1

Author Comment

by:nhorsley
ID: 11743991
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.


0
 
LVL 8

Accepted Solution

by:
daffodils earned 250 total points
ID: 11744020
0
 
LVL 17

Assisted Solution

by:AerosSaga
AerosSaga earned 250 total points
ID: 11744031
Just create a sub to do it in temporarily, you can always convert it back into a function after it works.

Regards,

Aeros
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now