Variable selection of columns in a sql query

Question

In ASP.NET, can a 'select-query' be dynamic as follows ? This throws an error, how can this be achieved?

SelectCommand="SELECT TOP 30 [date],**[@tc]** FROM [tbl_TC] WHERE ([ID] = ?) ORDER BY [date]">
<SelectParameters>
**<asp:QueryStringParameter Name="@tc" QueryStringField="tc" />**
<asp:QueryStringParameter Name="ID" QueryStringField="pid" Type="String" />
</SelectParameters>

D Stanley · Accepted Answer · 2014-07-02 21:34:26Z

1

You can't so that in SQL, let alone ASP.NET. You'll have to generate the SQL by inserting the column name into the string.

I don't know of a way to do that declaratively - you're probably going to have to do the binding in code-behind and build the SQL dynaimcally:

string SQL = "SELECT TOP 30 [date], [" + tc + "] FROM [tbl_TC] WHERE ([ID] = ?) ORDER BY [date]";

answered Jul 2, 2014 at 21:34

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Twelfth Over a year ago

sounds right, but the [] around [" + tc + "] will likely make the database engine take that to mean one column, even if it's a list of columns. removing the [] from the string here and including them in the tc variable should work

D Stanley Over a year ago

@Twelfth I included the brackets because the code in the question had them, which makes me think that the intent is to only have one column.

user3715501 Over a year ago

Thank you guys. I was trying to get it done on ASP side which is not possible , so i have to dynamically select columns in the code (vb)

GJKH · Accepted Answer · 2014-07-02 21:41:09Z

0

Use a command object and replace the placeholder text with the column names.

Dim sqlConnection1 As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand
Dim reader As SqlDataReader

Dim qryString = "SELECT TOP 30 [date],**[@tc]** FROM [tbl_TC] WHERE ([ID] = [?]) ORDER BY [date]"

qryString = qryString.Replace("**[@tc]**", "yourcolumnnameshere").replace("[?]", "datequerystringhere")

cmd.CommandText = qryString
cmd.CommandType = CommandType.Text
cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()
' Data is accessible through the DataReader object here.

sqlConnection1.Close()

MSDN Ref: http://msdn.microsoft.com/en-us/library/fksx3b4f.aspx

answered Jul 2, 2014 at 21:41

GJKH

1,73513 silver badges33 bronze badges

1 Comment

user3715501 Over a year ago

Thanks @GJKH for the complete solution, however i feel its more straightforward to append the query string with a variable as suggested by Stanley.

Collectives™ on Stack Overflow

Variable selection of columns in a sql query

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related