asked on

Retrieving Sql Data And Storing In Session Variables

Hi

I'm trying/want to store the results of a sql query in session variables, how do I do this?

My code so far works and returns results to a gridview..

        If True Then
            'Establishing the MySQL Connection
            Dim strConnString As String = ConfigurationManager.ConnectionStrings("ZUConnectionString").ConnectionString

            Dim sqlConnection1 As New SqlConnection(strConnString)

            Dim query As String
            Dim SqlCommand As SqlCommand
            Dim reader As SqlDataReader

            Dim adapter As New SqlDataAdapter()
            'Open the connection to db
            sqlConnection1.Open()

            'Generating the query to fetch the contact details
            query = "SELECT * FROM All"

            SqlCommand = New SqlCommand(query, sqlConnection1)
            adapter.SelectCommand = New SqlCommand(query, sqlConnection1)
            'execute the query
            reader = SqlCommand.ExecuteReader()
            'Assign the results 
            GridView1.DataSource = reader

            'Bind the data
            GridView1.DataBind()

Open in new window

I'd like to store each returned field as

Session("xxxxxx") =

Thanks

Carl Tawn

It would be simpler to load the data into a DataTable and store that - plus you can't use a DataReader in this context because it is read-forward only so you can't bind to it and then read it again manually:

        If True Then
            'Establishing the MySQL Connection
            Dim strConnString As String = ConfigurationManager.ConnectionStrings("ZUConnectionString").ConnectionString

            Dim sqlConnection1 As New SqlConnection(strConnString)
            Dim query As String

            Dim adapter As New SqlDataAdapter()
            Dim table As New DataTable()

            'Generating the query to fetch the contact details
            query = "SELECT * FROM All"

            adapter.SelectCommand = New SqlCommand(query, sqlConnection1)
            adapter.Fill(table)

            'Assign the results 
            GridView1.DataSource = table

           '// Store in session
           Session("Table") = table

            'Bind the data
            GridView1.DataBind()

Open in new window

The bigger question, of course, is why do you want to store it in a Session variable? Holding large amounts of data in user specific context is usually a bad idea.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carl Tawn

That isn't quite what you asked for in your question....but at least you're sorted.

ASKER

Worked it out myself