Deleting data by calling the stored procedure in the .NET

Hi, does anyone know how to delete data from the SQL database bycalling the stored procedure in the Visual Basic.NET? Because I did theDelete hyperlink bounded inside a datagrid. I have already displayedthe appointment date, time in the datagrid so I do not have to inputany values inside it. These are my stored procedures code for deleting:
ALTER PROCEDURE spCancelReservation(@.AppDate DATETIME, @.AppTime CHAR(4), @.MemNRIC CHAR(9))
AS
BEGIN
IF NOT EXISTS
(SELECT MemNRIC, AppDate, AppTime
FROM DasAppointment
WHERE (MemNRIC = @.MemNRIC) AND (AppDate = @.AppDate) AND (AppTime = @.AppTime))
RETURN -400
ELSE IF EXISTS
(SELECT MemNRIC
FROM DasAppointment
WHERE (DATEDIFF(DAY, GETDATE(), @.AppDate) < 10))
RETURN -401
ELSE
DELETE FROM DasAppointment
WHERE MemNRIC = @.MemNRIC
END
IF @.@.ERROR <> 0
RETURN @.@.ERROR
RETURN
DECLARE @.status int
EXEC @.status = spCancelReservation '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @.status
Can someone pls help? Thanks!
So what is the problem? Is the stored proc not running or are you having trouble calling the stored proc from .NET ?|||Ya, I am having trouble calling from the .NET. The stored procedure itself can work in the SQL Server database.|||The call from ASP.NET should be essentially the same as the code you had posted in this thread:http://forums.asp.net/1004793/ShowPost.aspx. Please try to get the code working and post exact problems and exact error messages.
|||Hi, these are my codes for the .NET.
Dim connStr As String = System.Configuration.ConfigurationSettings.AppSettings("SqlConnection.connectionString")
Dim dbConn As New SqlConnection
Dim dr As SqlDataReader
dbConn.ConnectionString = connStr
Try
dbConn.Open()
Dim dbCmd As New SqlCommand
dbCmd.Connection = dbConn
dbCmd.CommandType = CommandType.StoredProcedure
dbCmd.CommandText = "spCancelReservation"
Dim dbParam As SqlParameter
dbParam = dbCmd.Parameters.Add("@.AppDate", SqlDbType.DateTime)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = Session("AppDate")

dbParam = dbCmd.Parameters.Add("@.AppTime", SqlDbType.Char, 4)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = Session("AppTime")

dbParam = dbCmd.Parameters.Add("@.MemNRIC", SqlDbType.Char, 9)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = Session("NRIC")
dr = dbCmd.ExecuteReader()
Finally
'Re-direct to View page for display of reservation
Server.Transfer("View.aspx")
End Try

End Sub
For the part on red: it is supposed to be already displayed in the datagrid. So can i used a session to store it?
For the part on green: it is supposed to just go into viewing theappointments after one has already logged in. So he is entited to viewhis own appt without having to input his NRIC no again.
For the part on blue: I am supposed to delete the appt but this line ofcode is to add into the database right? So should I changed to dbCmd.Parameters.Clear()? If yes, how do I write?
All the above are written inside the page_load event and all the dataare being displayed as a datagrid. I created this page to do the behindscene of deleting without the user actually seeing it. So for thebinding of the datagrid, wad should I typed for the URL and the URLfield in the property builder as i used a hyperlink column to deletethe appt?
When the page is runned, the following occurred:A field or property with the name 'Cancel.aspx' was not found on the selected datasource.
|||Dim connStr As String = System.Configuration.ConfigurationSettings.AppSettings("SqlConnection.connectionString")
Dim dbConn As New SqlConnection
Dim dr As SqlDataReader
dbConn.ConnectionString = connStr
Try

Dim dbCmd As New SqlCommand
dbCmd.Connection = dbConn
dbCmd.CommandType = CommandType.StoredProcedure
dbCmd.CommandText = "spCancelReservation"
Response.write("Appdate=" & Session("AppDate") & ", AppTime=" & Session("AppTime") & ", NRIC=" & Session("NRIC"))
dbCmd.Parameters.Add(New SQL Parameter("@.AppDate", SqlDbType.DateTime)))
dbParam.Value = Session("AppDate")

dbParam = dbCmd.Parameters.Add(New SQL Parameter("@.AppTime", SqlDbType.Char, 4))
dbParam.Value = Session("AppTime")
dbParam = dbCmd.Parameters.Add(New SQL Parameter("@.MemNRIC", SqlDbType.Char, 9))
dbParam.Value = Session("NRIC")
dbConn.Open()'open the connection as late as you can and close it immediately when you are done.
dr = dbCmd.ExecuteReader()
Finally
dbConn.Close'<---- This Is very important
'Re-direct to View page for display of reservation
Server.Transfer("View.aspx")
End Try

End Sub

I have simplified your code a little bit. I added a response.write statement so you can check what values you ae trying to pass. IF they are NULL's you need to accomodate for that. To check the values you could comment the line : dr = dbCmd.ExecuteReader()and run the page.|||I tried your codes, but I end up getting this error:
A field or property with the name 'Cancel.aspx' was not found on the selected datasource.
I think this error is due to the URL field or URL format string neededin the datagrid under Properties Builder. But I do not know what URLlinks to specify..
So I tried another cancel function, this time I tried a simpler deleteby using another stored procedure to view the existing appointment on awebform with a "Delete" button to delete the existing appointment. Butnow, the date, time can be shown, but when the "Delete" button isclicked, there is no deletion of data.
These codes are written in the button click event:
If Not Page.IsPostBack Then
Dim nric As String
nric = Session("NRIC")
lblMNRIC.Text = nric
DimconnStr As String =System.Configuration.ConfigurationSettings.AppSettings("SqlConnection.connectionString")
Dim dbConn As New SqlConnection
dbConn.ConnectionString = connStr
Dim dr As SqlDataReader
Try
dbConn.Open()
Dim dbCmd As New SqlCommand
dbCmd.Connection = dbConn
dbCmd.CommandType = CommandType.StoredProcedure
dbCmd.CommandText = "spCancelReservation"
Dim dbParam As SqlParameter
dbParam = dbCmd.Parameters.Add("@.return", SqlDbType.Int)
dbParam.Direction = ParameterDirection.ReturnValue
dbParam = dbCmd.Parameters.Add("@.MemNRIC", SqlDbType.Char, 9)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = lblMNRIC.Text
dr = dbCmd.ExecuteReader()
Dim status As Integer
status = dbCmd.Parameters("@.return").Value
If status = -401 Then
lblError.Visible = True
lblError.Text = "You must cancel at least 10 days in advance!"
Else
lblDate.Visible = False
lblTime.Visible = False
lblADate.Visible = False
lblATime.Visible = False
lblError.Text = "You do not have any appointment!"
Response.Redirect("View.aspx")
End If
Catch ex As SqlException
lblError.Text = ex.Message
Catch ex As Exception
lblError.Text = ex.message
Finally
dbConn.Close()
End Try
End If
End Sub
|||can you check to see if the values in the textboxes are right? I have not used ReturnValue before. I have used OUTPUT parameters and it is slightly different. I am used to defining the parameter the way I showed in my code. I dont know what else to help you with - your code looks ok since there seem to be no syntax errors. Does the stored proc work from query analyzer?|||Ya, the values in the textboxes are right. The stored procedure works from the query analyzer. It can delete perfectly there. But in the .NET, the page will be refreshed but the data will not be deleted.|||For one thing, you should be using ExecuteNonQuery, not ExecuteReader.|||Ya, it should be ExecuteNonQuery.. Thanks.. Now it works.. but what isthe difference between NonQuery and ExecuteReader? I thought whendeleting, it should read the data from the database?
|||ExecuteNonQuery does not return any records. It will only return the number of rows affected kind alike your @.@.rowcount.
ExecuteReader returns the result set of a SELECT statement.

I thought when deleting, it should read the data from the database?

Nope. When deleting you are only deleting the records. Unless you have a SELECT statement you would not get any recordset back.

DELETE Procedure. How to return a value?

Hello,

I created a DELETE Stored Procedure in SQL 2005.
When calling this procedure from my server code (VB.NET in my case) I
need to know if the record was deleted or not.

How should I do this?

Should I make the procedure to return True or False? If yes, how can I
do this?

My Stored Procedure is as follows (I think it is ok):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Content_DeleteContent]

-- Define the procedure parameters
@.ContentName NVARCHAR(100),
@.ContentCulture NVARCHAR(5)

AS

-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare and define ContentId
DECLARE @.ContentId UNIQUEIDENTIFIER;
SELECT @.ContentId = ContentId FROM dbo.Content WHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentId IS NOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCulture IS NULL
BEGIN

-- Delete all localized contents from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE ContentId = @.ContentId

-- Delete content from dbo.Content
DELETE
FROM dbo.Content
WHERE ContentName = @.ContentName;

END
ELSE

-- Delete localized content from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

GO

Thanks,
Miguel

hi miguel.

below is the sp i modified. i've added an output parameter, which will be null if record(s) are deleted properly. so you can check in your code using output parameter.

SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO

ALTER PROCEDURE [dbo].[Content_DeleteContent]

-- Define the procedure parameters
@.ContentNameNVARCHAR(100),
@.ContentCultureNVARCHAR(5) ,
@.nFlag tinyint output

AS

-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare and define ContentId
DECLARE @.ContentIdUNIQUEIDENTIFIER;
SELECT @.ContentId = ContentIdFROM dbo.ContentWHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentIdISNOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCultureISNULL
BEGIN

-- Delete all localized contents from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE ContentId = @.ContentId

-- Delete content from dbo.Content
DELETE
FROM dbo.Content
WHERE ContentName = @.ContentName;

Select @.nFlag = ContentIDfrom dbo.ContentLocalized
WHERE ContentId = @.ContentId
if @.nFlagisnull
Begin
Select @.nFlag = 1FROM dbo.Content
WHERE ContentName = @.ContentName;
End

END
ELSE

BEGIN
-- Delete localized content from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

Select @.nFlage = ContentIDFROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

END

hope it helps.

regards,

satish.|||Rather than do SELECT's after the DELETE's checking for @.@.ROWCOUNT works faster since the number of records deleted is already stored in the function. You could just check if @.@.ROWCOUNT >0 and set your flag to 1 or 0 appropriately.|||

Hi Dinakar

miguel has usedSET NOCOUNT ON.

thanks,

satish.

|||

Hello,

I defined "Set NoCount On" because this is done by default in SQL Server 2005 Management Studio and because this is how I see in most code examples.

I am just starting with SQL. Should I change it?

Could somebody give me some info on this?

Thanks,

Miguel

|||

hi miguel,

sorry i m not aware of default in sql 2k5

set nocount on --in case you update/insert/delete record on a table it wont return number of affected records

set nocount off --in case you update/insert/delete record on a table it will return number of affected records

hope it clarifies

now if you set it to off in your sp then after delete you can check

if @.@.ROWCOUNT > 0

begin

end

@.@.ROWCOUNT contains no.of records affected due to preceeding operation.

regards,

satish.

|||

SET NOCOUNT will only suppress messages from being printed in the Messages tab which are returned to the calling application. It does not have any effect on @.@.ROWCOUNT. You can still do your T-SQL Queries and have the results of rows affected in @.@.ROWCOUNT, get the value into variable and return it via OUTPUT parameters. Having the SET NOCOUNT ON is a good thing, generally. It prevents unnecessary messages from being passed across to the application.

Satish,

You are probably checking the Messages tab by running a T-SQL statement between SET NOCOUNT commands.

Try this:

SET NOCOUNT ON

SELECT TOP 10 * FROM anyTable

SELECT @.@.ROWCOUNT as RowsAffected

SET NOCOUNT OFF

|||

errrrr silly of me.

thanks dinakar you removed my misunderstanding about @.@.ROWCOUNT and SET NOCOUNT

cheers,

satish.

Delete problem

Dear all,

I have an asp.net webform which will provide delete function. If there are foreign key constraint and the user click the delete button, i would like the user to get response (Eg You must delete other data first......or something like this)

1. Any good idea?

2. One way i search from this form is like this, it raise error in db side(stored proc)

IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR (''VersionA.)
RETURN
END

IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR (''VersionB.)
RETURN
END

How can i get the raiseerror and identiy the veriosn of error in asp.net page?

Thanks in advance!!!!

Return a value to indicate that child records exists. Raising an error is not good idea due to performance.

|||This should already be taken care of from the sql side as long as youhave enforce constraits on for deleting and crud operations. Allyou have to do is catch the SQL errors and handle them from your aspxwhich isnt hard. Search handling sql errors from the codebehind. All you would have to do is handle the event and showjavascript to the user that you need to delete things.