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

[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;

 

[Sahil Malik]

ExecuteNonQuery is supposed to return you the number of rows affected.

How are you returning the DbResult out of it.

If you don't mind, can you post the commandtext to cmdSources? If it's a
s/proc, we'll need to see that too.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/

 

[Guest]

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)

 

[vanvee]

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;
}

 

[vanvee]

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

 

[Elton Wang]

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)

 

[Elton Wang]

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)

 

[vanvee]

I'll give it a try Elton and will post what happens. Thank you so much
for helping me.