Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Next, Previous, Last, First in ADO.NET

mark1110 (Programmer) (OP)
25 Jan 07 19:41
I am rewriting a VB6 application that is using ADO to C# using ADO.NET. What I would like to do seems simple enough, I have a form with several textboxes, comboboxes, and checkboxes. I would like to open a Access database, read its contents, then populate the form. I also need to go to the next record, go to the previous record etc. Here is part of the code I wrote:


        private void frmClient_Load(object sender, EventArgs e)
        {

           OleDbDataAdapter daClient;
           DataTable dtClient;
           string strClientSQL;
         


            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source= " + Utilities.stdDir;
            strClientSQL = "SELECT * FROM Client ORDER BY casename";
            daClient = new OleDbDataAdapter(strClientSQL,Utilities.connectionString);
            dtClient = new DataTable("ClientInfo");
            daClient.Fill(dtClient);


This works, however since I have over 500,000 records it takes 10 minutes to load. I am not sure if this is correct way to do this. Is there a better way?

Thanks,

Mark
jbenson001 (Programmer)
25 Jan 07 22:24
500,000 rows is way to many to get back and show to end users.  Even if it took 10 seconds to get all the data, it would be too overwhelming for the user.  I would suggest having a page that lets the user filter the data by some criteria.  Then display the results in a grid, with custom paging.  This way there is less stress on the DB. Then allow the user to select a row from the grid, and display the results as you want.
ca8msm (Programmer)
26 Jan 07 2:20
Also, try not to use Access as a database for web applications. It has a hard time coping with multiple connections.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

mark1110 (Programmer) (OP)
26 Jan 07 12:30
Unfortunately I have to use access as my database. The current code which uses ADO is:
Set cnn1 = New Connection
  
cnn1.CursorLocation = adUseClient

strCnn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & gcMDB
  
cnn1.Open strCnn
  
Set adoPrimaryRS = New Recordset
  
adoPrimaryRS.CursorLocation = adUseServer
  
strPrimarySQL = "select * from Client "
adoPrimaryRS.Open strPrimarySQL, cnn1, adOpenStatic, adLockOptimistic

This works fine. I can go to the next record, previous record, even the last record in just seconds.

There has to be a similar way of doing this in ADO.NET. The form I have just has about 15 textboxes that need to be populated when I press the previous or next button. Any help you can give me will be greatly appreciated.

Thanks,

Mark
ca8msm (Programmer)
26 Jan 07 13:06

Quote:


This works fine. I can go to the next record, previous record, even the last record in just seconds.
The difference is that VB6 is a windows programming environment and therefore the actual data can be stored in memory on the client. Web based applications don't work like that and you'll find that working with such large datasets isn't a good idea. Have a look at some of the ASP.NET examples such as this one from Scott Mitchell, as these should guide you in the right direction:

http://www.asp.net/learn/dataaccess/tutorial14cs.aspx?tabid=63

Quote:


Unfortunately I have to use access as my database
Any reasons for this?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close