Member Avatar for Alv45525

Hi guys, i have a login page where i created a session trainer after login. the session is the mail address of the trainer.

now from another apsx page, i would like to retrieve the staffid of the user logged in using the session as criteria.
I tried to write the below code but its not working. Any ideas please?

  Dim cmd As New SqlCommand
        Dim query1 As String

        query1 = "SELECT staffid FROM tblstaff WHERE email='" & Session("trainer") & "'"

        connect.ConnectionString = Constr
        connect.Open()

        cmd = New SqlCommand(query1, connect)

        Dim rdr As SqlDataReader = cmd.ExecuteReader
        Dim bFound As Boolean = False
        If rdr.Read() Then
            Dim staffid As String = rdr("staffid")
            lblStaffID.Text = staffid

        End If
        rdr.Close()

        connect.Close()
Edited by happygeek because: moved
  • 2 Contributors
  • 1 Reply
  • 337 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by xrjf

Dani AI

Generated

— the core issue is usually not the reader loop but the way the session value is used and how the database is queried. Concatenating Session("trainer") into SQL makes the call fragile (null/whitespace, type/DBNull surprises, connection problems) and opens the door to SQL injection. 's suggestion to wrap the access in error handling is good — the exception text is the fastest clue to what's actually failing.

Checks to perform before running the query: confirm Session("trainer") is set and trimmed (not Nothing or empty), verify the connection string (Constr) actually points to the right database, confirm the table/column names and the data type of staffid, and remove unused variables such as bFound. For a single scalar value prefer ExecuteScalar over a reader and always use a parameterized query so emails with apostrophes or special characters do not break the SQL.

Example (robust, parameterized pattern):

Try
    Dim email As String = If(Session("trainer") IsNot Nothing, Session("trainer").ToString().Trim(), String.Empty)
    If email <> String.Empty Then
        Using cn As New SqlConnection(Constr)
            Using cmd As New SqlCommand("SELECT staffid FROM tblstaff WHERE email = @email", cn)
                cmd.Parameters.Add("@email", SqlDbType.NVarChar, 256).Value = email
                cn.Open()
                Dim result = cmd.ExecuteScalar()
                If result IsNot Nothing AndAlso Not Convert.IsDBNull(result) Then
                    lblStaffID.Text = result.ToString()
                End If
            End Using
        End Using
    End If
Catch ex As Exception
    ' Log or inspect ex.Message during development
End Try

Notes: handle DBNull explicitly, convert staffid to the appropriate type (Int32 if numeric), and avoid showing raw exceptions in production — log them instead. If Session("trainer") is unexpectedly Nothing, investigate the login flow/session state settings (session expiration, cookies, or page-level EnableSessionState).

Member Avatar for xrjf

Do you get any error message? Enclose in a try/catch. Variable bFound is what for?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.