SQLDataReader does not show value of column with double colon in field name

A Select query works fine when I enter it in SQL Server Management Studio on SQL Server:

SELECT [00:00] 
FROM MY_TABLE 
WHERE whatevercondition ...

The column name is “00:00” – it has a double colon. When I try to get the value from this column in a VB.net function with the help of a SqlDataReader, the result is always an error:

Column does not exist

I have tried to put the column name in all variations of brackets, quotation marks, without brackets at all etc. I still can not retrieve the value in the table.

My VB.NET code:

Dim Counter_Adapter As New SqlDataAdapter
Dim Counter_Table As New DataTable
Dim Counter_Reader As SqlDataReader
Dim SQLTemp As String
Dim TheField as String
Dim result as String

TheField = "[00:00]"

SQLTemp = "SELECT " & TheField & " FROM MY_TABLE WHERE whatevercondition ..."

Data_CMD_Counter = New SqlCommand(SQLTemp, MyDatabaseConnection)
Counter_Adapter.SelectCommand = Core.Data_CMD_Counter
Counter_Adapter.Fill(Counter_Table)
Counter_Reader = Core.Data_CMD_Counter.ExecuteReader

If Counter_Reader.HasRows = True Then
    result = Counter_Table.Rows(0)(TheField).ToString() 'This line causes the error: Column "[00:00]" not included in the table.

Else
End If

How do have to quote/escape the column name correctly so that it works in a SqlDataReader?

Answer

This:

TheField = "[00:00]"
SQLTemp = "SELECT " & TheField & " FROM MY_TABLE WHERE whatevercondition ..."

should be

TheField = "00:00"
SQLTemp = "SELECT [" & TheField.Replace("]", "]]") & "] FROM MY_TABLE WHERE whatevercondition ..."

The name is 00:00. Not [00:00]. This is a delimited identifier and the [ and ] are only present in TSQL, and enable the parser to tell where the name begins and ends when the name doesn’t follow the rules for regular identifiers.

Advertisement

Answer