[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error Extracting Datarow from a Datatable using Datatable.Select

Posted on 2008-11-03
7
Medium Priority
?
2,679 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:pbissegger
7 Comments
 
LVL 4

Expert Comment

by:sijishJohn
ID: 22873759
you cannot set datarow as datasource.....

extract your datarow and put it into another datatable ...then set the new datatable as the datasource...
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22873762
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
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22873770
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
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:pbissegger
ID: 22873863
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

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22873912
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()
0
 
LVL 4

Accepted Solution

by:
sijishJohn earned 1800 total points
ID: 22873950
try this..

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


0
 
LVL 8

Assisted Solution

by:mkosbie
mkosbie earned 200 total points
ID: 22874091
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

834 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