Error in ExecuteNonQuery()

Hi,I am developing a small application where in I need to take a few data from the user and put it on the DB. I have stored procs for the same. I am getting an error when I execute the ExecuteNonQuery() command. the error is as follows:

System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Intranet_New.leaveForm.btnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\intranet_new\leaveform.aspx.cs:line 138

Snippet of code:

try

{

con =

new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["SqlCon"]);

cmd =

new SqlCommand();

cmd.Connection = con;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "SP_InsertIntoLeave";

cmd.Parameters.Add("@.empid", SqlDbType.Char, 20);

cmd.Parameters["@.empid"].Value = txtEmplyId.Text;

cmd.Parameters.Add("@.empName", SqlDbType.NVarChar, 50);

cmd.Parameters["@.empName"].Value = txtName.Text;

cmd.Parameters.Add("@.LeaveFrom", SqlDbType.DateTime);

string str_LeaveFrom = ddlDay.SelectedValue + "/" +ddlMonth.SelectedValue + "/" + ddlYear.SelectedValue;

DateTime LF =

new DateTime();

LF = DateTime.Parse(str_LeaveFrom);

string LeaveFrom1 = (LF.ToShortDateString());

cmd.Parameters["@.LeaveFrom"].Value = LeaveFrom1;

cmd.Parameters.Add("@.LeaveTo", SqlDbType.DateTime);

string str_LeaveTo = ddltoDay.SelectedValue + "/" + ddltoMonth.SelectedValue + "/" + ddltoYear.SelectedValue;

DateTime LT =

new DateTime();

LT = DateTime.Parse(str_LeaveTo);

string LeaveTo1 = (LT.ToShortDateString());

cmd.Parameters["@.LeaveTo"].Value = LeaveTo1;

cmd.Parameters.Add("@.TotalDays", SqlDbType.BigInt);

cmd.Parameters["@.TotalDays"].Value = txtNoofDays.Text;

cmd.Parameters.Add("@.TypeOfLeave", SqlDbType.NVarChar, 50);

cmd.Parameters["@.TypeOfLeave"].Value = rbtnType.SelectedValue;

cmd.Parameters.Add("@.ReasonOfLeave", SqlDbType.NVarChar, 1000);

cmd.Parameters["@.ReasonOfLeave"].Value = txtReason;

con.Close();

con.Open();

cmd.ExecuteNonQuery();

con.Close();

Stored proc is as follows:

ALTER PROCEDUREdbo.SP_InsertIntoLeave

(

@.empidas char(20), @.empNameas nvarchar(50), @.totalLeaveas decimal(9) = 12, @.LeaveFromas datetime,

@.LeaveToas datetime, @.TotalDaysas bigint, @.TypeOfLeaveas nvarchar(50), @.ReasonOfLeaveas nvarchar(1000),

@.RemainigLeaveas decimal(9)

)

/*

(

@.parameter1 datatype = default value,

@.parameter2 datatype OUTPUT

)

*/

AS

/* SET NOCOUNT ON */

INSERT INTOLeave_Table

(

emp_id, emp_Name, Total_Leave, Leave_From, Leave_To, Total_no_of_Days, Type_of_Leave, Reason_of_Leave,

Leave_Remaining

)

VALUES

(

@.empid, @.empName, @.totalLeave, @.LeaveFrom, @.LeaveTo, @.TotalDays, @.TypeOfLeave, @.ReasonOfLeave,

@.RemainigLeave

)

RETURN

Thanks in Advance.

just a thought

could it be this line?

cmd.Parameters["@.TotalDays"].Value = txtNoofDays.Text;

you defined the Parameter @.TotalDays as a big int but you are supplying it a string. try parsing txtNoofDays.Text to a int.

int.Parse(txtNoofDays.Text.ToString());

hth,
mcm

Error Handling/ Stored Procs

Hi,
I'm doing some fairly basic updates with stored procedures. 99% of them affect one row. I've jsut discovered that I can't get the value of @.@.rowcount and @.@.error to return as output parameters (if I check one, the other one gets reset!). My theory is then to return the rowcount and if it's not = 1, then I know I've had a problem. If I begin a transaction in vb.net and call each proc in the required order and check each step that rowcount = 1, is this a reliable method of ensuring no errors have occurred?
Thanks.As far as dealing with both @.@.ROWCOUNT and @.@.ERROR is concerned, this is what I do:

SELECT @.lError = @.@.ERROR, @.lRowCount = @.@.ROWCOUNT

I select the values into local variables in the stored procedure andthen do whatever is needed based on those values. Note that youmust SELECT both values in the same statement, and I *believe* @.@.ERRORmust appear first because it will be reset when @.@.ROWCOUNT is accessed.
In your case, @.lError should be 0 and @.lRowCount should be 1 when a record is inserted correctly.


|||Thanks, I can now get my two values back!

Error Handling in procs etc.

In the last hour a few people have referred to the site: http://www.sommarskog.se/
error-handling-II.html
I am trying to figure out the error checking. I used the document and the bo
l. For example, I cut this from the bol:
USE pubs
GO
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
IF @.@.ERROR = 547
print 'A check constraint violation occurred'
Once I FIXED the code (the bol uses double quotes) I was able to see the pri
nt statement appear. That was an improvement.
Now in the sample code from http://www.sommarskog.se/error-handling-II.html
he returns a value. When I modify my code like this:
USE pubs
GO
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
IF @.@.ERROR = 547
return 12
(12 is just a made up number to test with)
I get:
Server: Msg 178, Level 15, State 1, Line 7
A RETURN statement with a return value cannot be used in this context.
How CAN I return something?
Thanks.Roger,
Try putting your code inside a stored procedure.
create procedure dbo.p1
@.old_aid varchar(11),
@.new_aid varchar(11)
as
set nocount on
UPDATE dbo.authors
SET au_id = @.new_aid
WHERE au_id = @.old_auid
return @.@.error
go
declare @.rv int
declare @.error int
exec @.rv = dbo.p1 '172-32-1176', '172 32 1176'
set @.error = isnull(nullif(@.rv, 0), @.@.error)
print @.error
go
AMB
"Roger Twomey" wrote:

> In the last hour a few people have referred to the site: http://www.sommarskog.s
e/error-handling-II.html
> I am trying to figure out the error checking. I used the document and the
bol. For example, I cut this from the bol:
> USE pubs
> GO
> UPDATE authors SET au_id = '172 32 1176'
> WHERE au_id = '172-32-1176'
> IF @.@.ERROR = 547
> print 'A check constraint violation occurred'
>
> Once I FIXED the code (the bol uses double quotes) I was able to see the p
rint statement appear. That was an improvement.
> Now in the sample code from [url]http://www.sommarskog.se/error-handling-II.html[/url
] he returns a value. When I modify my code like this:
> USE pubs
> GO
> UPDATE authors SET au_id = '172 32 1176'
> WHERE au_id = '172-32-1176'
> IF @.@.ERROR = 547
> return 12
> (12 is just a made up number to test with)
> I get:
> Server: Msg 178, Level 15, State 1, Line 7
> A RETURN statement with a return value cannot be used in this context.
> How CAN I return something?
> Thanks|||>>
Server: Msg 178, Level 15, State 1, Line 7
A RETURN statement with a return value cannot be used in this context.
How CAN I return something?
By doing so from within a stored procedure. if you are not in a stored
procedure, where are you returning to? PRINT instead, or SELECT, or issue
your own RAISERROR().
A|||Here is my code:
CREATE PROCEDURE dbo.prTest
AS
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
IF @.@.ERROR = 547
Begin
return 123
End
select 'fred'
go
When I run the proc I get this:
Server: Msg 547, Level 16, State 1, Procedure prTest, Line 4
UPDATE statement conflicted with COLUMN CHECK constraint 'CK__authors__au_id
__0CBAE877'. The conflict occurred in database 'Pubs', table 'authors', colu
mn 'au_id'.
The statement has been terminated.
This is exactly the same result I get if there is no @.@.Error code. I don't s
ee how this error checking code did anything?
Shouldn't I see something different?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:u0ZJrGvYGH
A.4760@.TK2MSFTNGP03.phx.gbl...
> Server: Msg 178, Level 15, State 1, Line 7
> A RETURN statement with a return value cannot be used in this context.
>
> How CAN I return something?
>
> By doing so from within a stored procedure. if you are not in a stored
> procedure, where are you returning to? PRINT instead, or SELECT, or issue
> your own RAISERROR().
>
> A
>
>|||> Shouldn't I see something different?
Why do you say that? Think about it:
The update is executed. It encounters an error on a row. The error is return
ed to the client
application.
The IF statement is executed and the condition is true
The RETURN statement is executed, returning the value 123 to the calling pro
cedure/application.
You don't see the return value because no-one is catching and doing anything
with that value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message news:OctDzvvYGHA.4060@.
TK2MSFTNGP02.phx.gbl...
Here is my code:
CREATE PROCEDURE dbo.prTest
AS
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
IF @.@.ERROR = 547
Begin
return 123
End
select 'fred'
go
When I run the proc I get this:
Server: Msg 547, Level 16, State 1, Procedure prTest, Line 4
UPDATE statement conflicted with COLUMN CHECK constraint 'CK__authors__au_id
__0CBAE877'. The
conflict occurred in database 'Pubs', table 'authors', column 'au_id'.
The statement has been terminated.
This is exactly the same result I get if there is no @.@.Error code. I don't s
ee how this error
checking code did anything?
Shouldn't I see something different?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u0ZJrGvYGHA.4760@.TK2MSFTNGP03.phx.gbl...
> Server: Msg 178, Level 15, State 1, Line 7
> A RETURN statement with a return value cannot be used in this context.
> How CAN I return something?
> By doing so from within a stored procedure. if you are not in a stored
> procedure, where are you returning to? PRINT instead, or SELECT, or issue
> your own RAISERROR().
> A
>|||When executing the stored procedure you must declare (and use) a variable to
assign the return value from the procedure to:
declare @.r int
exec @.r = <procedure> <parameter list>
print @.r
What exactly are you trying to achieve?
ML
http://milambda.blogspot.com/|||Well... At this point I am trying to achieve the Zen state of knowlege. ;)
I am trying to figure out how this works so that I might be able to apply it
to something in the future.
Currently a huge amount of my .net code is error checking. Because I have
never been able to figure out the @.@.Error in SQL server I rely on my .Net
code to figure out what happened and fix it.
I think it would be ever so much easier if I understood @.@.Error use so that
I could take care of some of this in SQL itself. The sample I gave you is
just me trying to figure out what the heck is happening.
Okay. Executing the way you suggested worked. Now I just have to figure out
how I can use that knowlege.
For example, could my variable use to hold the error state be an output
param? I know how to get those from vb.net.
It's amazing the things I don't know! ;)
Thanks for your help.
Anyway, I will try
"ML" <ML@.discussions.microsoft.com> wrote in message
news:66BD512F-C1AE-4CD8-8C40-575153F30B9C@.microsoft.com...
> When executing the stored procedure you must declare (and use) a variable
> to
> assign the return value from the procedure to:
> declare @.r int
> exec @.r = <procedure> <parameter list>
> print @.r
> What exactly are you trying to achieve?
>
> ML
> --
> http://milambda.blogspot.com/|||As suggested before, read through Erland's articles. That will give you the
basics and a few good practices.
There are three ways a procedure can return data:
1) a result set (one or more) - the results of select statements (with the
exception of variable assigning selects);
2) output parameters - declared as output parameters when the procedure is
dreated and when the procedure is executed; and
3) the return value.
The latter is usually used for just the purpose of either returning 0 when
the procedure was executed without any errors, or returning a SQL Error
number (look at the sysmessages system table) or a user-defined numeric valu
e
that can be trapped in the calling process (e.g a user error could be the
fact that no rows were affected by an update statement).
But the essence of error-handling is IMHO:
1) to *prevent* possible errors (by validating inputs); and
2) to report fatal (unrecoverable) errors to the caller.
You mention the fact that you do most of your development work in .Net, so I
assume you are familiar with the TRY...CATCH method. If you need this
functionality, then you'd have to use SQL 2005, since it's not known in
previous versions.
What exactly is your goal here? We can help you find the best solution to
any problem, but we really need to know what it is that you need.
ML
p.s. the Zen model is the future. :)
http://milambda.blogspot.com/

Error Handling in Activation Procs

I am kinda curious how the rest of you are doing your error handling inside your activation stored procedures...best practices says you should not rollback the part of your transaction that receives the record off of the queue...but using a try...catch block will only allow you to rollback the entire transaction. I tried using savepoints and starting the try...catch after the savepoint and the proc still gives me the error: "

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." when the error scenario is encountered.

I tried using @.@.Error and checking for errors at the statement level which would allow me to do a partial rollback, but the type of errors I receive (i.e. invalid data types etc) are aborting the entire batch instead of passing the error and continuing.

Can other people on this forum using service broker give me an idea of how you are getting around this issue?

Hmmm...interesting. Where did you read the best practice about not rolling back the part of your code that receives the record off of the queue. I am not sure that makes sense to me. I would want to put the message back onto the queue if I know I have ran into an error.|||The idea is to avoid Poison Messaging where possible by accounting for whatever errors you know you will encounter (foreign key errors, Invalid Data types)...these types of errors will never be able to run and will therefore, bring down your queue every time if you rollback. The idea is to never roll back the receive, unless there is some type of fatal error that causes the batch to abort. In that case, as long as your receive is in a transaction, it will be rolled back automatically. If it is a handled error, you need to do something else with that message to move it off the queue.|||

On one of our systems we use seperate transactions for recieving the message and processing the message. The transaction around the receive simply removes the message from the queue and logs it to an audit table. If this step fails then we rollback and put the message back onto the queue. If we successfully receive and log the message we then begin another transaction (if applicable) for the processing of the message. Therefore if this fails for some reason (ie constraints) then we just rollback the processing and not the receive from the queue - we can then get the message from the audit table to re-process if required. Our idea is to keep the code in the transaction that includes the receive from the queue as simple as possible to avoid failures and prevent rollbacks - therefore avoiding poison messages. I'm not sure if this would be applicable in your case.