.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

SQL code works elsewhere, not in VS2010 Query Builder

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :
SELECT caseid, recid, trandate, description, amount, SUM(amount) AS billtotal
FROM billtran GROUP BY caseid

The above SQL executes and gives me what I want under PHP/MySQL, but it won't even execute under VS2010 Express. When I put the above SQL code in the Dataset Designer Query Builder, I get an error: "In aggregate and grouping expressions, the SELECT clause can contain only aggregate and grouping expressions. [ Select clause = ,recid ]"

I don't understand what the problem is. What is the thing I must know about SQL that is different under Microsoft?

View Complete Post

More Related Resource Links

Code works on aspx but not ascx


Not sure which category to put this question in, so I will put it in here.

Just to give you an idea of the layout of my solution, I have the main project, along with 2 class libraries, BLL and DAL.  (Business Logic Layer and Data Access Layer)

The BLL refers to the DAL and the main project refers to the BLL.

I had a test website and the code worked fine, but the same code will not work on the real project. The only difference is that in the test project, the code was on an aspx code behind, whereas in the real project, it is an ascx code behind (Web User Control )

The code is:

Dim connectionString As String = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ConnectionString
        Dim recordEntry As New UserRecord(connectionString)
        recordEntry.UserName = "TestName"
        recordEntry.BMI = bMI
        recordEntry.BMR = bMR
        recordEntry.FTM = fTM
        recordEntry.TDEE = tDEE
        recordEntry.TodayDate = dateForToday

Dim connectionString As String = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ConnectionString

Use DB query result to generate Jquery required lines of code



I 'm gonna query database for photo records. each record include "photo path" where imagesURL  stored in string format

 I Choses my favorite Juqery plugin to show images. Plugin needs some lines of code like below.

How could I  generate these lines

$(function() {
sleep: 2,
fade: 1
}, [
{ src: 'picture1.jpg' },
{ src: 'picture2.jpg' },
{ src: 'picture4.jpg' }

this my query works fine, i need to eliminate when resultset zero

hithis is the query working fineSELECT a.Selection,a.SizeRange,a.Thickness,       ISNULL(a.Assotment,0) - ISNULL(b.Issue,0)  AS difference1      FROM ( SELECT Selection,SizeRange,Thickness,                SUM(Pcs) AS Assotment            FROM  dbo.WBCAssortDescp where LotNo='LWC2'          group by Selection,SizeRange,Thickness) AS a LEFT OUTER   JOIN (SELECT Selections,SizeRange,Thickness,                SUM(Pieces) AS Issue            FROM dbo.WBIssue Where LotNo='LWC2'           group by Selections,SizeRange,Thickness) AS b on b.Selections=a.Selection and b.SizeRange=a.SizeRange and a.Thickness=b.ThicknessSELECT a.Se,a.Si.Th,      ISNULL(a.ent,0) - ISNULL(b.Ise,0)  AS difference1   FROM ( SELECT Se,Si,Th,               SUM(Pcs) AS ent           FROM  dbo.Table1where No='C2'         group by Se,Si,Th) AS aLEFT OUTER  JOIN (SELECT Ses,Si,Th,               SUM(Pieces) AS Ise         &nbs

VS2010 not displaying code when break hit.

Hosting a WCF application built on the .Net 3.0 framework in VS2010 and when a break is hit in the code the class will not display.  I can get other classes to display, but the class that I need to work on, where I put the break, is hidden and will not display no matter what I do. Thanks, Dave

Need help to merge all the code into only one query

Hi guys,   Can anyone help me, please to merge all the queries below into one query? Thanks in advance, Aldo.   DECLARE @i smalldatetime, @FirstDate smalldatetime, @LastDate smalldatetime, @SortGroupStart INT, @SortGroupEnd INT, @TargetSales REAL; SET @FirstDate = '2008-10-01T00:00:00' SET @LastDate = '2008-10-31T00:00:00' SET @SortGroupStart = 3000; SET @SortGroupEnd = 3999; SET @TargetSales = 300000;   if object_id('MonthlySalesIncrement_20081002000_Calendar') is not null exec(' DROP TABLE MonthlySalesIncrement_20081002000_Calendar'); CREATE TABLE MonthlySalesIncrement_20081002000_Calendar (CalendarDate smalldatetime PRIMARY KEY) WHILE @FirstDate <= @LastDate BEGIN INSERT INTO MonthlySalesIncrement_20081002000_Calendar (CalendarDate) VALUES (convert(smalldatetime, @FirstDate, 121)) SET @FirstDate = DateAdd(dd, 1, @FirstDate); END   if object_id('MonthlySalesIncrement_20081002000') is not null exec(' DROP TABLE MonthlySalesIncrement_20081002000'); SELECT convert(smalldatetime, Stock.VALUEDATE, 120) AS 'Stock.VALUEDATE', StockMoves.DOCUMENTID AS 'StockMoves.DOCUMENTID', StockMoves.SUPPLYQUANTITY AS 'StockMoves.SUPPLYQUANTITY', StockMoves.PRICE AS 'StockMoves.PRICE', StockMoves.RATE AS 'StockMoves.RATE', StockMoves.DISCOUNTPRC AS 'StockMoves.DISCOUNTPRC', Stock.DISCOUNTPRC AS 'Stock.DISCOUNTPRC', RATES.RATE AS 'RATES.RATE' INTO MonthlySalesIn

SSRS 2008 R2 - Query builder freezes and needs to be shut down Report Builder 3 when attempting quie

Hi, In SSRS, using Report Builder 3.0 (SSRS R2), after choosing a Report Model as the report's embedded data source, launched "Query Deisinger…" from GUI to build data source in report.  First 1-2 attempts at querying underlying data source (Oracle back-end), are successful, but later attempts freeze application to the point where application freezes and overall Report Builder application needs to be forcibly shut-down. This system freeze has occurred when referencing tables containing a very small number of records as well as larger data sets.  Suspect that application freezing may be caused by timing-out of connection to back-end database. but I am not sure. Any suggestion to solve this issue? Thanks   Environment - Distributed deployment Server1(report_srv) --Windows server 2003R2 64bit, Installed sql server 2008R2 reporting service, configuration tools Server2 (report_db_srv) --windows server 2003r2 64bit, sql server 2005 for report server database

MDX works fine in SSMS but not in query designer

Hi all,   I am trying to include this query in my query designer in SSRS. The query below is as follows:WITH MEMBER [Time].[Fiscal Month].[All].[ Subtotal] AS ' AGGREGATE( EXISTING { [Time].[Fiscal Month].[All].CHILDREN }) ', SOLVE_ORDER = 1000  SELECT NON EMPTY { { { { [Time].[Fiscal Month].[All].CHILDREN }, { [Time].[Fiscal Month].[All].[ Subtotal] } } * { { [Measures].[SKU Distinct Count] } } } } ON COLUMNS , NON EMPTY { [Sales Force].[Retailer Code].[All].CHILDREN } ON ROWS  FROM [Sales]CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL The error i am getting isThe query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..Please let me know where am i going wrong.Thanks and regards,ram

how can a query be executed in vs2010 ?

I work on a project labregistrations for students in VS2010 using vb i have a table in my db called registration and i want to put values (data) in it through this query.How can this be executed? Any code help please?   sqlstr As String = "INSERT INTO REGISTRATION (AcademicSemId,AcademicYearId,LabId,StudentId,RegDate) VALUES (" & Session("AcademicSemId") & "," & Session("AcademicYearId") & "," & lab.LabID & "," & Session("StudentId") & "," & Now.Date.ToString

Linq query in code behind

Hii would like to figure out this LinqDataSource in code-behind in vb.net<asp:LinqDataSource                 ID="lds" runat="server"                ContextTypeName="dbDataContext"                TableName="vKommentarUnion"                  Select="new(Key, Count() as Count, It as Items)" GroupBy="themen"                 where="isdrin=0"                AutoPage="true" AutoSort="true"            /> I found only samples in C#.How can i figure it out in vb?this does not run, what is wrong? Dim db As New dbDataContext Dim kommentare = From c In db.kommentar _ Group c By c.kursid Into Group _ Select key = c.kursid, count = Group.count, _ Items = From item In Group Select item lv.DataSource = k

Query crashes in MSAccess 2007 and 2010 on Windows 7 64bit, but works on 32 bit

Hello. I have a prepared a Database that contains 2 tables, and a Query. If you execte the query (Just a very simple join with a few where statments) Windows 7 crashes in 64 bit, but not on a 32 bit platform. Unfortunatly the Database has 300MB and is to big to be uploaded here. So if there is anyone interested at MS to get my Database, please feel free to contact me serge.dornseiffer@sdna.lu

Ldap authenication code is not working with IIS but works with Visual Studio developement webserver

Hi,I have weird issue here, I have a method that  takes a login Id and authenticates with LDAP active directory and returns the employee Id.It works great when I run the application in Visual studio 2008 ( it uses the default visual studio developement webserver). The code authenitcates the login id and returns emp Id perfectly. But the problem comes when I publish the code to the Local IIS in my developement PC.public string GetEastWestTeamMembersEmployeeID(string loginID)        {            string domain = string.Empty;            string login = string.Empty;            int position = loginID.IndexOf(@"\");            string empid = "";                  System.DirectoryServices.DirectoryEntry directoryEntry = null;            if (position >= 0)            {                domain = loginID.Substring(0, position);    &n

caml query builder for wss 3 site

hi, is there a caml query builder that can be use for WSS 3.0 sites?

Report Builder 3.0 for Oracle with Graphical Query Designer

Is there a possibility to have Graphical Query Designer in Report Builder 3.0 in case the connection is made to an Oracle database (through .NET Provider for Oracle for example)? I need to offer to the end users the means to build their own reports. Every user has associated a group of database objects and as soon as users are added new database objects are added. Therefore I cannot create a model because it needs to be changed frequently and the model in this case would contain objects to which the user does not have access. The only option is that the user should create his report query in a friendly manner. So the query text editor is of no good in this case.   I've found in documentation that for Oracle and other databases there is no Graphical Query Designer: In Report Builder 2.0, to specify a query for data source types Oracle, OLE DB, ODBC, and Teradata, you must use the text-based query designer (http://msdn.microsoft.com/en-us/library/dd220607(SQL.100).aspx). Is there a way to have Graphical Query Designer in Report Builder 3.0 or should I look for other products? In case Report Builder cannot solve this problem can you make some product recommendations - ad-hoc web reporting for .NET?

Code works with .NET 3.5, but not 4



I have the following code:

 protected void Page_Load(object sender, EventArgs e)
        if (!IsPostBack)
            if (!User.Identity.IsAuthenticated)
                Login lg = (Login)LoginView1.FindControl("Login1"

FillBy failing, SQL query works

I changed a query for a bound datagridview.  Original the datagridview showed all the columns, but I removed some of the columns by rightclicking on the datagridview.  I changed the fillby query to only include certain columns I selected.  Now it isn't working.  The same query works in MS-SQL, but it doesn't work in Preview data in the dataset designer. Can someone give me an idea of how to look for where my error is?

Query acting weird.. larger date range works in 1min 26sec and a smaller range say 1 month - 3 month


I have no clue why my query is acting weird

If i try to run it for 1/1/2010 9/30/2010 the query takes around 1min 26 sec and  return around a million rows

and If i run for 8/1/2010 to 8/31/2010 it takes forever to it...

basically i am getting data from 5 tables and putting it in a temp table and then updating that temp table 2-3 times with some information and then displaying it.

I am stumped as to why it works fast for a longer date range and runs the  snail for a small period of time..

I am in the verge of pulling my hair and going crazy..

any help will be appreciated.




Where to place code to update database using WPF in VS2010?


I have an app in WPF that has numerous controls that are bound to datatables that are filled from sqlserver.

I now need to add the code for updates, inserts and deletes

Where should I put the code to implement this?

I found code


._adapter.UpdateCommand.CommandText = "tool generated update command

in an auto-generated section of the source file that VS created it looks almost correct but I have never before seen an update statement with an order by clause.
I also found multiple instances of:
  if ((this .Adapter.UpdateCommand != null )) {
 this .Adapter.UpdateCommand.Connection = value ;             }
But I cannot find where value is defined or assigned
I have a tabbed interface with a selection list to move back and forth through the datatable. I want to update the data base with all changes to the current selection when a user changes selection or selects a different tab So what is the preferred method to initiate the update and where should
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