Dynamically setting the column name on a SQL query

Can anyone show me or tell if the following is possible.

I have a SQL query as shown below:
SQL = "SELECT revenue_forecast.oct AS 'revenue' ...........
What I need to do is dynamically set the column in this SQL query. So i need to be able to place a variable in the part after the revenue_forecast "." .
Can someone show me how to do this?
Thanks
0
afelicetti
12/1/2003 6:48:29 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

1 Replies
722 Views

Similar Articles

[PageSpeed] 41

It depends on your data source, but most data sources do not allow you to parameterize column names. You have a couple of choices. One is to concatenate the name into the string as in...


SQL = "SELECT revenue_forecast." + monthName + AS 'revenue' FROM..."

However, this potentially opens up a security problem by leaving you open to a SQL Injection attack. So, you'll want to place some validation on your column name variable value to ensure that it is "safe". There are a number of ways to do this. One possibility would be to take a numeric value as input and determine the column name from that. For example...

switch (monthNumber)
{
case 1:
monthName = "Jan";
break;
case 2:
monthName = "Feb";
break;
...
default:
throw;
}

Depending on the type of input you are dealing with, there may be easier ways to derive the column name such as using a DateTime object, etc. Also, this code could be encapsulated in a stored procedure and you could pass the column name as a parameter, which is not vulnerable to a SQL Injection attack.
HTH
Doug Rothaus
douglasr@microsoft.com
This posting is provided AS IS, with no warranty.
0
dougr
12/1/2003 7:13:03 PM
Reply:

Similar Artilces:

SQL DataSource : Object Reference not set to an instance of an object
 Dear friendsI am getting this error from few days (it was not in past)when i add new "sqlDataSource" , I set the Server name, user name and password then i select the Database, when i click on "Test Connection" button, it does not give me any Error and it shows that Test connection Successful, but when i click "OK", It shows the Error box with the error "Object Reference not set to an instance of an object"From few days it is happening, I was seting the datasource without any problem in past.I am using SQL Server 2005Any one can help? aliusmankha...

SQL query returns 'No column name' as a column heading
 hi,I'm trying to execute this query in SQL Server 2005 Express Edition, the problem is in the column heading of the result for the majorName fieldSELECT studentID,fname,mname,lname,familyName,gpa ,(SELECT majorName AS major FROM major WHERE majorID=(SELECT majorID FROM studentmajorWHERE studentID='0000000'))FROM student WHERE studentID='0000000'; Why the result of this query returns 'No column name' as a label for the majorName field? I use the AS clause to allows the column to be named, the column heading of the result table still 'No column name&...

.net + dynamic SQL-query
On our page we have texbox, dropdownlist, database and gridview for it.It DropDownList we have all names of columns from database (in our situation they are Item_Name, Item_Type,  City and Country). In database we have two more columns, but we do not need them in this task.And the task is to choose from DropDownList item/value and deliver it to SQL-query. We have done searching from database, but the column to search from is static, we want to make it dynamic, we want to choose from Dropdownlist column, from which we will search.We are making all in aspx, not in .cs code. We are not fam...

Column Name with Dynamic SQL
Hi, I use DynamicStagingArea for retrieval of a dynamic table. I there any possibility to get the column names for the table? Roland Sattler ...

Set a column name dynamically
I have a TemplateField whose caption I want to set using a method to get the caption based on an Enum.  I tried this:HeaderText='<% =GetCaption(MyEnum.First) %>'but it doesn't work.  GetCaption() is never called.Data binding syntax (ie '<%# %>') won't work because TemplateField is not a databound control. I do not want to do this in the Page_Load event because I don't want to refer to the column by index as the index is prone to change.How can I do this?    Try a GV.Attribute.Add and then set the attribute to MyEnum.Fi...

Need query to get distinct column name and details based on that column using sql server 2000
hi, iam having three tables as shown below table 1                                                      table 2                                     &...

SQL Msg 107 Error... The column prefix does not match with a table name or alias name used in the query.
Can someone please answer a problem that I've run into.  I know that it's probably something stupid.  I keep getting this error:Server: Msg 107, Level 16, State 3, Line 1The column prefix 'vFirstTimeEntered' does not match with a table name or alias name used in the query.Here is my query:-----------------------------------------------------------------Update  TimeSheetSectionSet TimesheetSection.SECSTARTDT = vFirstTimeEntered.schlstuidWhere timesheetsection.schlstuid = vFirstTimeEntered.schlstuid AND timesheetsection.sectionid = vFirstTimeEntered.sectionid AND&...

Dynamic column names in T-SQL?
Does anyone know of a clever hack that can be used to generate column names on the fly, either from local variables or values in a temp table? Robert Gumpertz wrote: > > Does anyone know of a clever hack that can be used to generate column names > on the fly, either from local variables or values in a temp table? For what purpose? I'm not following where you're going with this? (maybe I need some caffeine?) -- Mark A. Parsons Iron Horse, Inc. This is a reporting application where the user can submit a random list of states; the result is a count by categ...

dynamic sql with variable column name
I want to give select list(columan names) as variable to the dynamic sql statement.. is this possible ? sql statement that I wrote below : string Sqlstatement,sqlst1,Stringvar decimal decvar Sqlstatement = 'SELECT ? FROM TABLE_NAME WHERE TABLE_NAME.COLUMN1 ="'+variable1+'" and & TABLE_NAME.COLUMN2 = "'+variable2+'" and & TABLE_NAME.COLUMN3 = "'+variable3+'" and & TABLE_NAME.COLUMN3 = "'+variable3+'"' PREPARE SQLSA FROM :Sqlstatement ; DESCRIBE ...

How to set Column Header Name in Dynamically
 Hi all, In my form has GridView Control .in different time i Bind diffrent dataset to that GridView.therefor I need to set Column Header text of GridView  name of  DataSet Column Names when the data bound event. please tell me how i do this.Thanks,  Like this: protected void GridView1_DataBound(object sender, EventArgs e) { if (GridView1.HeaderRow != null) { GridView1.Columns[1].HeaderText = "My Header"; } }  Steve Wellens My blog  Dear SGWellens,        ...

Dynamic setting of column name in stored procedure
Hello, Is it possible to send field name to stored procedure. eg I want to fetch a single column of same type from the same table. But each time the column name is different. Can I set fieldname(column name) dynamically ? example.... proc 1--- create procedure xyz UID int select XStatus from XYZTable where UID = @UID proc 1--- create procedure xyz UID int select A_Status from XYZTable where UID = @UID I want to send 'select column' dynamically. I don't want to run different stored procedures every time. Is it possible to acomplish this ...

Invalid column name from c# sql query
 Hi I have the following problem. I am trying to get some data from a database which matches the name in a session from a previous page:e.g.        SqlCommand menubar = new SqlCommand("Select pernme from Person where pernme = " + (string)Session["tbname"], sqlConn);            SqlDataAdapter dataAdapter5 = new SqlDataAdapter();            dataAdapter5.SelectCommand = menubar;       &nb...

Dynamically Sql Query Execute in Datatable Object
Hi, I want excute the query in datatable Object like this: String _qryselect="Select * from tablename where condition"; Datatable dt=(Datatable)ViewState["_datatable"]; I want Excute this Query(_qryselect) in Datatable(dt); How can getting result Please Help and tell me this approch is right or wrong? Do in the following way   DataRow() dr = dt.Select("Name = 'MAK'")   here the query will get u all rows in datable where Name is MAKMAKMark as Answer if this reply helps youMVP ASP/ASP.NetASP.Net Hosting : Host DepotMy Site : ASPSnippe...

Showing my own column names instead of database column names in datagridview-windows app. C#.net
HiI already bind a DataGridView to a databse. My DataGridView is showing column names as it is in the table. I want to show user-friendly column names in the grid instead of column names in the database table. Please help me in this reagard. Thanx in advance Your statement indicates to me that your columns are dynamically added to the grid, that's why it's showing the database column names. You can show user-friendly column names by changing your query like so: SELECT fName as 'First Name' FROM Users.DarmarkMark as Answer, if this reply answers your post. Go to "E...

Web resources about - Dynamically setting the column name on a SQL query - asp.net.object-datasource

Placebase team at Apple file “Schematic Maps” patent dynamically detailing important data
In a report from Computerworld way back in 2009, we learned that Apple had quietly acquired Maps API company Placebase. Then, founder of Placebase ...

Apple's Lightning port dynamically assigns pins to allow for reversible use
... a closer look at how Apple's new Lightning connector works, and has come to the conclusion that the 8 pins on each side of the plug are dynamically ...

In a dynamically typed language, is it a bad idea to return different data types?
In some cases, returning different types from a single function works.

Urlbox Launches Screenshots-as-a-Service Platform to Dynamically Update Galleries
... changes. Launched yesterday, Urlbox allows anyone with a gallery displaying website screenshots to automate the process and keep them dynamically ...

Everything.me launcher dynamically adapts to what you’re doing right now
... phone according to what you’re doing. Perform a search for ‘football’ and your background will change to the NFL logo. A launcher that dynamically ...


A YouTube Experiment Resizes the Player Dynamically
There's a new YouTube experiment that changes the video player's size, depending on the browser window's size. When resizing Chrome's window, ...

Apple Granted “Dynamically Changing Cursor for User Interface” Patent, Originally Filed in 2003
... a utility patent that covers the use of the mouse cursor that changes according to the context of the task it is engaged in. Called a “Dynamically ...

Atmel's FPSLIC II Dynamically Reconfigurable SoC Supports "Silicon-Sharing" For Peripherals & Interfaces ...
Silicon-Sharing Fits 200K Gate Design into 40K Gate FPSLIC II's, Cuts Power Drain by 97%

Everything.me Launcher Wants to Dynamically Change Phones Based on Location, Interests, and Mental State ...
What if your phone’s launcher dynamically changed based upon interests, your location, or topics you had in mind at a specific moment? A new ...

Resources last updated: 12/1/2015 4:23:50 PM