Collect and use Session Variable in Stored Procedure

Dear Experts,

I am very new to creating stored procedures. I understand some of the basics. I need to select records based on a Session Variable. In my db teachers have a Teacher ID (TID) which is stored in Session as the variable TIDSession. I need to retrieve the variable and select records based on the TID. Is this possible? If so what would the code look like?  I work in VB. Have VS2008 Pro, and Sql Server 2005.

Thank you for your assisstance.
bobbellowsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

elimesikaCommented:
The SP should look like the attached code .

To call the SP from your VB code follow
http://www.macronimous.com/resources/calling_stored_procedures_from_ASP.NET_and_VB.NET.asp
CREATE PROCEDURE [dbo].[get_teachers]
	@tid_session nvarchar(max)
AS
BEGIN
	select TeacherId from teachers where session_id = @tid_session
END

Open in new window

0
bobbellowsAuthor Commented:
elimesika,

Thank you for your reply. I understand your parameter but I also see that I wasn't clear in what I needed to happen. I need to first set the parameter -- lets call it TIDfromSession -- to the string value of TIDSession. Then I need to have the select the records that are tagged by the TID -- so I want records where the TID = TIDfromSession.

Your solution might do that and I'm just not seeing it. If so can you explain the solution so I can learn from what you suggest? Thanks.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

bobbellowsAuthor Commented:
elimesika,

I tried what you suggested: Below is the code.

ALTER PROCEDURE [dbo].[uspSelectInterventionsByTID]
      -- Add the parameters for the stored procedure here

 @TIDfromSesssion nchar(50) = 100100

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT  * FROM InterventionRecordIndex_local Where TID = @TIDfromSession

END

I got this error:

Msg 137, Level 15, State 2, Procedure uspSelectInterventionsByTID, Line 18
Must declare the scalar variable "@TIDfromSession".
0
elimesikaCommented:
ok , seems that the TID  is int


ALTER PROCEDURE [dbo].[uspSelectInterventionsByTID]
      -- Add the parameters for the stored procedure here

 @TIDfromSesssion int = 100100

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT  * FROM InterventionRecordIndex_local Where TID = @TIDfromSession

END

Open in new window

0
bobbellowsAuthor Commented:
elmesika,

OK. I finally got it to work as far as the stored procedure. I went back and typed it up on a clean query screen from the beginning and that seemed to do the trick. I went back to nvarchar for the data type since the number will be coming out of session when we get this all working. Below is the code that worked.

I still have my very original question. The value of TID is being held in a session variable TIDSession. How do I pass that value from TIDSession to the TIDfromSession parameter in my stored procedure? I want to replace the hard coded value 100100 with the value of TIDSession.


ALTER PROCEDURE [dbo].[uspSelectInterventionsByTID2]


      @TIDfromSesssion nvarchar(MAX) = 100100

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT  * FROM InterventionRecordIndex_local Where TID = @TIDfromSesssion

END
0
elimesikaCommented:
HI

just call your stored procedure with the relevant value , for example:

exec uspSelectInterventionsByTID2 '100000'

the line in your current stored procedure
@TIDfromSesssion int = 100100
just sets a default value to the parameter in case you call your procedure with no parameters .
0
bobbellowsAuthor Commented:
elimeska,

I understand about the default value but I would rather it be dynamic and bring the TID from session so my teachers don't have to type it in everytime the sp is called.  Is that possible?

0
elimesikaCommented:
So, on which session id are you talking ??? 1)web session id 2) database process id 3) other option , please elaborate and I will try to help you
0
bobbellowsAuthor Commented:
elimesika,

the TID is a web session variable. All entries by a teacher are tagged with the TID so they can retrieve theirs and only their entries (because of a federal law). That is why I want the SP to dynamically use the TID from session -- makes it easier for the teachers -- some of which aren't very computer literate -- they don't have to type in their TID again.
0
Alfred A.Commented:
OK.  Let say your Session Variable use in your ASP.NET code is called TIDSession (note: you mentioned TID and TIDfromSession as well.  This is confusing so I'll stick with TIDSession where this is Teacher ID (TID) which is stored in Session).  To access your Stored Procedure uspSelectInterventionsByTID2, you can use a data reader.  I am putting the calling code in Page_Load for example purposes only.  I hope this helps.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    'Assuming that TID is stored in a session
    Dim strTID As String = CType(Session("TIDSession"),String)
    'Pass your TID to data reader to retrieve teacher data.
    Dim success As Boolean = GetTeacherData(strTID)
End Sub

Public Function GetTeacherData(ByVal TIDSession As String) As Boolean
        Dim DR As SqlClient.SqlDataReader
        Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("your connection string")
        Dim Cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("uspSelectInterventionsByTID2")
        Try 
            If (Conn.State <> ConnectionState.Open) Then
                Conn.Open
            End If
            Cmd.CommandType = CommandType.StoredProcedure
            Cmd.Connection = Conn
            Cmd.Parameters.AddWithValue("@TIDfromSession", TIDSession)
                       
            DR = Cmd.ExecuteReader
            
            While DR.Read
                ' 'Read you data reader here.  DR(<field name>)
                ' ex.  string TeacherName = DR("TeacherName")
                
            End While
            DR.Close
            Return true
        Catch ex As Exception
            _ErrorDescription = "Error retrieving All Details: "  _
                        + ex.Message + " - "  _
                        + ex.Source + " - " + ex.StackTrace
            Return false
        Finally
            If (Conn.State = ConnectionState.Open) Then
                Conn.Close
            End If
        End Try
    End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bobbellowsAuthor Commented:
Alfred1:
Thank you for your reply. I understand almost all of your code even though I'm very new to writing stored procedures. You were right I mis-typed in my last comment when I said TID was a session variable. TID is the teacher ID in the database. TIDsession is the Teacher ID that is entered @ login and sent to session -- so the teacher doesn't have to keep entering it -- reduces error. I used TIDfromSession to be the variable capturing the TID from session. I believe you used strTID the same way. I can live with that.  
I can't see where you use the value of strTID to be the select parameter. I need the seleection to be limited by the TIDsession.

0
Alfred A.Commented:
OK. If you look at line 5, it calls the GetTeacherData function and pass the value of strTID (let say it has value of 100222) to the parameter TIDSession defined in the signature of GetTeacherData function.

Inside the GetTeacherData, TIDSession will have a value of 1000222, so in line 18, it would be like passing it like this

Cmd.Parameters.AddWithValue("@TIDfromSession", "1000222")   ''TIDSession has a vaule of 1000222

The AddWithValue property adds a value for the required parameter in you uspSelectInterventionsByTID2 stored procedure.  From your stored procedure, the parameter defined is @TIDfromSession only.

Here is a basic tutorial about passing a parameter:

http://mka-soft.com/vbnet-tutorial/18-vbnet-tutorial-12-byval-byref

Also, test the code out so that you would further understand.
0
bobbellowsAuthor Commented:
Alfred1,

Thank you for your explanation -- it helped.

Here is what I did now:
1. On a page pre_load I hard coded TIDSession so I wouldn't have to login to try this out -- Sending TID to session happens during login.
2. Copied your code into the code behind page. Added in my connection string.
3. Received the following errors:
    SqlClient.SqlDataReader
       Type 'SqlClient.SqlDataReader' is not defined
       Type 'SqlClient.SqlConnection' is not defined
       Type 'SqlClient.SqlCommand ' is not defined
       Name 'ConnectionState.Open' is not declared
       Name 'Cmd.CommandType' is not declared
       Name 'ErrorDescription' is not declared

Attached is the code.
Thank you.

Partial Class TestCode_SessionVariableInStoredProcedure
    Inherits System.Web.UI.Page

    Protected Sub Page_PreLoad(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreLoad
        Session("TIDSession") = 100100
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Assuming that TID is stored in a session
        Dim strTID As String = CType(Session("TIDSession"), String)
        'Pass your TID to data reader to retrieve teacher data.
        Dim success As Boolean = GetTeacherData(strTID)
    End Sub

    Public Function GetTeacherData(ByVal TIDSession As String) As Boolean
        Dim DR As SqlClient.SqlDataReader
        Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=***********;Initial Catalog=FINAOsmsSQL1_94132;Persist Security Info=True;User ID=*********;Password=*********")
        Dim Cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("uspSelectInterventionsByTID2")
        Try
            If (Conn.State <> ConnectionState.Open) Then
                Conn.Open()
            End If
            Cmd.CommandType = CommandType.StoredProcedure
            Cmd.Connection = Conn
            Cmd.Parameters.AddWithValue("@TIDfromSession", TIDSession)

            DR = Cmd.ExecuteReader

            While DR.Read
                ' 'Read you data reader here.  DR(<field name>)
                ' ex.  string TeacherName = DR("TeacherName")

            End While
            DR.Close()
            Return True
        Catch ex As Exception
            _ErrorDescription = "Error retrieving All Details: " _
                        + ex.Message + " - " _
                        + ex.Source + " - " + ex.StackTrace
            Return False
        Finally
            If (Conn.State = ConnectionState.Open) Then
                Conn.Close()
            End If
        End Try
    End Function




End Class

Open in new window

0
Alfred A.Commented:
You need to add reference to System.Data

Add this in line 1,

Imports System.Data
0
bobbellowsAuthor Commented:
Alfred1,

It ran!!! -- I think.
I had an error in the code I missed before: The Name 'ErrorDescription' is not declared.
I remarked it out for the time being.
Ran the page.
It returned a blank screen -- which I think it's supposed to do till I use some type of control. I'm still very new to ASP, stored procedures and the like.

Please let me know what I need to correct the error and display the results so I know it's working -- thanks. It looks like we got this done!

0
Alfred A.Commented:
If you are just running code with any sort of exposed control in the page, you will indeed get a blank page.

For the _ErrorDescription, just declare it as Dim _ErrorDescription As String.

To really check you got the data, inspect the result of the DR.Read.  See the code above.
0
Alfred A.Commented:
Also, to display the results, you need to use a control such as GridView or plain multiline textbox if you want.

Check the link below:

http://www.asp.net/get-started
0
bobbellowsAuthor Commented:
Alfred1,

I'm sorry. Now I don't know what I did. I get the error message:

Name 'GetTeacherData' is not declared.

I hardly did anything to the code from this morning. I will attach the whole code behind.
Imports System.Data
Partial Class TestCode_SessionVariableInStoredProcedure
    Inherits System.Web.UI.Page

    Protected Sub Page_PreLoad(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreLoad
        Session("TIDSession") = 100100
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Assuming that TID is stored in a session
        Dim strTID As String = CType(Session("TIDSession"), String)
        'Pass your TID to data reader to retrieve teacher data.
        Dim success As Boolean = GetTeacherData(strTID)

    End Sub
End Class

Public Function GetTeacherData(ByVal TIDSession As String) As Boolean
    Dim DR As SqlClient.SqlDataReader
    Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=SQL398.mysite4now.com;Initial Catalog=FINAOsmsSQL1_94132;Persist Security Info=True;User ID=****;Password=*****")
    Dim Cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("uspSelectInterventionsByTID2")
    Try
        If (Conn.State <> ConnectionState.Open) Then
            Conn.Open()
        End If
        Cmd.CommandType = CommandType.StoredProcedure
        Cmd.Connection = Conn
        Cmd.Parameters.AddWithValue("@TIDfromSession", TIDSession)

        DR = Cmd.ExecuteReader

        While DR.Read
            ' 'Read you data reader here.  DR(<field name>)
            ' ex.  string TeacherName = DR("TeacherName")
                DR(<TID>)

            End While
            DR.Close()
            Return True
        Catch ex As Exception
            Dim _ErrorDescription As String
            _ErrorDescription = "Error retrieving All Details: " _
                    + ex.Message + " - " _
                    + ex.Source + " - " + ex.StackTrace
            Return False
        Finally
            If (Conn.State = ConnectionState.Open) Then
                Conn.Close()
            End If
        End Try
    End Function

Open in new window

0
Alfred A.Commented:
Move the function inside the partial class.
Imports System.Data
Partial Class TestCode_SessionVariableInStoredProcedure
    Inherits System.Web.UI.Page

    Protected Sub Page_PreLoad(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreLoad
        Session("TIDSession") = 100100
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Assuming that TID is stored in a session
        Dim strTID As String = CType(Session("TIDSession"), String)
        'Pass your TID to data reader to retrieve teacher data.
        Dim success As Boolean = GetTeacherData(strTID)

    End Sub

    Public Function GetTeacherData(ByVal TIDSession As String) As Boolean
    Dim DR As SqlClient.SqlDataReader
    Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=SQL398.mysite4now.com;Initial Catalog=FINAOsmsSQL1_94132;Persist Security Info=True;User ID=****;Password=*****")
    Dim Cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("uspSelectInterventionsByTID2")
    Try
        If (Conn.State <> ConnectionState.Open) Then
            Conn.Open()
        End If
        Cmd.CommandType = CommandType.StoredProcedure
        Cmd.Connection = Conn
        Cmd.Parameters.AddWithValue("@TIDfromSession", TIDSession)

        DR = Cmd.ExecuteReader

        While DR.Read
            ' 'Read you data reader here.  DR(<field name>)
            ' ex.  string TeacherName = DR("TeacherName")
                DR(<TID>)

            End While
            DR.Close()
            Return True
        Catch ex As Exception
            Dim _ErrorDescription As String
            _ErrorDescription = "Error retrieving All Details: " _
                    + ex.Message + " - " _
                    + ex.Source + " - " + ex.StackTrace
            Return False
        Finally
            If (Conn.State = ConnectionState.Open) Then
                Conn.Close()
            End If
        End Try
    End Function

End Class

Open in new window

0
bobbellowsAuthor Commented:
Sorry for taking so long. Had to put out some other fires at work before I could get back to this one. The above solution got the page working again -- thanks -- sorry we had to go backwards a little. While waiting for various meetings to begin I tried to read up on displaying the information collected by the datareader -- I learn better if I have to read, study, then try it -- but couldn't make any progress.

If one of the columns the datareader should have gathered was the TID, what would be the code to get that TID to be written to the page?

Thanks
0
Alfred A.Commented:
If you are literally looking for a way to write the resulting TID in a page,

try this,

Response.Write(TID)

where TID is a variable containing the TID value for example.

Also, you are now straying from the original question at hand.  Not to be rude but your question (writing to a page) right now is outside the scope of the original question (I need to retrieve the variable and select records based on the TID).  Please post another question.

If you want to learn about the basics of presentation/display, check http://www.asp.net.  There are video tutorials in there that can put you up to speed.
0
bobbellowsAuthor Commented:
Alfred1,

I agree -- I'm sorry -- just wanted to be sure it actually worked and I'm new to this programming -- always before just created old html pages -- thanks for the link -- I'll check it out.

Thank you so much for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.