SQLdatasource delete with parameter output

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I have troublem to get parameters output to return value back. I'm using
SQLdatasource and stored procedure to get information and delete row from
gridview. I don't have any problems get information and display but when I
want to delete some row from gridview and return parameters output back with
status or some message, the parameter return dbNull value. Any ideas what is
happen? Here is my code:
-- SqlDataSourece --
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:cnFOE %>"
DeleteCommand="p_siteproject_delete_measure"
DeleteCommandType="StoredProcedure"
SelectCommand="p_siteproject_measure_installed"
SelectCommandType="StoredProcedure">

<DeleteParameters>
<asp:SessionParameter Name="SiteProjectID"
SessionField="siteprojectid" Type="Int32" />
<asp:SessionParameter Name="MeasureID"
SessionField="measureid" Type="Int32" />
<asp:Parameter Direction="InputOutput" Name="mess"
Type="string" />
<asp:Parameter Direction="InputOutput" Name="ret"
type="int16" />
</DeleteParameters>
<SelectParameters>
<asp:SessionParameter Name="SiteProjectId"
SessionField="siteprojectid" Type="Int32" />
<asp:SessionParameter Name="sectorcd"
SessionField="sectorcd" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

-- Call event --
Protected Sub SqlDataSource1_Deleted(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
Handles SqlDataSource1.Deleted

Dim strMess As String
strMess = e.Command.Parameters("@mess").Value

'Dim ireturn As Integer
'ireturn = e.Command.Parameters("@ret").Value

End Sub

-- stored procedure --
I can run stored procedure and see the parameters output

ALTER PROCEDURE [dbo].[p_siteproject_delete_measure]
@SiteProjectID Int = null,
@MeasureID Int = null,
@mess VarChar(255)= null OUTPUT,
@ret Int = null OUTPUT
AS
BEGIN

SET NOCOUNT ON;

SET @mess = 'Will Delete this measure'
SET @ret = 2

END

Please help.
 
'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.
 
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.


:

'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.
 
You should be getting the return value back, however there must be something
else wrong in your code, are you getting any exceptions??
Also Note: that, your SP will always return "Will Delete this measure" ,
"3"
No matter what. You are not assigning anything to @cnt

you need to

set @cnt = (SELECT COUNT(MeasureID)
FROM Measure
WHERE SiteProjectID = @SiteProjectID
AND MeasureID = @MeasureID
AND ApprovedDt IS NULL
)
 
Also how can you be sure that your datasource is executing your stored
procedure at all
Have you done any debugging in your code.
 
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 said:
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 said:
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.


:

'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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

'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.
Click to expand...
Click to expand...
Click to expand...
 
Hi OZI,
Thank you very much.
I will try to debug and step into the stored procedure. If it is not working
I will go back to the old way by not using SqlDataSource. I though I could
use new technology in framework2 to make coding faster but instead I spent
almost 2 days try it. I knew that if I use connectionstring everything will
work fine.

Thank you again for your help.

OZI said:
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 said:
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 said:
Also how can you be sure that your datasource is executing your stored
procedure at all
Have you done any debugging in your code.


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.


:

'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.
Click to expand...
Click to expand...
Click to expand...
 
I know what you are saying it is frustrating,

Have you seen this link... some few people and few tricks, claiming that it
works.

http://forums.asp.net/p/990266/1613790.aspx

http://www.thescripts.com/forum/thread318259.html

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=199680&SiteID=1

http://geekswithblogs.net/chrishan/archive/2005/07/16/47164.aspx



Madison said:
Hi OZI,
Thank you very much.
I will try to debug and step into the stored procedure. If it is not
working
I will go back to the old way by not using SqlDataSource. I though I could
use new technology in framework2 to make coding faster but instead I spent
almost 2 days try it. I knew that if I use connectionstring everything
will
work fine.

Thank you again for your help.

OZI said:
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 said:
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.

:

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


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.


:

'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.
Click to expand...
Click to expand...
Click to expand...
 
The problem is - and I(from links given to you) figure that is a framework
bug- you can only retrieve those. The parameters that are declared in the
collection at design time (can be seen in the control markup on the
ASPX/ASCX) are not accessible on the Inserted event using this method. So
basically now I can access the Return and Output I added programmatically
during Insert but not those that were already declared. Very weird.
 
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 said:
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 said:
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 said:
Also how can you be sure that your datasource is executing your stored
procedure at all
Have you done any debugging in your code.


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.


:

'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.
Click to expand...
Click to expand...
Click to expand...
 
sorry, I did not mean a temp table, Just a real table for temporary use.


William Vaughn said:
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 said:
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 said:
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.

:

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


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.


:

'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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
sorry, I did not mean a temp table, Just a real table for temporary use.


William Vaughn said:
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 said:
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


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.

:

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


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.


:

'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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
sorry, I did not mean a temp table, Just a real table for temporary use.


William Vaughn said:
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)
-----------------------------------------------------------------------------------------------------------------------

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


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.

:

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


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.


:

'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.
Click to expand...
Click to expand...
Click to expand...
 
Hello Madison,

I have reproduced this issue on my side. I spend almost four hours to
analyze. At last, I found this is a know issue in SQLDataSource. ASP.net
Team has confirmed that.

This is specifically a bug in SqlDataSourceView.ExecuteDelete() - it
ignores the parameter types when creating the DbCommand object.
But, Insert/Update/Select do not have this problem.

You may check the following feedback.
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?Feedbac
kID=103363
[SQLDataSource Delete Command @Return_Value Parameter direction changes
when passed to a Stored Procedure]

The workaround is to change the parameter type back to output in Deleting
event.
For your case, it is as below:
protected void SqlDataSource1_Deleting(object sender,
SqlDataSourceCommandEventArgs e)
{
e.Command.Parameters["@mess"].Direction =
System.Data.ParameterDirection.InputOutput;
e.Command.Parameters["@mess"].Size = 256;
e.Command.Parameters["@ret"].Direction =
System.Data.ParameterDirection.InputOutput;
e.Command.Parameters["@ret"].Size = 10;
}

Hope this helps. Please feel free to let me know if there is anything we
can help with.We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hello Madison,

This is Wen Yuan again.

It seems you haven't tried my method, and given up using SqlDataSource
control.
I just want to check if there is anything else we can help with. We are
glad to assist you.

By the way, regarding to the SqlDataSource.DeleteParameter issue:
If you have concern on this, I suggest you also can log into Feedback
website and vote it.
The link is :
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?Feedbac
kID=103363

Have a great day.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top