Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 841
  • Last Modified:

asp.net vb--use session value in where clause of sql select query

On the page load event I need to populate textboxes with data from a sql server table using the session username in the where clause.  How do I make that happen?
0
nhorsley
Asked:
nhorsley
  • 6
  • 5
  • 2
  • +1
2 Solutions
 
mmarinovCommented:
Hi,
you have to do it manual

example:

Dim _connection as SqlConnection = new SqlConnection("your connection string")
Dim _command as SqlCommand = new SqlCommand ("your select statement", _connection)
Dim _reader as SqlDataReader = Nothing

_reader = _command.executeReader()

if _reader.HasRows THen
    Dim indexFirstField as Integer = 0
    Dim first as Boolean = true
    While _reader.Read()
        If first Then
            indexFirstField = _reader.GetOrdinal("your first field name")
        End If
       
        txtTextBoxForFirstFirld.Text = _reader.GetString(indexFirstField)
    End While
End If

Hope this helps
Regards,
B..M
0
 
mmarinovCommented:
i've missed the session

"your select statement" can be "SELECT * FROM YOURTABLE WHERE YOURFIELD=" & Session("Key")

Regards,
B..M
0
 
AerosSagaCommented:
you may need CStr(Session("Key"))

Regards,

Aeros
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
nhorsleyAuthor Commented:
I'm getting this error:

ExecuteReader requires an open and available Connection. The connection's current state is Closed.
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.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is Closed.

Source Error:

Line 21: Dim _command as SqlCommand = new SqlCommand ("SELECT * FROM Users WHERE UserName=" & Session("Username"), _connection)
Line 22: Dim _reader as SqlDataReader = Nothing
Line 23:                   _reader = _command.executeReader()
Line 24:
Line 25: if _reader.HasRows THen
 

Source File: C:\Inetpub\wwwroot\memberarea\InsertRecordwoGrid.aspx    Line: 23

0
 
AerosSagaCommented:
Thats because your connection isn't being opened see below for example:

Dim cnn As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("EmeraldConnStr"))
        Dim cmd As New SqlClient.SqlCommand
        Dim dr As SqlClient.SqlDataReader
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM sschecter.JobNumbers JN " & _
        "WHERE JN.JobNumberID = " & JobNumberID.ToString()
        cmd.Connection = cnn
        cnn.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        dr.Read()
        Me.lblJobNumber.Text = CStr(dr("JobNumber"))
        Me.txtCustomerID.Text = CStr(dr("CustomerID"))
        Me.txtStartDate.Text = CStr(dr("StartDate"))
        Me.txtEndDate.Text = CStr(dr("EndDate"))
        Me.txtAssignedStaff.Text = CStr(dr("AssignedStaff"))
        Me.txtDescription.Text = CStr(dr("Description"))
        cnn.Close()
        dr.Close()
        cmd.Dispose()
        cnn.Dispose()

Regards,

Aeros
0
 
nhorsleyAuthor Commented:
I tried to run the suggestion above in the page load event, this is the error:

The ConnectionString property has not been initialized.
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.InvalidOperationException: The ConnectionString property has not been initialized.

Source Error:

Line 17:
Line 18:         cmd.Connection = cnn
Line 19:         cnn.Open()
Line 20:         dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Line 21:         dr.Read()
 
Source File: C:\Inetpub\wwwroot\memberarea\InsertRecordwoGrid.aspx    Line: 19

0
 
nhorsleyAuthor Commented:
I also get an error on the select statement when I use this select statement:
Dim sql as string = "SELECT * FROM Users WHERE UserName=" & Session("Username")

This is the error:
Server Error in '/' Application.
--------------------------------------------------------------------------------
Invalid column name 'nhorsley'.
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.Data.SqlClient.SqlException: Invalid column name 'nhorsley'.

Source Error:
Line 18:       Dim Cmd as New SQLCommand(sql, conn)
Line 19:       conn.Open()
Line 20:       objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
Line 21:       While objDR.Read()
Line 22:          Company=objDR("Company")

Somehow the Username, nhorsley, is being seen as a column name.

I don't know how to correct it.  I upped the points because this is getting complicated.

 
0
 
AerosSagaCommented:
well from the code I cannot see where nhorsley is coming from obviously its getting assigned at some point step through it and watch all the values for it, then if it throws an exception please note which line number.  Also, the code I posted

Dim cnn As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("EmeraldConnStr"))

EmeraldConnStr is a connection string key defined  in my web.config file, have you done this, is it correct.  Let me know

Regards,

Aeros
0
 
nhorsleyAuthor Commented:
I replaced the EmaraldConnStr with the actual connection string I normally use.  It works on other pages.

nhorsley is the actual user name that I am referring to in this select string:
"SELECT * FROM Users WHERE UserName=" & Session("Username")
0
 
AerosSagaCommented:
ok then what SQL command is produced when you step through your code?

Aeros
0
 
nhorsleyAuthor Commented:
I'm not working with visual studio so I can't debug.  I'm not working with VS because I can't get the code behind forms to work.

Anyway, I've got the app nearly done. It's this sessions problem that's got me stumped.

All I'm trying to do is populate a form with values already present in the Users table.  But the form must filter on a particular Session username, and it must be updateable.  I have found plenty of examples that show how to populate a form from an sql server table.  And other examples on how to update a sql table from a form.  But the examples break when I try to populate the form using Session values.
0
 
AerosSagaCommented:
Well the error you described is not because the session value is not being retained, something in your SQL statement is wrong.  Its recoginizing the session variable as a column.

Regards,

Aeros
0
 
nhorsleyAuthor Commented:
I can't test the sql in sql query analyzer with a Session variable in the where clause.

I've tried to get this to work about a half dozen ways.  the select string changes depending on the way I'm trying to marshal the data.  Since I don't really understand the various way asp.net marshals the data, I'm just trying to get a sequence of characters in code that will make the form work.
0
 
gregoryyoungCommented:
from another question ...

I've tried a number of ways to get the values from an sql server table using the session username in the where clause of the select statement and I'm making progress in understanding what's going on but I'm still not able to do it.

This is the code for a test page that is almost working except that I can't use the session value in the where clause.

This is the page code for text.aspx:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
     <meta name="GENERATOR" Content="ASP Express 2.1">
     <title>Untitled</title>
<script language="VB" runat="server">
Dim intWdth as integer
Dim strFirst, strLast, strTitle, intID as string


Sub Page_Load(Source as Object, E as EventArgs)
if not Page.IsPostBack then

     Dim strConn as string = "server=(local);database=BCA;Trusted_Connection=yes"
     Dim sql as string = "SELECT UserName, Password, Email, Company FROM Users WHERE (UserName = Session('Username'))"

     Dim conn as New SQLConnection(strConn)
     Dim Cmd as New SQLCommand(sql, conn)
     Dim objDR as SQLDataReader
     conn.Open()
     objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
     While objDR.Read()
        intID=objDR("UserName")
        strFirst=objDR("Company")
        strLast=objDR("Email")
        End While
     page.databind()
     conn.Close()
End If
End Sub

Sub EditRecord(Source as Object, E as EventArgs)
     Dim strConn as string = "server=(local);database=BCA;Trusted_Connection=yes"
     Dim sql as string = "SELECT UserName, Password, Email, Company FROM Users WHERE (UserName = Session('Username'))"
     Dim conn as New SQLConnection(strConn)
     Dim objDR as SQLDataReader
     Dim Cmd as New SQLCommand(sql, conn)
     conn.Open()
     objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
     While objDR.Read()
        intID=objDR("UserName")
        strFirst=objDR("Company")
        strLast=objDR("Email")
        End While
     page.databind()
     label1.text=""
     label2.text=""
End Sub

Sub doUpdate(Source as Object, E as EventArgs)
     label1.text="Successfully updated -- - "
     label2.text="(There is no REAL update here - that will be left for another sample)"
     ' here, you would put your actual code to do whatever you need to, with the populated fields.
End Sub



</script>
</head>
<body>
<form Name="form1" runat="server">
<table border="1" width="100%">     <tr>
         

          <td align="Left" valign="Top"><b>ID : </b><%# intID  %><br>
<b>FirstName : </b> <asp:textbox id="frmFirstName" Text="<%# strFirst %>" runat="server" /><br>
<b>LastName : </b><asp:textbox id="frmLastName" runat="server" Text="<%# strLast %>" /><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</b>
<br>          
<asp:button id="button2" Text="Update" onclick="doupdate" runat="server" />
<i><asp:Label ID="label1" forecolor="red" runat="server" /></i><br>
<i><asp:Label ID="label2" runat="server" /></i>
          </td>
     </tr>
</table>
</form>
</body>
</html>
________________________________________________________________________________________

When I run that code, it throws this error:

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

'Session' is not a recognized function name.
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.Data.SqlClient.SqlException: 'Session' is not a recognized function name.

Source Error:


Line 20:      Dim objDR as SQLDataReader
Line 21:      conn.Open()
Line 22:      objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
Line 23:      While objDR.Read()
Line 24:         intID=objDR("UserName")
 

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

Stack Trace:


[SqlException: 'Session' is not a recognized function name.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +45
   ASP.test_aspx.Page_Load(Object Source, EventArgs E) in C:\Inetpub\wwwroot\memberarea\test.aspx:22
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +731

What's wrong and how do I fix it?

Thank you.
 


 
 feedback  Send to a Friend     Printer Friendly   See Solution  
   
 Accepted Answer from gregoryyoung
Date: 08/07/2004 02:46PM CDT
ID: 11744120
Grade: A
 Accepted Answer  


change it to this ... Session is in the web server, the SQL server doesn't have access to it.
     Dim sql as string = "SELECT UserName, Password, Email, Company FROM Users WHERE (UserName = '" + Session("Username") +"')" should work.

Cheers,

Greg
 
 
additional:

no you cannot use a session variable in query analyzer as it would not have any context within Query Analyzer. Instead you want to use actual data when you are querying the the QA.

Cheers,

Greg
0
 
gregoryyoungCommented:
nhorsley the problem you were just having (column name) is based upon

"SELECT * FROM Users WHERE UserName=" & Session("Username")

as suggested before it needs to be

"SELECT * FROM Users WHERE UserName='" & Cstr(Session("Username")).Replace("'","''") + "'"

a string is quoted with single quotes, other wise it is treated as a columnname.

the .Replace is just in case you have single quotes in your string.

Cheers,

Greg
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now