Exception Handling

Aug 22, 2007

Hi All

I m new to the sqlserver.In Oracle we can handle exceptions like this

declare
name varchar(20);
begin
select ename into name from emp where eno=&eno;
dbms_output.put_line(name);
exception
when no_data_found then
dbms_output.put_line('No Entry');
end;
/

We will get the message No Entry When corrsponding employee number dosent exists.

In Sqlserver how to handle these things.

Specifically my requirement is with select statement(Like above program)

Waiting for valuable suggestions

Baba

View 4 Replies

Exception Handling

Sep 7, 2007

it gives error while calling a sql stored procedure as "INPUT STRING WAS NOT IN A CORRECT FORMAT". I am providing the code here.public void get_issid(string cse_email, string tech_email, string subject, string issue_details, string response, string solv_date, out int issid)
{
// Establish ConnectionSqlConnection oConnection = GetConnection();
// build the commandSqlCommand oCommand = new SqlCommand("get_issid", oConnection);
oCommand.CommandType = CommandType.StoredProcedure;
// ParametersSqlParameter paracse_email = new SqlParameter("@cse_email", SqlDbType.VarChar, 50);
paracse_email.Value =cse_email;
oCommand.Parameters.Add(paracse_email);
SqlParameter paratech_email = new SqlParameter("@tech_email", SqlDbType.VarChar,50);
paratech_email.Value = cse_email;
oCommand.Parameters.Add(paratech_email);SqlParameter parasubject = new SqlParameter("@subject", SqlDbType.VarChar, 50);
parasubject.Value = subject;
oCommand.Parameters.Add(parasubject);SqlParameter paraissue_details = new SqlParameter("@issue_details", SqlDbType.VarChar, 500);
paraissue_details.Value = issue_details;
oCommand.Parameters.Add(paraissue_details);SqlParameter pararesponse = new SqlParameter("@response", SqlDbType.VarChar, 500);
pararesponse.Value = response;
oCommand.Parameters.Add(pararesponse);SqlParameter parasolv_date = new SqlParameter("@solv_date", SqlDbType.DateTime);
parasolv_date.Value = solv_date;
oCommand.Parameters.Add(parasolv_date);SqlParameter paraissid = new SqlParameter("@issid", SqlDbType.Int);paraissid.Direction = ParameterDirection.Output;
oCommand.Parameters.Add(paraissid);
try
{
oConnection.Open();
oCommand.ExecuteNonQuery();issid = int.Parse(paraissid.Value.ToString());
}catch (Exception oException)
{throw oException;
}
finally
{
oConnection.Close();
}
}

the stored procedure is:

create proc [dbo].[get_issid](@tech_email varchar(50), @cse_email varchar(50),@subject varchar(50),@issue_details varchar(500),@response varchar(500),@solv_date datetime, @issid int output)
as
select @issid=tech_response.issue_id from tech_response,issue_details where tech_response.tech_email=@tech_email and tech_response.cse_email=@cse_email and tech_response.subject=@subject and tech_response.issue_details=@issue_details and response=@response and solv_date=@solv_date and tech_response.issue_id=issue_details.issue_id
requested to help in this

View 2 Replies View Related

Handling SQL Exception

Nov 14, 2007

I'm unsure how to handle an SQL Exception correctly when the database is unavailable/offline.
I have my aspx file with the C# code-behind, but all of the SQL stuff is done in a separate code file in the App_Code directory.E.g.
CODE-BEHINDDatabaseModifier.deleteUser(username);
DATABASEMODIFIER.cspublic static void deleteUser(string username){ SqlConnection conn = SqlLogin.SqlConnect; SqlCommand command = new SqlCommand("DELETE FROM <table> WHERE Username = '" + username + "'", conn); conn.Open() command.ExecuteNonQuery(); conn.Close()}
Now, that code works perfectly, however if the database I'm connecting to is offline, an SQLException is thrown and because the SQL is handled in my DatabaseModifier class, I'm not sure how to handle it correctly.If I use a Try/Catch block in my code-behind, it doesn't get thrown because the error occurs in my DatabaseModifier class. If I use a Try/Catch block in my DatabaseModifier class, what can I put in the catch block that will inform the user of the database being offline and/or how can I perform a url redirection?
Any help is greatly appreciated.

View 3 Replies View Related

Exception Handling

Apr 18, 1999

Hi,
Im Nithyananda working on a project with SQL server 6.0
Im having a stored procedure which inserts into a table .
If I violate the primary key constraint on the table , I get a 2627 error.
I would like to replace this error with my own.Could u tell me how to do it?
Im an ORACLE guy and relatively new to SQL server.
Is it possible for me to propogate the same error message to any front end?
In my case ,Im using VC++ as front end.
Could someone also tell me the way of calling a SQL stored procedure from
VC++?

Regards
Nithyananda