Problem with ExecuteNonQuery() Result, Access, ASP.NET, C#, ADO, OleDB

  • Thread starter Thread starter vanvee
  • Start date Start date
V

vanvee

Hi

I have the block of code below which is supposed to determine the
number of rows affected with an update statement (ASP.Net, Access, C#,
ADO.Net, OleDB) to determine if the update was successful or if a
concurrency error occurred. There is an enumeration defined called
DBResult where 1 is Success, 2 is ConcurrencyError, and 3 is
DatabaseError. I keep getting a strange behavior on the
ExecuteNonQuery() where it returns 0 sometimes and returns 1 sometimes,
but in all cases it writes to the database correctly. So, I end up
getting a result returned to the user that states concurrency error
(because the reurn from the ExecuteNonQuery is a 0 so the else kicks
in) when the database actually updates correctly (and the result should
be 1)!!!??? There seems to be no reason why it returns inconsistent
results. With the same exact command, sometimes it return 0, sometimes
1, when in all cases 1 row is updated. Any help would be greatly
appreciated.

try
{
if (cmdSources.ExecuteNonQuery() > 0)
{
databaseResult = (int) DBResult.Success;
}
else
{
databaseResult = (int) DBResult.ConcurrencyError;
}
}
catch(Exception e)
{
databaseResult = (int) DBResult.DatabaseError;
}
conSources.Close();
return databaseResult;
 
When you execute update command, ExecuteNonQuery() return
0 just means under the condition it updates nothing (o row
is affected). It is not an error.

Elton Wang
(e-mail address removed)
 
Thanks for the messages. Here is the whole update method. The problem
though is that sometimes the returned value is 0 when the row actually
changes!! It just seems to give 0 sometimes and 1 sometimes but always
updates the new value??

Thanks for any more help!!

public static int UpdateSource(SourceItem NewSource, SourceItem
OldSource)
{
int databaseResult = -2;
OleDbConnection conSources = GetSourcesConnection();
String sSQLCommand;
sSQLCommand = "Update Sources "
+ "Set Source = ? "
+ "Where SourceID = ? "
+ "And (Source = ? "
+ "Or (? = '' And Source Is Null))";
conSources.Open();
OleDbCommand cmdSources = new OleDbCommand(sSQLCommand, conSources);
if (NewSource.Source == "")
{
cmdSources.Parameters.Add("NewSource", DBNull.Value);
}
else
{
cmdSources.Parameters.Add("NewSource", NewSource.Source);
}
cmdSources.Parameters.Add(new OleDbParameter("OldSourceID",
OldSource.SourceID));
cmdSources.Parameters.Add(new OleDbParameter("OldSource",
OldSource.Source));
cmdSources.Parameters.Add(new OleDbParameter("OldSourceNull",
OldSource.Source));
try
{
if (cmdSources.ExecuteNonQuery() > 0)
{
databaseResult = (int) DBResult.Success;
}
else
{
databaseResult = (int) DBResult.ConcurrencyError;
}
}
catch(Exception e)
{
string exc;
exc = e.ToString();
databaseResult = (int) DBResult.DatabaseError;
}
conSources.Close();
return databaseResult;
}
 
I did some more work on this problem. It seems that the

+ "And (Source = ? "
+ "Or (? = '' And Source Is Null))";

is the problem. When I just do a straight "Set Source = ? Where
SourceID = ? " , the ExecuteNonQuery returns 1...but if I put the
concurrency checking (with the lines above), it returns 0 (yet it DOES
update!!). Could this be something with Access where it is actually
executing a Select statement to check the database values first and
then that messes up the return value for the ExecuteNonQuery() ??

Thanks again for nay help
 
In my understanding your update command may should be

Update Sources Set Source = ? Where SourceID = ? And
(Source = '' Or Source Is Null)

Hope it helps,

Elton Wang
(e-mail address removed)
 
Forget my last message.

The logic of your update command is not very clear.

Suppose you want to update in following way

For SourceID = specified ID And Source = old Source Value
If new Source value = empty string
update Source to null
else
update Source to new value

You might do it:

sSQLCommand = "Update Sources Set Source = @NewSource "
+ "Where SourceID = @OldSourceID And Source = @OldSource"

conSources.Open();
OleDbCommand cmdSources = new OleDbCommand(sSQLCommand,
conSources);
if (NewSource.Source.Equals(""))
{
cmdSources.Parameters.Add("@NewSource", DBNull.Value);
}
else
{
cmdSources.Parameters.Add("@NewSource",
NewSource.Source);
}
cmdSources.Parameters.Add("@OldSourceID",
OldSource.SourceID);
cmdSources.Parameters.Add("@OldSource", OldSource.Source);

cmdSources.ExecuteNonQuery();
// ...

Hope it helps,

Elton Wang
(e-mail address removed)
 
Back
Top