Problem binding to GridView with DataTable

Hi,

I'm trying to bind to my GridView with a DataTable but nothing is happening, can any see glaring holes in my code?
<asp:GridView ID="gvwTest" AutoGenerateColumns="False" runat="server">
<Columns>
    <asp:TemplateField HeaderText="Question ID">
        <ItemTemplate>
            <asp:label ID="lblquestionID" Text='<%#Bind("questionID")%>' runat="server"></asp:label>
        </ItemTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>
 
Dim con As New SqlConnection(ConnectionStrings("t2").ConnectionString)
        Dim cmd As New SqlCommand("Select questionID, courseID, questionType, question, questionFileName, rightAnswer, wrongAnswer1, wrongAnswer2, wrongAnswer3, wrongAnswer4, " & _
            "wrongAnswer5, wrongAnswer6, wrongAnswer7, wrongAnswer8, wrongAnswer9, wrongAnswer10 From dbo.trn_preCourseAssessments Where questionStatus = 1 And " & _
            "courseID = " & courseID & "", con)
        cmd.CommandType = CommandType.Text
 
        con.Open()
        Dim rdr As SqlDataReader = cmd.ExecuteReader
        Dim dt As New DataTable()
 
        If rdr.HasRows Then
            dt.Columns.Add("questionID", GetType(Integer))
            dt.Columns.Add("courseID", GetType(Integer))
            dt.Columns.Add("questionType", GetType(Integer))
            dt.Columns.Add("question", GetType(String))
            dt.Columns.Add("questionFileName", GetType(String))
            dt.Columns.Add("rightAnswer", GetType(String))
            dt.Columns.Add("wrongAnswer1", GetType(String))
            dt.Columns.Add("wrongAnswer2", GetType(String))
            dt.Columns.Add("wrongAnswer3", GetType(String))
            dt.Columns.Add("wrongAnswer4", GetType(String))
            dt.Columns.Add("wrongAnswer5", GetType(String))
            dt.Columns.Add("wrongAnswer6", GetType(String))
            dt.Columns.Add("wrongAnswer7", GetType(String))
            dt.Columns.Add("wrongAnswer8", GetType(String))
            dt.Columns.Add("wrongAnswer9", GetType(String))
            dt.Columns.Add("wrongAnswer10", GetType(String))
 
 
            Dim newRow As DataRow = dt.NewRow()
            While rdr.Read()
                newRow("questionID") = rdr.GetValue(0)
                newRow("courseID") = rdr.GetValue(1)
                newRow("questionType") = rdr.GetValue(2)
                newRow("question") = rdr.GetValue(3).ToString
                newRow("questionFileName") = rdr.GetValue(4).ToString
                newRow("rightAnswer") = rdr.GetValue(5).ToString
                newRow("wrongAnswer1") = rdr.GetValue(6).ToString
                newRow("wrongAnswer2") = rdr.GetValue(7).ToString
                newRow("wrongAnswer3") = rdr.GetValue(8).ToString
                newRow("wrongAnswer4") = rdr.GetValue(9).ToString
                newRow("wrongAnswer5") = rdr.GetValue(10).ToString
                newRow("wrongAnswer6") = rdr.GetValue(11).ToString
                newRow("wrongAnswer7") = rdr.GetValue(12).ToString
                newRow("wrongAnswer8") = rdr.GetValue(13).ToString
                newRow("wrongAnswer9") = rdr.GetValue(14).ToString
                newRow("wrongAnswer10") = rdr.GetValue(15).ToString
            End While
        End If
 
        rdr.Close()
        con.Close()
 
        gvwTest.DataSource = dt
        gvwTest.DataBind()

Open in new window

LVL 11
G0ggyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
change:
gvwTest.DataSource = dt

into:

dt.AcceptChanges
gvwTest.DataSource = dt.DefaultView

0
G0ggyAuthor Commented:
That didn't work, sorry.
0
divinewind80Commented:
You can manipulate the columns as you are doing by a differnet method, perhaps using an SQLDataSource control.  Is this an option?

For instance, declare:

             <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Yourconnectionstring %>"
                SelectCommand="SELECT * FROM TBLREQUESTS WHERE ID = @ID ">
            </asp:SqlDataSource>

Then in code behind:

 Dim mastertable As DataTable
        mastertable = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), Data.DataView).ToTable()
gv1.datasource = mastertable
gv1.databind()

This should accomplish the same thing.

After setting your mastertable equal to your Sqldatasource1, you can alter as needed.


0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

divinewind80Commented:
My guess with your current proble, though, is that the data is not being populated into the datatable properly.  This code works though.

If you prefer to go with your method, let me know.
0
G0ggyAuthor Commented:
Interesting you should write "not being populated into the datatable properly" as my code is taken from MSDN and another website. I would like to get the above example working.

The other option is to switch to an array as I need to manipulate the columns of data prior to display. They are multiple choice questions...
0
ReecioCommented:
I'm not sure why you're using a datareader as you can use a dataadpater to dump all results from your SQL query in one go.
That should work if all the connection info / SQL etc is correct.
And if you want to show your data in row's rather than columns, switch the gridview to a detailsview, they're both pretty customisable anyway.
        Dim con As New SqlConnection(ConnectionStrings("t2").ConnectionString)
        Dim cmd As New SqlCommand("Select questionID, courseID, questionType, question, questionFileName, rightAnswer, wrongAnswer1, wrongAnswer2, wrongAnswer3, wrongAnswer4, " & _
            "wrongAnswer5, wrongAnswer6, wrongAnswer7, wrongAnswer8, wrongAnswer9, wrongAnswer10 From dbo.trn_preCourseAssessments Where questionStatus = 1 And " & _
            "courseID = " & courseID & "", con)
 
        Dim dataadapter As New SqlDataAdapter(cmd)
        Dim dt As New DataTable
        dataadapter.Fill(dt)
 
        gvwTest.DataSource = dt
        gvwTest.DataBind()

Open in new window

0
ReecioCommented:
Ahh wait, i'm thinking ASP there, let me get back to you on that as I dont think VB.Net has a detailsview. i'll check it :)
0
ReecioCommented:
Wait, you are using ASP anyway, I think i'm losing my mind! :P
0
LimbeckCommented:
hm try setting AutoGenerateColumns="False"  to true :)
0
G0ggyAuthor Commented:
That doesn't make any difference and on several website they explicitly tell you to set this to false.
0
ReecioCommented:
It seems that when you add to the table its a bit wrong.
As dt.NewRow creates an empty row with the same structure as the table, you have to assign the values to the columns for which you use .item which you seem to have missed.
I have written the following, see if this helps!?
        Dim NewRow As DataRow = dt.NewRow
        
        With NewRow
            .Item("questionID") = rdr.GetValue(0)
            .Item("courseID") = rdr.GetValue(1)
            .Item("questionType") = rdr.GetValue(2)
            .Item("question") = rdr.GetValue(3)
            .Item("questionFileName") = rdr.GetValue(4)
            .Item("rightAnswer") = rdr.GetValue(5)
            .Item("wrongAnswer1") = rdr.GetValue(6)
            .Item("wrongAnswer2") = rdr.GetValue(7)
            .Item("wrongAnswer3") = rdr.GetValue(8)
            .Item("wrongAnswer4") = rdr.GetValue(9)
            .Item("wrongAnswer5") = rdr.GetValue(10)
            .Item("wrongAnswer6") = rdr.GetValue(11)
            .Item("wrongAnswer7") = rdr.GetValue(12)
            .Item("wrongAnswer8") = rdr.GetValue(13)
            .Item("wrongAnswer9") = rdr.GetValue(14)
            .Item("wrongAnswer10") = rdr.GetValue(15)
        End With
                
        dt.Rows.Add(NewRow)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.