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.Coun t = 1 Then
FormsAuthentication.Redire ctFromLogi nPage(User Name.Text, false)
Else
Msg.Text = "Invalid Credentials: Please try again or " + "<a href='NewUsers.aspx'>regis ter 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.SqlC onnection( connection String)
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.SqlC ommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_username As System.Data.IDataParameter = New System.Data.SqlClient.SqlP arameter
dbParam_username.Parameter Name = "@username"
dbParam_username.Value = username
dbParam_username.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(d bParam_use rname)
Dim dbParam_password As System.Data.IDataParameter = New System.Data.SqlClient.SqlP arameter
dbParam_password.Parameter Name = "@password"
dbParam_password.Value = password
dbParam_password.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(d bParam_pas sword)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlD ataAdapter
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.
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.Coun
FormsAuthentication.Redire
Else
Msg.Text = "Invalid Credentials: Please try again or " + "<a href='NewUsers.aspx'>regis
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.SqlC
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.SqlC
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_username As System.Data.IDataParameter
dbParam_username.Parameter
dbParam_username.Value = username
dbParam_username.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(d
Dim dbParam_password As System.Data.IDataParameter
dbParam_password.Parameter
dbParam_password.Value = password
dbParam_password.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(d
Dim dataAdapter As System.Data.IDbDataAdapter
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.
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='BCA'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlC onnection( connection String)
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.SqlC ommand
Dim dbReader As System.Data.SqlClient.SqlD ataReader
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open()
dbReader = dbCommand.ExecuteReader(Co mmandBehav ior.CloseC onnection)
dbReader.Read()
Session("Email") = dbReader("Email")
Session("Company") = dbReader("Company")
Whatever else you need:
Regards,
Aeros
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlC
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.SqlC
Dim dbReader As System.Data.SqlClient.SqlD
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open()
dbReader = dbCommand.ExecuteReader(Co
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
that option would be your answer
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("Us ers").Rows (0).Item(" Email"), String)
I realize this is harder than I thought it would be so I raised the points.
Session("email") = CType(myDataSet.Tables("Us
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("Us ers").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.
dataAdapter.Fill(dataSet)
--> here
Session("email") = CType(myDataSet.Tables("Us
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.
ASKER
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.NullReferenceExcept ion: 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("User s").Rows(0 ).Item("Em ail"), String)
Line 48:
Line 49: Return dataSet
Source File: C:\Inetpub\wwwroot\membera rea\login. aspx Line: 47
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.NullReferenceExcept
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("User
Line 48:
Line 49: Return dataSet
Source File: C:\Inetpub\wwwroot\membera
Try
Session("Email") = dbReader("Email")
or
Session("Email") = CStr(dbReader("Email")
Regards,
Aeros
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..
Session["email"] = (string) myDS.Tables["Users"].Rows[
You need the VB.NET equivalent..
ASKER
I submitted a new question to VB.net section.
ASKER
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.
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.
ASKER
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.
ASKER
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.SqlC onnection( connection String)
Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[username] =" & Session("Username")
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlC ommand
Dim dbReader As System.Data.SqlClient.SqlD ataReader
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open()
dbReader = dbCommand.ExecuteReader(Co mmandBehav ior.CloseC onnection)
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\membera rea\login. aspx Line: 47
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='BCA'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlC
Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[username] =" & Session("Username")
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlC
Dim dbReader As System.Data.SqlClient.SqlD
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open()
dbReader = dbCommand.ExecuteReader(Co
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\membera
ASKER
Ok, I tested it and the query works.
VB.NET Conversion
Session("email") = CType(myDS.Tables("Users") .Rows(0)(" Email"), String)
Session("email") = CType(myDS.Tables("Users")
ASKER
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.NullReferenceExcept ion: 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("User s").Rows(0 )("Email") , String)
Line 48:
Line 49: Return dataSet
Source File: C:\Inetpub\wwwroot\membera rea\login. aspx Line: 47
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.NullReferenceExcept
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("User
Line 48:
Line 49: Return dataSet
Source File: C:\Inetpub\wwwroot\membera
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
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("ShippingMeth od"))
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
Private Sub BindUserData()
Me.lblSessionID.Text = CStr(Session("SessionID"))
Me.lblShippingMethod.Text = CStr(Session("ShippingMeth
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 ?
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("User s").Rows(0 )("Email") , String)
Return dataSet
dataAdapter.Fill(dataSet, "Users") --> LOOK HERE
Session("email") = CType(dataSet.Tables("User
Return dataSet
ASKER
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("User s").Rows(0 )("Email") , String)
Line 48:
Source File: C:\Inetpub\wwwroot\membera rea\login. aspx Line: 46
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,"
Line 47: Session("email") = CType(dataSet.Tables("User
Line 48:
Source File: C:\Inetpub\wwwroot\membera
you could always use datables instead:
cmd.CommandText = "SELECT * FROM CustomerTracking WHERE SessionID = '" & Me.lblTrackingID.Text & "'"
dr = cmd.ExecuteReader(CommandB ehavior.Cl oseConnect ion)
Dim da As New ByteFX.Data.MySqlClient.My SqlDataAda pter
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
cmd.CommandText = "SELECT * FROM CustomerTracking WHERE SessionID = '" & Me.lblTrackingID.Text & "'"
dr = cmd.ExecuteReader(CommandB
Dim da As New ByteFX.Data.MySqlClient.My
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.My SqlConnect ion(Config urationSet tings.AppS ettings("S iteDB"))
Dim cmd As New ByteFX.Data.MySqlClient.My SqlCommand
Dim da As New ByteFX.Data.MySqlClient.My SqlDataAda pter(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").Defa ultView
dg.DataKeyField = "ProductID"
dg.DataBind()
cnn.Close()
cmd.Dispose()
cnn.Dispose()
End Sub
Private Sub LoadProductData()
Dim cnn As New ByteFX.Data.MySqlClient.My
Dim cmd As New ByteFX.Data.MySqlClient.My
Dim da As New ByteFX.Data.MySqlClient.My
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").Defa
dg.DataKeyField = "ProductID"
dg.DataBind()
cnn.Close()
cmd.Dispose()
cnn.Dispose()
End Sub
ASKER
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(Co mmandBehav ior.CloseC onnection)
Line 23: dbReader.Read()
Line 24: Session("Email") = dbReader("Email")
Source File: C:\Inetpub\wwwroot\membera rea\login. aspx Line: 22
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(Co
Line 23: dbReader.Read()
Line 24: Session("Email") = dbReader("Email")
Source File: C:\Inetpub\wwwroot\membera
ok, try system.data.commandbehavio r
Untyped datasets are a real pain... okay..
dataset.Tables.Add("Users" ) --> HERE
dataAdapter.Fill(dataSet, "Users") --> AND HERE
Session("email") = CType(dataSet.Tables("User s").Rows(0 )("Email") , String)
Return dataSet
dataset.Tables.Add("Users"
dataAdapter.Fill(dataSet, "Users") --> AND HERE
Session("email") = CType(dataSet.Tables("User
Return dataSet
ASKER
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.
I'm going to call it a day. Thank you for trying. I'll work on it again tomorrow.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can access the values from the DataSet (if you have a typed dataset) as:
Dim str As String
str = myDataSet.<table>(rownum).
If you are using an untyped dataset.. then this would work...
Dim s As String
s = CType(myDataSet.Tables("Us
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("Us