setting sessions from a database query
-
1st post so go easy! coding: aspx web pages in vb.net 2.0 database: sql server express i would like to set session variable values from a database query and i cant find out how to do it! the page flow would be something like this: 1) query database using 'SELECT userID, firstName, lastName FROM users WHERE userID = 1' 2) if data is returned: create userID, firstName and lastName sessions from the database values for those columns. 3) if no data is returned tell the user an error has occurred. 4) finally display the session values or the error message on the page. i can find thousands of tutorials on how to bind data to controls (GridView, DataList, Repeater etc) but none telling me how to query the database and set sessions, cookies or page variables... if anyone can shed some light on this, it'd be great! thanks out there, jake
-
1st post so go easy! coding: aspx web pages in vb.net 2.0 database: sql server express i would like to set session variable values from a database query and i cant find out how to do it! the page flow would be something like this: 1) query database using 'SELECT userID, firstName, lastName FROM users WHERE userID = 1' 2) if data is returned: create userID, firstName and lastName sessions from the database values for those columns. 3) if no data is returned tell the user an error has occurred. 4) finally display the session values or the error message on the page. i can find thousands of tutorials on how to bind data to controls (GridView, DataList, Repeater etc) but none telling me how to query the database and set sessions, cookies or page variables... if anyone can shed some light on this, it'd be great! thanks out there, jake
i've made a bit of progress with this problem. this works, but i'm *pretty* sure that it's not the right way to do it. this code 'loops' through all the database returns but in my example, there should be only one row returned. i also cant figure out how to do a (this is in english not code):
if (dataSet is empty) Then set a error message Else create the sessions End Ifhmmm, tricky. this is the code i've put together:Dim conn As SqlConnection = [con string here] Dim dsProjects As DataSet = New DataSet() Dim cmd As SqlDataAdapter = New SqlDataAdapter("SELECT userID, firstName, lastName FROM users WHERE userID = 1", conn) cmd.Fill(dsProjects, "tblProjects") Dim dr As DataRow Dim dt As DataTable = dsProjects.Tables("tblProjects") For Each dr In dt.Rows Session("clientID") = dr("clientID") Session("clientID") = dr("firstName") Session("clientID") = dr("lastName") Nextwhat do you reckon out there? am i going about this the right or wrong way?! cheers, jake -
i've made a bit of progress with this problem. this works, but i'm *pretty* sure that it's not the right way to do it. this code 'loops' through all the database returns but in my example, there should be only one row returned. i also cant figure out how to do a (this is in english not code):
if (dataSet is empty) Then set a error message Else create the sessions End Ifhmmm, tricky. this is the code i've put together:Dim conn As SqlConnection = [con string here] Dim dsProjects As DataSet = New DataSet() Dim cmd As SqlDataAdapter = New SqlDataAdapter("SELECT userID, firstName, lastName FROM users WHERE userID = 1", conn) cmd.Fill(dsProjects, "tblProjects") Dim dr As DataRow Dim dt As DataTable = dsProjects.Tables("tblProjects") For Each dr In dt.Rows Session("clientID") = dr("clientID") Session("clientID") = dr("firstName") Session("clientID") = dr("lastName") Nextwhat do you reckon out there? am i going about this the right or wrong way?! cheers, jakeThat's pretty much how I'd do it too, with a couple of points to note... Firstly, I tend not to use raw SQL in my code, for a variety of reasons....I would use a stored proc in the DB to get the data using a sqlCommand Object Secondly, theere seems to be a little bit of redundency going on - instead of....
Dim dr As DataRow
Dim dt As DataTable = dsProjects.Tables("tblProjects")For Each dr In dt.Rows
Session("clientID") = dr("clientID")
Session("clientID") = dr("firstName")
Session("clientID") = dr("lastName")Next
you know that you're only going to return 1 or zero rows, so the loop is not necessary....so it would become
Dim dr As DataRow
Dim dt As DataTable = dsProjects.Tables("tblProjects")
dr = dt.Rows[0]Session("clientID") = dr("clientID")
Session("clientID") = dr("firstName")
Session("clientID") = dr("lastName")which could be further simplified to...
Session("clientId") = dt.Rows[0]["clientId"]
Session("firstName") = dt.Rows[0]["firstName"]
Session("lastName") = dt.Rows[0]["lastName"]Another alternative would be to take advantage of the fact that session stores objects just fine - so if you wanted to, you could store the DataRow itself, and pull the values out when needed using somehting like :
Session ["theDataRow"] = myDataRow;
.
.
.
DataRow dr = (DataRow)Session["theDataRow"];it's swings and roundabouts really!
"Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox
-
That's pretty much how I'd do it too, with a couple of points to note... Firstly, I tend not to use raw SQL in my code, for a variety of reasons....I would use a stored proc in the DB to get the data using a sqlCommand Object Secondly, theere seems to be a little bit of redundency going on - instead of....
Dim dr As DataRow
Dim dt As DataTable = dsProjects.Tables("tblProjects")For Each dr In dt.Rows
Session("clientID") = dr("clientID")
Session("clientID") = dr("firstName")
Session("clientID") = dr("lastName")Next
you know that you're only going to return 1 or zero rows, so the loop is not necessary....so it would become
Dim dr As DataRow
Dim dt As DataTable = dsProjects.Tables("tblProjects")
dr = dt.Rows[0]Session("clientID") = dr("clientID")
Session("clientID") = dr("firstName")
Session("clientID") = dr("lastName")which could be further simplified to...
Session("clientId") = dt.Rows[0]["clientId"]
Session("firstName") = dt.Rows[0]["firstName"]
Session("lastName") = dt.Rows[0]["lastName"]Another alternative would be to take advantage of the fact that session stores objects just fine - so if you wanted to, you could store the DataRow itself, and pull the values out when needed using somehting like :
Session ["theDataRow"] = myDataRow;
.
.
.
DataRow dr = (DataRow)Session["theDataRow"];it's swings and roundabouts really!
"Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox
hi richard, thank you for your post, it's really helped me out! as you say, it's swings and roundabouts; there's never a '100% this is the only way you do it' solution with programming and it's always good to get a 2nd opinion to make sure your heading in the right direction. i agree with using stored procedures instead of inline sql but for forum posts, i usually type it out to avoid confusion with column names etc (also handy when someone spots a blunder in the sql!). everything is now working, the only thing i've left to solve is this: i currently show the session values back to the page in text box's. as all my asp code in a 'code behind' file, i can response.write a message (or bind it to a label) back if no data is returned sort of like this: If (dt.Rows.Count = 0) Then Response.Write("no data returned") End If but what i cant figure out is how to not show the text boxs on the page, just the message? i know i could do the same if statement on the page, but that means not using the code behind file which i'm very keen on. back to google for a bit me thinks! thanks again for your help, jake
-
hi richard, thank you for your post, it's really helped me out! as you say, it's swings and roundabouts; there's never a '100% this is the only way you do it' solution with programming and it's always good to get a 2nd opinion to make sure your heading in the right direction. i agree with using stored procedures instead of inline sql but for forum posts, i usually type it out to avoid confusion with column names etc (also handy when someone spots a blunder in the sql!). everything is now working, the only thing i've left to solve is this: i currently show the session values back to the page in text box's. as all my asp code in a 'code behind' file, i can response.write a message (or bind it to a label) back if no data is returned sort of like this: If (dt.Rows.Count = 0) Then Response.Write("no data returned") End If but what i cant figure out is how to not show the text boxs on the page, just the message? i know i could do the same if statement on the page, but that means not using the code behind file which i'm very keen on. back to google for a bit me thinks! thanks again for your help, jake
I think I get what you mean... The usual way that I show and hide different content is by using divs. If I follow you correctly, I'd set my page up something like
Then I'd use client scripting and document.getElementById() to set the display to "inline" for the div I wanted to show. If you were determined to do something on the server side, then you could either add a runat=server to the divs and manipulate their styles, or alternatively (and IIRC) an exposes a Visible property, and renders as a div anyway :) If you wanted a simpler method, you could of course just manipulate the visible property of the label and textboxes directly - I just like doing it with divs to keep the markup nice and clear, and so that if you encounter a divSuccess.style.display = 'inline' then it's easier to get what's going on quickly. "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox
-
I think I get what you mean... The usual way that I show and hide different content is by using divs. If I follow you correctly, I'd set my page up something like
Then I'd use client scripting and document.getElementById() to set the display to "inline" for the div I wanted to show. If you were determined to do something on the server side, then you could either add a runat=server to the divs and manipulate their styles, or alternatively (and IIRC) an exposes a Visible property, and renders as a div anyway :) If you wanted a simpler method, you could of course just manipulate the visible property of the label and textboxes directly - I just like doing it with divs to keep the markup nice and clear, and so that if you encounter a divSuccess.style.display = 'inline' then it's easier to get what's going on quickly. "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox
hi richard, wow, thanks for the reply! finally feeling like i'm getting somewhere with the beast that is asp.net! the funny thing is it's beginning to remind me of the flash actionscripting i did a while back... interesting. taking your advice with the div thing to show success/failure i've put the code together as a 'finished' product. there's some area's i'm still unsure about: 1) cant figure out why i have to create a 'SqlDataAdapter' and then a 'DataTable' and then fill them respectively - the whole thing seems a bit long winded! 2) idealy i would like to show the returned data back to the page without using labels as they create additional code i dont need. for example, the 1st label returns as: userID = <span id="lblUserID">1</span> and i dont need the span! not sure how to get round that one... 3) tidying up the objects at the end of the script - which do i need to do?! at the moment, i have this: dsUsers.Dispose() dsUsers.Dispose() myAdapter.Dispose() dt.Dispose() conn.Close() do i need to do it?! that aside, here's the front end code (had to put on my site as it's too long for the forum): SettingSessionsFromDatabaseQuery.aspx and this is the code behind file: SettingSessionsFromDatabaseQuery.aspx.vb watcha reckon? any improvements?! thanks again for all your advice, jake