We help IT Professionals succeed at work.

Problem using DataReader

ptslv
ptslv asked
on
297 Views
Last Modified: 2012-05-05
Hi, everyone!  Please help!!  

I have a web site built with ASP.Net and I am using C# in my code.  I am trying to query a table for a value to autofill a field in a form but am having problems getting the data reader to work.  I am getting an error message telling me I am missing a parameter.  The debugger skips over the while loop.  Here is my code:

private void GetLocation()
{
      bool bIsError=false;
      string myLoc = "";
      CUsers user =new CUsers();
      user=(CUsers)Session["SessionUser"];
      OleDbConnection con = new OleDbConnection(strConnection);
      OleDbCommand cmd = con.CreateCommand();
      cmd.CommandText="SELECT UserID, Location FROM Personnel Where UserID ='" + user.UserID +"'";
      
      try
      {
            con.Open();
            OleDbDataReader drLoc = cmd.ExecuteReader();
            while (drLoc.Read())
            {
                  myLoc =(drLoc["Location"].ToString().Trim());
            }
            con.Close();
            Location.Text=myLoc;
            Location.DataBind();
      }
      catch (Exception err)
      {
            con.Close();
            string str=err.Message;
            Label2.Visible=true;
            Label2.Text=str.ToString();
            bIsError=true;
      }
}
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for the quick response, rajesh.  

The debugger shows that "User.UserID" has a value.  I went ahead and placed the HasRows check before the while loop.  The complete exception is:  {"No value given for one or more required parameters." }.   There is a value in the table.  The code is bouncing out at:

 OleDbDataReader drLoc = cmd.ExecuteReader();

It never makes it to " if(drLoc.HasRows)"


 
CERTIFIED EXPERT

Commented:
You never open the command connection.

cmd.connection.open

Try that and let me know if it works for you.

Drew
CERTIFIED EXPERT

Commented:
But I see you are using con.CreateCommand which I don't use so I can't be sure

Drew

Author

Commented:
Drew,

I don't think that's the problem.  My connection does open.  That's the way I set it up all the time.  Helps me follow my code!  :-)

ptslv

Commented:
Is the connection open? I mean, check the connection properties in debug mode. I feel the connection is failing. I guess you are using a MDB database. check to see if its pointng to the right location where the MDB resides.

Commented:
try this:

OleDbConnection .Open();

OleDbCommand = OleDbConnection .CreateCommand();
OleDbCommand .CommandText = "SELECT UserID, Location FROM Personnel Where UserID ='" + user.UserID +"'";
OleDbDataReader oleReader = OleDbCommand .ExecuteReader();

Author

Commented:
Rajesh,

You were right.  

The connection failed.  I have a ServerVersion error:  an exception type: {System.InvalidOperationException} occurred.
State is closed.  However, the path to the database is correct.

Why would I get this error if the previous function worked and the connection was previously closed?

Commented:
Not sure. Could you please post the exact inner exception? And make sure you post all sub inner exceptions as well please.

Commented:
your connection string should look like this..

Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb;")

Author

Commented:
OK,  this is what I get when switching the code to your suggestion:

Connection string is good.

StackTrace      "   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteReader()\r\n   at DLS.Forms.TaskPerformance.GetLocationValue() in c:\\inetpub\\wwwroot\\MyWebSite\\forms\\task_form.aspx.cs:line 140"      string

InnerException      { }      System.Exception

err.Message      "No value given for one or more required parameters."      string



Commented:
               try    
                {
                    OleDbConnection con =new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\lms.mdb");
                    con .Open ();
                    OleDbCommand cmd =new OleDbCommand"SELECT UserID, Location FROM Personnel Where UserID ='" + user.UserID +"'",con );
                    OleDbDataReader drLoc= cmd.ExecuteReader ();
                    while(drLoc.Read())
                    {
                          myLoc =(drLoc["Location"].ToString().Trim());
                    }
                     drLoc.Close ();
                    con.Close ();
                }


Please try the above code.

Author

Commented:
rajesh,

FYI, the I had to put the connection declaration outside of the try, otherwise the con close() in the catch throws an error.

I used your code, putting in my datasource path and got the same error as before.  I am going to drop the data reader and use a dataset instead and see if that works.  Will let you know what happens.

Commented:
ASPNET AND/OR the IUSR_(yourmachinename) users on your machine should be granted write permissions on your DB. The IUSR group is comprised of internet users who access your server. I'm quite sure that when you open a page in you browser locally, you are accessing files as a member of this group.You usually get this error when
the account being used for file/disk access doesn't have permission to write
to the .mdb file.

Author

Commented:
Users have the write permission. I am able to write to the database using my forms.  

All I'm trying to do is replace a dropdown list on one form with a dynamically populated value from a master table so that the users don't have to keep inputting the same data over again.  I have several fields that I am able to do this with.  I am just having problems with this one field and I don't understand why.  The only difference between this field and the others is that this field is just a label data bound to one value whereas the others are dropdowns data bound to arrays.

Previously I tried to add this field to my session user class, but the entire code was just being stepped over like it wasn't even there.  So I opted to place it on the form itself.

Commented:
If you could be more specific about your requirements, maybe I can help.

Commented:
Did you solve the connection issue you were having before? You didnt tell me how you fixed that??

Author

Commented:
Oh, sorry.  I moved these two lines inside the try/catch block:

     OleDbCommand cmd = con.CreateCommand();
     cmd.CommandText="SELECT UserID, Location FROM Personnel Where UserID ='" + user.UserID +"'";

Actually, I'm thinking  OleDbDataReader oleReader = OleDbCommand .ExecuteReader(); has something wrong with it as this is the line that is returning the error.  It never makes it to the HasRows check.

As for what I am trying to do, it's a very simple process.  Our users register for the site and enter their locations.  When they go back to the site and go to another form, it asks for their location.  Instead of them having to reenter their location, I am retrieving it from their Personnel file based on their loginID.  I am trying to auto-populate that Location field on the form so they can just skip it.  I also auto-populate dropdown lists for courses from lookup tables, which I have no problem doing.


Commented:
Try retrieving the query using quick watch in debug and then run the query using query analyzer,unless you have already done so. :)

Commented:
Also check the userid field in the database.

It should be same type as the data type " user.UserID " in your your application.



Commented:
Its definitely something to do with your query. Some of the things you can do is - Check the column names in the table and match it to the query. Run it using query analyzer.  check the data types.

Let me know once you do this.

Author

Commented:
rajesh,

Sorry it took so long to get back to this.  Ya know, it's funny how one wrong little word in a query can cause you to try to fix something for hours, even days!!!  It helps to have the correct field names!!!!!!!!!!!!!!  Duh!!!!!  Lol!!!!  I'm so stupid sometimes!

I am giving you the points since you were practically dead on from the start.  Thanks!

ptslv

Commented:
You are welcome. :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.