CODEWITHSUNDEEP
asp.netsqlms-access
user3715501
user3715501

Reputation: 3

Variable selection of columns in a sql query

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>

Upvotes: 0

Views: 110

Answers (2)

GJKH
GJKH

Reputation: 1725

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

Upvotes: 0

D Stanley
D Stanley

Reputation: 152576

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]";

Upvotes: 1

Related Questions