yeah, I would look closely at your prodName variable.
The way you handle it is risky because the string must be exact... I
usually wrap my strings with a method to make sure the string is clean
(like trimming spaces, doubling any single apostrophe's in the string,
etc), and maybe add a %<string>% around it. Usually for string
comparisons, I use a LIKE in the where too.
Also, as an aside, if you alias your sql tables, it will make the sql look
a lot nicer, and you don't have to type as much.
Miha Markic said:
Hi Grant,
Grant said:
I am connecting to an access database using a datareader in C#. I get
results when I run a certain query from Access but when I run it from
Code it does not retrieve any results. I have put a stop point after the
string is created and it is correct. Its an inner join query so I was
wandering whether that is too complicated for a datareader to execute, or
if Im missing something else here?
Something else.
Perhaps there is something wrong with INNER JOIN or WHERE part (wrong in
the sense that it won't return any records).
Try running the same query without WHERE part and if there are no rows,
cut the INNER JOIN, too.
So we can pinpoint the problem.
The other suggestion would be to use parametrised query (passing
parameters instead of putting them into the sql itself).
--
Miha Markic [MVP C#]
RightHand .NET consulting & development
www.rthand.com
Blog:
http://cs.rthand.com/blogs/blog_with_righthand/
Heres what Im doing in code:
-------------------------------------------
string mySelectQuery = "SELECT
[Product-Packaging-Junction].[Product-Code], " +
"[Product-Packaging-Junction].[Component-Code],
[Product-Packaging-Junction].Quantity, " +
"[Component-Codes].[Quantity-In-Stock] " +
"FROM [Component-Codes] INNER JOIN [Product-Packaging-Junction] " +
"ON [Component-Codes].Code =
[Product-Packaging-Junction].[Component-Code]" +
"WHERE (([Product-Packaging-Junction].[Product-Code]='" + prodName +
"'))";
//at this point I can copy this string value into Access and get a
result...
OleDbDataReader dataReader;
OleDbConnection myDataReaderConnection = new
OleDbConnection(Constants.CONNECTION_STRING);
OleDbCommand myDataReaderCommand = new OleDbCommand(mySelectQuery,
myDataReaderConnection);
myDataReaderCommand.Connection.Open();
dataReader =
myDataReaderCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
Nothing happens here because the datareader is not populated
}
---------------------------------------------
Any assistance is greatly appreciated,
Grant
