Return Distinct Values from Stored Procedures

I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!

Is OrderNum contained in the result set returned by the sp rptOpenOrderLines? If yes, you may need something likeDataView.RowFilter:

http://msdn2.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx

If no, there seems to be no way to do this without checking back in database.

|||Great! Thanks lori_Jay! Although I don't yet have the exact solution, this looks like a good lead toward the final product. That article is pretty useful and I appreciate the help. I've spent days on developing these datalist/gridview controls and its about time that it should be done. I'll post back with the solution when I'm complete with this part.|||

Please See:

http://forums.asp.net/thread/1371660.aspx

Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

Return 2 entries per group based on the two lowest values in group

Below is a query that I need to modify so that it returns just the 2
MastNUMs, Stores, and Distances where the Distances are the two lowest for a
mastnum. So the results would be more like this
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000068 76 4.447685
000000068 70 5.516853
000000069 70 3.836682
000000069 76 6.331691
000000070 76 3.729323
SELECT TOP 15 MastNUM, Store, Distance
FROM DistTest
GROUP BY MastNUM, Store, Distance
ORDER BY MastNUM, Distance
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000067 69 8.112116
000000067 112 19.924702
000000068 76 4.447685
000000068 70 5.516853
000000068 69 6.874022
000000068 112 18.622366
000000068 71 19.396528
000000069 70 3.836682
000000069 76 6.331691
000000069 69 8.924779
000000069 112 16.709897
000000069 71 17.462224
000000070 76 3.729323
--
KoryI can get the lowest for each with the following but how can I get the
lowest 2?
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||I think I have found a way to get the lowest and 2nd lowest, but I hope
their is a better way than the following..
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
(
SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest t2
WHERE Distance > (
SELECT MIN(Distance)
FROM DistTest d2
WHERE t2.MastNum = d2.MastNum
GROUP BY MastNum
)
GROUP BY MastNum
) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
order by T1.mASTnUM
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:51 AM CDT ] [Execution: 16/ms]
MastNUM Store Distance
-- -- --
000000067 70 7.082444
000000068 70 5.516853
000000069 76 6.331691
000000070 70 6.957127
000000071 70 5.944537
000000072 76 6.277529
000000073 70 5.487792
000000074 70 7.193161
000000076 70 5.201532
000000077 70 5.412091
000000078 76 5.295454
000000079 76 5.395715
000000080 76 5.950446
000000081 70 5.920339
000000082 70 5.529576
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:52 AM CDT ] [Execution: 93/ms]
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:uVgDQB9WDHA.2268@.TK2MSFTNGP11.phx.gbl...
> I can get the lowest for each with the following but how can I get the
> lowest 2?
> SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
> FROM DistTest t1,
> ( SELECT MastNUM, MIN(Distance) AS MinDist
> FROM DistTest
> GROUP BY MastNum ) as Dmin
> WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
> go
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000068 76 4.447685
> 000000069 70 3.836682
> 000000070 76 3.729323
> 000000071 76 4.046238
> 000000072 70 3.928709
> 000000073 76 4.663551
> 000000074 76 3.206388
> 000000076 76 4.745636
> 000000077 76 5.338428
> 000000078 70 5.121837
> 000000079 70 4.580213
> 000000080 70 4.338181
> 000000081 76 4.069455
> 000000082 76 4.465975
>
> "Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
> news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> > Below is a query that I need to modify so that it returns just the 2
> > MastNUMs, Stores, and Distances where the Distances are the two lowest
for
> a
> > mastnum. So the results would be more like this
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000070 76 3.729323
> >
> > SELECT TOP 15 MastNUM, Store, Distance
> > FROM DistTest
> > GROUP BY MastNUM, Store, Distance
> > ORDER BY MastNUM, Distance
> >
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000067 69 8.112116
> > 000000067 112 19.924702
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000068 69 6.874022
> > 000000068 112 18.622366
> > 000000068 71 19.396528
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000069 69 8.924779
> > 000000069 112 16.709897
> > 000000069 71 17.462224
> > 000000070 76 3.729323
> >
> > --
> >
> > Kory
> >
> >
>|||The initial step creating this list is as follows:
SELECT g.MASTNUM, s.STORE, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG ) AS Distance
INTO DistTest
FROM geomailing g, geoStore s
WHERE master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) <= 20
GROUP BY g.Mastnum, s.Store, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG )
ORDER BY g.Mastnum, master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG )
Can anyone suggest a way that it only inserts into this table the 2 lowest
Distances ( master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) ) ? So
as to avoid having to pull out the two lowest distances later?
Thanks.
Kory wrote in message news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||Does anyone have any suggestions on how to get just the 2 lowest values
grouped?|||I would imagine a lot of people have a lot of ideas but with what you have
given us it would be stabbing in the dark.
1. Can you post a simple table structure
2. Post sample data to enter in
3. Tell us what you expect to see as the end result.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:OPqoHUDXDHA.208@.tk2msftngp13.phx.gbl...
> Does anyone have any suggestions on how to get just the 2 lowest values
> grouped?
>

Return 0 if null

Hi all,
I have got a query that returns values based on a date range and grouped by
week. I need to return a value of 0 if there are no entries for that week. At
the moment it just skips that week all together.
Any ideas?
Thanks
Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
left outer join if you're using multiple tables and a lack of entries for
that week table is eliminating the week row. But to give a specific answer,
we'd need more details.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
> Hi all,
> I have got a query that returns values based on a date range and grouped
> by
> week. I need to return a value of 0 if there are no entries for that week.
> At
> the moment it just skips that week all together.
> Any ideas?
> Thanks
|||Hi Paul,
My current query is as follows.
SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
Type_QRY].CountOfType) AS SumOfCountOfType
FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN [Counting
Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting Type_QRY].Branch,
[Counting Type_QRY].Type;
This works great counting my entries and putting then grouping by week.
Trouble is if there is no info for a week it skipps that week.
34 = 4
35 = 2
37 = 7
I need it to return
34 = 4
35 = 2
36 = 0
37 = 7
I am fairly new to this so please excuse the query if it is not great. Just
trying to grow my skills in the real world.
Thanks
"Paul Nielsen" wrote:

> Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
> left outer join if you're using multiple tables and a lack of entries for
> that week table is eliminating the week row. But to give a specific answer,
> we'd need more details.
> --
> -Paul Nielsen, SQL Server MVP
> SQL Server 2000 Bible, Wiley Press
>
> "Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
> news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
>
>
|||Andrew Jurgens wrote:
> Hi Paul,
> My current query is as follows.
> SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
> Type_QRY].CountOfType) AS SumOfCountOfType
> FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN
> [Counting Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
> GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type;
> This works great counting my entries and putting then grouping by
> week. Trouble is if there is no info for a week it skipps that week.
> 34 = 4
> 35 = 2
> 37 = 7
> I need it to return
> 34 = 4
> 35 = 2
> 36 = 0
> 37 = 7
> I am fairly new to this so please excuse the query if it is not
> great. Just trying to grow my skills in the real world.
> Thanks
Sounds like you need an OUTER JOIN. I don't know your data, so use an
outer join against the table that may not have a foreign key
relationship. If you wanted all Accounts from the accounts table even if
some of the accounts didn't have a comment in the comments table, you
would:
From Accounts Outer Join Comments on Accounts.id = Comments.id
David Gugick
Imceda Software
www.imceda.com
|||Thanks David,
My problem is that I am querying a date range but there may not be entries
for all dates within that range in the table. I still need to return all
dates even if there is no data. Hence my previous
This works great counting my entries and putting then grouping by[vbcol=seagreen]
I really appreciate your input. Have been stuck for a bit and need to get
out of this hole!
Thanks.
"David Gugick" wrote:

> Andrew Jurgens wrote:
>
> Sounds like you need an OUTER JOIN. I don't know your data, so use an
> outer join against the table that may not have a foreign key
> relationship. If you wanted all Accounts from the accounts table even if
> some of the accounts didn't have a comment in the comments table, you
> would:
> From Accounts Outer Join Comments on Accounts.id = Comments.id
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||On Thu, 14 Oct 2004 01:59:20 -0700, Andrew Jurgens wrote:

>Thanks David,
>My problem is that I am querying a date range but there may not be entries
>for all dates within that range in the table. I still need to return all
>dates even if there is no data. Hence my previous
Hi Andrew,
Looks like you need a calendar table.
See http://www.aspfaq.com/show.asp?id=2516.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Retriving data from SQL text field

I have a text column in my db which stores more than 8000 characters. When I retrieve the values from the column in query analyzer (I have set the output buffer to 8000), it only shows me first 8000 chars only. How do I display all the text from the text field?Use a parameter.|||SQL QA does not allow declaring local variables with text type. I am trying to pull the SQL text filed's all the text. Declaring or converting to varchar will limit it to 8000 characters only.

An example will be very handy..

Thxsql

Retrieving values from dynamic SQL

Anyone know if it's possible to retrieve a parameterized value from dynamically executed SQL?

Code Example

Declare @.table varchar(25)
Declare @.somevalue varchar(3)
Set @.table = 'sometable'
Set @.somevalue = 'somevalue'
Declare @.sqlBuild varchar(2000)
Set @.sqlBuild = 'DECLARE @.return varchar(3); ' +
 ' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
exec (@.sqlBuild)

i want to be able to extract the value of @.return for later use. this procdure works fine but I need to grab that value somehow.

Any suggestions?put a the end of your stored procedure select @.return ...
Use a ExecuteScalar in your code and it will be returned ...|||i'm not quite sure what you mean...

can you give me an example?|||You will need to use sp_executesql in order to capture the OUTPUT parameter from a dynamic SQL statement. Something like this (note that your @.sqlBuild needs to be of type nvarchar):

DECLARE @.table varchar(25), @.somevalue varchar(3), @.return integer, @.sqlBuild nvarchar(4000)
SELECT @.table = 'sometable', @.somevalue = 'somevalue'
SELECT @.sqlBuild = ' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
EXEC sp_executesql @.sqlBuild, N'@.return integer OUTPUT', @.return OUTPUT

Terrisql

Retrieving values from a subreport to Body of Parent Report

Is it possible to retrieve the value of a subreport's field or control from the parent report? I'm doing some grouping in the subreport and need to retrieve the group by's data value from the subreport.

Also, is there a way to repeat the main page's body when subreport has a page break? ie you page break on some thing in the subreport and need the body and head of the parent report to repeat on subsequent pages.

Thanks,
Garick

I want to do something similar.

I want the value of the amount of records retrieved in the sub report.

The table row that the sub report is in needs to be hidden if the value is not greater than 1.

Can it be done?

|||

Jabuka

I think that there's a simple way to do what you want with creating the same dataset that you have in your subreport in the parent one. Then you can evalute the field in your visibility expression.

Hope it helps you

|||

Another way is to create a simple assembly (any language in .Net) and have a static (or shared) variable in it. Set the value of this variable in your subreport and refer to that in your main report.

The only problem with this approach is concurrency as you are using a static variable.

Shyam

Retrieving selected gridview column values for SQLDatasource asp:controlparameters

Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@." at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005):

<

asp:GridViewID="gvLoadEditProductPrices"runat="server"AutoGenerateColumns="False"AllowSorting="True"DataSourceID="SqlDataSource1"DataKeyNames="ProductID"><Columns><asp:CommandFieldShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"HeaderStyle-BackColor="white"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="Product"HeaderText="Product"SortExpression="Product"ReadOnly="True"/><asp:BoundFieldDataField="ProductCat"HeaderText="ProductCat"SortExpression="ProductCat"ReadOnly="True"/><asp:BoundFieldDataField="VarRate"HeaderText="VarRate"SortExpression="VarRate"/><asp:BoundFieldDataField="loadid"HeaderText="loadid"InsertVisible="False"ReadOnly="True"SortExpression="loadid"/><asp:BoundFieldDataField="loadamount"HeaderText="loadamount"SortExpression="loadamount"ReadOnly="True"/><asp:BoundFieldDataField="ProductCol"HeaderText="ProductCol"SortExpression="ProductCol"ReadOnly="True"/><asp:BoundFieldDataField="PageID"HeaderText="PageID"SortExpression="PageID"ReadOnly="True"/></Columns></asp:GridView>

and the sqldatasource's info:

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>"ProviderName="System.Data.SqlClient"SelectCommand="spGetLoadEditProductPrices"SelectCommandType="StoredProcedure"UpdateCommand="spUpdateProductPrices"UpdateCommandType="StoredProcedure"><UpdateParameters><asp:ControlParameterName="ProductID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter><asp:ControlParameterName="LoadID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter><asp:ControlParameterName="PageID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter><asp:ControlParameterName="ProductCol"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter><asp:ControlParameterName="NewRate"Type="Double"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter></UpdateParameters><SelectParameters><asp:ControlParameterControlID="ddlEstLoadsPerAcre"Name="LoadID"PropertyName="SelectedValue"Type="Int32"/><asp:ControlParameterControlID="txtEditType"Name="PageName"PropertyName="Text"Type="String"/></SelectParameters></asp:SqlDataSource>

TIA,

John

Nevermind...after hours of testing many different combinations and scenarios, I found that I had to add each field that the control parameter needs to reference in the gridview to the "DataKeyNames" property in the gridview. But, any field I wanted to be updated thru the gridview edit had to use the PropertyName="SelectedValue" as opposed to the PropertyName="SelectedDataKey.Values('fieldname')". I also had to use single quotes for the field name.

John

Retrieving Scalar or Calculated Values from Stored Procedures with C#

I am trying to build an Sql page hit provider. I am having trouble getting a count back from the database. If I use ExecuteScalar it doesn't see any value in the returned R1C1. If I use ExecuteNonQuery with a @.ReturnValue, the return value parameter value is always zero. Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#. My table has rvPathName, userName and a date. I have the AddWebPageHit method working so I know data connection and sql support code in provider is working. I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#. Any help with this will be greatly appreciated.

We're not going to be able to help without seeing the code, both the C# code and the stored procedure code.|||

Here you go. I worked on this for more than 8 hours so know that you help is greatly appreciated!!!

CREATE PROCEDURE dbo.WebPageHits_CountByWebPageVPathName @.webPageVPathName nvarchar(256)

AS

DECLARE @.count int

SELECT @.count = COUNT(WebPageHitId)

FROM dbo.WebPageHits

WHERE WebPageVPathName = @.webPageVPathName

RETURN (@.count)

GO

publicoverrideint GetWebPageHitCount(string webPageVPathName)

{

SecUtility.CheckParameter(ref webPageVPathName,true,false,true, 256,"webPageVPathName");

SqlConnectionHolder connectionHolder =null;

SqlConnection connection =null;

int webPageHitCount = 0;

try

{

try

{

connectionHolder =SqlConnectionHelper.GetConnection(_sqlConnectionString,true);

connection = connectionHolder.Connection;

CheckSchemaVersion(connectionHolder.Connection);

SqlCommand cmd =newSqlCommand("dbo.WebPageHits_CountByWebPageVPathName", connection);

cmd.CommandType =CommandType.StoredProcedure;

cmd.CommandTimeout = CommandTimeout;

SqlParameter p =newSqlParameter("@.ReturnValue",SqlDbType.Int);

p.Direction =ParameterDirection.ReturnValue;

cmd.Parameters.Add(p);

cmd.Parameters.Add(CreateInputParam("@.webPageVPathName",SqlDbType.VarChar, webPageVPathName));

cmd.ExecuteNonQuery();

webPageHitCount = GetReturnValue(cmd);

}

finally

{

if (connectionHolder !=null)

{

connectionHolder.Close();

connectionHolder =null;

}

}

}

catch

{

throw;

}

return webPageHitCount;

}

|||

It's a little tough, as you are using custom methods such as GetReturnValue and CreateInputParam.

My gut instinct is that you are having a problem because youare both using the wrong data type for the @.webPageVPathName as well asnot specifying the length. The lack of length specification is mostlikely causing the trouble; if you don't specify it then a length of 1is used. Use the nvarchar datatype and a length of 256 in your C#code, and I think you will have better luck.

I will tell you that it's a better practice to use Output parameters to return values from a stored procedure rather than a ReturnValue. Return values are typically used to communicate success or failure; using them in the manner you are attempting just because the value you want to communicate back to the calling code happens to be an integer can be seen as "cheating". Use an Output parameter instead.

|||

You are right about my cheating. Sometimes you have to hear things from somebody else to actually realize it even though it's right in front of your face.

I got this to work by following the aspnet profile provider code. The provider uses this stored procedure.

aspnet_Profile_GetCountOf...

If just does SELECT COUNT(*) FROM ... with no RETURN statement. Then the C# uses ExecuteScalar then dim o as object = cmd then if o <> null return cmd. My function returns an int so somehow the value gets from the cmd object to the function's return value. I don't know but it's easier.

|||

mtsonic:

If just does SELECT COUNT(*) FROM ... with no RETURN statement. Then the C# uses ExecuteScalar then dim o as object = cmd then if o <> null return cmd. My function returns an int so somehow the value gets from the cmd object to the function's return value. I don't know but it's easier.

ExecuteScalar is an OK way to accomplish what you need. Glad you got it working :-)

sql

Retrieving rows with minimum values within a column

I am attempting to retrieve only the rows in a table that have the minimum
value of the adr_numb column for each occurrence of the adr_code column. Whe
n
I run the below query, it still returns all the rows within the table. Any
suggestions on how to restructure this query to obtain the correct results
would be appreciated.
Select *
From dbo.addr_tbl a
Where exists (Select Min(adr_numb)
From dbo.addr_tbl b
Where a.adr_code = b.adr_code)
Current rows:
adr_code adr_numb
4M3IWNFP51 1
4M3IWNFP51 2
9UAZZRD5U1 1
C0VCLF5001 1
C0VCLF5001 2
DKZKR1ZFH1 2
F9D599KH01 1
F9D599KH01 2
F9D599KH01 3
FAPCM71YH1 2
FAPCM71YH1 3
Desired Results:
adr_code adr_numb
4M3IWNFP51 1
9UAZZRD5U1 1
C0VCLF5001 1
DKZKR1ZFH1 2
F9D599KH01 1
FAPCM71YH1 2Select * from table where adr_numb in
(select min(adr_numb) from table group by adr_code)
Madhivanan|||Select adr_code,Min(adr_numb)
From dbo.addr_tbl
group by adr_code|||and if the table has more than the columns you had shown, it should go like
this
Select * from table a where adr_numb =
(select min(adr_numb) from table b where a.adr_code = b.adr_code)
P.S: Madhivanan, Can you check your query. I feel it might give an erronous
output.|||Try,
select *
from dbo.addr_tbl as a
where not exists (
select *
from dbo.addr_tbl as b
where b.adr_code = a.adr_code and b.adr_numb < a.adr_numb
)
go
AMB
"MACason" wrote:

> I am attempting to retrieve only the rows in a table that have the minimum
> value of the adr_numb column for each occurrence of the adr_code column. W
hen
> I run the below query, it still returns all the rows within the table. Any
> suggestions on how to restructure this query to obtain the correct results
> would be appreciated.
>
> Select *
> From dbo.addr_tbl a
> Where exists (Select Min(adr_numb)
> From dbo.addr_tbl b
> Where a.adr_code = b.adr_code)
>
> Current rows:
> adr_code adr_numb
> 4M3IWNFP51 1
> 4M3IWNFP51 2
> 9UAZZRD5U1 1
> C0VCLF5001 1
> C0VCLF5001 2
> DKZKR1ZFH1 2
> F9D599KH01 1
> F9D599KH01 2
> F9D599KH01 3
> FAPCM71YH1 2
> FAPCM71YH1 3
>
>
> Desired Results:
> adr_code adr_numb
> 4M3IWNFP51 1
> 9UAZZRD5U1 1
> C0VCLF5001 1
> DKZKR1ZFH1 2
> F9D599KH01 1
> FAPCM71YH1 2
>|||You almost had it, but you need to change the exists to equals.
When you use exists it is returning all rows where adr_code exists in at
least one other row in the table with a minimum adr_numb, which is true for
all rows expect those where adr_numb is null.
If you use equals, along with the "join" that you already have in your
subquery, you will only get back rows where the adr_numb is the minimum
value for each adr_code.
Select *
From dbo.addr_tbl a
Where adr_numb = (Select Min(b.adr_numb)
From dbo.addr_tbl b
Where a.adr_code = b.adr_code)
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:0B11BBE1-7345-45FD-9B82-7D2670596C69@.microsoft.com...
> I am attempting to retrieve only the rows in a table that have the minimum
> value of the adr_numb column for each occurrence of the adr_code column.
When
> I run the below query, it still returns all the rows within the table. Any
> suggestions on how to restructure this query to obtain the correct results
> would be appreciated.
>
> Select *
> From dbo.addr_tbl a
> Where exists (Select Min(adr_numb)
> From dbo.addr_tbl b
> Where a.adr_code = b.adr_code)
>
> Current rows:
> adr_code adr_numb
> 4M3IWNFP51 1
> 4M3IWNFP51 2
> 9UAZZRD5U1 1
> C0VCLF5001 1
> C0VCLF5001 2
> DKZKR1ZFH1 2
> F9D599KH01 1
> F9D599KH01 2
> F9D599KH01 3
> FAPCM71YH1 2
> FAPCM71YH1 3
>
>
> Desired Results:
> adr_code adr_numb
> 4M3IWNFP51 1
> 9UAZZRD5U1 1
> C0VCLF5001 1
> DKZKR1ZFH1 2
> F9D599KH01 1
> FAPCM71YH1 2
>|||Thanks, Jim. Worked great. Should have posted sooner as I have been trying t
o
resolve this for the last couple days.
"Jim Underwood" wrote:

> You almost had it, but you need to change the exists to equals.
> When you use exists it is returning all rows where adr_code exists in at
> least one other row in the table with a minimum adr_numb, which is true fo
r
> all rows expect those where adr_numb is null.
> If you use equals, along with the "join" that you already have in your
> subquery, you will only get back rows where the adr_numb is the minimum
> value for each adr_code.
> Select *
> From dbo.addr_tbl a
> Where adr_numb = (Select Min(b.adr_numb)
> From dbo.addr_tbl b
> Where a.adr_code = b.adr_code)
> "MACason" <MACason@.discussions.microsoft.com> wrote in message
> news:0B11BBE1-7345-45FD-9B82-7D2670596C69@.microsoft.com...
> When
>
>|||Thanks Omnibuzz
Madhivanan

Retrieving ntext column value skips values.

I have the unfortunate task of dealing with an ntext column. I have to updat
e
part of the contents but first I was just trying to display the contents in
Query Analyzer using a script from page 61-62 of the Guru's transact sql
book. Well, the script does print out a few characters, skip a few, print a
few, skip a few, . . . It appears that accessing an ntext column is quite
different than accessing a text column. BOL is not very helpful on this.
Also, READTEXT only displays a few characters at a time no matter what the
chunk size is set to, so I can't tell if the problem is Query analyzer or
something else. Does anyone have a source for useful info in dealing with
ntext?
thanks,
MichaelSnake wrote:
> I have the unfortunate task of dealing with an ntext column. I have
> to update part of the contents but first I was just trying to display
> the contents in Query Analyzer using a script from page 61-62 of the
> Guru's transact sql book. Well, the script does print out a few
> characters, skip a few, print a few, skip a few, . . . It appears
> that accessing an ntext column is quite different than accessing a
> text column. BOL is not very helpful on this. Also, READTEXT only
> displays a few characters at a time no matter what the chunk size is
> set to, so I can't tell if the problem is Query analyzer or something
> else. Does anyone have a source for useful info in dealing with
> ntext?
> thanks,
> Michael
Can you post the code you are running. There shouldn't be a problem
reading the data from an ntext column.
I tried a test using the pubs.pub_info table which I recreated as
pub_info2 using an ntext column. QA does have a display setting for the
max number of character per column to display. Or it could be that there
are line breaks in the ntext that are not displaying correctly in the QA
grid. Try using text output and see if that helps.
create table dbo.pub_info2 (pub_id char(4) not null, logo image null,
pr_info ntext)
go
ALTER TABLE [dbo].[pub_info2] ADD CONSTRAINT [UPKCL_pubinfo2] PRIMARY
KEY CLUSTERED
(
[pub_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pub_info2] ADD FOREIGN KEY
(
[pub_id]
) REFERENCES [publishers] (
[pub_id]
)
GO
insert into pub_info2 select * from pub_info
go
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info2 pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
select @.ptrval
READTEXT pub_info2.pr_info @.ptrval 0 25
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com|||>I have the unfortunate task of dealing with an ntext column. I have to
>update
> part of the contents but first I was just trying to display the contents
> in
> Query Analyzer
See http://www.aspfaq.com/2445 for some help on using UPDATETEXT.
I don't think you will need READTEXT to do what you want, see the following
(note though that it will create bogus carriage returns every 4000th
character, but existing control characters (CHAR(10,13,9 etc)) will still be
displayed correctly):
CREATE TABLE data
(
id INT UNIQUE,
txt TEXT
)
GO
SET NOCOUNT ON
DECLARE @.foo NVARCHAR(4000)
SELECT @.foo = REPLICATE('a',4000)
-- make one far > 4000 and a small one
EXEC('INSERT data(id,txt) SELECT 1,N'''+@.foo+@.foo+@.foo+@.foo+@.foo+'''')
INSERT data(id,txt) SELECT 2,N'foobar'
DECLARE
@.dLen INT,
@.nRows INT,
@.i INT,
@.rowID INT,
@.curLine NVARCHAR(4000)
SET @.rowID = 1 -- change this to see the other result
SELECT
@.i = 0,
@.dLen = DATALENGTH(txt),
@.nRows = (@.dLen / 4000) + 1
FROM data WHERE id=@.rowID
WHILE @.i < @.nRows
BEGIN
SELECT @.curLine = SUBSTRING(txt, (4000*@.i)+1, 4000) FROM data WHERE id =
@.rowID
PRINT @.curLine
SET @.i = @.i + 1
END
DROP TABLE data

Retrieving multiple values from stored procedure: parameter and recordset

Hello,

The problem below appears to be a very common problem, but despite having tried every solution I can find offered online, the solution continues to elude me. Any tips you could offer would bemuchappreciated.

Problem: I'm successfully retrieving a recordset from a SQL Server 2000 stored procedure, but don't know how to retrieve an Output Parameter as well.

Components:

a simple Web Form with a Text Box (txtOutput) and a GridView (dgOutput). (Visual Web Developer Express 2005)
a simple stored procedure which - when executed in the Studio Output window - returns the correct output.Running [dbo].[usp_Test] ( @.Region = <NULL>, @.StartDate = <NULL> ).

State Total
-------- ----
ACT 27
NSW 26
NT 6
QLD 20
SA 44
TAS 17
VIC 28
WA 48
No rows affected.
(8 row(s) returned)
@.StartDate = 27/11/2006
@.RETURN_VALUE = 0
Finished running [dbo].[usp_Test].
the code behind the form, to call the stored proc and bind the grid to the output of the stored proc. 'Already declared and populated strConn and strSelect
Dim conn As New SqlConnection(strConn)
Dim storedProc As New SqlCommand
With storedProc
.CommandType = CommandType.StoredProcedure
.CommandText = strSelect
.Connection = conn
End With
Dim returnParam As SqlParameter
returnParam = New SqlParameter("@.StartDate", SqlDbType.DateTime, 30)
returnParam.Direction = ParameterDirection.Output
storedProc.Parameters.Add(returnParam)

Dim dr As SqlClient.SqlDataReader
conn.Open()
dr = storedProc.ExecuteReader
dgOutput.DataSource = dr
dgOutput.DataBind()
dr.Close()
conn.Close()

Situation:

the GridView successfully displays the 8-row recordsetI don't know how to display the Output Parameter ("@.StartDate") in the Text Box. Without success I've tried variations on:
txtOutput.Text = storedProc.Parameters("@.StartDate").Value.ToStringtxtOutput.Text = returnParam.Value.ToStringusing dr.NextResultIf possible, I would like to be able to display the 'number of row(s) returned', which is already being returned (as printed above).

Any assistance would be sincerely appreciated - it's been a long day of trying. Thank you for your time.

- Sarah

You need to declare the @.startDate as OUTPUT parameter in the stored proc. You could also return the rowcount but that would have to be another OUTPUT parameter.

CREATE PROC dbo.usp_test

@.StartDate datetime OUTPUT,

@.rowcount int OUTPUT

AS

BEGIN

--Your SELECT statement here

SELECT ....

END

Here's how you could call an OUTPUT parameter:

'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.StartDate"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.datetime
mycommand.Parameters.Add(myParam)

Dim startDate as DateTime

startDate = mycommand.Parameters("@.StartDate").Value)

|||

Hi Dinakar,

Thank you for looking at my problem Unfortunately the same problem is still happening.

The stored proc is already returning the output parameter with the correct value of27/11/2006(as shown in the output in my first post), but any time I try to retrieve it, it seems to have a null value.

In my VB code, I've changed my parameter creation to match your suggested code.

When I use the new DateTime variable as you suggest, and set it tostoredProc.Parameters("@.StartDate").Value, it's value is set to 1/01/0001 12:00:00 AM .

Any suggestions?

Thank you again for your time.

|||Can you post your stored proc code?|||

CREATE PROCEDURE usp_Test(
@.Region AS VARCHAR(8) = NULL, --Optional
@.StartDate DATETIME OUTPUT
)
AS

BEGIN
SET @.StartDate =DATEADD(Week, DATEDIFF(Week,0,GETDATE()), 0) --Monday of current week

SELECT Loc.LocationState AS State,
COUNT(Appt.ApartmentID) AS Total
FROM Apartment AS Appt
LEFT JOIN ReportLocation AS Loc ON Appt.LocationID = Loc.LocationID
WHERE (( Appt.InsertedOn >= @.StartDate) AND
(Appt.InsertedOn <= GETDATE()) AND
((@.Region IS NULL) OR (Loc.LocationState LIKE @.Region)) AND
(Appt.isBreak = 0) AND
(Appt.isBlock = 0) AND
(Appt.isCancelled = 0)
)
GROUP BY Loc.LocationState
ORDER BY Loc.LocationState
END
GO

|||Is there anything else it might be? I'm happy to post any of my code, which might expose the cause.|||

Allright, after a long long time, I wrote up a quick windows app. I used a datagridview and called a stored proc which returns a result set through a SELECT statement. The stored proc also has an OUTPUT parameter. I was able to retrieve both.

alter proc dbo.test_dk @.fileidint, @.rowcountint output as begin select fileid, orderid, creationdatefrom servicefilewhere fileid = @.fileidselect @.rowcount =@.@.rowcountend

And the code for the windows app is:

Dim objconAs SqlConnection objcon =New SqlConnection("server=...")Try If objcon.State = 0Then objcon.Open()Dim dsAs New DataSetDim daAs SqlDataAdapter =New SqlDataAdapter da.SelectCommand =New SqlCommand da.SelectCommand.CommandType = CommandType.StoredProcedure da.SelectCommand.CommandText ="test_dk"Dim sqlparamAs New SqlParameter("@.fileid", SqlDbType.Int) sqlparam.Value = 7035833Dim sqlparam2As New SqlParameter("@.rowcount", SqlDbType.Int) sqlparam2.Direction = ParameterDirection.Output da.SelectCommand.Parameters.Add(sqlparam) da.SelectCommand.Parameters.Add(sqlparam2) da.SelectCommand.Connection = objcon da.Fill(ds,"servicefile") DataGridView1.DataSource = ds DataGridView1.DataMember ="servicefile" TextBox1.Text = da.SelectCommand.Parameters("@.rowcount").ValueCatch excAs Exception MsgBox(exc)Finally If objcon.State = ConnectionState.OpenThen objcon.Close()End If'objCon.Dispose()End Try

|||

That worked beautifully - you're a champion, Dinakar! Some day they'll sing songs about you.

Thank you very much for your time and help.

|||You are welcome.|||

ndinakar:

You need to declare the @.startDate as OUTPUT parameter in the stored proc. You could also return the rowcount but that would have to be another OUTPUT parameter.

CREATE PROC dbo.usp_test

@.StartDate datetime OUTPUT,

@.rowcount int OUTPUT

AS

BEGIN

--Your SELECT statement here

SELECT ....

END

Here's how you could call an OUTPUT parameter:

'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.StartDate"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.datetime
mycommand.Parameters.Add(myParam)

Dim startDate as DateTime

startDate = mycommand.Parameters("@.StartDate").Value)

|||

I'm trying to follow your example but on this line..

Your line

TextBox1.Text = da.SelectCommand.Parameters("@.rowcount").Value

My line

lbl_ERROR.Text = da.SelectCommand.Parameters("@.RETURNError").Value;

I get a build error.. Error 1 'System.Data.SqlClient.SqlCommand.Parameters' is a 'property' but is used like a 'method'

sql

Retrieving multiple values from one field in SQL Server for use in multiple columsn in Reports

I am trying to create a report using Reporting Services.

My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.

I can currently get one value but how to get the information I need to be able to use in my reports.

So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.

Any help would be appreciated.

Thanks.

I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.

What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report

As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.

As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem

Thanks for the suggestions that were made, I apoligize for not making the problem clearer.

Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.

--Pulls the Service Opportunity

SELECT cs.value AS "Service Opportunity"

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE ca.name = 'Service Opportunity'

--Pulls the Number of Hours

SELECT cs.value AS 'Number of Hours'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Num of Hours'

--Pulls the Person Grade Level

SELECT cs.value AS 'Grade'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Grade'

--Pulls the Person Number, First and Last Name and Grade Level

SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"

FROM student s

INNER JOIN cperson cs ON cs.personid = s.personid

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE cs.value =(SELECT cs.value AS 'Grade'

WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')

There are a number of ways to solve this. Here are a few options:

If each value is in its own row, like this:

Name Value

Hours 100

Type AAA

Grade C

the SQL Pivot statement can be used in your query to pivot the rows to columns, so the result looks like:

Hours Type Grade

100 AAA C

If the values are concatenated in a single column, like:

Column

100;AAA;C

then you can use the Split VB function in the report to split the string into its component parts, and put them into fields. To get the values, you can use the following:

=Split(Fields!Column.Value, ";")(0)

=Split(Fields!Column.Value, ";")(1)

=Split(Fields!Column.Value, ";")(2)

|||

It is quite clear from your post that you have all the data in one column in a database table. If you have access to the query or the stored procedure, try to accomplish this in the SQL query level itself because calculating it in reporting service is costlier than doing it in the query level (better if there is a stored procedure becasue it is compiled and faster). Try using this sql query (assuming that your field separator is "," and the order is hours, type and then grade):

CASE WHEN CHARINDEX(',', Column1) > 0

SUBSTRING(Column1, 1, CHARINDEX(',', Column1)-1) AS 'Hours'

END,

CASE WHEN CHARINDEX(',', Column1) > 0

CASE WHEN CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1))) > 0

SUBSTRING(Column1, CHARINDEX(',', Column1)+1, CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1)))-1) AS 'Type'

END

END,

CASE WHEN CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1))) > 0

RIGHT(Column1, CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1)))+1) AS 'Grade'

END

If your still want to use reporting services, create 3 calculated dataset fields as follows:

Hours: IIf(Split(Fields!Column1.Value, ",").UpperBound>=0, Split(Fields!Column1.Value, ",")(0), "")

Type: IIf(Split(Fields!Column1.Value, ",").UpperBound>=1, Split(Fields!Column1.Value, ",")(1), "")

Grade: IIf(Split(Fields!Column1.Value, ",").UpperBound>=2, Split(Fields!Column1.Value, ",")(2), "")

Shyam

|||

John,

Thanks for the suggestion. I guess I didn't make myself very clear on to what I was attempting. I modified my thread and enclosed a sample of what I am attempting to do.

Thanks again for the help

|||

Shyam,

Thanks for the advice. I guess I didn't make myself clear enough on what I was attempting to do. I have modifed my thread and enclosed a sample of the code I am trying to make work.

Thanks again

|||

Use the following query to get all values at one shot:

SELECT cs1.value AS 'Service Opportunity', cs2.value AS 'Number of Hours', cs3.value AS 'Grade'

FROM Cstudent cs1

INNER JOIN cattribute ca ON ca.attributeid = cs1.attributeid

AND ca.name = 'Service Opportunity'

INNER JOIN Cstudent cs2 ON ca.attributeid = cs2.attributeid

AND ca.name = 'Num of Hours'

INNER JOIN Cstudent cs3 ON ca.attributeid = cs3.attributeid

AND ca.name = 'Grade'

Thanks,

Shyam

|||

Shyam,

I've been working with what you suggested but so far with no luck. It pulls the column headings but there is no information in the columns. I'm looking to see if I'm missing something somewhere.

Thanks for the advice though, I'll keep working at it and see what I can come up with.

Thanks

Wayne

|||

Maybe not all records are available in Cstudent, so use this query:

SELECT cs1.value AS 'Service Opportunity', cs2.value AS 'Number of Hours', cs3.value AS 'Grade'

FROM cattribute ca

LEFT OUTER JOIN Cstudent cs1

ca ON ca.attributeid = cs1.attributeid

AND ca.name = 'Service Opportunity'

LEFT OUTER JOIN Cstudent cs2 ON ca.attributeid = cs2.attributeid

AND ca.name = 'Num of Hours'

LEFT OUTER JOIN Cstudent cs3 ON ca.attributeid = cs3.attributeid

AND ca.name = 'Grade'

Shyam

|||

Shyam

Thanks for the help, Just have to do some more tweaking on my end but looks like it might give me what I'm looking for.

Thanks Again for the Help

Wayne

|||

So, can you mark the post as answer?

Shyam

|||

Shyam

Thanks for all the help. This was the first post I had done one here so I apoligize for that. It's taken care of and I posted it answered.

Retrieving generated keys

I have the following scenario:
MyTable
id number entity(1, 1)
name nvarchar
sniplet of Java code:
String sql = "Insert Into MyTable (name) Values( 'MyName' )";
int updateCnt = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
The executeUpdate() method throws an java.lang.AbstractMethodError exception.
How do I get the value of "id" after running my insert statement?
The microsoft example goes through a lot of object instantiation with
prepared calls and all that I don't necessarily want to complicate the
previous code by doing all that. But if I do, could someone please help me
with the conversion? I always use the above approach.
Thanks.
The MS driver is a JDBC 2.0 implementation.
Statement.RETURN_GENERATED_KEYS is a JDBC 3.0 feature. Either use a
JDBC 3.0 driver or append SELECT SCOPE_IDENTITY() to all your INSERT
statements.
Alin,
The jTDS Project.
|||If youre using SQL Server why not do the insert through a stored
procedure?...this way you can use the Scope_Identity function to return
back the value of the newly inserted identity to Java..let me know if
you need some sample code...
|||Jimbo wrote:
> If youre using SQL Server why not do the insert through a stored
> procedure?...this way you can use the Scope_Identity function to
return
> back the value of the newly inserted identity to Java..let me know if
> you need some sample code...
You can use scope_identity() without a stored procedure. Just execute
something like "INSERT ... SELECT scope_identity()". The problem is
that the JDBC code won't work across databases.
Alin.

Retrieving Default Values for Parameters in a Stored Procedure.

I'm generating a list of parameters needed by stored procedures, andI'd like to know which ones have default values assigned to them.
To retrieve the parameter information I use:

sp_sproc_columns @.Procedure_Name='InsertUser''

However, the column that is supposed to give the default value,'COLUMN_DEF' always returns as NULL, even when that column has adefault value assigned to it.
i.e.

CREATE PROCEDURE InsertUser
@.UserID INT = 10,
....

And then if I do a sp_sproc_columns @.Procedure_Name='InsertUser'', the COLUMN_DEF value for the @.UserID column is still NULL.
Does anyone know what I'm doing wrong and how I can retrieve the default value?
Thanks

It looks like you will have to parse the stored procedure code to get the default parameter info:

http://www.aspfaq.com/show.asp?id=2463
Hope this helps.
Chris R. Timmons
--------
C.R. Timmons Consulting, Inc.
http://www.crtimmonsinc.com/

retrieves the information about the pipeline components

Dear Experts,

I can look the values of the proprieties in each PipelineComponentInfo, for example:

ComponentType: Transform
CreationName: DTSTransform.Merge.1
Description: Merge Transformation
FileName: C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\TxMerge.dll
FileNameVersionString: 2000.90.1049.0
IconFile: C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\TxMerge.dll
IconResource: -201
ID: {08AE886A-4124-499C-B332-16E3299D225A}
Name: Merge
NoEditor: False
ShapeProgID:
UITypeName: Microsoft.DataTransformationServices.....

but I don't know what means the proprieties: FileName, FileNameVersionString, IconFile, IconResource, NoEdit, ShapeProgID and UITypeName...

Can anyone helps Me?

Thanks

Francesco

What information are you looking for.

The properties are those used by the runtime and designer to use the component.

|||

I wanna know the meaning of the words:

IconFile, IconResource, NoEdit, ShapeProgID and UITypeNam... .

I already know which are the values of these informations

|||

Please see the PipelineComponentInfo class documentation in the programming reference. Use the BOL Index to locate it quickly.

-Doug

|||

Good IDEA!

This is link

Sorry, but in this period I'm tense and very tired...

Thanks a lot

Francesco

Retrieve Values from SQL Server into Winforms

Hi all,

I want to insert information like SQL Server Version, current sql server user etc into a form, How will I achieve this? I have followed this question at http://www.vbforums.com/showthread.php?t=357605 ,but I havent had an answer to my question yet. Please help, I am stuck and can't go on with my application until I have figured this out.

Thanks alot in advance

Rudi Groenewald

You can use various SQL Server Functions for this (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7oqb.asp).

Here's an example:

select serverproperty('ProductVersion') as ProductVersion
serverproperty('Edition') as Edition;

On my computer this returns:

Product Version Edition
-
9.00.1116 Express Edition

Hope this helps,
Josh Lindenmuth

Retrieve values from child table

Hello there,

I need to get the last value (status) from a child table. I try to simplify the problem with the following example.

Create Table Users
(
UserId int,
Lastname nvarchar(50)
)

Create Table UserStatus
(
UserId int,
Date datetime,
StatusId int
)

Create Table Status
(
StatusId int
Status nvarchar(50)
)

A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized.
Now I want to know which users are in progress (2) but a simple select statement like:

Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2

Will not return the wanted records because all authorized Users have been in this status.

I hope you understand the problem and can help me out.

Thx in advance.

Etinuzso you want the last names of users that stopped at status 2?

Select Users.LastName From Users Inner Join (Select Max(StatusID) As MaxStatusID, UserID, UserStatus UserID) As LimitedStatus On Users.UserID = LimitedStatus.UserID WHERE MaxStatusID = 2

Or something to that effect.|||Thx KraGIE, i suppose this is the right direction. Only the values are not in a specific order (and can not be set this way). So the example was not right.
So a user can be Authorized (3) and later set to In progress (2). However in the table there is a ID.

Create Table UserStatus
(
Id int, (identifier)
UserId int,
Date datetime,
StatusId int
)

At this moment I havent figured out how to use

Select Users.LastName From Users Inner Join (Select Max(ID) As MaxID, UserID, StatusId From UserStatus Group BY USerId, StatusId) As LimitedStatus On Users.UserID = LimitedStatus.UserID WHERE StatusID = 2|||I found my solution

SELECT
u.LastName
FROM
Users u
JOIN UserStatus us
ON u.UserId = us.UserId
JOIN
(SELECT UserId, MAX([Date]) AS last_date
FROM UserStatus
GROUP BY UserId ) AS last_status
ON u.UserId = last_status.UserId
AND us.[Date] = last_status.last_date
WHERE
us.StatusId = 2

Retrieve new values of a database

Hello, I have the view from a table that keeps incrementing with new
values, I have no control of this database, but I need to retrieve the
new values to insert it on my table (sql server) and send some
parameters of the database to another application, I'm using C# to get
access to both databases, so does anybody know of an efficient way to
retrieve only the new values of the database?

Thanks a lot

Luis Saavedra

Luis,
I don't think there is any general solution to your problem. I usually use triggers, but since you said you have no control on the db that won't work.

The only solution I can think of is to see it your table contains some identity column. If that is the case (and it often is), then you may just select data from the table with an identity column greater than the last you found. This will only work to get new additions to the db. It cannot detect deletions.

HTH
--mc

|||

the problem is that the identity column appears to be autoincremental, but is not in all of the cases, for example I have something like

1001

1002

1003

1005

1007

1006

1004

what I have considered is using a counter (of the inserted registers) in a table of my database, then use a count of all the registers of the informix table and get a top (of the difference between these two tables), the problem is that if in the lapse between the count and the selection of the registers they insert something in the table of informix, I will be missing some values, any ideas to solve this or use another solution...

Thanks

Luis Saavedra

|||

I think you're on the right track here. You might want to store the same values in that field from the source system in your own column in a table on your system. You can then compare based on a join. That might not work for you if there are a lot of values or it is updated a lot. In that case, is there a "Row Number" in your source database you can get at pprogramatically? If so, you could store the last value of that column, which will increment, in one row of your table and update based on it. Does that make sense? Something like this:

Source DB:

(Hidden RowNumber feature) IdentityColumn

(1) 1001

(2) 1002

(3) 1003

(4) 1005

(5) 1007

(6) 1006

Your DB:

Tracking Number IdentityColumn

(1) 1001

(2) 1002

(3) 1003

(4) 1005

Buck Woody

http://www.buckwoody.com