Can't see data anymore!

I have had an issue when I try to return all rows or do a query on my data
tables. I get the error 8007007F unknown error
I have tried to instal the newest service releases sp3, and have had no
luck. I have upgraded my server to MS Server 2003.
If you have any ideas, I desparately need them!
Thanks
DerekTry re-install MDAC2.8.
Ayad
"dkoz" <dkoz@.discussions.microsoft.com> wrote in message
news:A58B499A-6AD8-4AA7-9E7B-C8FCD27C0B4F@.microsoft.com...
> I have had an issue when I try to return all rows or do a query on my data
> tables. I get the error 8007007F unknown error
> I have tried to instal the newest service releases sp3, and have had no
> luck. I have upgraded my server to MS Server 2003.
> If you have any ideas, I desparately need them!
> Thanks
> Derek|||Install SP3a. The SQL Server version will be 8.00.760 (SP3).
"dkoz" wrote:
> I have had an issue when I try to return all rows or do a query on my data
> tables. I get the error 8007007F unknown error
> I have tried to instal the newest service releases sp3, and have had no
> luck. I have upgraded my server to MS Server 2003.
> If you have any ideas, I desparately need them!
> Thanks
> Derek|||Still no go. I have installed the sp3a, and have done just about everything
I can think of other then reinstalling the SQL server.
Quite frustrating!
"Richard_SQL" wrote:
> Install SP3a. The SQL Server version will be 8.00.760 (SP3).
>
> "dkoz" wrote:
> > I have had an issue when I try to return all rows or do a query on my data
> > tables. I get the error 8007007F unknown error
> > I have tried to instal the newest service releases sp3, and have had no
> > luck. I have upgraded my server to MS Server 2003.
> >
> > If you have any ideas, I desparately need them!
> > Thanks
> > Derek|||Did you try re-install MDAC2.8?
Ayad
"dkoz" <dkoz@.discussions.microsoft.com> wrote in message
news:5DF77B1A-C076-46CF-B854-B830F5171B9D@.microsoft.com...
> Still no go. I have installed the sp3a, and have done just about
everything
> I can think of other then reinstalling the SQL server.
> Quite frustrating!
> "Richard_SQL" wrote:
> > Install SP3a. The SQL Server version will be 8.00.760 (SP3).
> >
> >
> >
> > "dkoz" wrote:
> >
> > > I have had an issue when I try to return all rows or do a query on my
data
> > > tables. I get the error 8007007F unknown error
> > > I have tried to instal the newest service releases sp3, and have had
no
> > > luck. I have upgraded my server to MS Server 2003.
> > >
> > > If you have any ideas, I desparately need them!
> > > Thanks
> > > Derek

Can't see data anymore!

I have had an issue when I try to return all rows or do a query on my data
tables. I get the error 8007007F unknown error
I have tried to instal the newest service releases sp3, and have had no
luck. I have upgraded my server to MS Server 2003.
If you have any ideas, I desparately need them!
Thanks
DerekTry re-install MDAC2.8.
Ayad
"dkoz" <dkoz@.discussions.microsoft.com> wrote in message
news:A58B499A-6AD8-4AA7-9E7B-C8FCD27C0B4F@.microsoft.com...
> I have had an issue when I try to return all rows or do a query on my data
> tables. I get the error 8007007F unknown error
> I have tried to instal the newest service releases sp3, and have had no
> luck. I have upgraded my server to MS Server 2003.
> If you have any ideas, I desparately need them!
> Thanks
> Derek|||Install SP3a. The SQL Server version will be 8.00.760 (SP3).
"dkoz" wrote:

> I have had an issue when I try to return all rows or do a query on my data
> tables. I get the error 8007007F unknown error
> I have tried to instal the newest service releases sp3, and have had no
> luck. I have upgraded my server to MS Server 2003.
> If you have any ideas, I desparately need them!
> Thanks
> Derek|||Still no go. I have installed the sp3a, and have done just about everything
I can think of other then reinstalling the SQL server.
Quite frustrating!
"Richard_SQL" wrote:
[vbcol=seagreen]
> Install SP3a. The SQL Server version will be 8.00.760 (SP3).
>
> "dkoz" wrote:
>|||Did you try re-install MDAC2.8?
Ayad
"dkoz" <dkoz@.discussions.microsoft.com> wrote in message
news:5DF77B1A-C076-46CF-B854-B830F5171B9D@.microsoft.com...
> Still no go. I have installed the sp3a, and have done just about
everything[vbcol=seagreen]
> I can think of other then reinstalling the SQL server.
> Quite frustrating!
> "Richard_SQL" wrote:
>
data[vbcol=seagreen]
no[vbcol=seagreen]

Can't see data anymore!

I have had an issue when I try to return all rows or do a query on my data
tables. I get the error 8007007F unknown error
I have tried to instal the newest service releases sp3, and have had no
luck. I have upgraded my server to MS Server 2003.
If you have any ideas, I desparately need them!
Thanks
Derek
Try re-install MDAC2.8.
Ayad
"dkoz" <dkoz@.discussions.microsoft.com> wrote in message
news:A58B499A-6AD8-4AA7-9E7B-C8FCD27C0B4F@.microsoft.com...
> I have had an issue when I try to return all rows or do a query on my data
> tables. I get the error 8007007F unknown error
> I have tried to instal the newest service releases sp3, and have had no
> luck. I have upgraded my server to MS Server 2003.
> If you have any ideas, I desparately need them!
> Thanks
> Derek
|||Install SP3a. The SQL Server version will be 8.00.760 (SP3).
"dkoz" wrote:

> I have had an issue when I try to return all rows or do a query on my data
> tables. I get the error 8007007F unknown error
> I have tried to instal the newest service releases sp3, and have had no
> luck. I have upgraded my server to MS Server 2003.
> If you have any ideas, I desparately need them!
> Thanks
> Derek
|||Still no go. I have installed the sp3a, and have done just about everything
I can think of other then reinstalling the SQL server.
Quite frustrating!
"Richard_SQL" wrote:
[vbcol=seagreen]
> Install SP3a. The SQL Server version will be 8.00.760 (SP3).
>
> "dkoz" wrote:
|||Did you try re-install MDAC2.8?
Ayad
"dkoz" <dkoz@.discussions.microsoft.com> wrote in message
news:5DF77B1A-C076-46CF-B854-B830F5171B9D@.microsoft.com...
> Still no go. I have installed the sp3a, and have done just about
everything[vbcol=seagreen]
> I can think of other then reinstalling the SQL server.
> Quite frustrating!
> "Richard_SQL" wrote:
data[vbcol=seagreen]
no[vbcol=seagreen]

can't return zero when no rows exist using aggregate function sum

Hi,
I'm having a heck of a time trying to get SQL Server 2000 to return a
zero (0) if no rows are returned in a sum() statement. I know in
Oracle you wrap an NVL around the whole shebang, and in Googling it
seems as if SQL Server should work this way, too. But I cannot get it
to work, I just keep getting null.
I have tried:
what I thought would work:
SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
a case statement testing for null:
SELECT CASE
WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
ELSE SUM(ISNULL(PointValue, 0))
END
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
wrapping the whole select statement in ISNULL as a subquery:
SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
and then to make sure what I was getting back was null and not empty
...something:
declare
@.n_sum numeric(6, 2)
SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
sum is: 0.00
What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
etc...but no go. I guess I could get around by using the variable and
then returning that to the VB (since this is a stored proc used to find
one value to return to the VB6 program), but that seems kind of messy.
I would appreciate any help!
TIA
PatCould you provide actual specs and desired results?
http://www.aspfaq.com/5006

> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement.|||I need to say this - I inherited this code and database structure!!!!
CREATE TABLE [tbl_Attendance_Event] (
[EventCode] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[File #] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Event Type] [numeric](9, 0) NULL ,
[EventDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Excused] [bit] NULL ,
[EventDate] [smalldatetime] NULL ,
[EventDuration] [money] NULL ,
[PointValue] [money] NULL ,
[ConsecDayCount] [numeric](9, 0) NULL ,
[Consecutive_Days] [bit] NULL ,
[EventRemove] [bit] NULL ,
[EventRemoveDate] [datetime] NULL ,
CONSTRAINT [PK_tbl_Attendance_Event] PRIMARY KEY NONCLUSTERED
(
[EventCode]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_tbl_Attendance_Event_tbl_Event_Types
] FOREIGN KEY
(
[Event Type]
) REFERENCES [tbl_Event_Types] (
[EventID]
)
) ON [PRIMARY]
GO
CREATE TABLE [tbl_Event_Types] (
[EventID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[EventDescription] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[EventPoints] [smallmoney] NULL ,
[ExcepID] [numeric](9, 0) NULL ,
[TardyLogic] [bit] NULL ,
[Minimum Minutes] [numeric](9, 0) NULL ,
[Maximum Minutes] [numeric](9, 0) NULL ,
[ExtendsYearWindow] [bit] NULL CONSTRAINT
[DF_tbl_Event_Types_ExtendsYearWindow] DEFAULT (0),
[RuinsPerfectMonthlyAttendace] [bit] NOT NULL CONSTRAINT
[DF_tbl_Event_Types_RuinsPerfectMonthlyA
ttendace] DEFAULT (0),
[LogEvent] [bit] NULL ,
[PayCodeEvent] [bit] NULL ,
[Paycode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Consecutive_Days] [bit] NULL ,
[AddToDropOffPeriod] [bit] NULL ,
CONSTRAINT [PK_tbl_Event_Types] PRIMARY KEY CLUSTERED
(
[EventID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
There are currently no rows that match the query, but I want to get
back a zero if that is the case. In other words, no rows = 0 for my
purposes. This query is in a stored procedure that returns the number
of points an employee has, so if there are no points I'd like a zero,
to do further processing with that zero. I could mess with the VB code
to make it zero there if there's no rows in the recordset, but this is
something that I've done with Oracle and is very useful in lots of
cases. I've Googled and seen responses that the
isnull(sum(isnull(expression, value)), value) should work, so I'm
missing something here!
Thanks
Pat|||If you want to see the groups where no matching rows exist by the where
clause, then you have to use [ALL] in the group by clause.
-- this is to reproduce what is happening to you
select c1, isnull(sum(1), 0)
from (select 1) as t1(c1)
where (c1 = 2)
group by c1
-- this is what you want
select c1, isnull(sum(1), 0)
from (select 1) as t1(c1)
where (c1 = 2)
group by all c1
go
AMB
"miapjp@.gmail.com" wrote:

> Hi,
> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement. I know in
> Oracle you wrap an NVL around the whole shebang, and in Googling it
> seems as if SQL Server should work this way, too. But I cannot get it
> to work, I just keep getting null.
> I have tried:
> what I thought would work:
>
> SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> a case statement testing for null:
> SELECT CASE
> WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
> ELSE SUM(ISNULL(PointValue, 0))
> END
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
>
> wrapping the whole select statement in ISNULL as a subquery:
> SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> and then to make sure what I was getting back was null and not empty
> ....something:
> declare
> @.n_sum numeric(6, 2)
> SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
> sum is: 0.00
> What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
> etc...but no go. I guess I could get around by using the variable and
> then returning that to the VB (since this is a stored proc used to find
> one value to return to the VB6 program), but that seems kind of messy.
> I would appreciate any help!
> TIA
> Pat
>|||You are dealing with two separate questions.
1. What is the sum on each row when there are records
2. What are the number of records (only important if 0)
One possible solution:
SELECT into a temp table. If the temp table has no rows, insert a row with
0. SELECT back off the temp table for your return value.
Since you have a filter on [File #], another option is to select into a
value and then test @.@.RowCount and set the value to 0 if @.@.RowCount = 0.
SOmething like:
DECLARE @.ReturnValue decimal
SELECT @.ReturnValue = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
IF (@.@.ROWCOUNT = 0)
SET @.ReturnValue = 0
SELECT @.ReturnValue
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"miapjp@.gmail.com" wrote:

> Hi,
> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement. I know in
> Oracle you wrap an NVL around the whole shebang, and in Googling it
> seems as if SQL Server should work this way, too. But I cannot get it
> to work, I just keep getting null.
> I have tried:
> what I thought would work:
>
> SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> a case statement testing for null:
> SELECT CASE
> WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
> ELSE SUM(ISNULL(PointValue, 0))
> END
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
>
> wrapping the whole select statement in ISNULL as a subquery:
> SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> and then to make sure what I was getting back was null and not empty
> ....something:
> declare
> @.n_sum numeric(6, 2)
> SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
> sum is: 0.00
> What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
> etc...but no go. I guess I could get around by using the variable and
> then returning that to the VB (since this is a stored proc used to find
> one value to return to the VB6 program), but that seems kind of messy.
> I would appreciate any help!
> TIA
> Pat
>|||> There are currently no rows that match the query, but I want to get
> back a zero if that is the case.
Assuming that PointValue will always be positive if it *has* a value, you
can do this ugly stuff...
SELECT MAX(foo) FROM
(SELECT foo = COALESCE(SUM(PointValue),0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '20050224' -- m/dd/yyyy is a terrible format
AND getdate()
UNION SELECT foo = 0) x|||>> ..get SQL Server 2000 to return a zero (0) if no rows are returned in a s
um() statement <<
SELECT COALESCE (SUM(i), 0) FROM EmptyTable;
However, what you are actually getting back is an empty set (ab nulo,
ex nulo) and the empty set is converted into a NULL. This is important
when you use EXISTS() predicates.|||>> There are currently no rows that match the query, but I want to get
back a zero if that is the case. In other words, no rows = 0 for my
purposes.
<<
then why don't you add a dummy row to every group:
select a, sum(b) from(
select a,b from some_table
union all
select distinct a, 0 from some_table) t|||Aha!
when I have the where clause limiting the sum, I don't need the group
by (right?). The original code also had the [file #] (again, not my
code, I don't make column names with spaces!!) in the select statement
even though it wasn't ever used - I don't know, I'm trying to make this
whole application work with bad code and bad underlying database
structure. In any case, I was trying to shoehorn in code to existing
code and not seeing the group by wasn't needed.
Thanks!!!!!

can't open tables -> return all rows

Hello Everyone,
I am having trouble with opening any of the tables in any
of my databases in SQL Serve 7.
The error message i get is:
"Unexpectd Error happened during this operation.
[Query] - Query Designer encountered a querey error:
Unspecified error"
Please advise.
Thank you!
MitraHi Mitra. How've you been?
This problem seems to be from a corrupted DLL in EM. You might need to
reinstall it. Good luck to you.
Alex Ivascu
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:046a01c398ee$e384b770$a401280a@.phx.gbl...
> Hello Everyone,
> I am having trouble with opening any of the tables in any
> of my databases in SQL Serve 7.
> The error message i get is:
> "Unexpectd Error happened during this operation.
> [Query] - Query Designer encountered a querey error:
> Unspecified error"
> Please advise.
> Thank you!
> Mitra
>|||Hi Alex,
I can't believe that you replied to my question. It's so
nice to hear from someone i know.
Thanks so much for your comment. I am going to reboot my
system and see if that will help else i have to reinstall
like you had suggested.
Mitra
>--Original Message--
>Hi Mitra. How've you been?
>This problem seems to be from a corrupted DLL in EM. You
might need to
>reinstall it. Good luck to you.
>Alex Ivascu
>
>"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
>news:046a01c398ee$e384b770$a401280a@.phx.gbl...
>> Hello Everyone,
>> I am having trouble with opening any of the tables in
any
>> of my databases in SQL Serve 7.
>> The error message i get is:
>> "Unexpectd Error happened during this operation.
>> [Query] - Query Designer encountered a querey error:
>> Unspecified error"
>> Please advise.
>> Thank you!
>> Mitra
>>
>
>.
>|||> I am having trouble with opening any of the tables in any
> of my databases in SQL Serve 7.
> The error message i get is:
> "Unexpectd Error happened during this operation.
> [Query] - Query Designer encountered a querey error:
> Unspecified error"
You might get more reliable results if you use Query Analyzer to view /
change data.

cant get sqldatasource to return results from a stored procedure

I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.

This is the stored procedure:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgo-- =============================================-- Author:Lance Colton-- Create date: 7/31/06-- Description:Find the contest winner-- =============================================ALTER PROCEDURE [dbo].[AppcheckContest]-- Add the parameters for the stored procedure here@.BeginDatedatetime ='1/1/2006', @.EndDatedatetime ='12/31/2006', @.SectionIDint = 10,@.WinnerIDint = 0OUTPUTASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT top 1 @.WinnerID = P.UserIDFROM cs_Posts PWHERE P.PostDateBETWEEN @.BeginDateAND @.EndDateAND P.SectionID = @.SectionIDAND P.UserID <> 2100-- I don't want to win my own contest...AND SettingsID = 1000-- This number could be different if i had more than one CS installed?AND IsApproved = 1ORDER BYNEWID()-- yes this is slow, but it works...RETURN @.WinnerIDEND

It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.

Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.

Here's the code from the aspx codebehind file:

Partial Class Contest Inherits System.Web.UI.Page Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click Dim stuff As New System.Web.UI.DataSourceSelectArguments SqlDataSource1.Select(stuff) SqlDataSource1.DataBind() lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString End SubEnd Class

As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @.WinnerID parameter, but instead it sets it to "WinnerID".

Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit.

<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server"> <asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" /> <asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar> <asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa%>" SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False"> <SelectParameters> <asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" /> <asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" /> <asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" /> <asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>   <asp:Label ID="lblWinnerID" runat="server" Text="?"></asp:Label></asp:Content>

okay well i gave up on using a sqldatasource since nobody replied.

here's my final working code using an sqlCommand object instead:

PartialClass ContestInherits System.Web.UI.PageProtected Sub btnSelectWinner_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnSelectWinner.ClickDim objCommandAs New SqlClient.SqlCommandDim objConnAs New SqlClient.SqlConnectionDim strQueryResultAs New Object objConn.ConnectionString = ConfigurationManager.ConnectionStrings("appcheck-csConnectionString-sa").ConnectionString objCommand.Connection = objConn objCommand.CommandType = CommandType.StoredProcedure objCommand.CommandText ="AppcheckContest"Dim intWinnerIDParamAs New SqlClient.SqlParameter("@.WinnerID", SqlDbType.Int)Dim DateFromParamAs New SqlClient.SqlParameter("@.BeginDate", SqlDbType.DateTime)Dim DateToParamAs New SqlClient.SqlParameter("@.EndDate", SqlDbType.DateTime) intWinnerIDParam.Direction = ParameterDirection.Output DateFromParam.Direction = ParameterDirection.Input DateToParam.Direction = ParameterDirection.Input objCommand.Parameters.Add(intWinnerIDParam) DateFromParam.Value = Calendar_From.SelectedDate DateToParam.Value = Calendar_To.SelectedDate objCommand.Parameters.Add(DateFromParam) objCommand.Parameters.Add(DateToParam) objConn.Open() objCommand.ExecuteScalar() objConn.Close()Try lblWinnerID.Text ="Winner ID: <a target='_blank' href='http://forum.appcheck.net/web/user/Profile.aspx?UserID=" & intWinnerIDParam.Value & "'>" & intWinnerIDParam.Value & "</a>" Catch ex As Exception lblWinnerID.Text = "error"End Try End SubEnd Class

<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server"> Contest Start Date:<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar> Contest End Date:<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar> <asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" /> <asp:Label ID="lblWinnerID" runat="server" Text="?"></asp:Label></asp:Content>

set ANSI_NULLSONset QUOTED_IDENTIFIERONgo-- =============================================-- Author:Lance Colton-- Create date: 7/31/06-- Description:Find the contest winner-- =============================================ALTER PROCEDURE [dbo].[AppcheckContest] @.BeginDatedatetime, @.EndDatedatetime, @.SectionIDint = 10,@.WinnerIDint = 0OUTPUTASBEGINSET NOCOUNT ON;SELECT top 1 @.WinnerID = P.UserIDFROM cs_Posts PWHERE P.PostDateBETWEEN @.BeginDateAND @.EndDateAND P.SectionID = @.SectionIDAND P.UserID <> 2100-- I don't want to win my own contest...AND SettingsID = 1000-- This number could be different if i had more than one CS installed?AND IsApproved = 1ORDER BYNEWID()-- yes this is slow, but it works...END

|||Hi,

I've had a similar issue for the last few days.
I only found 1 page that helped me solve it:
http://dotnetjunkies.com/QuickStartv20/util/srcview.aspx?path=~/aspnet/samples/data/RetValAndOutputParamsInsert.src

I hope this helps you.
Cheers
Luke

|||

The above posters link is correct. The output/return value parameters are only available during the "ED" events (Inserted/Updated/Deleted). So if you want them, you have to capture them during that event.

And just a FYI - NewID isn't random, not even close. Google a better random method, which usually involves taking the last 32 bits of newid and feeding it into the rand function.