String parameter in WHERE clause not providing exact matches.

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

I am exectuing a query (against an Access database) and the results
are not an exact match of the search criteria.

I have a list of packages saved in an Access file. Before doing
certain operations, I want to see if a tracking number has already
been saved to file.

The tracking number "1234567890" is already stored in the file.
The tracking number "12345678901" is NOT in the file.

When I execute the query "Select * from Pkg WHERE TrackingNo =
'12345678901', nothing should be returned. Instead the record for
tracking number "1234567890" is returned.

I thought that the '=' sign meant an exact match, but in this case
that is not what's happening.

For what it's worth the same query run in Access does not return any
records (which is the behavior I'm looking for). This leads me to
believe this is a c# issue and not an Access issue.


Any ideas?

TIA
Doug Ferguson

This is a snippet of the code:

string strSql = "Select * from Pkg where TrackingNo = '" +
this.TrackingNo + "'";

OleDbDataAdapter da = new OleDbDataAdapter(strSql,conStr);
da.Fill(dt) //dt is a datatable declared earlier
 
Hello Doug,

Have you tried tracing the code and seeing exactly what is being set as
this.TrackingNO? It could be that it is getting passed in as 1234567890
instead of 12345678901.

HTH,

Bill P.
 
This leads me to believe this is a c# issue and not an Access issue.

I would suspect that its access's OleDB provider, not C#, where the issue
is. You might want to try your query from VB or VFP or something through
ADO.

Also, is the TrackingNo field big enough for all 11 characters? If its 10
characters long, and the query is "smart" it might truncate your query
parameter to that length, thus, finding the match. Thats just a wild guess
though.
 
Why don't you try the query in Access exactly as you constructed it in code.
I noticed that you used a single quote around the string. I recall that
access usually uses double quotes around strings; so set the query like so:

"Select * from Pkg WHERE TrackingNo = ""12345678901"""

The above would pass the query to the provider as:

Select * from Pkg WHERE TrackingNo = "12345678901"
 
Thanks for the help, but I realized I did not fully understand how the
fill method worked with datatables. The error was not in the select
statement at all. Doh!

I thought the datatable would be repopluated *from scratch* when I
used the fill method. In other words, I thought the fill method would
repopulate the datatable *starting from the first row* each time the
method was called. It didn't work that way. Instead, it merely
appended the new query results to the existing datatable rows.

What happened was that I queried the file for the string "1234567890".
That string was found and was added to a new, empty datatable. Then,
I subsequently queried for the string "12345678901", which should not
have been found.

That string was NOT found and NOT added to the datatable. The one row
in the datatable was not from the second query, but was the row that
remained after my first query.

After running the second (unsuccessful) query, I looked at the
datatable and saw what turned out to be the results of the first
query. Because of the similarity in tracking numbers (and b/c I didn't
fully understand how .fill worked), I thought the SELECT statement was
misbehaving and acting like a "LIKE" SELECT instead of an "EXACT"
SELECT.

So...the SELECT statement worked just fine. It was the fill method
that got me.

Thanks again.
DF
 
Which brings me back to my earlier question about ado.net (and the
MS-provided examples specifically)... Does anyone *really* program
that way? (that's a dig at the designers of ado.net - at Doug)
 
Back
Top