Re: SQLdatasource delete with parameter output

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

a preivous post with links indicate a bug in the framework and some work
around people used and claimed to work.
However I did not test the work around myself at all.



"William Vaughn" <billvaNoSPAM@xxxxxxxxx> wrote in message
news:%232TiZNM$HHA.5840@xxxxxxxxxxxxxxxxxxxxxxx
A better solution might be to turn on the Profiler to see what's getting
sent to SQL Server.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"OZI" <sql_agentman@xxxxxxxxxxx> wrote in message
news:elOCILK$HHA.3716@xxxxxxxxxxxxxxxxxxxxxxx
sorry, I did not mean a temp table, Just a real table for temporary use.


"William Vaughn" <billvaNoSPAM@xxxxxxxxx> wrote in message
news:%23oa5IHK$HHA.484@xxxxxxxxxxxxxxxxxxxxxxx
Ah, huh? When a SP creates a temp table, it's dropped when the SP ends.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"OZI" <sql_agentman@xxxxxxxxxxx> wrote in message
news:eXOUn4I$HHA.5160@xxxxxxxxxxxxxxxxxxxxxxx
You might be running the wrong file or pointing to the wrong database.
start stripping your page down to the bare minimum to find this bug.
It is impossible to figure this one out when we don't have full code
and the same environment.

Also you can possibly debug and step into your stored procedure.

or you can create a temp table in SQL and in your SP add an insert to
the temp table to make sure your sp is executing. check if the insert
worked.

of possibly your SqlDataSource is being Initialized before you get your
data from your Parameters.

good luck


"Madison" <Madison@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5E3DC256-999B-4C34-911C-B1FEB5E64629@xxxxxxxxxxxxxxxx
Hi OZI,
Thank you for you reply. I forgot to set @cnt = my select when I post
the
code. I still did not get any things back. I did not get any
Exceptions from
the system. When I go
to debug mode I see that I get in Protected Sub
SqlDataSource1_Deleted(ByVal
sender As Object, ByVal e As
system.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
SqlDataSource1.Deleted and e.Command.Parameters("@mess").Value has
value with
system.DBNull
But with e.Command.Parameters("@mess") in debug has Direction = Input
I do not know what is going wrong. Or this is the bug???
Please help, thanks.

"OZI" wrote:

Also how can you be sure that your datasource is executing your
stored
procedure at all
Have you done any debugging in your code.


"Madison" <Madison@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:77F2718D-9A87-486D-B671-69655A2AF891@xxxxxxxxxxxxxxxx
Thank you for your reply.

The stored procedure, I posted just the testing to see if I can get
any
result back from SQL Server which it's working find. I do not want
to put
a
lot of coding and cannot tell which part is not working.

Here is the stored procedure that I will use it

ALTER PROCEDURE [dbo].[p_siteproject_delete_measure]

@SiteProjectID Int,
@MeasureID Int,
@mess VarChar(255) OUTPUT,
@ret Int OUTPUT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @cnt int

SELECT COUNT(MeasureID)
FROM Measure
WHERE SiteProjectID = @SiteProjectID
AND MeasureID = @MeasureID
AND ApprovedDt IS NULL

IF @cnt = 0
BEGIN
SET @mess = 'Measure has been approved...'
SET @ret = 1
RETURN
END

ELSE
BEGIN
SELECT COUNT(MeasureID)
FROM Measure
WHERE SiteProjectID = @SiteProjectID
AND MeasureID = @MeasureID
AND ImplementedDt IS NULL
IF @cnt = 0
BEGIN
SET @mess = 'Measure has been implemented...'
SET @ret = 2
RETURN
END

ELSE
BEGIN
-- My DELETE statement will go here
SET @mess = 'Will Delete this measure'
SET @ret = 3
RETURN
END
END

END


'ireturn = e.Command.Parameters("@ret").Value is commented out.
Because
it
will do nothing if the first one did not work.

I have try both to remove Null or have Null, It still not working.

Any ideas. Thanks.


"OZI" wrote:

'ireturn = e.Command.Parameters("@ret").Value is commented out.

why InputOutput when it is only Output
remove = null

Is the sp you are showing the actual sp you are using.












.

Relevant Pages