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?
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 6 5908
Hi Grant,
"Grant" <gp*****@hotmail.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl... 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
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 [MVP C#]" <miha at rthand com> wrote in message
news:eM*************@TK2MSFTNGP10.phx.gbl... Hi Grant,
"Grant" <gp*****@hotmail.com> wrote in message news:%2****************@tk2msftngp13.phx.gbl...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
There is also a more serious risk - sql injection.
That's why you should always use parametrised queries.
--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
"John Richardson" <j3**********@hotmail.com> wrote in message
news:OK**************@TK2MSFTNGP09.phx.gbl... 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 [MVP C#]" <miha at rthand com> wrote in message news:eM*************@TK2MSFTNGP10.phx.gbl... Hi Grant,
"Grant" <gp*****@hotmail.com> wrote in message news:%2****************@tk2msftngp13.phx.gbl...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
Wrap the whole lot in a
try
{
catch (OleDbException e)
{
strLastErrorMessage = e.Message;
}
and put a break point on the exception. Message should tell you why it is
not doing anything if it throwing an exception.
tcss
"Grant" wrote: 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?
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
I need a flogging for being so stoopid.
I was using the wrong database. Ive just spent the past couple of hours
swearing at the datareader.
Sorry bout that.
"Grant" <gp*****@hotmail.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl... 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?
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
Yeah, always blame the poor innocent datareader :-)
--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
"Grant" <gp*****@hotmail.com> wrote in message
news:us**************@TK2MSFTNGP12.phx.gbl... I need a flogging for being so stoopid.
I was using the wrong database. Ive just spent the past couple of hours swearing at the datareader. This discussion thread is closed Replies have been disabled for this discussion. Similar topics
1 post
views
Thread by VIJAY KUMAR |
last post: by
|
5 posts
views
Thread by Rob Wire |
last post: by
|
2 posts
views
Thread by Martin Raychev |
last post: by
|
20 posts
views
Thread by Mark |
last post: by
|
1 post
views
Thread by Jim via DotNetMonster.com |
last post: by
|
3 posts
views
Thread by tshad |
last post: by
|
4 posts
views
Thread by Shapper |
last post: by
|
4 posts
views
Thread by hazz |
last post: by
|
3 posts
views
Thread by Johnny Jörgensen |
last post: by
| | | | | | | | | | |