Solved

Retrieving Sql Data And Storing In Session Variables

Posted on 2014-03-27
4
367 Views
Last Modified: 2014-04-01
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
0
Comment
Question by:Ed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39960098
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.
0
 

Accepted Solution

by:
Ed earned 0 total points
ID: 39960352
Thanks for you help.

It can be done though and I've managed to get it working now.

See the code below. It works on a selected gridview row.

Protected Sub GridView1_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles GridView1.SelectedIndexChanged
        Dim row As GridViewRow = GridView1.SelectedRow

        Dim Val = row.Cells(0).Text





        Dim strConn As String = System.Configuration.ConfigurationManager.ConnectionStrings("QuoteConnectionString").ToString()
        Dim sql As String = "SELECT * FROM AllQuotes where QuoteID =@QuoteID"
        Dim conn As New Data.SqlClient.SqlConnection(strConn)
        Dim objDR As Data.SqlClient.SqlDataReader
        Dim Cmd As New Data.SqlClient.SqlCommand(sql, conn)

        Cmd.Parameters.AddWithValue("@username", row.Cells(0).Text)

        conn.Open()
        objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
        While objDR.Read()
    


            Session("LoanAmount") = objDR("LoanAmount")
            Session("LoanTerm") = objDR("LoanTerm")
     


        End While

        Response.Redirect("xxxxxxx.aspx")
    End Sub

Open in new window

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39960359
That isn't quite what you asked for in your question....but at least you're sorted.
0
 

Author Closing Comment

by:Ed
ID: 39968815
Worked it out myself
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question