Having problems with sqlDataReader

Question

I am using a sqlDataReader to get data and set it to session variables. The problem is it doesn't want to work with expressions. I can reference any other column in the table, but not the expressions. The SQL does work. The code is below. Thanks in advance, Anthony

Using myConnectionCheck As New SqlConnection(myConnectionString)
    Dim myCommandCheck As New SqlCommand()
    myCommandCheck.Connection = myConnectionCheck
    myCommandCheck.CommandText = "SELECT Projects.Pro_Ver, Projects.Pro_Name, Projects.TL_Num, Projects.LP_Num, Projects.Dev_Num, Projects.Val_Num, Projects.Completed, Flow.Initiate_Date, Flow.Requirements, Flow.Req_Date, Flow.Dev_Review, Flow.Dev_Review_Date, Flow.Interface, Flow.Interface_Date, Flow.Approval, Flow.Approval_Date, Flow.Test_Plan, Flow.Test_Plan_Date, Flow.Dev_Start, Flow.Dev_Start_Date, Flow.Val_Start, Flow.Val_Start_Date, Flow.Val_Complete, Flow.Val_Complete_Date, Flow.Stage_Production, Flow.Stage_Production_Date, Flow.MKS, Flow.MKS_Date, Flow.DIET, Flow.DIET_Date, Flow.Closed, Flow.Closed_Date, Flow.Dev_End, Flow.Dev_End_Date, Users_1.Email AS Expr1, Users_2.Email AS Expr2, Users_3.Email AS Expr3, Users_4.Email AS Expr4, Users_4.FNAME, Users_3.FNAME AS Expr5, Users_2.FNAME AS Expr6, Users_1.FNAME AS Expr7 FROM Projects INNER JOIN Users AS Users_1 ON Projects.TL_Num = Users_1.PIN INNER JOIN Users AS Users_2 ON Projects.LP_Num = Users_2.PIN INNER JOIN Users AS Users_3 ON Projects.Dev_Num = Users_3.PIN INNER JOIN Users AS Users_4 ON Projects.Val_Num = Users_4.PIN INNER JOIN Flow ON Projects.id = Flow.Flow_Pro_Num WHERE id = "
    myCommandCheck.CommandText += QSid
    myConnectionCheck.Open()
    myCommandCheck.ExecuteNonQuery()
    Dim count As Int16 = myCommandCheck.ExecuteScalar
    If count = 1 Then
        Dim myDataReader As SqlDataReader
        myDataReader = myCommandCheck.ExecuteReader()
        While myDataReader.Read()
            Session("TL_email") = myDataReader("Expr1").ToString()
            Session("PE_email") = myDataReader("Expr2").ToString()
            Session("DEV_email") = myDataReader("Expr3").ToString()
            Session("VAL_email") = myDataReader("Expr4").ToString()
            Session("Project_Name") = myDataReader("Pro_Name").ToString()
        End While
        myDataReader.Close()
    End If
End Using

Answer 1

This may be because column names need to be unique for the SqlDataReader to be able to index them using a string name for the column.

Answer 2

A couple of things:

1) You are executing the query 3 times. You can lose the ExecuteNonQuery and ExecuteScalar calls, and replace the while loop with "if myDataReader.Read() / end if" to get the data values for the first resulting record. If no records are found, no session variables are set, just as in your current code.

2) It looks more like the problem lies in your session management (ie getting values from Session) rather than your sql query, which looks OK to me.

Check:

• that you have sessionState enabled in your web.config file,
• that you don't reset the Session values anywhere, and
• that you ask for the same Session field name when you are trying to send the email. (e.g. are you setting Session("DEV_Email") but asking for Session("DEV Email") (space instead of underscore) ?

Answer 3

Sorry everyone. The code works just fine. The sqlDataReader WILL accept expressions as column names.

The reason I was getting an error saying the value of the from and to parameters cannot be null. There was no data in that column for any of the records in my table.