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

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

how to pass column value in WHERE clause

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Friends, my scanario is fully generic and i m implementing this in database level by your quick response and help... i m trying to demostrate my problem with some sample table and SP (please see comment in SP for more clarification and understanding) the question is which i also write in SP's bottom area, Question, HOW CAN I PASS VALUE FOR COLUMNS "WF_Company_ID" AND "WF_Employee_ID" FROM TABLE ##SAT IN ABOVE STATEMENT AND GET VALUE FOR COLUMN "UID" FROM TABLE "MAINTABLE Tables are, CREATE TABLE MAINTABLE(UID INT, WF_Company_ID INT, WF_Employee_ID INT, WF_Position VARCHAR(50), WF_TimeSheet_ID INT) INSERT INTO MAINTABLE VALUES(1, 100, 625, 'Clerk', 1234) INSERT INTO MAINTABLE VALUES(2, 101, 626, 'Clerk', 1235) --SELECT * FROM MAINTABLE CREATE TABLE SEARCHCOLUMNS(C_KEY INT, C_NAME VARCHAR(100)) INSERT INTO SEARCHCOLUMNS VALUES(1, 'WF_Company_ID') INSERT INTO SEARCHCOLUMNS VALUES(1, 'WF_Employee_ID') INSERT INTO SEARCHCOLUMNS VALUES(2, 'TOTAL_PRICE') INSERT INTO SEARCHCOLUMNS VALUES(2, 'TOTAL_AMOUNT') --SELECT * FROM SEARCHCOLUMNS SP is below, CREATE PROCEDURE [dbo].[Test2] @StrXML XML AS BEGIN TRY DECLARE @ColList NVARCHAR(MAX) DECLARE @SQL NVARCHAR(MAX) CREATE TABLE #tXML (InputXML XML) INSERT INTO #tXML (InputXML) SELECT @StrXML SET @ColList = STUFF ( (

View Complete Post

More Related Resource Links

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

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

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?

can an alias column be used in a WHERE clause

Hi, I am using Alias to a return values that i get back form my function (money datatype). Once done with that, i need ot filter my final result set based on the Alias column. My question is can i use an Aliased column in a where Clause?   SELECT BSC.TypeId,BSC.ParentId,BSC.Id,BSC.Name,(SELECT dbo.UdfSumActualsByPeroid('2010-05-02','2010-05-09',BSC.Id,NULL,NULL,NULL)) AS Actual1,NULL FROM Subscriber.Budget.SubscriberCategory BSC WHERE BSC.SubscriberId = '0CCECB4E-C151-42BF-8F47-FD36ED3D2955' AND Actual1 > 0 ORDER BY BSC.TypeId,BSC.InterviewSequence

Pass the entire where-clause of a SQL statement through the URL?

I need to pass a ridiculous number of where-clause parameters to a SQLquery. I'd like to build a long where-clause on the client side andpass it to the web form through the URL. Is possible to pass the entire where-clause of a SQL statement to anasp.net SelectCommand instead of multiple individual field parameter?if yes, now? ---------- working------------------------------------------------------- http://localhost:1532/Default.aspx?myage=99 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabase.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [tblMain] WHERE ([age] <= @age)"> <SelectParameters> <asp:QueryStringParameter DefaultValue="0" Name="age" QueryStringField="myage" Type="String" /> </SelectParameters> </asp:SqlDataSource> ----------end working-------------------------------------------   ----------start not-working------------------------------------- http://localhost:1532/Default.aspx?para='(age<99)' <asp:SqlDataSource ID="SqlDataSource1" runat="server" Conne

How to pass string values to parameterized sql query in Clause?

Hi,I'm using parameterized sql query to get data from database string query = "Select * from employee where employee_city in (@value)";strign city ="'NewDelhi','Bangalore','Mumbai'";I'm using following code to achive thisDataSet ds = new DataSet();SqlConnection con = new SqlConnection("Server=localhost;....");SqlCommand cmd = new SqlCommand();cmd.CommandText =query;SqlParameter param = cmd.Parameters.Add("@value",SqlDbType.VarChar);param.Value = city;SqlDataAdapter dap = new SqlDataAdapter();dap.SelectCommand = cmd;dap.Fill(ds);But this is not giving the result.If run the query in SQLServer query window as "Select * from employee where employee_city in ('NewDelhi','Bangalore','Mumbai')", records are there.But the same query will not return any records from ADO.Net.How to solve this?Thanks,Ashokan

Column Alias in ORDER BY Clause -- not supported in SQL Server 2005/2008 -- Causes enter migration



We are migrating from SQL Server 2000 to SQL Server 2008 R2 and our web application having thousands of SQL Statement with "Column Alias in ORDER BY Clause ".It is very difficulty to change all statements.

One of article says that SQL Server 2005/SQL Server 2008 not support "Column Alias in ORDER BY Clause and we have to replace with column name" because of this issue If we change Alias name to column name, we have do enter application/integrity testing. it is going to be big deal.

our client Decided to stop migration, Please some help is there any other alternative.



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.

Expressions (with Where clause?) to Build Calculated Column


Hi All-  I thought perhaps what I wanted to do was simple, but I can't figure it out, so I'm hoping someone will give me some tips.  I have a dataset coming in with multiple years of data for employment figures.  I have the year and the geography set as parameters.  the user chooses the year and the state (or multiple years and states) and can see some statistical data.  One of the columns displayed is Employment (or # of Jobs).  What I am attempting to do is add a calculated column that will show me the 1 year pct change in Jobs.  If the user selects 2009, then the report would dynamically create the change.  I thought perhaps I could create an expression for that column that looks something like below

=(sum(Fields!empl.Value) where Fields!year.Value = @year - sum(Fields!empl.Value) where Fields!year.Value = @year - 1) / sum(Fields!empl.Value) where Fields!year.Value = @year - 1

I am relatively new to SSRS - so I apologize in advance if I haven't given you the appropriate information that will allow you to help with this inquiry, but any advice that you can give me would be GREATLY appreciated.  This type of calculation is something that we want to do often.  I know that I can add a 1 year % change to my dataset and pull it through - however, I want the change to be dynami

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.

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.

Power of using Clause in SharePoint

The Using Clause will help to avoid memory leaks in SharePoint by automatically Disposing the MOSS objects.

For example when we create SPSite and SPWeb objects, if we don't dispose it explicitly it might create memory leaks,

How to Pass Values Between Web Pages ASP.NET

You can pass information between pages in various ways, some of which depend on how the redirection occurs. The following options are available even if the source page is in a different ASP.NET Web application from the target page, or if the source page is not an ASP.NET Web page:

Use a query string.

Get HTTP POST information from the source page.

The following options are available only when the source and target pages are in the same ASP.NET Web application.

Use session state.

Create public properties in the source page and access the property values in the target page.

Get control information in the target page from controls in the source page.

How to Pass Values Between ASP.NET Web Pages

You can pass information between pages in various ways, some of which depend on how the redirection occurs. The following options are available even if the source page is in a different ASP.NET Web application from the target page, or if the source page is not an ASP.NET Web page:

Use a query string.

Get HTTP POST information from the source page.

The following options are available only when the source and target pages are in the same ASP.NET Web application.

Use session state.

Create public properties in the source page and access the property values in the target page.

Get control information in the target page from controls in the source page.

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 value from textbox to action, formcollection best way here?


Hi I'm building a e-commerce store and on quantity of product I'm going with a textbox. I thought I would ask how would you do this? a viewmodel to use a strongly typed textbox or just get the value of the textbox from the formscollection passed in to the action?

Thanks for any input

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