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

Top 5 Contributors of the Month
Post New Web Links

How to reference 2 columns in a query

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :ASP.Net
Hi All I have 4 tables First Table Structure Id           AccountNum 1                Meals 2                Overtime 3                Wage SSAmount Id     SiteNbr     Meals            Overtime           Wage 1       145              100                 500                   1000 GGAmount Id     SiteNbr     Meals            Overtime           Wage 1       145              100                

View Complete Post

More Related Resource Links

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

Query Sharepoint list, rich text columns returning HTML tags

Hi! So I have an issue that maybe someone of you have encountered as well. I am querying a Sharepoint list and creating a report from the data collected from this list. This all works fine except that one of the columns in this list is of the type "Multiple lines of text" and also supports "rich text" with different fonts, sizes and so on. This has the effect that the text returned from this column is wrapped in HTML tags that specifies how the text should be formatted. Naturally I do not want this HTML code to appear in my report so for now I have used custom code to do a string.replace and replaced for example <div></div> and <br> with "". However, this column also has url references to documents and these links shows up like this "/servername/site/subsite/Gemensamma%20dokument/Mwh.docx">http://servername/site/subsite/Gemensamma%20dokument/Mwh.docx There is some sort of double reference to the document that I guess like the HTML tags are embedded in the answer from the rich text column and this is not so easy to do a string replace on because the url varies with the different document and document names. My question really is if anyone of you have done a similar report that I am doing, and have encountered this fenomen with this sharepoint column and how you did to solve this? I have tried to creat

application role how query a view whose reference table is in another db?

Hi everybody. I created  an application role in a database (DB1) and gave it all the rights on a view  in DB1 which refers to a table located in another db (DB2).  I also gave the rights to the app role on a table of  DB1 I tried to use this app. role through the sp_setapprole launched by a user  (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME() I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:   The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work? The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated.   Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania

Content Query Web Part Styling and Columns with Underscores



I'm having a problem with using a column which has an underscore in a custom styled Content Query Web Part. To keep it simple the column name is Mobile_Content, with an underscore. This is causing the issue as columns without underscore work fine. My CommonView Fields property looks like this in the .webpart file:



property name="CommonViewFields"

Access Services - The query has too many output columns.


Trying to port a database from Access (2000 or older! via an intermediate upgrade to 2010) to use Access Services on Sharepoint 2010.  The main data entry table contains 41 columns of data and I'm getting this error when trying to load it as a web form: "The query has too many output columns. The maximum number of output columns is 40."

I take it the database needs re-designing to split some of the data off into separate tables to be accessed on-demand?  Or is there a setting inside Sharepoint I can tweak to get the form to display?

Trouble working examples of Cumulative columns in SQL Query


I can run the following query without the built in query in the select statement and it works fine.  All i am trying to do is to create a cumulative total for each day for a specific date range.  Any ideas?


SELECT OrderHed.OrderDate,

     (SELECT SUM(OD.OrderQty * OD.ListPrice)

        FROM   MFGSYS.PUB.OrderDtl OD INNER JOIN MFGSYS.PUB.OrderHed OH ON (OD.Company=OH.Company) AND (OD.OrderNum=OH.OrderNum)

        WHERE OH.orderdate<=OrderHed.OrderDate)

 FROM   MFGSYS.PUB.OrderDtl OrderDtl INNER JOIN MFGSYS.PUB.OrderHed OrderHed ON (OrderDtl.Company=OrderHed.Company)

    AND (OrderDtl.OrderNum=OrderHed.OrderNum)

 WHERE  (OrderHed.OrderDate>={d '2010-10-01'}

    AND OrderHed.OrderDate<={d '2010-10-31'})

    AND OrderDtl.Company='HEAPG'

    AND OrderDtl.RMANum<1

 GROUP BY OrderHed.OrderDate

 ORDER BY OrderHed.OrderDate

How can I query blob columns from Paradox database via ODBC?



I want to read blob columns from Paradox database using c#. Here is my code:

  DataSet dsUsers = new DataSet();

  string strQuery = "SELECT blobColumn FROM users";

  dsUsers = GetNames(strQuery, "Driver={Microsoft Paradox Driver (*.db )}; Default Dir=<directory>;DBQ=<directory>");

 private DataSet GetNames(String strQuery, String strConnect)


  DataSet dsNames = new DataSet();



  OdbcConnection conOdbc = new OdbcConnection(strConnect);

  OdbcCommand cmdOdbc = new OdbcCommand(strQuery, conOdbc);

  OdbcDataAdapter odaOdbc = new OdbcDataAdapter(cmdOdbc);





  catch (OdbcException eExc)



  catch (Exception eExc)



  return dsNames;


Note that the Paradox driver version is 4.0.

The code failed to query the blob column.  The error is:

Error code: -2146232009
 Error msg: ERROR [07002] [Microsoft][ODBC Paradox Driver] Too few parameters. Expected 1.
 Source: odbcjt32.dll

Please note that I can query non-blob columns of the table with the same code.

Can a single Parameter be used for multiple columns in the query.


Hi all,

I was trying to create a report with a slight different kind of requirement,

Actually I have 5 Columns in a table from which I want the user to be capable to view the data based on any combination for those columns.

For Example:

I have a table Employee with attributes: Name / Age / Salary / STATE

Now I want the user to be capable of selecting any combination for these columns and then running the report, Actually the report will behave like a Select Query with Where clause with AND conditions for all columns.

Report design should be like this:

FIRST prompt:   Select Column name  ex: AGE

SECOND prompt:   Select Column Value ex: 30

(now store this value somewhere and allow the user to again select a different column name like STATE, and the SECOND prompt will show all distinct STATEvalues, then user selects for ex: NEW YORK

Then when USER clicks VIEW REPORT:

Show all the employees where AGE = 30 and STATE = NEW YORK.

Is it possible ? as I dont want to create 10 prompts for 5 columns separately.

Any help is appreciated.



Reference query values from custom code


How can I reference query values from custom code?

Normally in the expression I can use Fields!QueryValueX.Value

In custom code I can reference textboxes, for example - if ReportItems are passed as the parameter

But this doesnt include the query items

Javier Guillen

Query with a question Mark reference in .NET


Ok... I know this is likely easy and it should be.  Foot in mouth 

I have a query in Microsoft Access 2003 or 07'.  Like the following:  Let's assume this query is named 'GetDudesStuff'

Here is the Raw SQL of the Query in Access: SELECT avail.MLS_CODE, avail.AV_DESCAD, CLASS.CL_DESC, CLASS.CL_DESCSUB, customer.CP_NAME, customer.CP_EMAIL, MFG.MF_NAME, avail.AV_DESCLN, avail.AV_UED, avail.AV_MACLASS, avail.AV_MATYPE
WHERE (((avail.MLS_CODE)=[?]))

In .NET how do I call this Query and add in the unnamed query?

I tried the AccessDataSource class, or just the OleDBDataSource/OleDBCommand with adding a parameter.  But it no worky...
(VB or C# me no care I can translate)

Need a search query based on Columns


I have webform having these columns/fields WoNo,partNO,Customer,DDueDate,PDuedate,TMSDuedate,QADueDate,qty field  and a button called WorCenters.when i click on this button all the values should be listed based on these inputs DDueDate,PDuedate,TMSDuedate,QADueDate(4 columns are called as Worcenters).for example user wants to list all the WoNo(work orderNumber),customer under a particular work center,how can i do this,i dont have any column on WorkCenter either on webform or in database.Can any one help me how to acheive this


Linq Query with dynamic where clause i.e filter with dynamic no of filter columns



I want a linq query with dynamic where clause.

Following is explanation of problem:


Following line returns me n no of rows.

IEnumerable<XElement>  xeleRows = (from xele in xdocFile.Root.Elements(strCtlLookUpXMLNode) select xele);

structure of xeleRows will be like:













Now I want to get one row from N Number of rows on basis of filter criteria that can be dynamic, the no of columns in filter criteria will vary at run time.

Anybody know shows to achieve this using Linq. Please guide.



Abmiguous reference message triggered by VS database projects compiler for an SQL XML query

Working with a Visual Studio 2010 database project I've written a query:

    UPDATE [s]
        SET [s].[ValueB] = [s].[ValueB] + [x].[ValueB]
        [dbo].[Something] [s]
            [XmlInfo].[Entry].[value] ('@AttributeA', 'int') AS [ValueA],
            [XmlInfo].[Entry].[value] ('.', 'int') AS [ValueB]
            @XmlData.[nodes] ('/Node')
            [XmlInfo] ([Entry])) as [x]
        [s].[ValueA] = [x].[ValueA]

Now everything works fine. Management Studio runs it and does not say a word.

Database Projects compiler however finds two ambiguities:

> Warning SQL04151: Procedure: [dbo].[SP_XXX] contains an unresolved reference to an object. Either the object does not exist or the reference is ambig

A better way to reference your wizard steps using named steps

Note: this article uses the plain vanilla but the concepts apply equally well to its popular counterpart .

By far the most common way that I see wizard steps reference in code snippets is by their index.

Adding Multiple Columns and Rows in GridView without using a Database

This article describes on how to add multiple columns and rows in GridView without using a database. Basically the GridView will be populated with data based on the values entered in each TextBoxes on Button Click and retain the GridView data on post back.

add columns with textboxes in gridview using C#

'm working on a shopping cart in a website and I have my items(which have been added to the cart) in an arraylist. And using these values I've read the rest of the values from the db and and have populated on the gridview successfully.
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