I'm writing a small asp.net app that uses an access db. I'm using a layered architecture and business objects. In my dataaccess class, I have tis method that returns all records by UserID:
public static List<BilledeDetails> GetBillederByUserID(int userid)
{
using (OleDbConnection conn = new OleDbConnection(_connString))
{
OleDbCommand cmd = new OleDbCommand("GetBillederByUserID", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", OleDbType.Integer).Value = userid;
conn.Open();
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
return GetBilledeCollectionFromReader(reader);
else
return null;
}
}
The problem is that the reader object is null, even though the records with the specified userid exist in the db.Using the debugger, I can see that the parameter passed to the method is correct, and the SP is as simple as:
SELECT * FROM BILLEDER WHERE UserID = @UserID
I have tried using an AccessDataSource directly on the aspx page and hooking it up to a grid, and then the records are returned. Also, using my dataaccess class, I can insert new records without problems, so the connection works just fine.
I have used this model in many apps with sql server, and it allways works. So, my question is if there are any known issues when using OleDbDataReaders with asp.net? Am I using it correctly, or am I missing something?
Thanks guys, I guess you answered my initial questions, but I'm gonna keep the thread alive for a little bit. It seems the problem is with the parameter I'm passing to the dataaccess method. If I use the method GetBilleder() (sorry for the weird mix of Danish
and English), which returns all pictures without parameter, it works. I'm passing in the UserID from the Membership system from my codebehind file like this:
List<Billede> _billeder = new List<Billede>();
_billeder = Billede.GetBillederByUserID(Convert.ToInt32(Membership.GetUser(User.Identity.Name).ProviderUserKey));
I'm sure the userid exists in the database.
If I "force feed" the procedure with an existing userid like so:
public static List<BilledeDetails> GetBillederByUserID(int userid)
{
using (OleDbConnection conn = new OleDbConnection(_connString))
{
OleDbCommand cmd = new OleDbCommand("GetBillederByUserID", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", OleDbType.Integer).Value = 37;
conn.Open();
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
return GetBilledeCollectionFromReader(reader);
else
return null;
}
}
Nothing gets returned...
If I run the procedure directly in Access and give it the same number, all the right rows are returned.
Now, if I response.write(Membership.GetUser(User.Identity.Name).ProviderUserKey.GetType()) , I get: System.Int32
If I try to pass that directly to my method like this:
intellisense says: Cannot implicitly convert type object to int. I thought ProviderUserKey was of type System.Int32...
Is there a problem with the way I'm passing in the ProviderUserKey?
Maybe this is why my method cannot recognize the parameter I'm passing, only I would have thought the compiler had cought if there was a data type problem...
Try adding a breakpoint at the beginning of the GetBillederByUserID method to see what value is actually being passed in as the userid. If it's 0, then the value is null. I'm not sure how the Access Membership provider works in that respect, but you should
have access to the source code which you can pick through.
Uhm, not sure if I dare to tell what the problem was...
I have a business class with a" GetBillederByUserID" method that directs the call from the UI to the DAL, and in this method I called a different method in the DAL class.
So, GetBillederByUserID in DAL never got the call, it was picked up by another method that, by incident, also took an int as parameter type.
It was one of those late night\copy-paste\tight deadline type of errors, located about a foot and a half from the keyboard.
mrgruby
Member
7 Points
88 Posts
OleDbDataReader is null even when data exists...
Dec 08, 2009 03:46 AM|mrgruby|LINK
I'm writing a small asp.net app that uses an access db. I'm using a layered architecture and business objects. In my dataaccess class, I have tis method that returns all records by UserID:
public static List<BilledeDetails> GetBillederByUserID(int userid) { using (OleDbConnection conn = new OleDbConnection(_connString)) { OleDbCommand cmd = new OleDbCommand("GetBillederByUserID", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UserID", OleDbType.Integer).Value = userid; conn.Open(); OleDbDataReader reader = cmd.ExecuteReader(); if (reader.Read()) return GetBilledeCollectionFromReader(reader); else return null; } }The problem is that the reader object is null, even though the records with the specified userid exist in the db.Using the debugger, I can see that the parameter passed to the method is correct, and the SP is as simple as:
I have tried using an AccessDataSource directly on the aspx page and hooking it up to a grid, and then the records are returned. Also, using my dataaccess class, I can insert new records without problems, so the connection works just fine.
I have used this model in many apps with sql server, and it allways works. So, my question is if there are any known issues when using OleDbDataReaders with asp.net? Am I using it correctly, or am I missing something?
Thnx.
OleDbDataReader
pankajgohel
Participant
752 Points
285 Posts
Re: OleDbDataReader is null even when data exists...
Dec 08, 2009 04:59 AM|pankajgohel|LINK
just try calling simple select instate of clalling SP. Will that work?
Pankaj Gohel
Please Mark as Answer if you find the post useful.
hans_v
All-Star
25756 Points
7025 Posts
Re: OleDbDataReader is null even when data exists...
Dec 08, 2009 04:00 PM|hans_v|LINK
You're using it correctly, I just did a simple test using your code with a stored query just like you and everything is working ok!
Mikesdotnett...
All-Star
184255 Points
26627 Posts
Moderator
MVP
Re: OleDbDataReader is null even when data exists...
Dec 09, 2009 02:50 AM|Mikesdotnetting|LINK
No
Yes
Yes, but what it might be is a bit tricky. Where does the code fail?
ASP.NET Tutorials | Learn Entity Framework Core
mrgruby
Member
7 Points
88 Posts
Re: OleDbDataReader is null even when data exists...
Dec 09, 2009 03:15 PM|mrgruby|LINK
Thanks guys, I guess you answered my initial questions, but I'm gonna keep the thread alive for a little bit. It seems the problem is with the parameter I'm passing to the dataaccess method. If I use the method GetBilleder() (sorry for the weird mix of Danish and English), which returns all pictures without parameter, it works. I'm passing in the UserID from the Membership system from my codebehind file like this:
I'm sure the userid exists in the database.
If I "force feed" the procedure with an existing userid like so:
public static List<BilledeDetails> GetBillederByUserID(int userid) { using (OleDbConnection conn = new OleDbConnection(_connString)) { OleDbCommand cmd = new OleDbCommand("GetBillederByUserID", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UserID", OleDbType.Integer).Value = 37; conn.Open(); OleDbDataReader reader = cmd.ExecuteReader(); if (reader.Read()) return GetBilledeCollectionFromReader(reader); else return null; } }Nothing gets returned...
If I run the procedure directly in Access and give it the same number, all the right rows are returned.
Now, if I response.write(Membership.GetUser(User.Identity.Name).ProviderUserKey.GetType()) , I get: System.Int32
If I try to pass that directly to my method like this:
intellisense says: Cannot implicitly convert type object to int. I thought ProviderUserKey was of type System.Int32...
Is there a problem with the way I'm passing in the ProviderUserKey?
Maybe this is why my method cannot recognize the parameter I'm passing, only I would have thought the compiler had cought if there was a data type problem...
Mikesdotnett...
All-Star
184255 Points
26627 Posts
Moderator
MVP
Re: OleDbDataReader is null even when data exists...
Dec 09, 2009 03:38 PM|Mikesdotnetting|LINK
Try adding a breakpoint at the beginning of the GetBillederByUserID method to see what value is actually being passed in as the userid. If it's 0, then the value is null. I'm not sure how the Access Membership provider works in that respect, but you should have access to the source code which you can pick through.
ProviderUserKey is an object, by the way: http://msdn.microsoft.com/en-us/library/system.web.security.membershipuser.provideruserkey.aspx
ASP.NET Tutorials | Learn Entity Framework Core
mrgruby
Member
7 Points
88 Posts
Re: OleDbDataReader is null even when data exists...
Dec 11, 2009 08:36 AM|mrgruby|LINK
Uhm, not sure if I dare to tell what the problem was...
I have a business class with a" GetBillederByUserID" method that directs the call from the UI to the DAL, and in this method I called a different method in the DAL class.
So, GetBillederByUserID in DAL never got the call, it was picked up by another method that, by incident, also took an int as parameter type.
It was one of those late night\copy-paste\tight deadline type of errors, located about a foot and a half from the keyboard.