I have an SQL SP that is failing but C# does not reconize it is failing in the try catch. What do I have to return from my SQL script to make C# know it failed?
I have an SQL SP that is failing but C# does not reconize it is failing in the try catch. What do I have to return from my SQL script to make C# know it failed?
What do you mean? Exception in C# is not catching sp failure?
Post your C# code, because there is something missing to handle the failure in catch block..
have you trie this way?
try
{
//sp call
}
catch(Exception ex)
{
//here you will get each failure of store procedure
}
My try/catch in C# does not see this error though. Everything process as though the SP ran fine but I know it does not since no new data is added like it should. The proce runs fine when I call it via SQL but when done via C# it does not run so there is some
sort of failer and I need to capture that.
//Run a stored proc with a given set of parameters.
public static int ExecuteSP(string SPName, List<ParameterList> ParameterList)
{
using (SqlConnection con = GetCon())
{
SqlCommand cmd = new SqlCommand(SPName, con);
cmd.CommandType = CommandType.StoredProcedure;
foreach (ParameterList Parameter in ParameterList)
{
cmd.Parameters.AddWithValue(Parameter.Name, Parameter.Value);
}
//Setup a parameter to get the return value from the proc
SqlParameter returnValue = new SqlParameter();
returnValue.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnValue);
cmd.Connection.Open();
cmd.ExecuteScalar();
int RetVal = (int)returnValue.Value;
cmd.Connection.Close();
return RetVal;
}
}
My C# code
protected void lvRaces_ItemInserting(object sender, ListViewInsertEventArgs e)
{
//Upload any image and add new information to the database
try
{
if(fuNewRaceImage.HasFile)
{
//Set a new file name and declaire the upload path then upload file
string NewFileName = (tbNewRaceName.Text + Path.GetExtension(fuNewRaceImage.PostedFile.FileName));
string FilePath = (@"Images\" + GetLinkName(ddlNewMediaShortName.SelectedValue.ToString()));
fuNewRaceImage.SaveAs(FilePath + NewFileName);
//Add the file path to the parameters list
Parameters.Add(new ParameterList() { Name = "ImagePath", Value = FilePath });
}
//Fill out paramters list and add new information to the database
Parameters.Add(new ParameterList() { Name = "UpdateType", Value = " Insert" });
Parameters.Add(new ParameterList() { Name = "SectionID", Value = ddlNewMediaShortName.SelectedValue.ToString() });
Parameters.Add(new ParameterList() { Name = "RaceName", Value = tbNewRaceName.Text });
Parameters.Add(new ParameterList() { Name = "RaceDescription", Value = tbNewRaceDescription.Text });
SQLHelper.ExecuteSP("dbo.spRaceInfo", Parameters);
Parameters.Clear();
lblFeedbackMSG.Text = "New Race information added.";
BindListView();
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex); //Log the error to the DB.
lblFeedbackMSG.Text = "There was an issue added the new race information, please try again later.";
e.Cancel = true; //Cancel the insert
}
}
There is nothing wrong with the proc, I can case the proc to error out and my RAISEERROR works fine and prints the correct details out to MSSQL. The problem is to get C# to reconise that the error happened
Eagle_f90
Member
466 Points
532 Posts
What to I have to pass from my SQL script to make C# know it failed?
May 11, 2012 12:49 PM|LINK
I have an SQL SP that is failing but C# does not reconize it is failing in the try catch. What do I have to return from my SQL script to make C# know it failed?
Nasser Malik
Star
11650 Points
1791 Posts
Re: What to I have to pass from my SQL script to make C# know it failed?
May 11, 2012 12:55 PM|LINK
You can use RAISERROR in store procedure to throw exception
http://msdn.microsoft.com/en-us/library/ms178592.aspx
http://stackoverflow.com/questions/1531450/raise-an-error-manually-in-t-sql-to-jump-to-begin-catch-block
http://support.microsoft.com/kb/321903
Skype: maleknasser1
cheruku.sai
Member
44 Points
12 Posts
Re: What to I have to pass from my SQL script to make C# know it failed?
May 11, 2012 12:56 PM|LINK
Hi,
You can throw error from the sp using the below code
DECLARE @ErrorMessage NVARCHAR(100)
SET @ErrorMessage = 'error Message'
RAISERROR(@ErrorMessage, 16, 1)
And catch that error in c sharp
christiandev
Star
8607 Points
1841 Posts
Re: What to I have to pass from my SQL script to make C# know it failed?
May 11, 2012 01:02 PM|LINK
How is the SP failing? do you mean as in not doing the expected work? or causing an error?
If it's the first, then you will need to capture that in the SP, and use RAISEERROR to report back to the application
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)
yrb.yogi
Star
14460 Points
2402 Posts
Re: What to I have to pass from my SQL script to make C# know it failed?
May 11, 2012 01:19 PM|LINK
What do you mean? Exception in C# is not catching sp failure?
Post your C# code, because there is something missing to handle the failure in catch block..
have you trie this way?
try { //sp call } catch(Exception ex) { //here you will get each failure of store procedure }.Net All About
Eagle_f90
Member
466 Points
532 Posts
Re: What to I have to pass from my SQL script to make C# know it failed?
May 11, 2012 02:53 PM|LINK
I am using RAISERROR
DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);My try/catch in C# does not see this error though. Everything process as though the SP ran fine but I know it does not since no new data is added like it should. The proce runs fine when I call it via SQL but when done via C# it does not run so there is some sort of failer and I need to capture that.
Eagle_f90
Member
466 Points
532 Posts
Re: What to I have to pass from my SQL script to make C# know it failed?
May 11, 2012 03:07 PM|LINK
//Run a stored proc with a given set of parameters. public static int ExecuteSP(string SPName, List<ParameterList> ParameterList) { using (SqlConnection con = GetCon()) { SqlCommand cmd = new SqlCommand(SPName, con); cmd.CommandType = CommandType.StoredProcedure; foreach (ParameterList Parameter in ParameterList) { cmd.Parameters.AddWithValue(Parameter.Name, Parameter.Value); } //Setup a parameter to get the return value from the proc SqlParameter returnValue = new SqlParameter(); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); cmd.Connection.Open(); cmd.ExecuteScalar(); int RetVal = (int)returnValue.Value; cmd.Connection.Close(); return RetVal; } }My C# code
protected void lvRaces_ItemInserting(object sender, ListViewInsertEventArgs e) { //Upload any image and add new information to the database try { if(fuNewRaceImage.HasFile) { //Set a new file name and declaire the upload path then upload file string NewFileName = (tbNewRaceName.Text + Path.GetExtension(fuNewRaceImage.PostedFile.FileName)); string FilePath = (@"Images\" + GetLinkName(ddlNewMediaShortName.SelectedValue.ToString())); fuNewRaceImage.SaveAs(FilePath + NewFileName); //Add the file path to the parameters list Parameters.Add(new ParameterList() { Name = "ImagePath", Value = FilePath }); } //Fill out paramters list and add new information to the database Parameters.Add(new ParameterList() { Name = "UpdateType", Value = " Insert" }); Parameters.Add(new ParameterList() { Name = "SectionID", Value = ddlNewMediaShortName.SelectedValue.ToString() }); Parameters.Add(new ParameterList() { Name = "RaceName", Value = tbNewRaceName.Text }); Parameters.Add(new ParameterList() { Name = "RaceDescription", Value = tbNewRaceDescription.Text }); SQLHelper.ExecuteSP("dbo.spRaceInfo", Parameters); Parameters.Clear(); lblFeedbackMSG.Text = "New Race information added."; BindListView(); } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); //Log the error to the DB. lblFeedbackMSG.Text = "There was an issue added the new race information, please try again later."; e.Cancel = true; //Cancel the insert } }TabAlleman
All-Star
15575 Points
2702 Posts
Re: What to I have to pass from my SQL script to make C# know it failed?
May 11, 2012 03:48 PM|LINK
Try specifying values in your RaiseError, like:
RAISERROR('My Custom Error Msg', 16, ,0)
Eagle_f90
Member
466 Points
532 Posts
Re: What to I have to pass from my SQL script to make C# know it failed?
May 11, 2012 06:09 PM|LINK
There is nothing wrong with the proc, I can case the proc to error out and my RAISEERROR works fine and prints the correct details out to MSSQL. The problem is to get C# to reconise that the error happened
christiandev
Star
8607 Points
1841 Posts
Re: What to I have to pass from my SQL script to make C# know it failed?
May 11, 2012 06:34 PM|LINK
have you tried putting the try/catch in the executeSP method? can you post the code for the SP too?
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)