Using 2 databases for Session information in ASP.NET

Question

This post was originally trying to find the problem, which I thought was a web.config issue. I also thought it was something in the code behind of my master page. All the text up here is part of the process of finding out the problem, scroll to the bottom for the most recent updates.

My website allows users to type a code into a textbox. If a recognized code is entered, the page will refresh and display a welcome message to that user, otherwise an error message will come up. I am putting their manually entered code into a session so that their name can be pulled up. I can't get the session to stay between pages. All of my code is on the master page's vb page and I don't know what I am doing wrong.

• I have been told to make sure EnableSessionState="true" but that doesn't work on master pages.
• I've been told to check IIS settings, but I can't because I don't have permissions to it.
• Tried SessionState cookieless="UseUri" and somehow that created a never-ending redirect loop.
• I have debugged the functions and they DO return values.
• The textbox DOES go away when I enter a code and the welcome message gets displayed with the user's first and last name, so I know that works.
• I've checked to make sure there is no Session.Abandon code anywhere in the site.
• I added a Watch to every instance of Session("IB") on the page and they are filled correctly when I enter a code into the textbox. Then when I click on a link to move to another page, the debugger stops on the very first line in my Page_Load, Dim ib As String = CType(Session.Item("IB"), String) and all of my watched IB variables immediately turn into Nothing.

---

Here is the code behind for the master page:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Partial Class MasterPage
  Inherits System.Web.UI.MasterPage
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
      Dim BAccount As String = CType(Session("BAccount"), String)
       If Not IsPostBack Then
        If Session("BAccount") Is Nothing Then
            'no such value in session state, show textbox for IB to enter code
            IBText.Visible = True
            IBTextBox.Visible = True
            IBTextBoxButton.Visible = True
            lbNotIB.Visible = False
        Else
            'call function
            GetSessionValues(BAccount)
        End If
    End If
End Sub
Protected Function GetSessionValues(ByVal Code As String) As Boolean
    Dim FirstName As String = CType(Session("First_Name"), String)
    Dim LastName As String = CType(Session("Last_Name"), String)
    Dim Name As String = CType(Session("Name"), String)
    If GetAccountName(FirstName, LastName) Then
        'hide textbox
        IBText.Visible = False
        IBTextBox.Visible = False
        IBTextBoxButton.Visible = False
        'show welcome message to user if IB code exists in database
        lblIB.Visible = True
        lblIB.Text = "Welcome, " + Session("First_Name") + " " + Session("Last_Name") + "."
        lbNotIB.Visible = True
        lbNotIB.Text = "Not " + Session("First_Name") + " " + Session("Last_Name") + "?"
        Return True
    ElseIf GetBackUpAccountName(Name) Then
        'hide textbox
        IBText.Visible = False
        IBTextBox.Visible = False
        IBTextBoxButton.Visible = False
        'show welcome message to user if IB code exists in database
        lblIB.Visible = True
        lblIB.Text = "Welcome, " + Session("Name") + "."
        lbNotIB.Visible = True
        lbNotIB.Text = "Not " + Session("Name") + "?"
        Return True
    Else
        'IB code not found
        'shows error message in red
        lblIB.ForeColor = Drawing.Color.Red
        lblIB.Text = "Account not found, please try again."
        Return False
    End If
End Function
Private Function GetAccountName(ByRef FirstName As String, ByRef LastName As String) As Boolean
    'declare variable
    Dim BAccount As String = CType(Session("BAccount"), String)
    'sql statement for baccount information
    Dim sql As String = "SELECT BAccount, First_Name, Last_Name FROM IB INNER JOIN IB_BUISNESS_INFORMATION ON (IB.IB_ID = IB_BUISNESS_INFORMATION.IB_ID) WHERE BAccount = @BAccount"
    Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("IBConnectionString").ConnectionString)
        Using cmd As New SqlCommand(sql, conn)
            cmd.Parameters.AddWithValue("@BAccount", SqlDbType.VarChar)
            cmd.Parameters("@BAccount").Value = IBTextBox.Text
            If IBTextBox.Text Is Nothing Then
                cmd.Parameters("@BAccount").Value = DBNull.Value
            Else
                cmd.Parameters("@BAccount").Value = IBTextBox.Text
            End If
            conn.Open()
            Using rdr As SqlDataReader = cmd.ExecuteReader
                If (rdr.Read) Then
                    FirstName = rdr("First_Name").ToString()
                    LastName = rdr("Last_Name").ToString()
                    Return True
                Else
                    Return False
                End If
            End Using
            conn.Close()
        End Using
    End Using
End Function
Private Function GetBackUpAccountName(ByRef Name As String) As Boolean
    'declare variable
    Dim BAccount As String = CType(Session("BAccount"), String)
    'sql statement for baccount information in case BAccount is not found, search here next
    Dim backupsql As String = "SELECT BAccount, Name FROM brokermaster WHERE BAccount = ?"
    Using conn As New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("BackUpConnectionString").ConnectionString)
        Using cmd As New OleDbCommand(backupsql, conn)
            cmd.Parameters.AddWithValue("?", SqlDbType.VarChar)
            cmd.Parameters("?").Value = IBTextBox.Text
            If IBTextBox.Text Is Nothing Then
                cmd.Parameters("?").Value = DBNull.Value
            Else
                cmd.Parameters("?").Value = IBTextBox.Text
            End If
            conn.Open()
            Using backuprdr As OleDbDataReader = cmd.ExecuteReader
                If (backuprdr.Read) Then
                    Name = backuprdr("Name").ToString()
                    Return True
                Else
                    Return False
                End If
            End Using
            conn.Close()
        End Using
    End Using
End Function
Protected Sub CustomValidator1_ServerValidate(ByVal source As Object, ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs) Handles CustomValidator1.ServerValidate
    'declare variables
    Dim FirstName As String = CType(Session("First_Name"), String)
    Dim LastName As String = CType(Session("Last_Name"), String)
    Dim Name As String = CType(Session("Name"), String)
    If (Not GetSessionValues(args.Value)) Then
        args.IsValid = False
    Else
        args.IsValid = True
    End If
    If GetAccountName(FirstName, LastName) Then
        'set session variables
        Session("First_Name") = FirstName
        Session("Last_Name") = LastName
        'hide textbox
        IBText.Visible = False
        IBTextBox.Visible = False
        IBTextBoxButton.Visible = False
        args.IsValid = True
        'show welcome message to user if IB code exists in database
        lblIB.Visible = True
        lblIB.Text = "Welcome, " + Session("First_Name") + " " + Session("Last_Name") + "."
    ElseIf GetBackUpAccountName(Name) Then
        'set session variables
        Session("Name") = Name
        'hide textbox
        IBText.Visible = False
        IBTextBox.Visible = False
        IBTextBoxButton.Visible = False
        args.IsValid = True
        'show welcome message to user if IB code exists in database
        lblIB.Visible = True
        lblIB.Text = "Welcome, " + Session("Name") + "."
    Else
        'IB code not found
        args.IsValid = False
        'shows error message in red
        lblIB.ForeColor = Drawing.Color.Red
        lblIB.Text = "Account not found, please try again."
    End If
End Sub
Protected Sub IBTextBoxButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles IBTextBoxButton.Click
    If Page.IsValid Then
        'declare variables
        Dim LSD As String = CType(Session("LSD"), String)
        Dim LSC As String = CType(Session("LSC"), String)
        Dim BAccount As String = CType(Session("BAccount"), String)
        Session("BAccount") = IBTextBox.Text
        'add session variable
        If GetCompanyName(LSD) Then
            Session("LSD") = LSD
        End If
        'add session variable
        If GetWebsite(LSC) Then
            Session("LSC") = LSC
        End If
    End If
End Sub
Private Function GetCompanyName(ByRef LSD As String) As Boolean
    'declare variable
    Dim BAccount As String = CType(Session("BAccount"), String)
    'sql statement to get company information
    Dim sql As String = "SELECT Company_Name, BAccount FROM IB_CONTACT_INFORMATION INNER JOIN IB_BUISNESS_INFORMATION ON (IB_CONTACT_INFORMATION.IB_ID = IB_BUISNESS_INFORMATION.IB_ID) WHERE BAccount = @BAccount"
    Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("IBConnectionString").ConnectionString)
        Using cmd As New SqlCommand(sql, conn)
            cmd.Parameters.AddWithValue("@BAccount", SqlDbType.VarChar)
            cmd.Parameters("@BAccount").Value = IBTextBox.Text
            If IBTextBox.Text Is Nothing Then
                cmd.Parameters("@BAccount").Value = DBNull.Value
            Else
                cmd.Parameters("@BAccount").Value = IBTextBox.Text
            End If
            conn.Open()
            Using rdr As SqlDataReader = cmd.ExecuteReader
                If (rdr.Read) Then
                    LSD = rdr("Company_Name").ToString()
                    Return True
                Else
                    Return False
                End If
            End Using
            conn.Close()
        End Using
    End Using
End Function
Private Function GetWebsite(ByRef LSC As String) As Boolean
    'declare variable
    Dim BAccount As String = CType(Session("BAccount"), String)
    'sql statement for website information
    Dim sql As String = "SELECT TOP 1 WebSites, BAccount FROM IB_WEBSITES INNER JOIN IB_BUISNESS_INFORMATION ON (IB_WEBSITES.IB_ID = IB_BUISNESS_INFORMATION.IB_ID) WHERE BAccount = @BAccount"
    Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("IBConnectionString").ConnectionString)
        Using cmd As New SqlCommand(sql, conn)
            cmd.Parameters.AddWithValue("@BAccount", SqlDbType.VarChar)
            cmd.Parameters("@BAccount").Value = IBTextBox.Text
            If IBTextBox.Text Is Nothing Then
                cmd.Parameters("@BAccount").Value = DBNull.Value
            Else
                cmd.Parameters("@BAccount").Value = IBTextBox.Text
            End If
            conn.Open()
            Using rdr As SqlDataReader = cmd.ExecuteReader
                If (rdr.Read) Then
                    LSC = rdr("WebSites").ToString()
                    Return True
                Else
                    Return False
                End If
            End Using
            conn.Close()
        End Using
    End Using
End Function
Protected Sub lbNotIB_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbNotIB.Click
    'if user is not IB that currently holds session, this will destroy the session and allow them to enter different code
    Session.Abandon()
    Response.Redirect(Request.RawUrl)
End Sub

End Class

aspx:

<asp:Label ID="IBText" runat="server" Text="Enter your IB code here:"></asp:Label>
  <asp:TextBox ID="IBTextBox" runat="server"></asp:TextBox>
  <asp:Button ID="IBTextBoxButton" runat="server" Text="Submit" />
  <asp:CustomValidator ID="CustomValidator1" runat="server"
  ControlToValidate="IBTextBox" ForeColor="Red"
  OnServerValidate="CustomValidator1_ServerValidate"></asp:CustomValidator>
<asp:Label ID="lblIB" runat="server" Text=""></asp:Label>

web.config:

<sessionState mode="InProc" cookieless="false" timeout="20" sqlConnectionString="Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***">
</sessionState>

UPDATE: Hah! I finally got it! So there are 2 problems here. I did not have <httpModules> set in my web.config. I needed to add:

<httpModules>
   <add name="Session" type="System.Web.SessionState.SessionStateModule"/>
</httpModules>

Reference

Now the problem is that I have information being pulled from 2 databases for these Sessions but have only 1 database listed in the <sessionState> section of my web.config file. I tried adding a 2nd <sessionState> but it threw an error.

Is there a way to include the 2nd database? If I don't, half of my sessions will stay throughout the website, and half will disappear. By the way, I didn't have anything to do with the database creation, this was all done way before my time.

I tried this in web.config but it also doesn't work:

<sessionState mode="InProc" 
              cookieless="false" 
              timeout="20" 
              sqlConnectionString="IBConnectionString, BackUpConnectionString">
</sessionState>

Yet another update: Here's another thing I tried, suggested by a user on the asp.net forums. This generated a 500 internal server error as well, so that makes me think that having 2 instances of <sessionState> is not something that is allowed.

<sessionState mode="SQLServer"
              cookieless="false"
              timeout="20"
              sqlConnectionString="IBConnectionString">
</sessionState>
<sessionState mode="SQLServer"
              cookieless="false"
              timeout="20"
              sqlConnectionString="BackUpConnectionString">
</sessionState>

More: sessionState has been changed and the site still acts like it has been, the ConnectionString must not have anything to do with the problem with the 2nd database losing it's session. It's gotta be something in the code behind, I can't think of what else could be wrong with the web.config.

<sessionState mode="InProc" timeout="20"></sessionState>

We also found out that the session variable is still there, it just won't display the user's information when it's connected to the back up database connection.

---

After much deliberation and frustration, I asked my boss how hard it would be to just combine the databases. Although there are more than 2400 records in the Back Up Account database, there is really no other option. I do not foresee a solution coming to me anytime soon and I have already wasted a month on this....thanks everyone for the help.

If I ever do figure something out, I will come back and edit this post!

Answer 1

First off, remove your code in the Init portion of the page. It is unnecssary.

Second, why are you setting the IB values of the session to True in portions of your code? It's overwriting the account number. Change both..

Session("IB") = True

to

Session("IB") = args.Value

Or just don't even mess with the session at that point..it should already be set from the IBTextBoxButton_Click Sub Routine.

Answer 2

Make sure session state enabled in web.config. If it is set to "StateServer" or "SQLServer", change it to "InProc" for testing purposes to rule out external failing dependencies.

<sessionState mode="InProc" />

Answer 3

Also, I have seen exceptions get thrown in the past that seemed to "eat" the session. Look for any try/catch blocks that might be giving you problems.

Single stepping through the code normally shows the problems, but if not, one technique I use that helps sometimes, is to litter your code with "i am here" messages.

The thinking is that sometimes it helps trigger an epiphany when you can see which line (or close to it) appears to be creating the problem.

For example, you could dump a session variable state along with an approximate line number and just output it at several spots to your web page.

You also should try to narrow down the amount of code you are debugging.

If you can isolate the problem to a smaller set of code, often this helps spot problems.

** edit ** Apparently I was just scanning. Upon actually reading your post, i see you have identified the line causing the issue. I don't see anything wrong with the line, but for some reason i want to try using a different session variable name. I don't know if that will help, but it's an easy thing to try. Maybe use a longer session variable name. Without having the debugger open in front of me, I don't know what else to check off the top of my head.