Solved

Retrieving data from SqlServer database into Asp.Net Gridview

Posted on 2010-09-18
9
615 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
9 Comments
 
LVL 3

Expert Comment

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

Expert Comment

by:Carl Tawn
Comment Utility
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 500 total points
Comment Utility
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
 

Author Comment

by:adamtrask
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:adamtrask
Comment Utility
and here is the image for the table
table.jpg
0
 

Author Comment

by:adamtrask
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now