.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQLCommand parameters for column name

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :ASP.Net


I want to insert column names as parameters into a SELECT statement (for MSSQL 2008): 

string sql = "SELECT @clm1 FROM db.tblUser WHERE aspUserId = @userId";
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlParameter parUser = comm.Parameters.Add("@userId", SqlDbType.NVarChar);
            SqlParameter parColumn= comm.Parameters.Add("@clm1", SqlDbType.NVarChar);
            parUser.Value = aspUserId;
            parColumn.Value = "clm1";
            string str = comm.ExecuteScalar().ToString();

and the ExecuteScalar returns the column name ("clm1") instead of the value.

But if I change the SELECT to "SELECT clm1 FROM db.tblUser WHERE aspUserId = @userId", ExecuteScalar returns the value of the "clm1" column (which is want I want!).

In what way can I insert column names into SQL statements as parameters? The purpose is to build dynamic SELECT commands...



View Complete Post

More Related Resource Links

Filter items with a date column with parameters start date and end date on a sharepoint list?

If I have alist with a view that has the columns Title, Status and Status Date and the view has some items. How Can I add a filter with two parameters, start date and end date, to only show records with status date equal to or greater than parameter start date and status date is less than or equal to parameter end date? This is a sharepoint online site and I can't create and deploy custom code, can use SPD though. Do I need to use SPD or is this something I can do in the list settings? Thanks in advance.   Edit: I had a look here http://www.endusersharepoint.com/2009/09/29/sharepoint-date-filter-filtering-a-list-by-greater-than-or-equal-to-date/ but I don't have the Date Filter web part.

How to pass page column value to data view parameters

Hi All, I have a page library with custom column, lets call it "FormCourseId". On that page I have data view which retrieves data from custom list filtered by page column "FormCourseId". In parameter wizard creates the following tag <ParameterBinding Name="ListCourseId" Location="Form(FormCourseId)" DefaultValue=""/> Then I try to use that parameter in XSLT of data view as a filter creteria.  <xsl:param name="CourseId"></xsl:param> It does not work for me. If I change data view parameter source from Form to Query String and pass parameter as url one it works fine. What is wrong with page column parameter binding? May be if FormCourseId column is not presented in form layout as a field(I do not need to show that field in user form of the page) it could not be treated as form parameter?

ComboBox with Parameters & Column visibility


Dear All,

I have a report with many fields displaying in the report. I am checking the visibility of the columns.

When i am defining a separate parameter of boolean type iam able to hide the column visibility using the following xpression,



Now i have defined a parameter of

Data type = text, allows multiple values

Available values - in 'Specify Value' have specified the resulting column names in the Label and Value fields.

No Default Value.

So the parameter field holds all column names in a combobox with check box aside.

i am trying to hide the columns with the expression in the column visibility properties. but i am not able to use the same xpression, for it is only taking the parameter name in xpression but not the defined combobox values.


kindly help me with the formula..

I need to display the columns that are alone checked in the parameter combobox value.


Thanks !!

How can I pass parameters to my Business Data Column?

I have a custom list in which I have created a  Business Data Column that pulls data from a web service.  I have written the ADF file, it is installed on the server and I can get to the service's fields via the Business Data Type picker. However, and this is the part I can't figure out how to do: the web service requires 2 parameters be passed to it.  These parameters exist in other fields of the list item.  How do I make that association and pass those parameters?  Is this possible?

Also, I have not delved into using Visual Studio yet for SharePoint development, so I only have the UI and Designer as my tools.  I am on MOSS 2007.

Edit - I found this article: http://sharepointmagazine.net/technical/administration/everything-you-need-to-know-about-bdc-part-4-of-8.  Toward the bottom it explains how to do a 1:1 mapping of the list item to, in his example, the database item.  This is similar to what I need to do, except mine is a web service and it requires two parameters to identify it.

WebGrid: column definition - Set 'style:' parameters depending on value


I have a list of objects (List<myObject>) which I pass to a webGrid. I would like to set cells for a column of the table to a specific value depending the object's 'StatusColor' property. Something like

List<myObject> myObjectList = 
WebGrid webGrid = new WebGrid(source: myObjectList, ..);
columns: webGrid.Columns
webGrid.Column("Status", "Status", format: @<text>@item.StatusName</text>, style:



But for some reason, I get "cannot convert from 'lambda expression' to 'string'". When I remove the @<text> suround the item property, I get "the name '@item' does not exists in the current context"

For the 'format:' part, it works fine.

Linq: how to share parameters between lambda expressions ?

When using Linq to objects, you will quickly feel the need to pass some parameters from a method to another but it's not so easy because each Linq method is not calling the following one. In a Linq sequence, each method is using the result computed by the previous one. So, local contexts are not visible from one method to another.
The compiler is using two technical different ways to let parameters go out of a method.

Tooltip for GridView Column Headers (ASP.Net)

I just wanted to add auto tooltips for my GridView headers. There are couple of ways but I don't want to use javascript and wanted to use simple 'title' attribute. It took couple of hours to get the working code supporting sortable columns. I thought it will be very useful for others. Here's the code and logic behind it.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Multiple Column Dropdownlist for the ASP.NET DataGrid

Based on my previous control "Multiple Column DropDownList for ASP.NET", I received many emails asking for the same control to be used in the DataGrid for web applications. Here we go.. This control can be used as the regular MS DropDownList in the DataGrid and also as a regular dropdownlist. It has all the properties, like DataTextField, DataValueField, DataSource, SelectedIndex etc. The download file contains the samples both in VB.NET and C#. In this sample, I have used the Northwind database of SQL Server.

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

view state vs request parameters


If  the view state is the data entered into the form fields then they are supposed to be available in the request parameters or request body. Then why would we need view state for?


Alternating row background for first column of a report


is there a way to make the first column of a report use the an alternating background color? 

I have the background color set for the entire row however it does not work for the first column if the color is set to an expression.  If I just set it to a color i.e. red it works just fine. If I insert a dummy column to the left it works just fine (but the dummy column does not work, presumably because it is first??).

In the file below only the textbox for part number does not show an alternating background.  All others work correctly.

                    <Textbox Name="PartNumber">

Use a column as parameter for a sds in another column; gridview


Hola, I have a gridview which is bound to a sds which returns 7 columns and I added two columns from the GUI.  What I want is to fill up the 9th column with DDLs and these  DDLS will get their data from a different sds which is using a stored procedure with a single parameter.  I created the 9th row, used template field, made the sds for 9th row, the stored_procedure, threw a DDL in Item template and all  I need now is to pass a parameter in the first column to the sds in the 9th column.  I can code this but i'm getting tired of aspx.cs :) and would like to do it from the GUI.

Pass a grid column value to a Modal


Hi, I have a requirement to hide a field called details on a gridview and show it in a jQuery modal dialog when users click on the Customer ID hyperlink. This grid is created programmatically. I will be hiding the details column via the CSS display:none property. Does some one know how can I pass the grid's selected hidden column value to the modal? Here is some of the code I started:

            GridView mygrid = new GridView();
            //.....................................More columns code for the grid

            //Here is where I need to pass the Details column value associated with the customer ID field selected to the Modal and open the Modal dialog.            
            HyperLinkField linkField = new HyperLinkField();
            string[] dataNavigateUrlFields = { "CustomerID" };
            linkField.DataTextField = "CustomerID";

Bug: Does not change column type from nvarchar to ntext


Scenario: If you create a table with a column of type nvarchar, populate it with some content and try to modify it using the WebMatrix editor, from nvarchar to ntext, it does not display any error message or warning that this is not possible. It silently saves and reverts the change, back to nvarchar. Wasted some hours on this issue as I thought my column was ntext, but in fact was still nvarchar.

Expected behavior: It should change to ntext, or if SQL CE doesn't support that, it should notify the user that no change was applied.

Display column from child table. Possible ?



I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

Order List :

Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

Does someone have an easier or better idea ?

Thank you for any help.

Rendering data with HTML tags in the DD Gridview for a selected column


I am having trouble finding out where and how to HTML Encode a cell's data on the Dynamic Data (v4.0) gridview of List.aspx.  As a simple case, suppose I have formatted cell data that is A<br/>B in the DB.    Obviously, I want A stacked on B in the cell.

It seems gridView1.HtmlEncode = true has gone away.

So maybe I'll try to catch it on the RowDataBound event:

protected void GridView1_RowDataBound(Object sender, GridViewRowEventArgs e)

            if (e.Row.RowType == DataControlRowType.DataRow)
                // Html Encode the cells


but this event never fires?

Has anyone figured out how to properly render HTML tags data in the List.aspx's GridView1? 

If I figure this out, then I can add a MetaAttribute called something like [EncodeAsHtml(true)] and be on my way.  Thanks!

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend