Fetch Data From A Database And Display It In A For

[kvarnerexpress 0]

Hi,i have one more query:i have created a MS-Access database with my table having two fields: Name and Id. i have added around 5 names and corresponding ids in the table.Now using VB, i have created a form that has a List view control. i have a button named 'Show' which when clicked should display the data in the list view from the table in the database.can anyone help me out with pseudo code or some useful links/sites??thanks in advance,kvarnerexpress

[michaelper22 0]

To put data access functionality into your code (I assume you're using VB 6), you need to use ADO. The two major classes that accomplish this are ADODB.Connection and ADODB.Recordset. I can't give you a tutorial on this since I haven't touched anything non-.Net related in a really long time, but I'm sure you can find a good one somewhere on the Web. Sorry I can't help.

[TPFWebmaster 0]

Hi,

i have one more query:

 

i have created a MS-Access database with my table having two fields: Name and Id. i have added around 5 names and corresponding ids in the table.

Now using VB, i have created a form that has a List view control. i have a button named 'Show' which when clicked should display the data in the list view from the table in the database.

can anyone help me out with pseudo code or some useful links/sites??

thanks in advance,kvarnerexpress

201884[/snapback]

Database Object

The first thing you must do in your application is to open a database where your tables are stored. You need to declare a variable to hold your database in order to do this. This is done with:

 

Dim dbMyDB As Database

This gives you a variable/object that can hold a reference to your database. To open a simple Access database named "MyDatabase.mdb", do this:

 

Set dbMyDB = OpenDatabase("MyDatabase.mdb")

You should really specify the complete path to the db, but if your current directory is the directory where the database is situated, this will work.

 

So, now you have opened a database. This won't give you any data. What you need to do is open a table in the database.

 

RecordSet Object

Visual Basic uses an object called RecordSet to hold your table. To declare such an object and to open the table, do this:

 

Dim rsMyRS As RecordSetSet rsMyRS = dbMyDB.OpenRecordSet("MyTable", dbOpenDynaset)

What happened there? Well, I declared a RecordSet object and used the Database object's OpenRecordSet method to open a table of type Dynaset. You can open a RecordSet in several modes. VB's online help file explains the different modes and what they ar e for. The Dynaset mode is the mode I use mostly. It gives you a RecordSet that you can add, delete and modify records in.

 

Accessing records

Now that we have opened a table (referred to as RecordSet from now on) we want to access the records in it. The RecordSet object allows us to move in it by using the methods MoveFirst, MoveNext, MovePrevious, MoveLast (among others). I will use some of these to fill up a list box with the records of our RecordSet.

 

To get this example to work, make a database (with Access) called "MyDatabase.mdb" with the table "MyTable" in it. This table should have the fields "ID" of type "AutoNumber" that you set to be the primary key, the field "Name" of type Text and a field "Phone" of type Text. Add some records to it. Put a list box on a form and call it "lstRecords".

 

Dim dbMyDB As DatabaseDim rsMyRS As RecordSetPrivate Sub Form_Load()Set dbMyDB = OpenDatabase("MyDatabase.mdb")Set rsMyRS = dbMyDB.OpenRecordSet("MyTable", dbOpenDynaset)If Not rsMyRS.EOF Then rsMyRS.MoveFirstDo While Not rsMyRS.EOF	lstRecords.AddItem rsMyRS!Name	lstRecords.ItemData(lstRecords.NewIndex) = rsMyRS!ID	rsMyRS.MoveNextLoopEnd Sub

This will make the list box fill up with your records when the form loads. I have introduced some new concepts with this example. We have all ready covered the first part where we open the table. The line that says If Not rsMyRS.EOF Then rsMyRS.M oveFirst tells the program to move to the first record in case there are any records at all. The EOF is a Boolean property that is true if the current record is the last. It is also true if there are no records in the RecordSet.

 

 

Then we make the program add the "Name" field of all records to the list box by adding the current records field "Name" and moving to the next record. You ask for a field of a RecordSet by putting a ! between the name of the RecordSet object and the na me of the field. The while loop checks to see if there are more records to add.

 

Searching the RecordSet

You might have wondered why I put the value of the field "ID" in the list box's ItemData property. I did this so that we would know the primary key for all the records in order to search for a record.

 

Put a text box somewhere on the form and call it "txtPhone". Then copy the following code to the project.

Private Sub lstRecords_Click()rsMyRS.FindFirst "ID=" & Str(lstRecords.ItemData(lstRecords.ListIndex))txtPhone.Text = rsMyRS!PhoneEnd Sub

This will display the phone number of the selected person when clicking in the list box. It uses the FindFirst method of the RecordSet object. This takes a string parameter that is like what is after WHERE in a SQL expression. You state the field that you want to search in (here "ID"), then the evaluation criteria (here "=") and last the value to search for (here the ItemData of the selected item in the list box).

 

So what we did was to search for the record with the "ID" field value that was the same as the ItemData property of the selected item in the list box. Then we show the value of the "Phone" field in the text box.

 

Updating the Database

You will probably want to update some value of some field when doing database programming. This is done with Edit and Update. We will try to change the value of the "Phone" field by editing the text in the text box and clicking a button.

 

Put a command button on the form and name it "cmdUpdate". Then copy the following code to the project.

Private Sub cmdUpdate_Click()rsMyRS.EditrsMyRS!Phone = txtPhone.TextrsMyRS.UpdateEnd Sub

It's that simple. This changes the phonenumber of our selected person. Or to put it technically: This changes the value of the "Phone" field of our current record. Imagine the current record being a set of boxes, with a field in each box. T he Edit method takes the lid off all of the boxes and Update puts them back on. When we write rsMyRS!Phone = txtPhone.Text we replace the content of the "Phone" box with the content in the text box.

 

Deleting and Adding records

Deleting

Deleting records couldn't be simpler. To delete the current record you just invoke the Delete method of the RecordSet object. We will put this feature in our little project. Make one more command button named "cmdDelete" and the following code will do the work of deleting our currently selected person.

Private Sub cmdDelete_Click()rsMyRS.DeletelstRecords.RemoveItem lstRecords.ListIndex

End Sub

 

The first statement deletes the record and the second removes the list box entry.

 

Adding

Adding records is much like updateing, except you use AddNew instead of Edit. Let's add one more command button to our application. Let's call it "cmdNew". Here is the code that adds a new record.

Private Sub cmdNew_Click()rsMyRS.AddNewrsMyRS!Name = "A New Person"lstRecords.AddItem rsMyRS!NamelstRecords.ItemData(lstRecords.NewIndex) = rsMyRS!IDrsMyRS!Phone = "Person's Phone Number"rsMyRS.UpdateEnd Sub

I will use the box analogy to explain this. The AddNew method takes a set of new boxes and adds them to our RecordSet. We then put some new values in them and close the lids with Update. As you can see we never stated any valu e for "ID", but as you remember, this is a field of type "Counter" wich automatically gets a unique value. The code also adds this new record to the list box so that we will be able to change the phone number of this person.

[michaelper22 0]

I have never heard of the Database class (from my limited knowledge of VB6), only ADODB.Connection. Can you explain it and tell me where it originates from?