OLEDBDataReader has no rows, yet query is fine in Access

Thanks in advance for any help,
(Hope this is the right forum - if not perhaps you could point me to the
'correct' one. Thanks.)

I want to query an Access database, using a saved query via C#. If I run the
query in Access it works fine and returns loads, (5548), of rows, but if I
run the query from a small form app, the datareader comes back with no rows.
Specifically:-

There are two tables, one contains file information, (called File), and one
contains CD/DVD information, (called Storage). Its for a little app so as I
can find my old backup files. I want to do a wildcard search and find all the
files that have, say, "gary" either in the filename or the file path.

The stored query is this

SELECT [File].[Name] AS Name, [File].[Path] AS Path, [Storage].[Name] AS
DiscName
FROM Storage INNER JOIN File ON [Storage].[StorageID]=[File].[StorageID]
WHERE (([File].[Name] Like "*" & [a] & "*") Or ([File].[Path] Like "*" & [b]
& "*"));

If I run the query in access I get prompted for 2 parameters, which I enter
"gary" for both and sure enough 5548 rows come back.

If I query the database with this code:

using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand("sqFileFind",conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sqFindFile";
cmd.Parameters.Add(new OleDbParameter(
"a",
OleDbType.VarChar,
255
));
cmd.Parameters[0].Value = textBox2.Text;
cmd.Parameters.Add(new OleDbParameter(
"b",
OleDbType.VarChar,
255
));
cmd.Parameters[1].Value = textBox2.Text;
cmd.UpdatedRowSource = UpdateRowSource.None;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
textBox1.AppendText(dr["Name"].ToString() + " :
" + dr["Path"].ToString() + "\r\n");
}
dr.Close();
}
}
conn.Close();
}

then nothing comes back from the database, the HasRows property of the
datareader is false, and yet it does not error and the FieldCount property =
3.

I thought of changing the stored query to this (taking out the 'prompts')

SELECT [File].[Name] AS Name, [File].[Path] AS Path, [Storage].[Name] AS
DiscName
FROM Storage INNER JOIN File ON [Storage].[StorageID]=[File].[StorageID]
WHERE (([File].[Name] Like "*" & [] & "*") Or ([File].[Path] Like "*" & [] &
"*"));

which runs fine in Access, (you only get prompted for one input), and
returns 5548 rows again. Yet if I run the C# above, but using only one
parameter of course, again I get no rows returned.

Then I thought of adding a PARAMETERS declaration at the top of the query,
like this:

PARAMETERS SearchTxt Text ( 255 );
SELECT [File].[Name] AS Name, [File].[Path] AS Path, [Storage].[Name] AS
DiscName
FROM Storage INNER JOIN File ON [Storage].[StorageID]=[File].[StorageID]
WHERE (([File].[Name] Like "*" & SearchTxt & "*") Or ([File].[Path] Like "*"
& SearchTxt & "*"));

Again, this works fine if you run it from Access, but again no rows come
back when trying to run from a form app via C#.


Any help on this, including an explanation of exactly what is going on would
be really welcome. (Its probably me being 'dense' again...ho ho)

regards,
Gary.

.

Relevant Pages

  • Re: Great SWT Program
    ... Windows and Mac and I expect KDE and Gnome search boxes reappear ... populated with the last performed query, while those / prompts give ... query in a few tools. ... history and/or autocomplete of previous searches. ...
    (comp.lang.java.programmer)
  • Re: Message displayed when running action query...
    ... > "You are about to run an update query that will modify data in your table. ... > If I open the database from a test VB project using the code below then I ... > not get these prompts when running the query - the query executes ... >> Jeff Boyce ...
    (microsoft.public.access.queries)
  • Re: Creating a crosstab parameter report
    ... > parameters but never with a cross tab query. ... > query design view or, if the query accepts the parameter entry, the report ... >> You can use criteria from parameter prompts or from controls on forms. ... >> Duane Hookom ...
    (microsoft.public.access.reports)
  • Re: Report with subreport and input parameters
    ... Thanks for pointing out that I had other query issues. ... prompts me for a State and then limits the records based on that State. ... Now I'm ready to ask my Report question. ... I've never worked with a subreport ...
    (microsoft.public.access.reports)
  • Re: Query prompts twice for same parameters
    ... Duane Hookom ... >> Don't know why you get the multiple prompts. ... >> MS Access MVP ... >>> save a query with user entered parameters. ...
    (microsoft.public.access.queries)