Why does this query string work in Access but not C#?

Question

I have this bit of code:

conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\\myDB.accdb");
conn.Open();

sql = string.Format("SELECT Version FROM tblVersions where [FUL Flag] = 'Y'");
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();

da.Fill(ds);
dt = ds.Tables[0];

if (ds.Tables[0].Rows.Count == 1)
{
    tV.Text = dt.Rows[0][0].ToString();    //only looking for the first result
}
else
{
    tV.Text = "no match";
}

When I run it, it doesn't return any results. However if I copy the SELECT statement and paste it right into a query window in Access, it does find results. Here's what I paste into Access:

SELECT Version FROM tblVersions where [FUL Flag] = 'Y'

This returns many rows.

Am I missing a difference somewhere? Thanks!

EDIT: Found the solution .. I should be looking for

(ds.Tables[0].Rows.Count > 0)

instead of

(ds.Tables[0].Rows.Count == 1)

Since more than 1 row may be returned.

Jon Skeet · Accepted Answer · 2013-03-11 18:13:32Z

3

I'm assuming that your statement of behaviour here:

When I run it, it doesn't return any results.

means "the TextBox text is replaced with 'no match'". Is that right?

This returns many rows.

Well that explains it. Look at your condition:

if (ds.Tables[0].Rows.Count == 1)

You're claiming there are no matches in every case unless there's exactly one match.

You probably want:

if (ds.Tables[0].Rows.Count > 0)

answered Mar 11, 2013 at 18:13

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeff Brady Over a year ago

yeah i caught that right after posting my question. thank you!

Brian · Accepted Answer · 2013-03-11 18:23:46Z

1

You should do this:

ds.Tables[0].Rows.Count > 0 instead of ==1

Full example:

if (ds.Tables[0].Rows.Count > 0)
{
    tV.Text = dt.Rows[0][0].ToString();    //only looking for the first result
}
else
{
    tV.Text = "no match";
}

edited Mar 11, 2013 at 18:23

Brian

5,1377 gold badges41 silver badges52 bronze badges

answered Mar 11, 2013 at 18:19

Jesse Jin

1093 bronze badges

Collectives™ on Stack Overflow

Why does this query string work in Access but not C#?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related