user2993767 user2993767 - 6 months ago 10
SQL Question

I need to insert into 1 of 4 possible columns based off where the variable is

So I have a fantasy draft like system where the user can draft 4 players, I need to send scores based off stats. In the page where I enter the stats and output them I realized I can not insert to one column because the column can change based off of what order that player was drafted. For example, player A can be in the database under the columns

draftedPlayer1
,
draftedPlayer2
,
draftedPlayer3
, or
draftedPlayer4
. This is the SQL string I wrote to see if it would work, but its not working. If anyone knows whats wrong with my string or a better solution that would be great. For reference, Selectedplayer is the player that the stats will be going to and totalFP are the fantasy points that player generated.

string SQLtotal1 =
"INSERT INTO Drafted_Table WHEN draftedPlayer1 = '"+selectedPlayer1
+"' THEN player1FP ='"+totalscore+ "' WHEN draftedPlayer2 = '"
+ selectedPlayer1 + "' THEN player2FP ='" + totalscore
+ "' WHEN draftedPlayer3 = '" + selectedPlayer1 + "' THEN player3FP ='"
+ totalscore + "' WHEN draftedPlayer4 = '" + selectedPlayer1
+ "' THEN player4FP ='" + totalscore + "'";

Answer

Its probably better to do the logic check earlier in the code like this. Declare two variables for columnNames

var playerPosition;
var scorePosition;

use simple c# if else statement to assign

columnNames to draftPlayer1 or 2 or 3 or 4 and then to player1FP or 2 or 3 or 4 based on value of selectedPlayer1

Then use your insert statement like this...

string SQLtotal1 = "INSERT INTO Drafted_Table(" + playerPosition + "," + scorePosition+") values('"+selectedPlayer1+"'," + totalScore + "')";
Comments