Error Extracting Datarow from a Datatable using Datatable.Select

I am populating a datatable with a bunch of player stats for a hockey league. The stats contain a list of goals, assists, etc... for a bunch of players.

I have chosen to pull the data into a datatable, so that I can modify the table later. So I really need an answer that sticks with the datatable format.

In order to select the stats for a SINGLE player, I wanted to use the datatable.select statement, and set  "PlayerID = 1234" to pull out a certain player. But I am getting an error, and I cannot figure out why.

Error message:
A field or property with the name 'GamesPlayed' was not found on the selected data source.

However, if I change the line
      ScoringGrid.DataSource = dt
then I get the entire datatable with no problems.

What could be the issue ?


Sqlstring = "SELECT P.Playernum as Playernum, SUM(P.GP) As GamesPlayed, SUM(P.G) As Goals, SUM(P.A) As Assists, SUM(P.PTS) As Points, SUM(P.PIM) As Penalty from playerstats P, contacts C, Games G, Leaguesetup L where C.Usernum = P.Playernum and G.Gamedatetime > L.Startdate and L.Leaguenum = P.Leaguenum and G.Gamenum = P.Gamenum and C.Leaguenum = " & Session("Leaguenum") & " Group by P.Playernum"
 
Dim cmd1 As SqlCommand = New SqlCommand(Sqlstring, MyConn)
Dim dr As SqlDataReader = cmd1.ExecuteReader(CommandBehavior.CloseConnection)
          
Dim dt As DataTable = New DataTable()
Dim column As DataColumn 
    
column = New DataColumn()
column.DataType = System.Type.GetType("System.Int32")
column.ColumnName = "Playernum"
column.ReadOnly = False
column.Unique = False
dt.Columns.Add(column)
 
column = New DataColumn()
column.DataType = System.Type.GetType("System.String")
column.ColumnName = "GamesPlayed"
column.ReadOnly = False
column.Unique = False
dt.Columns.Add(column)
 
column = New DataColumn()
column.DataType = System.Type.GetType("System.String")
column.ColumnName = "Goals"
column.ReadOnly = False
column.Unique = False
dt.Columns.Add(column)
            
column = New DataColumn()
column.DataType = System.Type.GetType("System.String")
column.ColumnName = "Assists"
column.ReadOnly = False
column.Unique = False
dt.Columns.Add(column)
            
column = New DataColumn()
column.DataType = System.Type.GetType("System.String")
column.ColumnName = "Points"
column.ReadOnly = False
column.Unique = False
dt.Columns.Add(column)
            
column = New DataColumn()
column.DataType = System.Type.GetType("System.String")
column.ColumnName = "Penalty"
column.ReadOnly = False
column.Unique = False
dt.Columns.Add(column)           
 
dt.Load(dr)
 
' Now select only the row that you want (PlayerID)
Dim GetRow As DataRow() = dt.Select("Playernum = " & PlayerID)
     
ScoringGrid.DataSource = GetRow
ScoringGrid.DataBind()
 
.............. and later in my HTML
 
<asp:DataGrid ID="ScoringGrid" GridLines="Both" ItemStyle-BorderColor="#0054A6" ItemStyle-BorderWidth="1" AutoGenerateColumns="false" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="true" HeaderStyle-Font-Names="Arial" HeaderStyle-Font-Size="10pt" HeaderStyle-Font-Underline="true" ItemStyle-HorizontalAlign="Center" runat="server" ItemStyle-Font-Size="10pt" ItemStyle-Font-Names="Arial" >
     <Columns>
          <asp:BoundColumn DataField="GamesPlayed" />
          <asp:BoundColumn DataField="Goals" />
          <asp:BoundColumn DataField="Assists" />
          <asp:BoundColumn DataField="Points" />
          <asp:BoundColumn DataField="Penalty" />
      </Columns>
</asp:DataGrid>

Open in new window

pbisseggerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

sijishJohnCommented:
you cannot set datarow as datasource.....

extract your datarow and put it into another datatable ...then set the new datatable as the datasource...
Pratima PharandeCommented:
where you are gettngPlayerID here ??

SELECT P.Playernum as Playernum, SUM(P.GP) As GamesPlayed, SUM(P.G) As Goals, SUM(P.A) As Assists, SUM(P.PTS) As Points, SUM(P.PIM) As Penalty from playerstats P, contacts C, Games G, Leaguesetup L where C.Usernum = P.Playernum and G.Gamedatetime > L.Startdate and L.Leaguenum = P.Leaguenum and G.Gamenum = P.Gamenum and C.Leaguenum = " & Session("Leaguenum") & " Group by P.Playernum
Pratima PharandeCommented:
You must need to PlayerID from select query

SELECT P.Playernum as Playernum, SUM(P.GP) As GamesPlayed, SUM(P.G) As Goals, SUM(P.A) As Assists, SUM(P.PTS) As Points, SUM(P.PIM) As Penalty from playerstats P, contacts C, Games G, Leaguesetup L where C.Usernum = P.Playernum and G.Gamedatetime > L.Startdate and L.Leaguenum = P.Leaguenum and G.Gamenum = P.Gamenum and C.Leaguenum = " & Session("Leaguenum") & " Group by P.Playernum
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

pbisseggerAuthor Commented:
pratima_mcs:

Earlier in my code I had:

PlayerID = Request.Querystring("pstep")

I had only attached a portion of my code in the code snippet. I have attached all the code in the file below in case you have further questions.

sijishJohn:

As for the 2nd datatable idea, I have tried to create a 2nd datatable using the additional code below:

Dim dt1 As DataTable = New DataTable()
dt1 = dt.Select("Playernum = " & PlayerID)

but I get the following error:

Compiler Error Message: BC30311: Value of type '1-dimensional array of System.Data.DataRow' cannot be converted to 'System.Data.DataTable'.

Any suggestions ?

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
 
<script runat="server">
 
    Dim cmd As New SqlCommand()
    Dim MyConn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
    Dim TransReader As SqlDataReader
    Dim MyTools As New Krylium.KryliumTools
    Dim Ttext(26) As String
    Dim PlayerID as Integer
    Dim Sqlstring as String
    Dim TF, Firstplace, Secondplace, Thirdplace, Fourthplace, Fifthplace, Sixthplace as Integer
    Dim ds As DataSet = New DataSet()
    
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        
        PlayerID = Request.Querystring("pstep")
 
        If Not Page.IsPostBack Then
        
            cmd.Connection = MyConn
            cmd.Connection.Open()
 
            ' Load name and player's team
            cmd.CommandText = "Select firstname + ' ' + lastname as name from contacts where usernum = " & PlayerID
            Swimmername.Text = cmd.ExecuteScalar()
            
            cmd.CommandText = "Select l.groupname from leagues l, contacts c where c.teamnum = l.groupnum and c.usernum = " & PlayerID
            Swimmergroup.Text = cmd.ExecuteScalar()
            
            ' Load the appropriate language files
                
            TText = MyTools.LoadTranslation("playerdash", MyTools.GetLanguage)
 
            ' Maintext.Text = Ttext(21)
            ' newsHyperlink.Text = Ttext(1) 
            
            ' Define Header Text for Playerstats Datagrid
            ScoringGrid.Columns(0).HeaderText = "GP"
            ScoringGrid.Columns(1).HeaderText = Ttext(2)
            ScoringGrid.Columns(2).HeaderText = Ttext(3)
            ScoringGrid.Columns(3).HeaderText = Ttext(4)
            ScoringGrid.Columns(4).HeaderText = Ttext(5)
    	
            ' Load Playerstats Datagrid
 
          Sqlstring = "SELECT P.Playernum as Playernum, SUM(P.GP) As GamesPlayed, SUM(P.G) As Goals, SUM(P.A) As Assists, SUM(P.PTS) As Points, SUM(P.PIM) As Penalty from playerstats P, contacts C, Games G, Leaguesetup L where C.Usernum = P.Playernum and G.Gamedatetime > L.Startdate and L.Leaguenum = P.Leaguenum and G.Gamenum = P.Gamenum and C.Leaguenum = " & Session("Leaguenum") & " Group by P.Playernum"
          Dim cmd1 As SqlCommand = New SqlCommand(Sqlstring, MyConn)
          Dim dr As SqlDataReader = cmd1.ExecuteReader(CommandBehavior.CloseConnection)
          
          Dim dt As DataTable = New DataTable()
          Dim column As DataColumn 
    
            column = New DataColumn()
            column.DataType = System.Type.GetType("System.Int32")
            column.ColumnName = "Playernum"
            column.ReadOnly = False
            column.Unique = False
            dt.Columns.Add(column)
 
            column = New DataColumn()
            column.DataType = System.Type.GetType("System.String")
            column.ColumnName = "GamesPlayed"
            column.ReadOnly = False
            column.Unique = False
            dt.Columns.Add(column)
 
            column = New DataColumn()
            column.DataType = System.Type.GetType("System.String")
            column.ColumnName = "Goals"
            column.ReadOnly = False
            column.Unique = False
            dt.Columns.Add(column)
            
            column = New DataColumn()
            column.DataType = System.Type.GetType("System.String")
            column.ColumnName = "Assists"
            column.ReadOnly = False
            column.Unique = False
            dt.Columns.Add(column)
            
            column = New DataColumn()
            column.DataType = System.Type.GetType("System.String")
            column.ColumnName = "Points"
            column.ReadOnly = False
            column.Unique = False
            dt.Columns.Add(column)
            
            column = New DataColumn()
            column.DataType = System.Type.GetType("System.String")
            column.ColumnName = "Penalty"
            column.ReadOnly = False
            column.Unique = False
            dt.Columns.Add(column)           
 
          dt.Load(dr)
 
          ' Update the table to include all rankings for all players
       '  Rankplayers(dt)
     
          ' Now select only the row that you want (PlayerID)
          Dim GetRow As DataRow() = dt.Select("Playernum = " & PlayerID)
     
            ScoringGrid.DataSource = GetRow
            ScoringGrid.DataBind()
            If ScoringGrid.Items.Count = 0 Then
                ScoringGrid.Visible = False
            End If           
        
            cmd.Connection.Close()
            
            Linkbutton1.PostBackUrl="players-list.aspx"
            
        End if     
 
    End Sub
    
    Function Formatplace(Playerplace as Integer) as String
        Dim strVal As String = Trim(Playerplace.ToString())
        Dim onesPlace As String = Right(strVal, 1)
        Dim restOfNumber As String = Left(strVal, strVal.Length - 1)
        
        If Playerplace > 10 and Playerplace < 20 then
            Formatplace = Trim(strVal & "th")
        else    
            Select Case onesPlace
                Case "1"
                    Formatplace = restOfNumber & "1st"
                Case "2"
                    Formatplace = restOfNumber & "2nd"
                Case "3"
                    Formatplace = restOfNumber & "3rd"
                Case Else
                    Formatplace = Trim(strVal & "th")
            End Select 
        end if                             
    
    End Function
    
    Private Sub RankPlayers(ByRef players As DataTable)
        Dim ranks As String() = New String() {"Goals", "Assists", "Points", "Penalty"}
 
       For Each rank As String In ranks
            Dim sorted As DataRow() = players.Select("", rank & " DESC")
            
            Dim rowNumber As Integer = 1
            Dim rankNumber As Integer = 1
            Dim prev As String = ""
 
            For Each row As DataRow In sorted
                If row.Item(rank).ToString <> prev Then
                    prev = row.Item(rank).ToString
                    rankNumber = rowNumber
                End If
 
                row.Item(rank) = row.Item(rank).ToString & " (" & rankNumber.ToString() & ")"
                rowNumber += 1
            Next
        Next
    End Sub
 
</script>
 
<asp:Content ID="Content1" ContentPlaceHolderID="Mycontent" Runat="Server">
 
<table style="border-collapse: collapse" cellpadding="0" cellspacing = "0" width="600">
    <tr style="height:19; vertical-align:bottom">
        <td style="width: 600" colspan="3" valign="bottom"><asp:Image ID="Image2" ImageUrl="~/images/bevel-top.gif" Width="600" runat="server" /></td></tr>
    <tr style="height: 40; border-bottom: dashed 2px white; background-color: #0054A6">
        <td style="width: 19" rowspan="2" valign="top"><asp:Image ID="Image3" ImageUrl="~/images/bevel-left.gif" Width="19" Height="100%" runat="server" /></td>
        <td>
            <table>
                <tr>
                    <td class="biotitle1" style="width: 281; text-align: left; padding-left: 10px">
                        <asp:Label ID="swimmername" runat="server" ></asp:Label></td>
                    <td class="biotitle2" style="width: 282">
                        <asp:Label ID="swimmergroup" runat="server" ></asp:Label></td>
                </tr>
            </table>
        </td>
        <td style="width: 18" rowspan="2" valign="top"><asp:Image ID="Image4" ImageUrl="~/images/bevel-right.gif" Width="18" Height="100%" runat="server" /></td>
    </tr>
    <tr style="background-color: #0054A6; vertical-align: top">
        <td align="center">
            <table width="540" style="border-collapse: collapse">
                <tr>
                    <td style="width: 135px; vertical-align: middle; text-align: center"><asp:Image ID="Image5" Width="135PX" runat="server" ImageUrl="~/images/no-pic.gif" /></td>
                    <td style="text-align:right; vertical-align: top; height: 100%">
                        <table style="width: 97%; height:165; border-collapse: collapse" cellpadding="0" cellspacing="1">
                            <tr style="height: 30">
                                <td class="biotitle3" colspan="4">
                                    <asp:Label ID="Label3" runat="server" Text="PLAYERBIO"></asp:Label>
                                </td>
                            </tr>
                            <tr style="height: 40">
                                <td class="biotitle4" width="75px">
                                    <asp:Label ID="Label4" runat="server" Text="POSIIION"></asp:Label>
                                </td>
                                <td>
                                    <asp:Label ID="Label5" runat="server" Text="Forward"></asp:Label> 
                                </td>
                                <td class="biotitle4" width="100px">
                                    <asp:Label ID="Label6" runat="server" Text="BIRTH DATE"></asp:Label>
                                </td>
                                <td>
                                    <asp:Label ID="Label16" runat="server" Text="Jan 6, 1967"></asp:Label> 
                                </td>
                            </tr>
                            <tr style="height: 40">
                                <td class="biotitle4" width="75px">
                                    <asp:Label ID="Label17" runat="server" Text="SHOOTS"></asp:Label>
                                </td>
                                <td>
                                    <asp:Label ID="Label18" runat="server" Text="Right"></asp:Label> 
                                </td>
                                <td class="biotitle4" width="100px">
                                    <asp:Label ID="Label19" runat="server" Text="HEIGHT"></asp:Label>
                                </td>
                                <td>
                                    <asp:Label ID="Label20" runat="server" Text="6ft-2in"></asp:Label> 
                                </td>
                            </tr>
                            <tr style="height: 40">
                                <td class="biotitle4" width="75px">
                                    <asp:Label ID="Label21" runat="server" Text="NICKNAME"></asp:Label>
                                </td>
                                <td>
                                    <asp:Label ID="Label22" runat="server" Text="'Top Shelf'"></asp:Label> 
                                </td>
                                <td class="biotitle4" width="100px">
                                    <asp:Label ID="Label23" runat="server" Text="WEIGHT"></asp:Label>
                                </td>
                                <td>
                                    <asp:Label ID="Label24" runat="server" Text="220 lbs"></asp:Label> 
                                </td>
                            </tr>
                        </table>
                    </td>
                </tr>
            </table>
            <p style="margin-top: 10px">
            </p>
            <table width="96%">
                <tr>
                    <td class="biotitle3">
                        <asp:Label ID="Label8" runat="server" Text="2008-2009 STATS"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <td class="biotitle4" align="center">
                    <asp:DataGrid ID="ScoringGrid" GridLines="Both" ItemStyle-BorderColor="#0054A6" ItemStyle-BorderWidth="1" AutoGenerateColumns="false" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="true" HeaderStyle-Font-Names="Arial" HeaderStyle-Font-Size="10pt" HeaderStyle-Font-Underline="true" ItemStyle-HorizontalAlign="Center" runat="server" ItemStyle-Font-Size="10pt" ItemStyle-Font-Names="Arial" >
                        <Columns>
                            <asp:BoundColumn DataField="GamesPlayed" />
                            <asp:BoundColumn DataField="Goals" />
                            <asp:BoundColumn DataField="Assists" />
                            <asp:BoundColumn DataField="Points" />
                            <asp:BoundColumn DataField="Penalty" />
                        </Columns>
                    </asp:DataGrid>  
                    </td>
                </tr>
                <tr>
                    <td class="biotitle3">
                        <asp:Label ID="Label15" runat="server" Text="2007-2008 STATS"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <td class="biotitle4" align="center">
                    <asp:DataGrid ID="PrevScoringGrid" GridLines="Both" ItemStyle-BorderColor="#0054A6" ItemStyle-BorderWidth="1" AutoGenerateColumns="false" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="true" HeaderStyle-Font-Names="Arial" HeaderStyle-Font-Size="10pt" HeaderStyle-Font-Underline="true" ItemStyle-HorizontalAlign="Center" runat="server" ItemStyle-Font-Size="10pt" ItemStyle-Font-Names="Arial" >
                        <Columns>
                            <asp:BoundColumn DataField="GamesPlayed" />
                            <asp:BoundColumn DataField="Goals" />
                            <asp:BoundColumn DataField="Assists" />
                            <asp:BoundColumn DataField="Points" />
                            <asp:BoundColumn DataField="Penalty" />
                        </Columns>
                    </asp:DataGrid>  
                    </td>
                </tr>                
                
            </table>
        </td>
    </tr>
    <tr valign="top"><td style="width: 600" colspan="3"><asp:Image ID="Image1" ImageUrl="~/images/bevel-bottom.gif" Width="600" runat="server" /></td></tr>
</table>
<br />
    <asp:LinkButton ID="LinkButton1" runat="server">View Another Player Stat</asp:LinkButton>
<br /><br />
</asp:Content>

Open in new window

Pratima PharandeCommented:
Try this instade of datareader
 
          Sqlstring = "SELECT P.Playernum as Playernum, SUM(P.GP) As GamesPlayed, SUM(P.G) As Goals, SUM(P.A) As Assists, SUM(P.PTS) As Points, SUM(P.PIM) As Penalty from playerstats P, contacts C, Games G, Leaguesetup L where C.Usernum = P.Playernum and G.Gamedatetime > L.Startdate and L.Leaguenum = P.Leaguenum and G.Gamenum = P.Gamenum and C.Leaguenum = " & Session("Leaguenum") & " Group by P.Playernum"
          Dim cmd1 As SqlCommand = New SqlCommand(Sqlstring, MyConn)

          Dim dsInfo As DataSet

            Dim dacmd As New SqlClient.SqlDataAdapter(cmd1)
         
             dacmd.Fill(dsInfo )
     
          ' Now select only the row that you want (PlayerID)
          Dim GetRow As DataRow() = dsInfo.Tables(0).Select("Playernum = " & PlayerID)
     
            ScoringGrid.DataSource = GetRow
            ScoringGrid.DataBind()
sijishJohnCommented:
try this..

dt.DefaultView.RowFilter = "Playernum = " & PlayerID
ScoringGrid.DataSource =dt.DefaultView
ScoringGrid.DataBind()


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
mkosbieCommented:
When you use an Array as the DataSource for an DataGrid, .NET looks in the first array value for column names.  Because your DataRow array (GetRow) has only the values in it and NO HEADERS, it can't find the appropriate columns.  Basically, that means you can't use a DataRow array as the data source.

I would agree with sijishJohn, use a DataView instead.  Code provided should work.
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.