Re: OLEDBDataReader has no rows, yet query is fine in Access
- From: "Brendan Reynolds" <brenreyn@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 15 Dec 2005 21:55:24 -0000
Try using "%" rather than "*" as the wildcard ...
WHERE (([File].[Name] Like "%" & [a] & "%") Or ([File].[Path] Like "%" &
[b] & "%"));
--
Brendan Reynolds
"Gary Bond" <GaryB@xxxxxxxxxxxxxxxx> wrote in message
news:43D1160E-339D-4063-88DD-966EB0754FA4@xxxxxxxxxxxxxxxx
> 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.
>
.
- References:
- OLEDBDataReader has no rows, yet query is fine in Access
- From: Gary Bond
- OLEDBDataReader has no rows, yet query is fine in Access
- Prev by Date: OLEDBDataReader has no rows, yet query is fine in Access
- Next by Date: Gridview Date formatting problem.
- Previous by thread: OLEDBDataReader has no rows, yet query is fine in Access
- Next by thread: RE: OLEDBDataReader has no rows, yet query is fine in Access
- Index(es):
Relevant Pages
|
|
