Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Retrieving data from SqlServer database into Asp.Net Gridview

Posted on 2010-09-18
9
Medium Priority
?
623 Views
Last Modified: 2012-06-27
Hi,
I am trying to retrieve data from one table in an SqlServer database to display it in a gridview called "Grid".

The trouble is that the data consist of answers to 22 questions. Each question stores the answer in one of 5 columns (A,B,C,D,E).
To read the answer for each question, I created several Sql commands each command reads the results of one question.. So, the first command would read the answer for the first question, the second command would read the data from second question, etc.

However, I don't know how to bind the Gridveiw to all the commands in a way that would display all their data in a table-like fashion .

I attempted using the following code to retrieve the data and bind the gridview to each command, one by one, but the gridview ends up displaying only the data for the last command. Is it possible to build one command that would read all the data and bind the gridview to it, or is there a way to have the gridview display the data retrieved by all the commands? Thanks.

  Dim con As SqlConnection
        Dim comm1 As SqlCommand
        Dim reader As SqlDataReader
        '---------------
        ' Create the connection to the database
        con = New SqlConnection("Server=Localhost\SQLEXPRESS; Database=Survey;Integrated Security=True")
         comm1 = New SqlCommand("Select a,b,c,d,e from Response where SurveyID =@surveyID and QuestionID =@QuestionID ", con)
        comm1.Parameters.AddWithValue("@surveyID", System.Data.SqlDbType.Int)
        comm1.Parameters("@surveyID").Value = usedID
        comm1.Parameters.AddWithValue("@QuestionID", System.Data.SqlDbType.Int)
        comm1.Parameters("@QuestionID").Value = 1
        ' Open Connection
        con.Open()
        ' execute comm
        reader = comm1.ExecuteReader
        Me.grid.DataSource = reader
        Me.grid.DataBind()
        reader.Close()
        con.Close()
        '------------
        Dim comm2 As SqlCommand
        comm2 = New SqlCommand("Select a,b,c,d,e from Response where SurveyID =@surveyID and QuestionID =@QuestionID ", con)
        comm2.Parameters.AddWithValue("@surveyID", System.Data.SqlDbType.Int)
        comm2.Parameters("@surveyID").Value = usedID
        comm2.Parameters.AddWithValue("@QuestionID", System.Data.SqlDbType.Int)
        comm2.Parameters("@QuestionID").Value = 2
        ' Open Connection
        con.Open()
        ' execute comm
        reader = comm2.ExecuteReader
        Me.grid.DataSource = reader
        Me.grid.DataBind()
        reader.Close()
        con.Close()
0
Comment
Question by:adamtrask
[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
9 Comments
 
LVL 3

Expert Comment

by:xiong8086
ID: 33707993
maybe you can post some sample data in your database, and sample output for your gridview.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33708018
The Grid can only have a single datasource, therefore you would need to combine the results of each of your queries into a single DataTable and bind to that. However, your method of retrieving data is extremely inefficient. I can't quite tell from the code what you are trying to achieve, but you would be better trying to come up with a query that can retrieve all of the data you need in a single query.
0
 
LVL 12

Accepted Solution

by:
jagssidurala earned 2000 total points
ID: 33708284
We can assign single data source to the grid.

        Dim con As SqlConnection
        Dim comm1 As SqlCommand
        Dim reader As SqlDataReader
        '---------------
        ' Create the connection to the database
        con = New SqlConnection("Server=Localhost\SQLEXPRESS; Database=Survey;Integrated Security=True")
         comm1 = New SqlCommand(
      "Select Questionname,a,b,c,d,e
      from    Response TR
      Inner
      Join     tblQuestion TQ TR.QuestionId = TQ.questionId
      where SurveyID =" + intSurveyId, con)
       SqlDataAdapter da = new  SqlDataAdapter();
       DataTable dt = new DataTable()
       da.Fill(dt);        

        Me.grid.DataSource = dt;
        Me.grid.DataBind()
       
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:adamtrask
ID: 33708289
Please find attached two image files - one for the GridView and the other for the table containing the data.

I am attaching the images as sample for the data as requested by xiong8086.

I also want to correct the way I wrote the sqlcommand - I neglected to put in part of the command:

 comm1 = New SqlCommand("Select SurveyID, QuestionID, a,b,c,d,e from Response where SurveyID =@surveyID and QuestionID =@QuestionID ", con)
        comm1.Parameters.AddWithValue("@surveyID", System.Data.SqlDbType.Int)
        comm1.Parameters("@surveyID").Value = usedID
        comm1.Parameters.AddWithValue("@QuestionID", System.Data.SqlDbType.Int)
        comm1.Parameters("@QuestionID").Value = 1
        ' Open Connection
        con.Open()
        ' execute comm
        reader = comm1.ExecuteReader
        Me.grid.DataSource = reader
        Me.grid.DataBind()
        reader.Close()
        con.Close()
Gridview.jpg
0
 

Author Comment

by:adamtrask
ID: 33708290
and here is the image for the table
table.jpg
0
 

Author Comment

by:adamtrask
ID: 33708315
I am sorry guys, that was a stupid way of retrieving data..... I substitued it with:

  comm1 = New SqlCommand("Select * from Response where SurveyID =@surveyID ", con)

Which is what I really needed. I am very much a novice in this.

Thanks a lot any way
0
 

Author Comment

by:adamtrask
ID: 33708496
I actually meant to award the points to the two experts, I must have clicked some thing by mistake
0
 

Author Closing Comment

by:adamtrask
ID: 33708502
Thanks
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33709514
You can put in a request to community support to have points reallocated, or have it reopened if you want to reallocate points yourself.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 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