How do you creating SQL field names dynamically

[mouseChaser]

I have a table with fields player1Name,player2Name..etc. How does one create the field names dynamically. I tried this..doesn't work obviously.

Dim sql As String = "SELECT * FROM myTable WHERE tableName = '" + thisTableName + "'"
Dim cmd As SqlCommand = New SqlCommand(sql, thisConnection)

Dim dr As SqlDataReader
dr = cmd.ExecuteReader

While dr.Read()
For i As Integer = 1 To 7
tablePlayerNames(i) = "dr!player" + Str(i) + "Name"
Next i
End While

Thanks

[kareninstructor]

What is the error message when you try executing the command statement?

[mouseChaser]

I don't get an error but the line player!Name = "dr!player"+str(i)+"Name" doesn't work.
Thanks

[mouseChaser]

sorry line is

tablePlayerNames(i) = "dr!player" + Str(i) + "Name"

[kareninstructor]

sorry line is

tablePlayerNames(i) = "dr!player" + Str(i) + "Name"

I do not have VS open but it would seem you want this, assuming you want the value of dr!player and not what you have in the quotes.

tablePlayerNames(i) = CStr(dr!player) & Str(i) & "Name"

[techgnome]

@Kevin - that's not what the OP is looking to do... what the OP is trying to do is dynamically access the field by name.... player1 player2 player3 etc.... but they are going about it the wrong way.

@Mouse - Maybe you should read up on the data reader and how it works and what it can do.
http://msdn.microsoft.com/en-us/libr...atareader.aspx
http://msdn.microsoft.com/en-us/libr...r_members.aspx

Because then you would know there is a GetString method that you can pass the index of the column to and get the value back.
http://msdn.microsoft.com/en-us/libr...sqlstring.aspx

tablePlayerNames(i) = dr.GetString(i)

There is also an Item collection, where the field name is the key.
http://msdn.microsoft.com/en-us/library/f01t4cfy.aspx

tablePlayerNames(i) = dr.Item(i).ToString 
' -or-
tablePlayerNames(i) = dr.Item("Player" & i.ToString).ToString

-tg

[mouseChaser]

techgnome thanks so much. This worked just wonderfully

tablePlayerNames(i) = dr.Item("player" & i.ToString & "Name").ToString

mouseChaser