No exception thrown?

[jal]

I'm running a query in Csharp against a Jet database. As it turns out, I used the wrong column name - the table doesn't have a column called "ID". I do have an autonumber column, but I called it "autoNum".

I used the wrong column name - but no exception threw! Instead, the query executed, and what is even more scary, it updated all the rows! (It was only supposed to update the one row based on the record ID/autonumber).

(As for it not throwing exceptions, this is not in a try-catch block, nor is it called by a try-catch block - I know this because other errors threw exceptions in that block).


Here's what the C# Immediate Window outputs:

? cmd.CommandText
"UPDATE SortedEobs SET PrintedBy = @PrintedBy WHERE ID = @ID"


? cmd.Parameters.Count
2


? cmd.Parameters[0].ParameterName
"@PrintedBy"

? cmd.Parameters[1].ParameterName
"@ID"

? cmd.Parameters["@ID"].Value
52

What the heck is going on here? So I pasted the query into SQL view and ran it there. Oddly enough, Jet only popped up two param boxes. The first one was for "@PrintedBy". The second was "ID" - it was NOT "@ID". It then warned me "You are about to update 3 rows." Why didn't it ask me for "@ID"? That is, since "ID" isn't a real column name, shouldn't Access assume three params here:
ID
@Id
@PrintedBy

 

[jal]

The only explanation I can think of is that Jet is tripping up in regard to the use of the @-sign instead of question marks. But I've been running dozens of queries in this app against Jet, using the @-sign - all of them successful.

 

[jal]

Well, apparently the real trip-up here isn't the @-sign. It's the word "ID". When i use something else like this - that is, when I deliberately use a bad column name other than "ID", an exception DOES throw, indicating that no such column name exists. For instance:

UPDATE SortedEobs SET PrintedBy = @PrintedBy WHERE badColumnName = @ID

The above throws an exception in C#, as it should. As to why the original query didn't throw an exception, I'm still somewhat at a loss.

 

[Banana]

I think you'll see the same thing (e.g. no error) if you did this:

UPDATE SortedEobs SET PrintedBy = @PrintedBy WHERE 1 = @ID

It's not perceived as a error (and any other RDBMS wouldn't either).

It's same thing as saying:

SELECT PrintedBy, 1 FROM SortedEobs;

which will give you all dates and "1" for all rows. It's expected behavior in SQL. As you obviously noticed, if you did something same as this:

SELECT PrintedBy, [1] FROM SortedEobs;

Jet will interpret [1] as a parameter. Not sure what would happen if queried via code, though.


So, in short, it wasn't an error. It was valid SQL that didn't had the intended outcome. To throw exception over that one would require the computer be capable of telepathy.

Did that help?

 

[jal]

Um...no, Banana, for once your comments didn't help. Are you sure you read my query correctly?

 

[jal]

So, in short, it wasn't an error. It was valid SQL that didn't had the intended outcome. To throw exception over that one would require the computer be capable of telepathy.

Did that help?

Again, the SQL was

SELECT...WHERE ID=@ID

The word "ID" must be either a column name or a parameter. It's not a column name. Therefore Jet should have read it as a parameter, bringing the total to 3 parameters (note that the debugger reported only 2 params in the output above).

Therefore this discrepancy
3 params in query
2 params in the cmd object
should have thrown an exception. It didn not. This is not valid SQL. It's a malfunction on Jet's part.

 

[Banana]

You know what? That reply of mine? Total garbage.

I don't even know what I was thinking back then- I had just woke up and didn't have my caffeine.

I tested what I said earlier and found I did get errors expecting a parameter. So... that post was garbage. Sorry.

How are you connecting to the Access? Using OLEDB provider, right?

 

[jal]

Yes, OleDB provider. I'm okay with all this - I once read somewhere the Access has trouble with the @-sign in some situations, apparently this is probably one of them. I love the @-sign way too much to renounce it. I'll just steer clear from using "@ID" - instead I'll use something like
"@The_ID"
from now on. I had another large Access project where I ran dozens of queries using the @-sign, all of them worked great.