Solved

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

Posted on 2004-08-06
15
726 Views
Last Modified: 2008-02-01
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
Comment
Question by:nhorsley
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 28

Expert Comment

by:mmarinov
ID: 11735114
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
 
LVL 28

Expert Comment

by:mmarinov
ID: 11735125
i've missed the session

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

Regards,
B..M
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11735436
you may need CStr(Session("Key"))

Regards,

Aeros
0
 
LVL 1

Author Comment

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

Expert Comment

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

Author Comment

by:nhorsley
ID: 11742814
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
 
LVL 1

Author Comment

by:nhorsley
ID: 11742935
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 17

Expert Comment

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

Author Comment

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

Expert Comment

by:AerosSaga
ID: 11743489
ok then what SQL command is produced when you step through your code?

Aeros
0
 
LVL 1

Author Comment

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

Assisted Solution

by:AerosSaga
AerosSaga earned 200 total points
ID: 11743592
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
 
LVL 1

Author Comment

by:nhorsley
ID: 11743629
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
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 300 total points
ID: 11744604
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11744612
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

747 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

12 Experts available now in Live!

Get 1:1 Help Now