Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    7

    Unanswered: Value cannot be null. Parameter name: dataSet (was "SQL help :(")

    I keep getting "Value cannot be null. Parameter name: dataSet" when I run this statement and bind it to a dataset using a sqldataadapter in ASP.NET using VB.NET.


    Code:
    "SELECT playerstats.playerid, playerstats.gameid, SUM(playerstats.fta), SUM(playerstats.ftm), SUM(playerstats.tpm), SUM(playerstats.rb), SUM(playerstats.fga), SUM(playerstats.fgm), SUM(playerstats.tp), SUM(playerstats.st), SUM(playerstats.a), 100(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp, player.playerid, player.lname + ', ' + player.fname AS fullname FROM playerstats, player WHERE playerstats.playerid = player.playerid AND player.leagueid = " & ddlLeague.SelectedValue & " ORDER BY " & strSortField

    I know there is data in the db.

    Please help

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is your query, reformatted so that it is readable/debugable:
    Code:
    "SELECT	playerstats.playerid,
    	playerstats.gameid,
    	SUM(playerstats.fta),
    	SUM(playerstats.ftm),
    	SUM(playerstats.tpm),
    	SUM(playerstats.rb),
    	SUM(playerstats.fga),
    	SUM(playerstats.fgm),
    	SUM(playerstats.tp),
    	SUM(playerstats.st),
    	SUM(playerstats.a),
    	100(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp,
    	player.playerid,
    	player.lname + ', ' + player.fname AS fullname
    FROM	playerstats,
    	player
    WHERE	playerstats.playerid = player.playerid
    	AND player.leagueid = " & ddlLeague.SelectedValue & "
    ORDER BY " & strSortField

    There are several problems.
    1) You are aggregating data (the SUM functions), so you must GROUP BY all the non-aggregated fields. You have no GROUP BY clause at all.

    2) What kind of field is LeagueID? If it is non-numeric, then you will need to enclose it in single quotes.

    3) You should convert your query relationship to a JOIN, instead of linking tables in the WHERE clause.

    4) You have no alias names for your aggregate fields.

    5) You have no multiplication operator after 100.

    Try this:

    Code:
    SELECT	playerstats.playerid,
    	playerstats.gameid,
    	SUM(playerstats.fta) SUMfta,
    	SUM(playerstats.ftm) SUMftm,
    	SUM(playerstats.tpm) SUMtpm,
    	SUM(playerstats.rb) SUMrb,
    	SUM(playerstats.fga) SUMfga,
    	SUM(playerstats.fgm) SUMfgm,
    	SUM(playerstats.tp) SUMtp,
    	SUM(playerstats.st) SUMst,
    	SUM(playerstats.a) SUMa,
    	100*(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp,
    	player.playerid,
    	player.lname + ', ' + player.fname AS fullname
    FROM	playerstats
    	inner join player on playerstats.playerid = player.playerid
    WHERE	player.leagueid = " & ddlLeague.SelectedValue & "
    GROUP BY playerstats.playerid,
    	playerstats.gameid,
    	player.playerid,
    	player.lname + ', ' + player.fname
    ORDER BY " & strSortField
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2005
    Posts
    7
    Thank you so much for helping me!!!

    Now I do not get an error, I just get an entery for every record, and I only want one per player.playerid.

    Here is the code.

    Code:
        Sub BindDataLeagues(Optional ByVal strSortField As String = "SUMtp")
    
            Dim Conn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim da As SqlDataAdapter = New SqlDataAdapter("
    SELECT playerstats.playerid, playerstats.gameid, 
    SUM(playerstats.fta) SUMfta, 
    SUM(playerstats.ftm) SUMftm, 
    SUM(playerstats.tpm) SUMtpm, 
    SUM(playerstats.rb) SUMrb, 
    SUM(playerstats.fga) SUMfga, 
    SUM(playerstats.fgm) SUMfgm, 
    SUM(playerstats.tp) SUMtp, 
    SUM(playerstats.st) SUMst, 
    SUM(playerstats.a) SUMa, 
    100*(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp, 
    player.playerid, 
    player.lname + ', ' + player.fname AS fullname 
    FROM playerstats INNER JOIN player on playerstats.playerid = player.playerid WHERE player.leagueid = " & ddlLeague.SelectedValue & " 
    GROUP BY playerstats.playerid, 
                  playerstats.gameid, 
                  player.playerid, 
                  player.lname + ', ' + player.fname 
    ORDER BY " & strSortField & " DESC", Conn)
    
            Dim ds As DataSet = New DataSet
    
            Conn.Open()
    
            da.Fill(ds)
    
            dgdPlayerStats.DataSource = ds
            dgdPlayerStats.DataBind()
    
            Conn.Close()
        End Sub
    Also this is always displaying as "0".

    Code:
    100*(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp
    Last edited by tnichols; 02-12-05 at 23:25.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One record per PlayerID? And if a PlayerID has more than one associated GameID, which one would you want to return?

    For your calculation, try using this:

    100.0*(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp,

    Also, I don't see the point of returning and grouping by both playerstats.playerid and player.playerid. They are the same value...
    Last edited by blindman; 02-13-05 at 11:44.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2005
    Posts
    7
    Blindman thanks for your help.

    Beleive it or not I actually fixed my own problem... the only thing that I can't get right now is the fgp aggregate... Here is the current code:

    Code:
        Sub BindDataLeagues(Optional ByVal strSortField As String = "SUMtp")
    
            Dim Conn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim da As SqlDataAdapter = New SqlDataAdapter("
    SELECT playerstats.playerid, 
    SUM(playerstats.fta) SUMfta, 
    SUM(playerstats.ftm) SUMftm, 
    SUM(playerstats.tpm) SUMtpm, 
    SUM(playerstats.rb) SUMrb, 
    SUM(playerstats.fga) SUMfga, 
    SUM(playerstats.fgm) SUMfgm, 
    SUM(playerstats.tp) SUMtp, 
    SUM(playerstats.st) SUMst, 
    SUM(playerstats.a) SUMa, 
    100.0*(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp, 
    player.lname + ', ' + player.fname AS fullname 
    FROM playerstats INNER JOIN player ON playerstats.playerid = player.playerid WHERE player.leagueid = " & ddlLeague.SelectedValue & " 
    GROUP BY playerstats.playerid, player.lname + ', ' + player.fname 
    ORDER BY " & strSortField & " DESC", Conn)
            Dim ds As DataSet = New DataSet
    
            Conn.Open()
    
            da.Fill(ds)
    
            dgdPlayerStats.DataSource = ds
            dgdPlayerStats.DataBind()
    
            Conn.Close()
        End Sub
    Got any ideas?
    Last edited by tnichols; 02-13-05 at 21:15.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here's an idea:

    100.00 * (cast(SUM(playerstats.fgm) as Decimal(8,2))/cast(SUM(playerstats.fga) as Decimal(8,2))) AS fgp,
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2005
    Posts
    7
    You are awesome!!!

    Here is the finished product...

    Code:
    (cast(100.00 * (cast(SUM(playerstats.fgm) as Decimal(8,2))/cast(SUM(playerstats.fga) as Decimal(8,2))) as decimal(8,1))) AS fgp
    Thanks for all your help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •