Concatenating Variables To Create A Column Name

I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.

I have 2 database table3 as follows:
eg
1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4] , WeeksSinceDr0, WeeksSinceDr1, WeeksSinceDr2
2) myGameFrequency which contains columns : AllBallsFrequency , WeeksSinceDrawnAllBalls

Using the myGameRecencyAllBalls table ---
I wish to insert a 0 into a column corresponding to a ball that has been drawn, eg if a 4 has been drawn, then a 0 will be inserted into that column. If a ball has not be drawn, then the value in that column will be a running total, signifying the number of draws since it was last drawn ( ie since a 0 was inserted into that column).

I place a 1 in the column corresponding to the number of weeks since a number has been drawn. The name of the column is therefore the concatenation of the string literal 'WeeksSinceDrawn' and the value held by the variable, @lastRowCount obtained by the lastrow_CURSOR.

I have declared a variable @colName to hold the concatenation / Set @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) and then tried to use it as follows: SET [@colName] = 1

however, I receive an error message advising me that I have an invalid column name. Is there any means of setting a column name by concatenating two variables or , a string literal and a variable ?

Thank you
lpbcorp


sqlCmd.CommandText = "DECLARE @colName nvarchar(max) " +

"DECLARE @lastRowCount int " +

"DECLARE lastrow_cursor CURSOR SCROLL FOR " +

"(SELECT [" + i.ToString() + "] FROM " + DBGameName.ToString() + "RecencyAllBalls) " +

"OPEN lastrow_cursor " +

"FETCH LAST FROM lastrow_cursor INTO @lastRowCount " +

"SET @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) " +

"IF @lastRowCount <= 175 " +

"BEGIN UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET [@colName] = 1 WHERE Date = '" + Date + "' " +

"END " +

"ELSE " +

"UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET WeeksSinceDrOver175 = 1 WHERE Date = '" + Date + "' " +

"CLOSE lastrow_cursor " +

"DEALLOCATE lastrow_cursor";

sqlCmd.ExecuteScalar();