Why wont this datareader return any values?

Grant

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

Nov 17 '05 #1

Miha Markic [MVP C#]

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

Nov 17 '05 #2

John Richardson

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

Nov 17 '05 #3

Miha Markic [MVP C#]

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

Nov 17 '05 #4

Hylton

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

Nov 17 '05 #5

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

Nov 17 '05 #6

Miha Markic [MVP C#]

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.

Nov 17 '05 #7