cmd.ExecuteNonQuery() is always returning -1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

cmd.ExecuteNonQuery() is always returning -1

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2009-02-23 : 18:39:04

Dear All,

I have a piece of code that is calling a stored procedure to update a table in the database. The funny thing is that the update is happening successfully, and the return value when I execute the stored procedure from SQL Management Studio is correct, returning the ID that I am updating. However whenever I run my c# code, its always returning -1.

Here is my c# code:-

public override bool UpdateSeasonLeagueTable(SeasonLeagueTableDetails SeasonLeagueTable)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("HS_SeasonLeagueTable_UpdateSeasonLeagueTable", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@SeasonLTID", SqlDbType.Int).Value = SeasonLeagueTable.ID;
cmd.Parameters.Add("@SeasonStart", SqlDbType.Int).Value = SeasonLeagueTable.SeasonStart;
cmd.Parameters.Add("@SeasonEnd", SqlDbType.Int).Value = SeasonLeagueTable.SeasonEnd;
cmd.Parameters.Add("@TeamID", SqlDbType.Int).Value = SeasonLeagueTable.TeamID;
cmd.Parameters.Add("@Played", SqlDbType.Int).Value = SeasonLeagueTable.Played;
cmd.Parameters.Add("@Won", SqlDbType.Int).Value = SeasonLeagueTable.Won;
cmd.Parameters.Add("@Draw", SqlDbType.Int).Value = SeasonLeagueTable.Draw;
cmd.Parameters.Add("@Lost", SqlDbType.Int).Value = SeasonLeagueTable.Lost;
cmd.Parameters.Add("@Favour", SqlDbType.Int).Value = SeasonLeagueTable.Favour;
cmd.Parameters.Add("@Against", SqlDbType.Int).Value = SeasonLeagueTable.Against;
cmd.Parameters.Add("@Points", SqlDbType.Int).Value = SeasonLeagueTable.Points;
cn.Open();
int ret = ExecuteNonQuery(cmd);
return (ret == 1);
}
}

And here is the stored procedure:-

BEGIN TRAN
BEGIN TRY
BEGIN
BEGIN
UPDATE HS_SeasonLeagueTable
SET AddedDate = GETDATE(),
SeasonStart = @SeasonStart,
SeasonEnd = @SeasonEnd,
TeamID = @TeamID,
Played = @Played,
Won = @Won,
Draw = @Draw,
Lost = @Lost,
Favour = @Favour,
Against = @Against,
Points = @Points
WHERE SeasonLTID = @SeasonLTID
SET @returnvalue = @SeasonLTID

END
COMMIT TRAN
return @returnvalue
END
END TRY
BEGIN CATCH
ROLLBACK TRAN
return -1
END CATCH
COMMIT TRAN
return @returnvalue

Any help will be very much appreciated, since I cannot see why -1 is always returned and not the id of the updated record

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-23 : 18:54:50

You definitely don't need a transaction since you are only performing one update statement.

I don't see where you are setting the value of @returnvalue. Could you show us the actual code rather than just a snippet?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

monfu
Yak Posting Veteran

81 Posts

Posted - 2009-02-23 : 19:00:44

Hi tkizer

This part

int ret = ExecuteNonQuery(cmd);

is actually doing cmd.ExecuteNonQuery and is always returning -1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-23 : 19:06:00

You misunderstood my question. Where in your stored procedure are you setting the value of @returnvalue? Show us the actual stored procedure code and not just a snip of it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-23 : 20:33:46

Regarding ExecuteNonQuery, this is what MSDN says:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

So you may want to examine the stored proc "HS_SeasonLeagueTable_UpdateSeasonLeagueTable" to see if it really is updating, inserting or deleting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources