What The Catch With SqlParameterCollection.Add Constructors
Oct 24, 2005
Hi!
I'm doing a class for working with MSDE.
My goal (as you understood) is to separate business and data layers.
I'm facing the following problem:
I have to use something like cmd.Parameters.Add("@Name", value) in my business layer to avoid using
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 16, value) where you can see that 'SqlDbType.NVarChar' doesn't respect the 'rules' for separating Data/Business layer.
Does it cost a lot to use a 'light' constructor (more job for the SQL engine to convert datas)?
Do I have to do a special function in my 'Data class' to convert an Int32 to a SqlDbType.Int for example?
I could see that Parameters.Add is deprecated and I have to use Parameters.AddWithValue in .Net 2 when you have only 2 parameters.
Is there a better way to handle that?
I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:
a) Why doesn't my Catch block catch the exception? b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?
Dear all, I'm using sqldatareader to return data that i need and then bind it to the gridview. Anyway, I'm facing the above error message. Please help, thanks. Levine
I am trying to bind the data to a list box....i am using a stored procedure instead of a select query. Unfortunately the error is throwing up. Correct me where iam going wrong. Is my syntax correct SqlConnection cn = new SqlConnection("User id=******;password=******;database=sampleecsphase2test;Data source=primasqltst\qa"); cn.Open();SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure;SqlDataReader dr = new SqlDataReader(); dr = cmd.ExecuteReader(); listBox1.DataSource = dr;
I have a site which works fine with an Access DB, I now want to upsize it to use a SQL DB... I have changed my OleDB Commands etc... And Used SqlCommands and everything seems fine..Only one problem I have and its really stumped me... I always used SQLDataSource for the app even with the AccessDB, as I knew I would be upsizing at some point. We I have the below SQLDataSource 1 <asp:SqlDataSource ID="RandomBusinessDataSource" runat="server" 2 ConnectionString="<%$ ConnectionStrings:SQLDataBaseConnectionString %>" 3 SelectCommand="SELECT TOP (1) intBusinessID, txtBusinessName, intSubCatID, intCatID, txtTelNo, txtPostCode, txtWebAdd, txtReferred, bitBusinessShow, txtLong, txtLat, memBusinessDesc, intIPAddress, bitBusinessPaid FROM tblBusiness WHERE (intBusinessID = @Param1)" OnSelecting="RandomBusinessDataSource_Selecting">4 <SelectParameters>5 <asp:Parameter Name="Param1" Type="Int32" />6 </SelectParameters>7 </asp:SqlDataSource> Very Simple.... I have my function that is creating my random number and returning a VALID intbusinessID ... And now I have this OnSelecting event1 Protected Sub RandomBusinessDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)2 e.Command.Parameters("Param1").Value = MyNewRandomNum3 End Sub But when I try and run the page I get the following error (Don't forget this page IS working using the SQLDataSource and an Access DB??)An SqlParameter with ParameterName 'Param1' is not contained by this SqlParameterCollection. I am a bit confused?? why would it say this now... Yet it works fine if I just change the connectionString to the access DB?? Any helps very much appreciated... Thanks
Stored procedure:1 ALTER PROCEDURE [dbo].[insert_DagVerslag] 2 -- Add the parameters for the stored procedure here 3 @serverID int, 4 @datum datetime, 5 @ID int OUTPUT 6 AS 7 BEGIN 8 -- SET NOCOUNT ON added to prevent extra result sets from 9 -- interfering with SELECT statements. 10 SET NOCOUNT ON; 11 -- Insert statements for procedure here 12 BEGIN TRANSACTION 13 INSERT Log ( serverID, datum) 14 VALUES( @serverID, @datum) 15 COMMIT TRANSACTION 16 SET @ID = @@identity 17 END 18
Method who is calling the stored procedure and causes the error1 public void AddDagVerslag(Historiek historiek) 2 { 3 SqlConnection oConn = new SqlConnection(_connectionString); 4 string strSql = "insert_DagVerslag"; 5 SqlCommand oCmd = new SqlCommand(strSql, oConn); 6 oCmd.CommandType = CommandType.StoredProcedure; 7 oCmd.Parameters.Add(new SqlParameter("@serverID", SqlDbType.Int)).Value = historiek.ServerID; 8 oCmd.Parameters.Add(new SqlParameter("@datum", SqlDbType.DateTime)).Value = historiek.Datum; 9 oCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)); 10 11 oCmd.Parameters["@ID"].Direction = ParameterDirection.Output; 12 13 try 14 { 15 oConn.Open(); 16 int rowsAffected = oCmd.ExecuteNonQuery(); 17 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek"); 18 oCmd.Parameters.Clear(); 19 historiek.HistoriekID = System.Convert.ToInt32(oCmd.Parameters["@ID"].Value); 20 21 foreach (HistoriekDetail hDetail in historiek.LstHistoriekDetails) 22 { 23 AddDagVerslagCategorie(historiek.HistoriekID, hDetail); 24 } 25 } 26 catch (Exception ex) 27 { 28 throw new ApplicationException("Fout toevoegen historiek : " + ex.Message); 29 } 30 finally 31 { 32 if (oConn.State == ConnectionState.Open) oConn.Close(); 33 } 34 }
Whats going wrong, i've added the ID parameter my ParameterCollection... so why cant it be found
Hello All, I'm having a problem tracking down why my app is not instantiating a parameter in a sqldatasource, when I'm expecting it to do so. When the app fails, this info is displayed:
Server Error in '/' Application.-------------------------------------------------------------------------------- An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IndexOutOfRangeException: An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection. Source Error: Line 30: Private Sub SqlDataSource1_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.DeletingLine 31: e.Command.Parameters.Item("@createdby").Value = CType(Membership.GetUser.ProviderUserKey, Guid)Line 32: Line 33: End Sub Source File: ...filename... Line: 31 Stack Trace: [IndexOutOfRangeException: An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection.] System.Data.SqlClient.SqlParameterCollection.GetParameter(String parameterName) +713105 System.Data.Common.DbParameterCollection.get_Item(String parameterName) +7 diabetes.reading.SqlDataSource1_Deleting(Object sender, SqlDataSourceCommandEventArgs e) in C:Documents and SettingsAlbertMy DocumentsVisual Studio 2005Projectsdiabetesdiabetesdiabetes eading.aspx.vb:31 System.Web.UI.WebControls.SqlDataSourceView.OnDeleting(SqlDataSourceCommandEventArgs e) +114 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +682 System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +75 System.Web.UI.WebControls.FormView.HandleDelete(String commandArg) +839 System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +556 System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102 Here's the datasource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DiabetesOne %>" DeleteCommand="reading_delete" DeleteCommandType="StoredProcedure" > <DeleteParameters> <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" /> <asp:Parameter Name="rid" Type="Int32" /> <asp:Parameter Name="createdby" Type="Object" /> </DeleteParameters> </asp:SqlDataSource> Here's the beginning of the stored procedure showing the parameters:
CREATE PROCEDURE [dbo].[reading_delete] @rid int,@createdby sql_variantASset nocount on;begin try...the procedural code... Here's debugger capture, pointing to same issue:
System.IndexOutOfRangeException was unhandled by user code Message="An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection." Source="System.Data" StackTrace: at System.Data.SqlClient.SqlParameterCollection.GetParameter(String parameterName) at System.Data.Common.DbParameterCollection.get_Item(String parameterName) at diabetes.reading.SqlDataSource1_Deleting(Object sender, SqlDataSourceCommandEventArgs e) in C:... at System.Web.UI.WebControls.SqlDataSourceView.OnDeleting(SqlDataSourceCommandEventArgs e) at System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) at System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback)
I've inspected the sqldatasourcecommandeventargs command.parameters in the deleting method during debugging and could not find the parameter in the array, although I "think" it should be because it's explicitly stated in the sqldatasource parameters declarations. I see two parameters in the array, @RETURN_VALUE and @RID, both of which are expected and coincide with the declaration in the stored procedure and the sqldatasource.
RunProc("test",param); RunProc("test",param); //if i remove this line ,it's OK } public void RunProc(string procName, SqlParameter pram) { SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); con.Open();
I currently have a connection class that handles all of my database connectivity. What I am trying to do is build a SqlParameterCollection object on one page, then pass that object to my connection class. Is it possible to do this? I am not getting any compilation errors, but I can not get the parameters to be recognized. Here is what I am trying to do:
conn.OpenReader(sql, newcollect); while (conn.DR.Read()) { read data onto page here... } conn.CloseReader();
Page 2 (connection class) :
public void OpenReader(string sql, SqlParameterCollection collect) { Conn = new SqlConnection(strConn); Conn.Open(); Command = new SqlCommand(sql,Conn);
Command.Parameters.Add(collect); <------This is the root of my question Command.CommandTimeout = 300; // executes sql and fills data reader with data DR = Command.ExecuteReader(); }
Can you do this??? And if so, can anyone tell me why the statement will not return any data? The procedure works perfectly, and will work if I use the standard way of passing the parameters to the command statement.
Hi,What I am trying to do is to get the new ID for every record is inserted into a table. I have a For...Each statement that does the loop and using the sqldatasource to so the insert. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ myConnectionString%>"> <InsertParameters> <asp:Parameter Name="NewID" Type="int16" Direction="output" /> </InsertParameters> </asp:SqlDataSource> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim sqlSelect As String Session("Waste_Profile_Num") = 2 Session("Waste_Profile_Num2") = 5
sqlSelect = "SELECT Range, Concentration, Component_ID FROM Component_Profile WHERE (Waste_Profile_Num = " & Session("Waste_Profile_Num").ToString() & ")" SqlDataSource1.SelectCommand = sqlSelect
Dim dv As Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), Data.DataView) For Each dr As Data.DataRow In dv.Table.Rows
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted Session("NewID") = e.Command.Parameters("@NewID").Value.ToString
End Sub What I have done so far is to display the new id and I am going to use that return id later. However, the first time through the loop, I am able to get the new id but not the second time. So, I wonder, how do I every single new id each time the INSERT statement is executed?STAN
All of a sudden my application started crashing when trying execute dml statements on sql server mobile database (sdf file). Frustating thing is that whole application crashes without any error message. this happens for all kinds for DML statement but not all the time. Sometimes it would fail for delete statement such as delete from table; or for insert into statement
my problem catch does not catch the error. There is no way to find out teh what is causing this error
SqlCeConnection sqlcon = new SqlCeConnection("
Data Source = '\Program Files\HISSymbol\HISSymboldb.sdf';"
);
SqlCeCommand sqlcmd = new SqlCeCommand();
sqlcmd.CommandText = Insert into company('AA', 'Lower Plenty Hotel');
sqlcmd.Connection = sqlcon;
SqlCeDataReader sqldr = null;
try
{
sqlcon.Open();
//use nonquery if result is not needed
sqlcmd.ExecuteNonQuery(); // application crashes here
}
catch (Exception e)
{
base.myErrorMsg = e.ToString();
}
finally
{
if (sqlcon != null)
{
if (sqlcon.State != System.Data.ConnectionState.Closed)
See also -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
The above behaviour is as described in BOL, but comparing to other languages such as Java, C#, PL/SQL I would expect that catch always catch unless you take the power from the machine, special in the case for "catchnonexistingproc". Remark the different behaviour compared to "nonexistingtable" Documentation shows not example for non existing proc! That catch doesn't catch "is as bug". So is the catch of non existing proc a bug in the bug? ;^) I hope not, because it is the behaviour we want...
I want to use a try/catch to catch any error with this datareader so if no data then exit sub, eventually I will create custom error pagebut for now I just want to exit sub so following sql statements are not executed. I get the following problem when I try to close connection.this was not a problem before using try and catch, do I have in right place? Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'declare vars Dim iPreqid As String = CStr(Session("iPreqid")) Dim sql As String = "select * from Vrequest where req_id = '" & iPreqid & "'" 'Connection String value Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString 'Create a SqlConnection instance Using myConnection As New SqlConnection(conn) Dim myCommand As New SqlCommand(sql, myConnection) 'declare sqldatareader Dim dbReader As SqlDataReader Try myConnection.Open() myCommand.ExecuteNonQuery() dbReader = myCommand.ExecuteReader 'Initialize reader dbReader.Read() 'assign reader index values using column names to controls Label8.Text = CStr(dbReader("User_Name")) Label11.Text = CStr(dbReader("NHI")) Label9.Text = CStr(dbReader("Event_Number")) Label10.Text = CStr(dbReader("ACC_Number")) Label7.Text = CStr(dbReader("Required_Date")) Label1.Text = CStr(dbReader("Title")) Label3.Text = CStr(dbReader("address1")) Label4.Text = CStr(dbReader("Birth_date")) Label2.Text = CStr(dbReader("surname")) Label5.Text = CStr(dbReader("home_phone")) Label21.Text = CStr(dbReader("firstname")) Label20.Text = CStr(dbReader("delivery")) Label22.Text = CStr(dbReader("ward")) Label23.Text = CStr(dbReader("request_date")) Catch ex As Exception Session("exception") = ex exit sub End Try End Using 'if connection not closed then close If (Not conn Is Nothing) Then myConnection.Close()<---------------------------------------->Name 'myconnection is not declared' myConnection.Dispose()<---------------------------------------->Name 'myconnection is not declared' End If
Hi, Here is the code:Try If DS.Tables(0).Rows.Count = 0 ThenResponse.Write("Cannot delete an unexist row!") Response.End() End If DS.Tables(0).Rows(0).Delete() Catch BUG as Exception Response.Write(Bug.Message) End Try I removed all the connection stuff.. What I don't understand is why if ds.table(0).rows.count is zero I still get Bug.Message? I did Response.End ... Thank you.
Protected Sub detailsview1_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs) Dim label2 As Label = CType(detailsview1.FindControl("label2"), Label) Try
Catch sqlEx As SqlClient.SqlException If sqlEx.Message.Contains("DELETE statement conflicted with COLUMN REFERENCE") Then label2.Visible = True label2.Text = "You cannot delete this Agent Type as it has a call weighting assigned to it, remove the weightings before you try to delete it" e.Cancel = True End If End Try End Sub Hi, Im using vb.net sql2005 and visual studio 2005 I have 2 tables which have a foregin key relationship. When i try to delete information from within one of my aspx pages it rightly comes up with an application errror, something along the lines of DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_callScore_agentType'. The conflict occurred in database 'Merlin_####', table 'callScores', column 'typeID'.The statement has been terminated. I have looked around and can see people talking about using a try catch excpetion however i need to know how id implement this using the detailsview1_itemdeleting event. Ive never used this before and havent found a decent tutorial to help.So far i have this code but im stuck as im not sure that this is correct but more importantly what i put in the try method. Protected Sub detailsview1_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs) Dim label2 As Label = CType(detailsview1.FindControl("label2"), Label) Try (WHAT GOES HERE) Catch sqlEx As SqlClient.SqlException If sqlEx.Message.Contains("DELETE statement conflicted with COLUMN REFERENCE") Then label2.Visible = True label2.Text = "You cannot delete this Agent Type as it has a call weighting assigned to it, remove the weightings before you try to delete it" e.Cancel = True End If End Try End Sub Your help would be greatly appreciated
I have the following stored procedure which is added to the windows scheduler. When it is run I'm getting an "Invalid use of null error", therefore I need to capture and any errors but I'm not sure how to do this. Can someone help? Thanks.
Code:
CREATE PROCEDURE [dbo].[usp_Reminders] AS
DECLARE @ReminderSent datetime DECLARE @getRecords CURSOR DECLARE @err int
SELECT E.[RL Staff No], E.Forename, E.Surname, C.CourseName, V.CourseDate , E.Email, V.ReminderSent FROM empdetails.dbo.v_Employee E INNER JOIN Validation V ON E.[RL Staff No] = V.[RL Staff No] INNER JOIN empdetails.dbo.v_Course C ON V.CourseCode = C.CourseCode WHERE V.Completed Is Null AND V.ReminderSent Is Null AND V.CourseDate <= dateadd(dd, -3, getdate()) order by e.[rl staff no]
SET @getRecords = CURSOR FOR
SELECT V.ReminderSent FROM empdetails.dbo.v_Employee E INNER JOIN Validation V ON E.[RL Staff No] = V.[RL Staff No] INNER JOIN empdetails.dbo.v_Course C ON V.CourseCode = C.CourseCode WHERE V.Completed IS NULL AND V.ReminderSent IS NULL AND V.CourseDate <= dateadd(dd, -3, getdate()) ORDER BY e.[rl staff no]
OPEN @getRecords FETCH NEXT FROM @getRecords INTO @ReminderSent WHILE @@FETCH_STATUS= 0 BEGIN UPDATE Validation SET ReminderSent = GetDate() WHERE CURRENT OF @getRecords FETCH NEXT FROM @getRecords INTO @ReminderSent END
HI, i'm trying to execute some sql using the Try.. Catch blocks.
Following code does not execute in Catch Block
Begin begin try insert into dbo.Test values (1,'aaa') -- here we are inserting int value in identity field... END TRY Begin catch PRINT 'TEST' SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END Catch End GO
Whereas the following block works fine and the Catch block executes.
Begin begin try Select 1/0 --This causes an error. END TRY Begin catch PRINT 'TEST' SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END Catch End GO
BEGIN TRY INSERT INTO ORDERS VALUES( 1122,'BAC123' ); INSERT INTO ORDLINE VALUES( 1122,991,1 ); PRINT 'VERY GOOD' END TRY BEGIN CATCH PRINT 'FALSE' INSERT INTO LOGTAB ('BAC123',1122,991,1, ERROR_NUMBER,ERROR_MESSAGE); END CATCH
i am getting error: Msg 102, Level 15, State 1, Line 11 Incorrect syntax near 'ERROR_NUMBER'.
hi there i have 2 questions about the TRY/CATCH statement that i have written below. any advice from more experienced persons would be highly appreciated. thankyou.
1. re the @ErrorMessage variable, i have seen this declared with a datatype of varchar(MAX), nvarchar(2048) and nvarchar(4000). which is the correct one for me to use?
2. do i need the statement 'RETURN @ErrorCode' at the end or is this redundant?
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
DECLARE @ErrorCode int, @ErrorMessage nvarchar(4000), @ErrorSeverity int
Is there something like exception handling in T-SQL?For example, how to catch an error of convertion at thissample:CREATE PROCEDURE SP@param VARCHAR(50)AS BEGINDELCARE @var INT-- try {SET @var = CONVERT( int, @param)-- } catch (error#245) {-- handle an error right here-- }ENDIt must be invisible for a caller of SP if something wrong inside SP.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi everyone, I am using an SQL extended stored procedure to send emails in aDTS package using a cursor that goes through each row in a table.Email sending code below======================exec master.dbo.xp_smtp_sendmail@FROM = @sFrom,@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'======================Fetch Next From EmailCursor ...Now the problem I have is that if an individual email address in invalidthen an error occurs and the whole DTS package falls over. What I would liketo be able to do is "catch the error", something like this (C# code used asexample)try{exec master.dbo.xp_smtp_sendmail@FROM = @sFrom,@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'} catch {exec master.dbo.xp_smtp_sendmail@FROM = "arealaddress@mybusiness.com",@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'}Is this possible??? Normally I would do all the email validation before theemail is entered into the database but unfortunately, I do not have accessto the application code so I am stuck doing it this way.Thanks in advanceMark
I have 3 procedures where the first one calls the second one and the second one calls the third one. All procedures have try..catch blocks and transactions. In the third procedure I declare an INT variable and try to give it the value 'A', to create an error. This makes the code in the catch block to run. In the catch block I rollback the transaction and return 1. It then returns to the 2nd procedure, but here I get the exception "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 0. Is there I way to keep running the code in the second procedure without getting this error? I take care of the return value from the 3rd procedure which is 1 so I know that there was an error in the third procedure but I want to run the code below the procedure call to the 3rd procedure in the second procedure, is there I way to do this?
I tried to have a transaction only in the first procedure but then I get this error "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."
What shoule I do to continue running the code in procedure 2 and procedure 1 after the procedure calls? In real life I have another scenario but the code below is just an easy example that creates the same error that my real procedures create.
Procedure 1:
Code Snippet ALTER PROCEDURE [dbo].[ProcFirst] AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY
DECLARE @return int BEGIN TRANSACTION
EXEC @return = [dbo].[ProcSecond] IF @return <> 0 BEGIN
SELECT 'return value <> 0 i ProcFirst' RETURN 1 END IF (XACT_STATE()) = 1 COMMIT TRANSACTION RETURN 0
END TRY BEGIN CATCH -- Error-handling statements
-- End Transaction IF @@TRANCOUNT > 0 BEGIN
IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION; IF (XACT_STATE()) = 1 COMMIT TRANSACTION; END
SELECT 'catch ProcFirst', @ErrorMessage
RETURN 1
END CATCH END
Procedure 2:
Code Snippet ALTER PROCEDURE [dbo].[ProcSecond] AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY
DECLARE @return int BEGIN TRANSACTION
EXEC @return = [dbo].[ProcThird]
IF @return <> 0 BEGIN
-- End Transaction IF @@TRANCOUNT > 0 BEGIN
IF (XACT_STATE()) = -1 BEGIN
SELECT 'RollBack transaction' ROLLBACK TRANSACTION; END IF (XACT_STATE()) = 1 BEGIN
Select 'commit transaction' COMMIT TRANSACTION; END END SELECT 'return value <> 0 i ProcSecond' RETURN 1 END
IF (XACT_STATE()) = 1 COMMIT TRANSACTION RETURN 0 END TRY BEGIN CATCH -- Error-handling statements
-- End Transaction IF @@TRANCOUNT > 0 BEGIN
IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION; IF (XACT_STATE()) = 1 COMMIT TRANSACTION; END
SELECT 'catch ProcSecond', @ErrorMessage
RETURN 1 END CATCH END
3rd procedure:
Code Snippet ALTER PROCEDURE [dbo].[ProcThird] AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY
DECLARE @tal int BEGIN TRANSACTION
SET @Tal = 'a' IF (XACT_STATE()) = 1 COMMIT TRANSACTION; RETURN 0 END TRY BEGIN CATCH -- Error-handling statements --End Transaction
IF @@TRANCOUNT > 0 BEGIN
IF (XACT_STATE()) = -1 BEGIN
SELECT 'RollBack transaction ProcThird' ROLLBACK TRANSACTION; END IF (XACT_STATE()) = 1 BEGIN
Select 'commit transaction ProcThird' COMMIT TRANSACTION; END END
How do you make an update cascade opperation if you want to set the PK to an excisting one? I have a table where to post are the same only the PK differs, now I want to delete one but transfer it's dependecies. i.e redirect its references to the other post. How can I do that using on update cascade/delete. I see a catch 22 coming up!
one of our study group members noticed a strange behavior and has the following question. Any thoughts are appreciated. I am unable to understand as to why the CATCH block is not executed when an INSERT is made On table T3 which is dropped after the first transaction.
The severity of Insert into t3 values (3) is Msg 208, Level 16, State 1, Line 2 Invalid object name 't3'.
BOL says TRY€¦CATCH constructs do not trap the following conditions: Warnings or informational messages with a severity of 10 or lower. Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY€¦CATCH will handle the error.
Here is the script.
use tempdb go
create table t1 (a int) create table t2 (b int) create table t3 (c int)
Begin tran
Insert into t1 values (1) Insert into t2 values (2) Insert into t3 values (3)
IF @@error <> 0 Rollback tran else commit tran -------------------------------------------------------------
Select * from t1 Select * from t2 Select * from t3 -------------------------------------------------------------
Insert into t1 values (1) Insert into t2 values (2)
save tran insertNow
Insert into t3 values (3) commit tran insertNow End try
Begin Catch
IF (XACT_STATE()) = -1 BEGIN PRINT 'The transaction is in an uncommittable state.' + ' Rolling back transaction.' ROLLBACK TRANSACTION insertNow END;
-- Test if the transaction is active and valid. IF (XACT_STATE()) = 1 BEGIN PRINT 'The transaction is committable.' + ' Committing transaction.' COMMIT TRANSACTION insertNow END
Hey,Here's yet another question for you more knowledgeable than me Up to this point I have been using a try/catch statement when dealing with SQL, for exampleint result = 0;try{result = Int32.Parse(command.ExecuteScalar().ToString());}catch{result = 0;}But I read up one some error handling and I have no idea how to solve this anymore. Since I'll be using the catch block to catch exceptions, something like thiscatch (SqlException){throw;}So I was wondering what is the good, standard practice for dealing with this since I need the catch statement to set result to zero or I would end up with an error.Thanks in advance,Sixten
