what is wrong with this?

[GS]

the procedure when executed from c#, it does update or insert but
I got result code of -1 return to c#
is not successful execution of stored proc 0 or number rows affected?

connectionString ="....whatever";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();

command.CommandText = " SPPersonSavedbo @personName = 'test 1'
wkStatus = '1', @comment='test comment1', @misc = 'whatever';"
iRows = command.ExecuteNonQuery();

// I got iRows = -1
why?



I got result code of -1 return to c#

ALTER PROCEDURE dbo.SPPersonSave
(
@personName varchar(16),
@wkStatus int,

@comment varchar(512),
@misc varchar(1024),
@mode int /*1 insert only, 2 update only, 3 insert or update as required
depending on key */
)
/* @Result int out 1 as success, - some number of record out there
with the same exist already - should not happen
0 failed to insert
*/

AS BEGIN
SET NOCOUNT ON ;
if ( @mode = 1 or (@mode = 3 and (select count(*) from Regex where
RegexName = @RegexName) < = 0) )
Begin
insert into Person(personName, wkStatus , comment, misc )
values (@personName, wkStatus, @comment, @misc ) ;
/* return status */
End
else if ( @mode = 2 or @mode = 3 )
update Person SET wkStatus = @wkStatus,
comment = @comment, misc = @misc
where personName = @personName;
else if (@mode = 4)
delete from Personwhere personName = @personName ;
else raiserror ('Invalid mode %d of operation for SPPersonSave. Valid: 1
insert, 2 update only, 3 update or insert as required, 4 delete', 1, 1,
@mode);
/* return @Result = @@ERROR ; */
RETURN
END

 

[Mr. Arnold]

the procedure when executed from c#, it does update or insert but
I got result code of -1 return to c#
is not successful execution of stored proc 0 or number rows affected?

connectionString ="....whatever";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();

command.CommandText = " SPPersonSavedbo @personName = 'test 1'
wkStatus = '1', @comment='test comment1', @misc =
'whatever';"
iRows = command.ExecuteNonQuery();

// I got iRows = -1
why?

ExecuteScalar if you want rows affected, as in the examples.

http://support.microsoft.com/kb/310070

 

[GS]

thank you. I am reading the article

my initial impression still leave me preferring executenonquery as I don't
care about the result row, I only care to know if it succeeded or failed

The application is for single user and mdf is local- using msde and I don't
expect multi-user at least for this release.

Consequently I don't see any benefit of getting the result row beside I may
get easier handling of result.

if the database is remote or multi-user, I would have preferred like you say
executeScalar along with timestamp for concurrency check

I will see if I can get something like @@rows or @@error return form proc
first


any hint?

 

[Mr. Arnold]

thank you. I am reading the article

my initial impression still leave me preferring executenonquery as I don't
care about the result row, I only care to know if it succeeded or failed

The application is for single user and mdf is local- using msde and I
don't
expect multi-user at least for this release.

Consequently I don't see any benefit of getting the result row beside I
may
get easier handling of result.

if the database is remote or multi-user, I would have preferred like you
say
executeScalar along with timestamp for concurrency check

I will see if I can get something like @@rows or @@error return form proc
first

Set an output variables with @@rows or @@error in the Stored Procedure and
have them returned.

http://www.sqlservercentral.com/columnists/kKellenberger/usingparameterswithstoredprocedures.asp

 

[Mr. Arnold]

One other thing here, you should put the code in a try/catch block

connectionString ="....whatever";
Try
(
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();

command.CommandText = " SPPersonSavedbo @personName = 'test 1'
wkStatus = '1', @comment='test comment1', @misc =
'whatever';"
iRows = command.ExecuteNonQuery();

}
}
catch SQLExecption exsql
{
string msg1 = exsql.message

// test for open connection
// if connection is open, close the connection even with an Using
statement on an // abort.
}

If the Stored Procedure errored in someway, it's going to be Caught.