String parameter in WHERE clause not providing exact matches.

[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 "1234567890 1" 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 + "'";

OleDbDataAdapte r da = new OleDbDataAdapte r(strSql,conStr );
da.Fill(dt) //dt is a datatable declared earlier

[Bill Priess]

Re: String parameter in WHERE clause not providing exact matches.

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.
"Doug" <google@dcfergu son.com> wrote in message
news:f8cc232a.0 307101311.5b8b2 3b4@posting.goo gle.com...[color=blue]
> 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 "1234567890 1" 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 + "'";
>
> OleDbDataAdapte r da = new OleDbDataAdapte r(strSql,conStr );
> da.Fill(dt) //dt is a datatable declared earlier[/color]

[Mike Helland]

Re: String parameter in WHERE clause not providing exact matches.

> 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.

[Taiwo]

Re: String parameter in WHERE clause not providing exact matches.


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 = "1234567890 1"

---
Taiwo

"Doug" <google@dcfergu son.com> wrote in message
news:f8cc232a.0 307101311.5b8b2 3b4@posting.goo gle.com...[color=blue]
> 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 "1234567890 1" 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 + "'";
>
> OleDbDataAdapte r da = new OleDbDataAdapte r(strSql,conStr );
> da.Fill(dt) //dt is a datatable declared earlier[/color]

[Doug]

Re: String parameter in WHERE clause not providing exact matches.

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 "1234567890 1", 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

[Chris Hornberger]

Re: String parameter in WHERE clause not providing exact matches.

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)

google@dcfergus on.com (Doug) wrote in message news:<f8cc232a. 0307110700.135c 05fe@posting.go ogle.com>...[color=blue]
> 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 "1234567890 1", which should not
> have been found.
>[/color]