GROUPING problem

I am trying to get counts of jobs accum into 4 columns by date. When I try
to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is
invalid in the select list because it is not contained in either an
aggregate function or the GROUP BY clause."
I don't want to group by JobSize. Below is my code if someone can help me
resolve this. Thanks.
David
ALTER VIEW dbo.vw_JobSizeByDate
AS
SELECT ScheduledInDate,
XsmallJobs = CASE
WHEN JobSize = 'X' THEN 1
ELSE 0
END,
SmallJobs = CASE
WHEN JobSize = 'S' THEN 1
ELSE 0
END,
MedJobs = CASE
WHEN JobSize = 'M' THEN 1
ELSE 0
END,
HeavyJobs = CASE
WHEN JobSize = 'H' THEN 1
ELSE 0
END
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
GROUP BY ScheduledInDate
HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))All that is missing is the SUM() - the missing aggregate function of
the error message - around each CASE expression:
XsmallJobs = SUM(CASE
WHEN JobSize = 'X' THEN 1
ELSE 0
END),
Roy Harvey
Beacon Falls, CT
On Fri, 21 Apr 2006 17:03:36 -0500, "David" <dlchase@.lifetimeinc.com>
wrote:

>I am trying to get counts of jobs accum into 4 columns by date. When I try
>to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is
>invalid in the select list because it is not contained in either an
>aggregate function or the GROUP BY clause."
>I don't want to group by JobSize. Below is my code if someone can help me
>resolve this. Thanks.
>David
>ALTER VIEW dbo.vw_JobSizeByDate
>AS
>SELECT ScheduledInDate,
>XsmallJobs = CASE
>WHEN JobSize = 'X' THEN 1
>ELSE 0
>END,
>SmallJobs = CASE
>WHEN JobSize = 'S' THEN 1
>ELSE 0
>END,
>MedJobs = CASE
>WHEN JobSize = 'M' THEN 1
>ELSE 0
>END,
>HeavyJobs = CASE
>WHEN JobSize = 'H' THEN 1
>ELSE 0
>END
>FROM dbo.RepairOrder
>WHERE (RepairOrderID IS NOT NULL)
>GROUP BY ScheduledInDate
>HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
>|||why are you grouping at all, maybe I've missed it, but I don't see any
aggregate functions, just add the having clause as an AND to the where,
and remove the group by:
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
AND (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))|||Use the CASE expressions inside aggregate functions:
ALTER VIEW dbo.vw_JobSizeByDate
AS
SELECT ScheduledInDate,
SUM(CASE WHEN JobSize = 'X' THEN 1 ELSE 0 END) AS "XsmallJobs",
SUM(CASE WHEN JobSize = 'S' THEN 1 ELSE 0 END) AS "SmallJobs",
SUM(CASE WHEN JobSize = 'M' THEN 1 ELSE 0 END) AS "MedJobs",
SUM(CASE WHEN JobSize = 'H' THEN 1 ELSE 0 END) AS "HeavyJobs"
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
GROUP BY ScheduledInDate
HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
"David" wrote:

> I am trying to get counts of jobs accum into 4 columns by date. When I tr
y
> to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' i
s
> invalid in the select list because it is not contained in either an
> aggregate function or the GROUP BY clause."
> I don't want to group by JobSize. Below is my code if someone can help me
> resolve this. Thanks.
> David
> ALTER VIEW dbo.vw_JobSizeByDate
> AS
> SELECT ScheduledInDate,
> XsmallJobs = CASE
> WHEN JobSize = 'X' THEN 1
> ELSE 0
> END,
> SmallJobs = CASE
> WHEN JobSize = 'S' THEN 1
> ELSE 0
> END,
> MedJobs = CASE
> WHEN JobSize = 'M' THEN 1
> ELSE 0
> END,
> HeavyJobs = CASE
> WHEN JobSize = 'H' THEN 1
> ELSE 0
> END
> FROM dbo.RepairOrder
> WHERE (RepairOrderID IS NOT NULL)
> GROUP BY ScheduledInDate
> HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
>
>|||Someone actually put a "vw-"prefix on your view name! They did not
know the ISO-11179 standards - unless this table deals with
Volkswagens. Also, use the portable AS syntax instead of dialect =.
Can I assume that you have more than one repair order, in spite of a
singular table name?
Your WHERE and HAVING clauses made no sense. How can a
"repair_order_id" ever be NULL? What is the definition of an
identifier? Why are you casting temporal data to strings? That would
imply your DDL is soooo screwed up that temporal data is in strings!
Try this, after you clean up the DDL.
CREATE VIEW JobsizeByDate
(xsmalljob_cnt,
smalljob_cnt,
medjob_cnt,
heavyjob_cnt)
AS
SELECT scheduledin_date,
SUM(CASE WHEN jobsize = 'x' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 's' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 'm' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 'h' THEN 1 ELSE 0 END)
FROM RepairOrders
GROUP BY scheduledin_date;|||Perfect. That worked. Thanks.
David
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:fimi42l7diferi1jmlaa6k1anf8lvo6t1d@.
4ax.com...
> All that is missing is the SUM() - the missing aggregate function of
> the error message - around each CASE expression:
> XsmallJobs = SUM(CASE
> WHEN JobSize = 'X' THEN 1
> ELSE 0
> END),
> Roy Harvey
> Beacon Falls, CT
>
> On Fri, 21 Apr 2006 17:03:36 -0500, "David" <dlchase@.lifetimeinc.com>
> wrote:
>|||Ah the beauty of an sql dbms...the overblown importance of columns names :P
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145658584.662773.174100@.i39g2000cwa.googlegroups.com...
> Someone actually put a "vw-"prefix on your view name! They did not
> know the ISO-11179 standards - unless this table deals with
> Volkswagens. Also, use the portable AS syntax instead of dialect =.
> Can I assume that you have more than one repair order, in spite of a
> singular table name?
> Your WHERE and HAVING clauses made no sense. How can a
> "repair_order_id" ever be NULL? What is the definition of an
> identifier? Why are you casting temporal data to strings? That would
> imply your DDL is soooo screwed up that temporal data is in strings!
> Try this, after you clean up the DDL.
> CREATE VIEW JobsizeByDate
> (xsmalljob_cnt,
> smalljob_cnt,
> medjob_cnt,
> heavyjob_cnt)
> AS
> SELECT scheduledin_date,
> SUM(CASE WHEN jobsize = 'x' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 's' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 'm' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 'h' THEN 1 ELSE 0 END)
> FROM RepairOrders
> GROUP BY scheduledin_date;
>|||Why do you think the vague, non-standard names will make for a good
database? That they will port? That a data dictionary will appear
magically from them? That ISO is a waste of time? That 30+ years of
SE research is wrong?|||There is a certain quality to your quantity of orthodoxy.
But you have missed the mark:)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145668737.205833.119960@.j33g2000cwa.googlegroups.com...
> Why do you think the vague, non-standard names will make for a good
> database? That they will port? That a data dictionary will appear
> magically from them? That ISO is a waste of time? That 30+ years of
> SE research is wrong?
>

Grouping by column alias

I'm trying to run a query and group by a calcuated column using its alias.
When I do, I get an error that says:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'WeekEnding'.
Here is the SQL code. Can someone tell me what is wrong with this.
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
--count(*)
From tblWorkQueue
where datecompleted is not null
group by completiontype, WeekEnding
order by weekendingThis is a multi-part message in MIME format.
--=_NextPart_000_00FE_01C396EF.A792ED00
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You cannot use an alias in that context. However, you can use a derived =table to do the same thing:
select
completionType,
WeekEnding,
count(*)
from
(select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
From tblWorkQueue
where datecompleted is not null
) as x
group by completiontype, WeekEnding
order by weekending
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Czyzewski" <jeff@.red5poductions.com_NOSPAM> wrote in message =news:umg0xBxlDHA.3700@.TK2MSFTNGP11.phx.gbl...
I'm trying to run a query and group by a calcuated column using its =alias.
When I do, I get an error that says:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'WeekEnding'.
Here is the SQL code. Can someone tell me what is wrong with this.
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
--count(*)
From tblWorkQueue
where datecompleted is not null
group by completiontype, WeekEnding
order by weekending
--=_NextPart_000_00FE_01C396EF.A792ED00
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You cannot use an alias in that =context. However, you can use a derived table to do the same thing:
select
completionType, WeekEnding,
count(*)from
(select =completionType, (case datepart(dw,dateCompleted) When 2 then dateAdd(dd,4,datecompleted) When 3 then dateAdd(dd,3,datecompleted) When 4 then dateAdd(dd,2,datecompleted) When 5 then dateAdd(dd,1,datecompleted) When 6 then dateAdd(dd,0,datecompleted) end) as WeekEndingFrom tblWorkQueuewhere datecompleted is not null) as =x
group by completiontype, WeekEndingorder by weekending
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Czyzewski" wrote in message news:umg0xBxlDHA.3700=@.TK2MSFTNGP11.phx.gbl...I'm trying to run a query and group by a calcuated column using its =alias.When I do, I get an error that says:Server: Msg 207, Level 16, State 3, =Line 2Invalid column name 'WeekEnding'.Here is the SQL code. =Can someone tell me what is wrong with this.select completionType, (case datepart(dw,dateCompleted) When =2 then dateAdd(dd,4,datecompleted) When 3 then dateAdd(dd,3,datecompleted) When 4 then dateAdd(dd,2,datecompleted) When 5 then dateAdd(dd,1,datecompleted) When 6 then dateAdd(dd,0,datecompleted) end) as WeekEnding --count(*)From tblWorkQueuewhere =datecompleted is not nullgroup by completiontype, WeekEndingorder by weekending

---

--=_NextPart_000_00FE_01C396EF.A792ED00--|||Jeff
Make a derived table
select completionType,WeekEnding
from
(
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
From tblWorkQueue
where datecompleted is not null
) as x
group by completionType,WeekEnding
--order by weekending
"Jeff Czyzewski" <jeff@.red5poductions.com_NOSPAM> wrote in message
news:umg0xBxlDHA.3700@.TK2MSFTNGP11.phx.gbl...
> I'm trying to run a query and group by a calcuated column using its alias.
> When I do, I get an error that says:
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'WeekEnding'.
>
> Here is the SQL code. Can someone tell me what is wrong with this.
> select completionType,
> (case datepart(dw,dateCompleted)
> When 2 then dateAdd(dd,4,datecompleted)
> When 3 then dateAdd(dd,3,datecompleted)
> When 4 then dateAdd(dd,2,datecompleted)
> When 5 then dateAdd(dd,1,datecompleted)
> When 6 then dateAdd(dd,0,datecompleted)
> end) as WeekEnding
> --count(*)
> From tblWorkQueue
> where datecompleted is not null
> group by completiontype, WeekEnding
> order by weekending
>

Grouping

I keep recieving an error: "The table 'table1' is in a list that has no group expressions defined for it. To use a data region in a list, the list must have group expressions." I have a list that contains a table, and would like the table to change with each item of the list. I looked at the grouping, but I didn't know what to do, and didn't see any tutorials out there for ReportViewer.Be sure that you are looking at the grouping for the list, not for the table. List Properties -> Edit Details Group...|||

Many Thanks, your First Post is very Helpfull

Thankswildbill0283

sql

GROUP BY/select list error

I am trying to get a rollup report with several summaries included, but I
keep getting errors that complain about columns not in the GROUP by included
in the select list , even though are not. They are used in a subquery and a
join however. If I use the integer IDs the query works fine, but I need to
order by the names rather than IDs so I get an alphabetical report.
Given the snippet that follws can anyone tell me what might be causing the
issue and how I can correct it?
SELECT
P.LotPropertyName,
S.LotSubdivision,
-- LotCount
'Lots' =
( SELECT Count(*)
FROM dbo.tbl_Lots L (NOLOCK)
WHERE L.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1) -- Lot
FROM dbo.tbl_LotProperties P (NOLOCK)
JOIN dbo.tbl_LotSubdivisions S on S.LotPropertyID = P.LotPropertyID
WHERE P.IsEnabled = 1
AND S.IsEnabled = 1
GROUP BY P.LotPropertyName, S.LotSubdivision
WITH ROLLUP
Results in the errors:
Msg 8120, Level 16, State 1, Line 1
Column 'P.LotPropertyID' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'S.LotSubdivisionID' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause."Byron" <Byron@.discussions.microsoft.com> wrote in message
news:029AD83E-DE44-4B6F-B689-89345A12FF72@.microsoft.com...
>I am trying to get a rollup report with several summaries included, but I
> keep getting errors that complain about columns not in the GROUP by
> included
> in the select list , even though are not. They are used in a subquery and
> a
> join however. If I use the integer IDs the query works fine, but I need
> to
> order by the names rather than IDs so I get an alphabetical report.
> Given the snippet that follws can anyone tell me what might be causing the
> issue and how I can correct it?
>
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> -- LotCount
> 'Lots' =
> ( SELECT Count(*)
> FROM dbo.tbl_Lots L (NOLOCK)
> WHERE L.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1) -- Lot
> FROM dbo.tbl_LotProperties P (NOLOCK)
> JOIN dbo.tbl_LotSubdivisions S on S.LotPropertyID = P.LotPropertyID
> WHERE P.IsEnabled = 1
> AND S.IsEnabled = 1
> GROUP BY P.LotPropertyName, S.LotSubdivision
> WITH ROLLUP
>
> Results in the errors:
> Msg 8120, Level 16, State 1, Line 1
> Column 'P.LotPropertyID' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Msg 8120, Level 16, State 1, Line 1
> Column 'S.LotSubdivisionID' is invalid in the select list because it is
> not
> contained in either an aggregate function or the GROUP BY clause.
You are trying to referencing unaggregated columns in the subquery:
...
WHERE L.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
That won't work. I can only guess what you intended by this query. Try the
following but if that's not it please post DDL, sample data and show your
required end result.
SELECT
P.LotPropertyName,
S.LotSubdivision,
COUNT(*) AS lots
FROM dbo.tbl_Lots L (NOLOCK)
JOIN dbo.tbl_LotProperties P (NOLOCK)
ON L.LotPropertyID = P.LotPropertyID
JOIN dbo.tbl_LotSubdivisions S
ON S.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
WHERE P.IsEnabled = 1
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1
AND S.IsEnabled = 1
GROUP BY P.LotPropertyName, S.LotSubdivision, P.LotPropertyName,
S.LotSubdivision
WITH ROLLUP ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Below are the tables involved, less many of the uninvolved columns.
Properties have Lots and Subdivisions and Lots are assigned to both a
Property and one of its Subdivisions. The general idea is to get the number
of Lots in each Subdivision subtotalled by Property, followed by a grand
total. The problem is that I have to group by integer IDs, but want the
results displayed by Property name, further broken out by Subdivision name.
For example:
Property 1 Subdivision 1 23
Property 1 Subdivision 2 15
Property 1 38
Property 2 Subdivision 1 10
Property 2 Subdivision 2 10
Property 2 20
All Properties 58
CREATE TABLE [dbo].[tbl_LotProperties](
[LotPropertyID] [int] IDENTITY(1,1) NOT NULL,
[LotPropertyName] [nvarchar](128))
CREATE TABLE [dbo].[tbl_LotSubdivisions](
[LotSubdivisionID] [int] IDENTITY(1,1) NOT NULL,
[LotPropertyID] [int] NOT NULL,
[LotSubdivision] [nvarchar](100))
CREATE TABLE [dbo].[tbl_Lots](
[LotID] [int] IDENTITY(1,1) NOT NULL,
[LotPropertyID] [int] NOT NULL,
[LotSubdivisionID] [int] NOT NULL)
"David Portas" wrote:

> "Byron" <Byron@.discussions.microsoft.com> wrote in message
> news:029AD83E-DE44-4B6F-B689-89345A12FF72@.microsoft.com...
> You are trying to referencing unaggregated columns in the subquery:
> ...
> WHERE L.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> That won't work. I can only guess what you intended by this query. Try the
> following but if that's not it please post DDL, sample data and show your
> required end result.
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> COUNT(*) AS lots
> FROM dbo.tbl_Lots L (NOLOCK)
> JOIN dbo.tbl_LotProperties P (NOLOCK)
> ON L.LotPropertyID = P.LotPropertyID
> JOIN dbo.tbl_LotSubdivisions S
> ON S.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> WHERE P.IsEnabled = 1
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1
> AND S.IsEnabled = 1
> GROUP BY P.LotPropertyName, S.LotSubdivision, P.LotPropertyName,
> S.LotSubdivision
> WITH ROLLUP ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||Just add the integer IDs to the select list (generally). It's a major
problem that you don't have any PRIMARY KEYs set on your tables. I
assume that the PKs are the "ID"s, though. Just make sure that you
define your data that way. So, you could do:
SELECT
P.LotPropertyName,
S.LotSubdivision,
-- LotCount
'Lots' =
( SELECT Count(*)
FROM dbo.tbl_Lots L (NOLOCK)
WHERE L.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1) -- Lot
FROM dbo.tbl_LotProperties P (NOLOCK)
INNER JOIN dbo.tbl_LotSubdivisions S
ON S.LotPropertyID = P.LotPropertyID
WHERE P.IsEnabled = 1 AND S.IsEnabled = 1
GROUP BY
P.LotPropertyID,
P.LotPropertyName,
S.LotSubDivisionID,
S.LotSubdivision
WITH ROLLUP
but like Dave Portas alluded to, here's a better way (btw, I am "reading
in" to your requirements a bit here):
SELECT
P.LotPropertyName,
S.LotSubdivision,
COUNT(DISTINCT L.LotID) AS Lots
FROM dbo.tbl_Lots L (NOLOCK)
INNER JOIN dbo.tbl_LotProperties P (NOLOCK)
ON L.LotPropertyID = P.LotPropertyID
INNER JOIN dbo.tbl_LotSubdivisions S
ON S.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
WHERE P.IsEnabled = 1
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1
AND S.IsEnabled = 1
GROUP BY P.LotPropertyName, S.LotSubdivision, L.LotID
WITH ROLLUP;
Byron wrote:
> Below are the tables involved, less many of the uninvolved columns.
> Properties have Lots and Subdivisions and Lots are assigned to both a
> Property and one of its Subdivisions. The general idea is to get the numb
er
> of Lots in each Subdivision subtotalled by Property, followed by a grand
> total. The problem is that I have to group by integer IDs, but want the
> results displayed by Property name, further broken out by Subdivision name
.
> For example:
> Property 1 Subdivision 1 23
> Property 1 Subdivision 2 15
> Property 1 38
> Property 2 Subdivision 1 10
> Property 2 Subdivision 2 10
> Property 2 20
> All Properties 58
> CREATE TABLE [dbo].[tbl_LotProperties](
> [LotPropertyID] [int] IDENTITY(1,1) NOT NULL,
> [LotPropertyName] [nvarchar](128))
> CREATE TABLE [dbo].[tbl_LotSubdivisions](
> [LotSubdivisionID] [int] IDENTITY(1,1) NOT NULL,
> [LotPropertyID] [int] NOT NULL,
> [LotSubdivision] [nvarchar](100))
> CREATE TABLE [dbo].[tbl_Lots](
> [LotID] [int] IDENTITY(1,1) NOT NULL,
> [LotPropertyID] [int] NOT NULL,
> [LotSubdivisionID] [int] NOT NULL)
>
> "David Portas" wrote:
>|||I apparently erred by simplifying my example code to save space. There are
actually primary keys on the integer IDENTITY columns, and there are many
other columns I eliminated from the CREATE code, leavong only the keys and
names. There are also about a dozen other subqueries in addition to the one
that counts the lots, though all of them use data in the Lots table and need
to be grouped by Property and Subdivision. All of them work fine as long as
I group by ID, but I need to return the results to the user with the Propert
y
and Subdivision names sorted by name rather than ID. Since I ran into so
much trouble using GROUP BY I started down the path of using temporary table
s
and a cursor to iterate through an intermediate result set adding the
subtotals and grand total, but I realize there must be a better way to do it
.
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:

> Just add the integer IDs to the select list (generally). It's a major
> problem that you don't have any PRIMARY KEYs set on your tables. I
> assume that the PKs are the "ID"s, though. Just make sure that you
> define your data that way. So, you could do:
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> -- LotCount
> 'Lots' =
> ( SELECT Count(*)
> FROM dbo.tbl_Lots L (NOLOCK)
> WHERE L.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1) -- Lot
> FROM dbo.tbl_LotProperties P (NOLOCK)
> INNER JOIN dbo.tbl_LotSubdivisions S
> ON S.LotPropertyID = P.LotPropertyID
> WHERE P.IsEnabled = 1 AND S.IsEnabled = 1
> GROUP BY
> P.LotPropertyID,
> P.LotPropertyName,
> S.LotSubDivisionID,
> S.LotSubdivision
> WITH ROLLUP
> but like Dave Portas alluded to, here's a better way (btw, I am "reading
> in" to your requirements a bit here):
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> COUNT(DISTINCT L.LotID) AS Lots
> FROM dbo.tbl_Lots L (NOLOCK)
> INNER JOIN dbo.tbl_LotProperties P (NOLOCK)
> ON L.LotPropertyID = P.LotPropertyID
> INNER JOIN dbo.tbl_LotSubdivisions S
> ON S.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> WHERE P.IsEnabled = 1
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1
> AND S.IsEnabled = 1
> GROUP BY P.LotPropertyName, S.LotSubdivision, L.LotID
> WITH ROLLUP;
>
> Byron wrote:
>|||Post your real DDL and query. You don't want to be using a cursor for
this...
Byron wrote:
> I apparently erred by simplifying my example code to save space. There ar
e
> actually primary keys on the integer IDENTITY columns, and there are many
> other columns I eliminated from the CREATE code, leavong only the keys and
> names. There are also about a dozen other subqueries in addition to the o
ne
> that counts the lots, though all of them use data in the Lots table and ne
ed
> to be grouped by Property and Subdivision. All of them work fine as long
as
> I group by ID, but I need to return the results to the user with the Prope
rty
> and Subdivision names sorted by name rather than ID. Since I ran into so
> much trouble using GROUP BY I started down the path of using temporary tab
les
> and a cursor to iterate through an intermediate result set adding the
> subtotals and grand total, but I realize there must be a better way to do
it.
>
>
> "Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:
>

Group By problem, how to?

Hi, The following works:
Select SUM(Amount) As Total, YEAR(TransDate) As TheYear
From SomeTable
Group By YEAR(TransDate)
The following gives an error, I have to group by Year and Month,
Select SUM(Amount) As Total,
YEAR(TransDate) As TheYear,
MONTH(TransDate) As TheMonth
From SomeTable
Group By YEAR(TransDate), MONTH(TransDate)
The problem is that TransDate is now used twice in the Group By clauseChris,
Why is this a problem? If you want one result row for
each year and month combination, you need to group on
year and month, or alternatively, use a single expression
for the year and month in both the select and group by
clauses.
The code you gave works correctly with no error
on SQL Server 2000 and 2005. An alternative with
a single group by item is given below also:
create table SomeTable (
TransDate datetime,
Amount money
)
insert into SomeTable values ('20050403', $100)
insert into SomeTable values ('20050703', $200)
insert into SomeTable values ('20060703', $300)
insert into SomeTable values ('20050708', $400)
Select SUM(Amount) As Total,
YEAR(TransDate) As TheYear,
MONTH(TransDate) As TheMonth
From SomeTable
Group By YEAR(TransDate), MONTH(TransDate)
go
Select
Total,
YEAR(TransMonth) As TheYear,
MONTH(TransMonth) As TheMonth
From (
Select
SUM(Amount) AS Total,
DATEADD(month,DATEDIFF(month,0,TransDate
),0) AS TransMonth
FROM SomeTable
GROUP BY DATEADD(month,DATEDIFF(month,0,TransDate
),0)
) M
GO
drop table SomeTable
Steve Kass
Drew University
Chris Botha wrote:

>Hi, The following works:
>Select SUM(Amount) As Total, YEAR(TransDate) As TheYear
>From SomeTable
>Group By YEAR(TransDate)
>The following gives an error, I have to group by Year and Month,
>Select SUM(Amount) As Total,
> YEAR(TransDate) As TheYear,
> MONTH(TransDate) As TheMonth
>From SomeTable
>Group By YEAR(TransDate), MONTH(TransDate)
>The problem is that TransDate is now used twice in the Group By clause
>
>|||Hi Steve, you are right, it works, must have been a typo somewhere.
Sorry for wasting your time (shrink, shrink, shrink).
Chris.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uIXBMjvOGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Chris,
> Why is this a problem? If you want one result row for
> each year and month combination, you need to group on
> year and month, or alternatively, use a single expression
> for the year and month in both the select and group by
> clauses.
> The code you gave works correctly with no error
> on SQL Server 2000 and 2005. An alternative with
> a single group by item is given below also:
> create table SomeTable (
> TransDate datetime,
> Amount money
> )
> insert into SomeTable values ('20050403', $100)
> insert into SomeTable values ('20050703', $200)
> insert into SomeTable values ('20060703', $300)
> insert into SomeTable values ('20050708', $400)
> Select SUM(Amount) As Total,
> YEAR(TransDate) As TheYear,
> MONTH(TransDate) As TheMonth
> From SomeTable
> Group By YEAR(TransDate), MONTH(TransDate)
> go
> Select
> Total,
> YEAR(TransMonth) As TheYear,
> MONTH(TransMonth) As TheMonth
> From (
> Select
> SUM(Amount) AS Total,
> DATEADD(month,DATEDIFF(month,0,TransDate
),0) AS TransMonth
> FROM SomeTable
> GROUP BY DATEADD(month,DATEDIFF(month,0,TransDate
),0)
> ) M
> GO
> drop table SomeTable
> Steve Kass
> Drew University
> Chris Botha wrote:
>

Group By Problem

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Quote:

Originally Posted by Merio

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id|||

Quote:

Originally Posted by tezza98

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id

------------

The problem was solved when i changed the first line with this:
SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_no, SUM(tbl1.amount) As Amount

I needed to put the SUM on tbl1.amount. - I thought that tbl1.amount would be totaled automatically when GROUP BY is used... I was wrong. :0

Thanks for your comment :)

Group by on the text colum throws error

Hi ,

I have this query

paprojnumber is varchar
patx500 is text
palineitemseq is int

select Paprojnumber,Patx500,max(palineitemseq) from pa02101,pa01601
where
pa02101.pabillnoteidx=pa01601.pabillnoteidx group by
paprojnumber,patx500

it throws this error
Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Thanks a lot for your help.

AJHere it means exactly what the error says. You cannot sort on a text field
(or NText field), which is what your "group by" code is trying to do.

"AJ" <aj70000@.hotmail.com> wrote in message
news:6097f505.0409300838.a81c800@.posting.google.co m...
> Hi ,
> I have this query
> paprojnumber is varchar
> patx500 is text
> palineitemseq is int
> select Paprojnumber,Patx500,max(palineitemseq) from pa02101,pa01601
> where
> pa02101.pabillnoteidx=pa01601.pabillnoteidx group by
> paprojnumber,patx500
> it throws this error
> Server: Msg 306, Level 16, State 2, Line 1
> The text, ntext, and image data types cannot be compared or sorted,
> except when using IS NULL or LIKE operator.
> Thanks a lot for your help.
> AJ|||On Thu, 30 Sep 2004 18:24:58 +0100, Robin Tucker wrote:

> Here it means exactly what the error says. You cannot sort on a text field
> (or NText field), which is what your "group by" code is trying to do.

You can, however, group by an expression using it:

select Paprojnumber,Patx500,max(palineitemseq)
from pa02101,pa01601
where pa02101.pabillnoteidx=pa01601.pabillnoteidx
group by paprojnumber,convert(varchar(50),patx500)

Group By Memo Field

I need to group by a memo field but it doesn't allow me, I tried making a formula in order to use it and I got a error Blob/Memo can't be used in formula...
I'm using CR 8.5
Please helptry using...

Insert -> Group

Choose the Memo Field... it will group by the memo field by then...

If this is not what you want you gotta give details...

Group By Error Under SQL 2005

Our stored procedure throws the following error when running on 2005
w/compatibility set to 90, but not 80:
"Each GROUP BY expression must contain at least one column that is not an
outer reference. Severity 15, State 1, Procedure "procname", line 351"
The code in question is:
....
OR
(EXISTS (SELECT e.logical_seat_row, b.logical_seat_num, count(*)
FROM #ZoomSet e
WHERE e.logical_seat_num >= b.logical_seat_num
and e.logical_seat_num <= c.logical_seat_num
and e.bit_col & 64 = 64
and e.logical_seat_row = b.logical_seat_row
GROUP BY e.logical_seat_row, b.logical_seat_num
HAVING count(*) >= @.num_wc_ind))
)
.....
I've searched support.msft.com, as well as this newsgroup, and all of the
web, but can't find this error anywhere. Any ideas would be appreciated.
Thanks!
Steven Bras
Tessitura Network, Inc.>> Our stored procedure throws the following error when running on 2005 w/co
mpatibility set to 90, but not 80:: "Each GROUP BY expression must contain a
t least one column that is not an outer reference. Severity 15, State 1, Pr
ocedure "procname", line 35
1" <<
As best I can tell from the fragment posted, the code lookd fine from a
standards viewpoint (ignoring the bit operator crap). I would clean it
up for human use (one BETWEEN is easier to read and understand than two
comparisons) and see if that helps.
Since it is an EXISTS() predicate, use the * instead of a list; my
thought is that the engine might be trying to build the list when all
it needs to do is find is one row.
OR
(EXISTS (SELECT *
FROM #ZoomSet AS E
WHERE E.logical_seat_num
BETWEEN B.logical_seat_num
AND C.logical_seat_num
AND E.bit_col & 64 = 64
AND E.logical_seat_row = B.logical_seat_row
GROUP BY E.logical_seat_row, B.logical_seat_num
HAVING COUNT (*) >= @.num_wc_ind))
)
The other things are to re-write the whole query to get rid of the
temp table and the assembly language bit fiddling.
It looks like you are trying to find a block of vacant seats on the
same row. Ihave queries for that in SQL FOR SMARTIES which are
simpler.|||Thanks; I do appreciate your response and am a long-standing admirer of your
columns and books.
But why does the error now occur under 2005 where it didn't used to under
SQL 2000?
--
Steven Bras
Tessitura Network, Inc.
"--CELKO--" wrote:

351" <<
> As best I can tell from the fragment posted, the code lookd fine from a
> standards viewpoint (ignoring the bit operator crap). I would clean it
> up for human use (one BETWEEN is easier to read and understand than two
> comparisons) and see if that helps.
> Since it is an EXISTS() predicate, use the * instead of a list; my
> thought is that the engine might be trying to build the list when all
> it needs to do is find is one row.
> OR
> (EXISTS (SELECT *
> FROM #ZoomSet AS E
> WHERE E.logical_seat_num
> BETWEEN B.logical_seat_num
> AND C.logical_seat_num
> AND E.bit_col & 64 = 64
> AND E.logical_seat_row = B.logical_seat_row
> GROUP BY E.logical_seat_row, B.logical_seat_num
> HAVING COUNT (*) >= @.num_wc_ind))
> )
> The other things are to re-write the whole query to get rid of the
> temp table and the assembly language bit fiddling.
> It looks like you are trying to find a block of vacant seats on the
> same row. Ihave queries for that in SQL FOR SMARTIES which are
> simpler.
>|||> But why does the error now occur under 2005 where it didn't used to under
> SQL 2000?
In the Books Online topic 'sp_dbcmptlevel', it states the following:
Compatibility level setting of 80 A GROUP BY clause in a subquery that
references a column from the outer query succeeds.
Compatibility level setting of 90 A GROUP BY clause in a subquery that
references a column from the outer query returns an error as per the SQL
standard.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"StevenBr" <sbras@.community.nospam> wrote in message
news:ACFBFA3A-8CAF-4875-A83F-38607B5BDC23@.microsoft.com...
> Thanks; I do appreciate your response and am a long-standing admirer of
> your
> columns and books.
> But why does the error now occur under 2005 where it didn't used to under
> SQL 2000?
> --
> Steven Bras
> Tessitura Network, Inc.
>
> "--CELKO--" wrote:
>|||n Mon, 26 Jun 2006 17:39:15 -0700, "Gail Erickson [MS]"
<gaile@.online.microsoft.com> wrote:

> In the Books Online topic 'sp_dbcmptlevel', it states the following:
>Compatibility level setting of 80 A GROUP BY clause in a subquery that
>references a column from the outer query succeeds.
>Compatibility level setting of 90 A GROUP BY clause in a subquery that
>references a column from the outer query returns an error as per the SQL
>standard.
Interesting.
It may also be worth pointing out why this would be so. Any reference
within the subquery that refers to the outer query is, for the purpose
of the subquery, a reference to a constant. And there is no reason to
include a constant in a GROUP BY list.
In the specific example posted:

>....
> OR
> (EXISTS (SELECT e.logical_seat_row, b.logical_seat_num, count(*)
> FROM #ZoomSet e
> WHERE e.logical_seat_num >= b.logical_seat_num
> and e.logical_seat_num <= c.logical_seat_num
> and e.bit_col & 64 = 64
> and e.logical_seat_row = b.logical_seat_row
> GROUP BY e.logical_seat_row, b.logical_seat_num
> HAVING count(*) >= @.num_wc_ind))
> )
>.....
it is the reference to b.logical_seat_num in the GROUP BY that is
redundant, since there can be only one value for any given evaluation
of the subquery. But we can go farther and observe that since
e.logical_seat_row = b.logical_seat_row, the reference to
e.logical_seat_row in the GROUP BY is also redundant.
Which means the entire GROUP BY clause is not required, as they
resolve to a single row, and since (as has already been pointed out)
the SELECT list for an EXISTS subquery should be *, the GROUP BY
should be redundant... BUT WAIT! Will it be legal to have a HAVING
clause reference an aggregate expression COUNT when the select list is
an *? Good question, I'm not sure.
--Quick test in 2000 and 2005, using system tables, demonstrates that
--it works!
select *
from sysobjects
where exists
(select * from syscolumns
where syscolumns.id = sysobjects.id
having count(*) > 30)
But it still looks funny to my sensitive nature. I think it might be
safer to rewrite without the EXISTS:
OR
(@.num_wc_ind <=
(SELECT count(*)
FROM #ZoomSet e
WHERE e.logical_seat_num >= b.logical_seat_num
and e.logical_seat_num <= c.logical_seat_num
and e.bit_col & 64 = 64
and e.logical_seat_row = b.logical_seat_row)
)
Roy Harvey
Beacon Falls, CT|||Hi,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Group By clause with an inserted column

Good afternoon all,
I'm trying to write SQL that adds a descriptive column and groups on that
column but I get an error saying my inserted column is invalid. Can anyone
help? An example follows.
Sales Table:
Sales Type Amount
A 5.00
A 6.00
B 2.00
SQL:
SELECT
(CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES'
END), SUM(Amount)
GROUP BY '?
Desired Result:
TAXABLE SALES 11.00
NONTAXABLE SALES 2.00
Thanks in advance,
Don J> GROUP BY '?
CASE
WHEN Sales Type = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END
AMB
"Don Jellie" wrote:

> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can anyon
e
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES
'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J|||Don Jellie wrote:
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on
> that column but I get an error saying my inserted column is invalid.
> Can anyone help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
> SALES' END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J
create table #a (SalesType char(1) NOT NULL, Amount DECIMAL(10, 2) NOT
NULL)
go
Insert Into #a Values ('A', 5.00)
Insert Into #a Values ('A', 6.00)
Insert Into #a Values ('B', 2.00)
go
SELECT
CASE
WHEN SalesType = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END,
SUM(Amount)
From
#a
GROUP BY SalesType
go
drop table #a
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Since your column named Sales Type has a space in it, you must "Quote" it by
wrapping it in " " or [ ] like so:
SELECT
CASE WHEN [Sales Type] = 'A'
THEN 'Taxable Sales'
ELSE 'NonTaxable Sales'
END AS "Sales Type",
SUM(Amount) AS "Amount"
GROUP BY [Sales Type]
"Don Jellie" <Jelliebean1@.msn.nospam.com> wrote in message
news:ADDCEAC9-659D-4EA2-8252-25E5DB0D6840@.microsoft.com...
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can
anyone
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
SALES'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J

gridview sql timeout

I have a simple gridview displaying data from an MSSQL server 2005. Every now and then I get a sql timeout error. Listed below. Can anyone explain why I am getting this error? The connection pool is 100 and the timeout is set to 360. I have checked to current connections in SQL and they never max over 23. There are not locks in SQL when the problem occurs. The query is a stored procedure in sql and when sent sample data it normally takes about 5 seconds.

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 12/3/2007 9:46:37 PM
Event time (UTC): 12/4/2007 3:46:37 AM
Event ID: 140501f9a7744dfea2e445ed00939e44
Event sequence: 42
Event occurrence: 1
Event detail code: 0

Application information:
Application domain: /LM/W3SVC/1/ROOT-1-128412128787656250
Trust level: Full
Application Virtual Path: /
Application Path: c:\inetpub\wwwroot\
Machine name: DD-MAIN

Process information:
Process ID: 5544
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE

Exception information:
Exception type: SqlException
Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Request information:
Request URL:http://localhost/Search_DG.aspx?SearchWord=1212
Request path: /Search_DG.aspx
User host address: 10.10.10.1
User:
Is authenticated: False
Authentication Type:
Thread account name: NT AUTHORITY\NETWORK SERVICE

Thread information:
Thread ID: 1
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)
at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback)
at System.Web.UI.WebControls.DataBoundControl.PerformSelect()
at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
at System.Web.UI.WebControls.GridView.DataBind()
at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
at System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls()
at System.Web.UI.Control.EnsureChildControls()
at System.Web.UI.WebControls.GridView.get_Rows()
at Install_DG.Page_Load(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)


Custom event details:

For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/events.asp.

Hi

I think you need to set the timeout property of Data objects, other than Connection object.

Please refer the URL :http://techielion.blogspot.com/2007/01/error-timeout-expired-timeout-period.html|||

I think this is what you were referring to.

http://www.velocityreviews.com/forums/showpost.php?s=4fa167ceba48b31fab486f5381b1455d&p=436028&postcount=4

I will try this and let you know if it worked. It will take a good day to test.

Thanks for the info.

|||

Thanks for the help. I have tested the timeout setting on the selecting event for the gridview and it worked great.

Gridview / SqlDataSource error - Procedure or function <stored procedure name> has t

Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

Post your Gridview and SQL Proceedure code.|||

SqlDataSource:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:TPCConnectionString %>"SelectCommand="SELECT ID, AccountNumber, CompanyName, ExceptionDescription, PricingAdjustments FROM ExceptionList ORDER BY CompanyName"DeleteCommand="DELETE FROM ExceptionList WHERE (ID = @.ID)"ProviderName="<%$ ConnectionStrings:TPCConnectionString.ProviderName %>"UpdateCommand="updExceptionList"UpdateCommandType="StoredProcedure"><DeleteParameters>

<asp:ParameterName="ID"/>

</DeleteParameters><UpdateParameters><asp:ControlParameterControlID="GridView2"Name="AccountNumber"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="CompanyName"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="ExceptionDescription"PropertyName="SelectedValue"/></UpdateParameters></asp:SqlDataSource>

Stored Procedure:

CREATE PROCEDURE updExceptionList @.ID numeric(5), @.AccountNumber nvarchar(255),@.CompanyName nvarchar(255), @.ExceptionDescription nvarchar(255) AS

UPDATE ExceptionList SET AccountNumber = @.AccountNumber, CompanyName = @.CompanyName, ExceptionDescription = @.ExceptionDescription WHERE ID = @.ID
GO

But also fails if I specify ID parameter in SqlDataSpurce--here is the error:

Procedure or function updExceptionList has too many arguments specified.

|||

SqlDataSource:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:xxxConnectionString %>"SelectCommand="SELECT ID, AccountNumber, CompanyName, ExceptionDescription, PricingAdjustments FROM ExceptionList ORDER BY CompanyName"DeleteCommand="DELETE FROM ExceptionList WHERE (ID = @.ID)"ProviderName="<%$ ConnectionStrings:xxxConnectionString.ProviderName %>"UpdateCommand="updExceptionList"UpdateCommandType="StoredProcedure"><DeleteParameters>

<asp:ParameterName="ID"/>

</DeleteParameters><UpdateParameters><asp:ControlParameterControlID="GridView2"Name="AccountNumber"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="CompanyName"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="ExceptionDescription"PropertyName="SelectedValue"/></UpdateParameters></asp:SqlDataSource>

Stored Procedure:

CREATE PROCEDURE updExceptionList @.ID numeric(5), @.AccountNumber nvarchar(255),@.CompanyName nvarchar(255), @.ExceptionDescription nvarchar(255) AS

UPDATE ExceptionList SET AccountNumber = @.AccountNumber, CompanyName = @.CompanyName, ExceptionDescription = @.ExceptionDescription WHERE ID = @.ID
GO

But also fails if I specify ID parameter in SqlDataSpurce--here is the error:

Procedure or function updExceptionList has too many arguments specified.

|||

Change your UpadteParametrs their Control Id's are wronge.

Are u using some DropDownlists inside a Gridview?

|||

In what way are they wrong? They are for control Gridview2.

I actually solved this problem by entering the entirety of the stored procedure in the SqlDataSource configuration, which is the most unideal solution I could make work. I do not want any sql at all in my application but it seems that I'm forced to put it there.

|||

I got the same error.

As it turned out, the ConflictDetection on my datasource was set to "CompareAllValues" which forces the datasource to supplies all the columns to my stored procdure. Hence, the error because the stored procedure only take one parameter.

My fix, was just change the ConflictDetection to "OverwriteChanges". Then it worked.

NOTE: I did NOT have to write any code to add new parameter or set the parameter's value for the delete command at all.

Regards,

Minh

|||

correction on the "NOTE".

I did have to add parameter and value for the stored procdure in the RowDeleting event.

But make sure you don't add the parameters in your designer.

protectedvoid GridView1_RowDeleting(object sender,GridViewDeleteEventArgs e)

{

foreach (DictionaryEntry entryin e.Keys)

{

this.SqlDataSource1.DeleteParameters.Add(entry.Key.ToString(), entry.Value.ToString());

}

}

|||

Minh,

Thank you for looking at the issue. I revisited this page and found that the ConflictDetection parameter was set to "OverwriteChanges" so that doesn't seem to be the issue. I am finding many other gridview / parameter problems, although I've had some better success since this post. I would like to understand why you had to delete all the parameters in code, does the designer not function properly? I'm not really interested in writing code for my next update which has like 20 parameters.

|||

Hi sestyd,

I've had a similar problem before, where the update command is sending more parameters than I have defined in the Sqldatasource. (Assuming you have this connected to a grid view), what it seems to be doing is sending any parameter that is defined in the grid view that is not specified as read only (as well as the data keys).

I pretty much just either made the parameters read only in the grid view (if that was viable) or defined the parameters in the stored procedure, then just ignored them.

BTW here is some code that I wrote that will display all of the parameters and their values in a label on the web page for an update function and stop the function from executing, this helped me work out what was going on.

[VB Code]

Protected Sub MyDataSource_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles MyDataSource.Updating lblTest.Text =""For iAs Integer = 0To e.Command.Parameters.Count - 1Step 1 lblTest.Text &= e.Command.Parameters.Item(i).ParameterName.ToString &" :: " & e.Command.Parameters.Item(i).Value &"<br>"Next e.Cancel =TrueEnd Sub

[/VB Code]

HTH

Grid view-cant update or delete

I put a grid view on a web form ,when I run it -the SELECT, EDIT works

the UPDATE,DELETE makes an error although I use the sama data,I added the error :

Anyone can help?

Server Error in '/CrystalReportsWebSite1' Application.

---

The data types text and nvarchar are incompatible in the equal to operator.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): The data types text and nvarchar are incompatible in the equal to operator.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140

Hey,

What does those update/delete stored procedures look like? It seems like it may be an issue with the query.

|||I'm guessing he has a text field, and he told it to use optimistic concurrency or (CompareAllValues), which doesn't work with text fields.