CODEWITHSUNDEEP
c#sqlms-access
Jeff Brady
Jeff Brady

Reputation: 1498

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

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.

Upvotes: 0

Views: 180

Answers (2)

Jesse Jin
Jesse Jin

Reputation: 109

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

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1503859

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)

Upvotes: 3

Related Questions