using session variable within sql query

Question

i have an application where a user logs in and can edit his/other's data. however, if the user is an admin, he gets a gridview with all user's records which he can edit. if the user is not an admin, he will just get a listview where he can edit his own data.

when a user logs into the page, his userid, which is in itself also stored in the db, is stored as a session variable in Session["ID"]. now i need to populate the listview with the user's data. i thought it would be good to just query the data based on the Session["ID"] parameter. but i am not sure how to do this.

EDIT:

ok i have little code regarding this as i have no idea how to do it but i will post what i have. first is the method where i set the session variable of the userid:

            objda = new SqlDataAdapter("[GetIDOfUser]", objcon);
            objda.SelectCommand.CommandType = CommandType.StoredProcedure;
            objda.SelectCommand.Parameters.Add("@Username", SqlDbType.VarChar).Value = tbUsername.Text;
            objda.SelectCommand.Parameters.Add("@UserPassword", SqlDbType.VarChar).Value = tbPassword.Text;
            String id = (string)objda.SelectCommand.ExecuteScalar();
            Session["ID"] = id;

this is my markup:

 <asp:ListView ID="ListView1" Visible="False" runat="server" DataSourceID="SqlDataSource2"></asp:ListView>

this is the code where i enable the listview:

protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["UserAuthentication"] == null)
            {
                Response.Redirect("Login.aspx");
            }

            if (Session["Benutzerart"].ToString() == Enums.Enumerations.Benutzer.Administrator.ToString())
            {
                GridView1.Visible = true;  

                //Set controls for admin
            }

            if (Session["Benutzerart"].ToString() != Enums.Enumerations.Benutzer.Administrator.ToString())
            {
                ListView1.Visible = true;
                //Set controls for other users
            }
        }

ok guys i have figured it out:

i just make normal listview as in the code above. only the data source has no selectcommand attribute in the markup. this attribute is set in-code:

if (Session["Benutzerart"].ToString() != Enums.Enumerations.Benutzer.Administrator.ToString())
            {
                ListView1.Visible = true;
                SqlDataSource2.SelectCommand = "SELECT [Titel], [Bezeichnung], [Vorname], [Nachname], [Geburtsdatum], [Geburtsort], [Straße], [Nationalität], [Hausnummer], [PLZ], [Ort], [Land], [Mobil], [UrlaubstageGenommen], [UrlaubstageInsgesamt], [Status], [Benutzerart], [Homepage], [Email], [Festnetz], [Fax], [UrlaubstageRest], [Username], [UserPassword] FROM [Benutzer] WHERE [BenutzerID] = '" + Session["ID"] + "'";
            }

markup of datasource:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ></asp:SqlDataSource>

thanks for this comment, it gave me an idea (see edit) which worked. now only the data of the logged in user is displayed! — LeonidasFett
– LeonidasFett, Commented Mar 13, 2013 at 15:13
@LeonidasFett if you want all record just remove where condition from SelectCommand and if it helped you then dont forget to mark as answer — Siz S
– Siz S, Commented Mar 13, 2013 at 15:54

Siz S · Accepted Answer · 2013-03-13 15:09:49Z

2

you are binding listview with SqlDataSource, use sqldatasource SelectParameter

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                                ConnectionString="<%$ ConnectionStrings:yourConnection %>" 
                                SelectCommand="SELECT * FROM yourTable WHERE userid = @userid">
                                <SelectParameters>
                                    <asp:SessionParameter Name="userid" SessionField="ID" Type="String" />
                                </SelectParameters>
                            </asp:SqlDataSource>

answered Mar 13, 2013 at 15:09

Siz S

8663 gold badges10 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eugene Pavlov · Accepted Answer · 2013-03-13 14:56:46Z

1

To select data from DB you can create sql data source and bind it to ListView:

    SqlDataSource ds = new SqlDataSource();

    ds.ConnectionString = yourDBconnectionString;

    ds.SelectCommand = "SELECT * FROM records_table WHERE user_id=@user_id";
    ds.SelectParameters.Add("user_id", Convert.ToInt32(Session["id"]));

    ListView1.DataSource = ds;
    ListView1.DataBind();

Then to bind records fields to ListView on aspx page use (just an example):

<%# Eval("recort_title") %>

answered Mar 13, 2013 at 14:56

Eugene Pavlov

6985 silver badges19 bronze badges

Collectives™ on Stack Overflow

using session variable within sql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related