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.
Rijckewaert wrote:
Arnie Rowland wrote:
shapper: