 |
|
 |
yronium
Level: Moderator
Registered: 14-04-2002
Posts: 907
|
Re: have some problem when showing item from database to listbox
Hello. Your code is not good readable, and I suggest you to follow the Hungarian Notation rules for controls and variables' names: no spaces among words, and all words in proper case.
Furthermore, I suggest you to use the underscore char ("_") to continue the long instructions on next line.
Now, I try to correct your code a bit. You attempt to manage the empty textbox case, but even if the textbox is empty you execute the code after the messagebox
If txtitem.Text = vbNullString Then
a = MsgBox("No code inserting!", vbOKOnly, vbNullString)
' === I ADDED THIS INSTRUCTION ===
Exit Sub
End If |
You didn't tell us what datitem is, nor what connection provider you use (I guess ADO), but generally you open a recordset (it is positioned on first record), check if the recordset is not empty, then for each record until recordset's EOF property is False:
- read the desired field's Value property, and add it to the listbox by the AddItem method
- optionally, read the ID field and add it to the ItemData property
- move on the next record by the MoveNext method;
If you need to add something modified to the listbox, different than the bare field's Value property, you can edit the string before AddItem method, but the principle is the same. There are plenty of samples into the forum, just type "AddItem" in the Search box.
Hope it helps.
____________________________
Real Programmer can count up to 1024 on his fingers
|
|
03-04-2007 at 01:48 PM |
|
|
davidjones
Level: Graduate
Registered: 03-04-2007
Posts: 10
|
Re: have some problem when showing item from database to listbox
sorry for not given enough info, im using Micro. Access to do my database.The datitem is my database.
Now the problem is it only can add the first item in the database to the listbox, how do i can make it show the second and third item continuously item from the database? Do i need to add some coding at the Form load or in the listbox?
|
|
03-04-2007 at 04:21 PM |
|
|
yronium
Level: Moderator
Registered: 14-04-2002
Posts: 907
|
Re: have some problem when showing item from database to listbox
Hello. I can't see if you are using DAO or ADO. In my previous post I argued you are using ADO, but now I notice the FindFirst method, that's a DAO method, and further I see a MoveFirst (ADO) call. So, what game are you playing? Are you using ADO or DAO?
However, if you use DAO I can't help you with VB code as I'm not using DAO since eight years, and at the moment I can only give you some pseudo-code help.
Furthermore you say datitem is your database: are you meaning is a DAO Database object? I didn't know a DAO Database object exposed any Recordset property, but anyway....
The pseudocode is: ' declare the object
Dim rs As (ADO/DAO) Recordset
Dim openquery As String
' initialize the Recordset object
Set rs = New (ADO/DAO) Recordeset
' open the recordset *1)
openquery = "SELECT * FROM MyTable"
rs.Open openquery
' check if the recordset is empty
If Not (rs.BOF And rs.EOF) Then *2)
(rs.MoveFirst --- not necessary here, the recordset is already on the first record) *3)
' loop the recordset until its end
Do While Not rs.EOF *4)
' add the current record to listbox' items
ListBox.AddItem rs.Fields("Name").Value *5)
' set the ItemData property of the current item on the ID value of the current record
(ListBox.ItemData(ListBox.NewIndex) = rs.Fields("ID").Value --- not always necessary) *6)
' moves on the next record - you forgot this instruction
rs.MoveNext *7)
Loop
Else
(The recordset is empty, no records are found in the table)
End If
' close the recordset and destroy the object variable
rs.Close
Set rs = Nothing *8) |
This pseudocode, very similar to a real VB code, is ADO-based. If you are using DAO, it won't work if you simply copy it into your project (nor with ADO, as its syntax is not correct).
But here you got to comprehend the working mechanism:
1) you need a recordset. The entire database is not a recordset, as there are many tables included. A recordset is a single table, or even the result of a single query. (You can't retrieve a recordset from the entire database because the tables have not the same structure, so you could obtain some records of three fields, and some other of four or five or more fields, and they could not stay together on the same recordset with its unique structure)
2) once you have open a recordset, you got to check if there are records in it or not. If there are no records (the recordset is empty when both the EOF and BOF properties are True at the same time) every reading or moving instruction will raise an error. So you got to do the check before attempting to read it, and if the recordset is empty you must avoid to loop the recordset
3) if you got some records you have to move on the first one in order to begin reading them all. Normally, this is not necessary as the recordset is positioned on the first record after opening. But sometimes programmers execute a MoveLast method right after opening in order to fill the cache, or some other method that moves the position, so in this case you got to explicitly move on first record before start
4) you are on the first record, so you start a loop on the recordset. The loop has to be done until the end of recordset, so While Not recordset's End_Of_File (EOF) property is True
5) within each loop, you have to read the desired recordset's field, and add its value to the listbox items. At the beginning the current record is the first one in the recordset, so you read the desired field in the first record, but later you change the current record step by step, until the end of the recordset
6) when needed, you can store the ID of the current record in listbox' ItemData property
7) once you added the current record's item to the listbox, you move on the next record in the recordset. The current record is changed, so you can repeat the loop again
8) when the loop is completed - the recordset is finished - if you don't need anymore the recordset, you should close it and destroy the variable to deallocate memory space.
This is the loading algorithm. Once more, there are plenty of samples in the forum. But you got to decide what provider you want to use, as I got the feeling you pasted somebody else's code without completely understand what it does and whether it matches your goals or not.
Hope it helps.
____________________________
Real Programmer can count up to 1024 on his fingers
|
|
04-04-2007 at 11:53 AM |
|
|
yronium
Level: Moderator
Registered: 14-04-2002
Posts: 907
|
Re: have some problem when showing item from database to listbox
WHAT IS THIS?!?
quote:
If Not dbitem.Recordset.BOF Then
You got to test if the BOF and the EOF conditions together are True. If you are positioned at the beginning and at the end of the recordset at the same time, it means that the recordset is empty. Actually, you are only testing that you're not at the beginning of the file. You got to code like following:| If Not (dbitem.Recordset.BOF And dbitem.Recordset.EOF) Then |
... and don't forget to notice the parenthesis couple.
quote:
lstorder.ItemData(lstorder.ListCount - 1) = dbitem.Recordset("ItemID")'<====i know here is wrong can teach me how to do item data property?
Once you added an item, the item you are adding is 'not yet completely registered' in the list until you move the focus, and its index is still a new index in fact. So you got to set the ItemData property of the NewIndex item, as the item you added one step before has got no ListIndex yet. Like this:| lstorder.ItemData(lstorder.NewIndex) = dbitem.Recordset("ItemID") |
Finally, never make confusion between ADO and DAO (..."DOA" is the acronym of "Dead Or Alive", not closely VB related...): they are two deeply different technologies, with different objects model, methods and properties: you should deeply know the one you are using. And after all, Database Interface programming is not a three-minutes task, so you got to learn your technology as fast as you can.
Hope it helps
____________________________
Real Programmer can count up to 1024 on his fingers
|
|
05-04-2007 at 12:14 PM |
|
|
davidjones
Level: Graduate
Registered: 03-04-2007
Posts: 10
|
Re: have some problem when showing item from database to listbox
i followed as u said still cannot......
the error message is runtime error 424: object required
wat object do i required??AHHHHH!!!!!
Here is my code:
Private Sub cmdadd_Click()
Dim openquery As String
If txtitem.Text = "" Then
a = MsgBox("No code inserting!", vbOKOnly, "")
Exit Sub
End If
openquery = "SELECT * FROM Item_List"
If Not (dbitem.Recordset.BOF And dbitem.Recordset.EOF) Then
Do While Not dbitem.Recordset.EOF
If txtitem.Text = dbitem.Recordset.Fields("Code") Then
lstorder.AddItem dbitem.Recordset.Fields("Code") + Space(5 - Len(dbitem.Recordset.Fields("Code"))) + dbitem.Recordset.Fields("Item") + Space(35 - Len(dbitem.Recordset.Fields("Item"))) + Str(dbitem.Recordset.Fields("Prices"))
lstorder.ItemData(lstorder.NewIndex) = datitem.recordser("ItemID")
Else
dbitem.Recordset.MoveNext
End If
Loop
End If
txtitem.Text = vbNullString
txtitem.SetFocus
End Sub
i am advancing my technology now, pls give me sometime.......
|
|
06-04-2007 at 01:49 AM |
|
|
yronium
Level: Moderator
Registered: 14-04-2002
Posts: 907
|
Re: have some problem when showing item from database to listbox
You don't say what line gives you the error, but usually the "Object required" error is due to an unappropriate usage in the code of the Set keyword. Anyway...
quote:
...
openquery = "SELECT * FROM Item_List"
If Not (dbitem.Recordset.BOF And dbitem.Recordset.EOF) Then
...
Here, I noticed you omitted to open the recordset on the query. Usually, when working with an ADO recordset object, you set the sql instruction text, you open the recordset on this sql instruction, then check for records existance.
Have a look to the following ADO-based code:Private Sub LoadListbox()
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
' build the sql instruction
sql = "SELECT IDCustomer, CustomerName FROM Customers;"
' open the recordset on the above sql instruction (assume conn is a valid and open ADODB.Connection object)
rs.Open sql, conn, adForwardOnly, adLockReadOnly, adCmdText ' <== You missed an equivalent instruction ==
' check for records' existance
If Not (rs.BOF And rs.EOF) Then
[.... other code ....] |
(Hope, though I used an ADO sample, you can understand the principle and apply it to your DAO code).
I meant, once you extablish the sql instruction text, you must say to your recordset that this instruction have to be its new data source. Now, your recordset is 'dbitem.Recordset' and I don't know neither how you created it, nor its actual state (open, close, fetching or else), but once you created the sql string you have to re-link the recordset to this new sql string. Or, if the recordset is closed, you got to open it with the sql string. Otherwise, how can it know you changed the sql instruction?
I don't understand yet what kind of object your 'dbitem' variable is, but someway, somehow, you got to refresh it and requery its recordset.
Hope it helps.
[Edited by yronium on 06-04-2007 at 01:50 PM GMT]
____________________________
Real Programmer can count up to 1024 on his fingers
|
|
06-04-2007 at 12:47 PM |
|
|
davidjones
Level: Graduate
Registered: 03-04-2007
Posts: 10
|
Re: have some problem when showing item from database to listbox
And the datitem is my database name. my project is attached here, can help me see wat the problem is?
my problem is just here only:
Private Sub cmdadd_Click()
Dim ItemID As ApplicationStartConstants
Dim Prices As Currency
Dim openquery As String
If txtitem.Text = "" Then
a = MsgBox("No code inserting!", vbOKOnly, "")
Exit Sub
End If
SQL = "SELECT ItemID,Code, Item, Prices FROM Item_List"
If Not (dbitem.Recordset.BOF And dbitem.Recordset.EOF) Then
Do While Not dbitem.Recordset.EOF
If txtitem.Text = dbitem.Recordset.Fields("Code") Then
lstorder.AddItem dbitem.Recordset.Fields("Code") + Space(5 - Len(dbitem.Recordset.Fields("Code"))) + dbitem.Recordset.Fields("Item") + Space(35 - Len(dbitem.Recordset.Fields("Item"))) + Str(dbitem.Recordset.Fields("Prices"))
lstorder.ItemData(lstorder.NewIndex) = datitem.recordser.Fields("ItemID") '<======here is the error it said object required
Else
dbitem.Recordset.MoveNext
End If
Loop
End If
txtitem.Text = vbNullString
txtitem.SetFocus
End Sub
|
|
08-04-2007 at 04:18 AM |
|
|
|
|
 |
|
im still cannot show the item one by one to the list box from my database.
.Sorry im very new in this........
