|
Need Example Of Database Usage |
Post Reply 
|
| Author | |
ackshun 
Newbie 
Joined: 15 April 2008 Location: United Kingdom Status: Offline Points: 9 |
 Post Options
 Thanks(0)
 Quote Reply
 Topic: Need Example Of Database UsagePosted: 15 April 2008 at 3:23pm |
|
I have an access database with loads of data. The database is for example tableComputers and inside there is computername, computerlocation, computerserial, so on and so forth. I want to figure out this report control but it seems pretty difficult as I learn from examples. What I need it to do is connect to database and display the information but for some reason it can't get the loop right and the data is going everywhere and leaving spots blank. Can someone post an example using a database? This needs to be done in vb6 if possible
|
|
 |
|
|
nvierros
Groupie 
Joined: 02 July 2006 Location: Australia Status: Offline Points: 44 |
Post Options
Thanks(0)
Quote Reply
Posted: 15 April 2008 at 7:40pm |
|
|
|
ackshun
Newbie
Joined: 15 April 2008 Location: United Kingdom Status: Offline Points: 9 |
Post Options
Thanks(0)
Quote Reply
Posted: 15 April 2008 at 8:00pm |
|
sweet thanks alot!
|
|
|
|
|
nvierros
Groupie
Joined: 02 July 2006 Location: Australia Status: Offline Points: 44 |
Post Options
Thanks(0)
Quote Reply
Posted: 15 April 2008 at 8:09pm |
You're welcome  |
|
|
|
|
Aaron
Senior Member 
Joined: 29 January 2008 Status: Offline Points: 2192 |
Post Options
Thanks(0)
Quote Reply
Posted: 21 April 2008 at 2:06am |
|
Hi,
I looked at your sample and there's nothing wrong with it. I have only a suggestion. When you load the report items from a database you could loop through the Fields (advantage: I don't have to know the name of the Field) and second you can load the columnheaders captions from the Fields name in the table. You will have a more flexible function for loading items into a ReportControl, the only parameter you have to pass is the table name.
|
|
|
|
|
nvierros
Groupie
Joined: 02 July 2006 Location: Australia Status: Offline Points: 44 |
Post Options
Thanks(0)
Quote Reply
Posted: 21 April 2008 at 2:17am |
|
Yeah i know, the reason i use .fields("fieldname").value, is simply just for my own clarity...there are several ways to do it, but its easier than .fields(0), .fields(1) etc etc |
|
|
|
|
Aaron
Senior Member
Joined: 29 January 2008 Status: Offline Points: 2192 |
Post Options
Thanks(0)
Quote Reply
Posted: 21 April 2008 at 2:55am |
|
Hi,
Ok
 There are many ways of doing this. When you load an entire table you don't have to know the names of the field. And I don't want to use .Fields(0) etc... Just use the for each loop.
Dim XtremeRecord As XtremeReportControl.ReportRecord
Dim XtremeRecordItem As XtremeReportControl.ReportRecordItem Dim fld As Field Set mDB = OpenDatabase(<your database>, False, False, ";PWD= ")
sqlString = "SELECT * FROM " & "<Your table>" Set qdfTemp = mDB.CreateQueryDef("", sqlString) Set mRS = qdfTemp.OpenRecordset For Each fld In qdfTemp.Fields ''Add the columnheaders caption wndReportControl.Columns.Add fld.OrdinalPosition, fld.Name, 1000, True Next fld Do While mRS.EOF = False Set XtremeRecord = wndReportControl.Records.Add() ''Add the record For Each fld In qdfTemp.Fields XtremeRecord.AddItem mRS.Fields(fld.Name).Value ''Add the columns to the record Next fld mRS.MoveNext Loop mRS.Close mDB.Close wndReportControl.Populate ''show the added records Now I dont care what the fieldnames are and how may and I don't have to add columnheader captions.
Just a simple sample for loading table into ReportControl |
|
|
|
|
nvierros
Groupie
Joined: 02 July 2006 Location: Australia Status: Offline Points: 44 |
Post Options
Thanks(0)
Quote Reply
Posted: 21 April 2008 at 3:06am |
|
hey!
Thats awesome, thanks a lot for that.
No more double typing for column names, i can just do it all in one shot  Addition:
im guessing that i were to structure my sql query correctly i would get all the column names in correctly to, for example: SELECT CustFName + ' ' + CustLName AS [Customer Name] FROM dbo.tblCustomers The column header will be 'Customer Name' correct? |
|
|
|
|
Aaron
Senior Member
Joined: 29 January 2008 Status: Offline Points: 2192 |
Post Options
Thanks(0)
Quote Reply
Posted: 21 April 2008 at 5:53am |
|
Hi,
This will do the trick
Private Sub LoadRecordSet()
Dim adoCommand As adodb.Command
Dim rs As adodb.Recordset
Dim sql As String
sql = "SELECT * " & "FROM tblComputers ORDER BY ComputerName"
Set rs = New Recordset Set adoCommand = New adodb.Command With adoCommand .ActiveConnection = Con .CommandType = adCmdText .CommandText = sql Set rs = .Execute End With Dim fld As adodb.Field Dim XtremeRecord As XtremeReportControl.ReportRecord Dim newCol As Integer For Each fld In rs.Fields rptControl.Columns.Add newCol, fld.Name, 1000, True newCol = newCol + 1 Next fld If rs.RecordCount > 0 Then rptControl.Records.DeleteAll Do While Not rs.EOF
Set XtremeRecord = rptControl.Records.Add() For Each fld In rs.Fields XtremeRecord.AddItem rs.Fields(fld.Name).Value Next fld rs.MoveNext Loop Else MsgBox "Sorry, there are no records in the database.", vbInformation End If rptControl.Populate rs.Close Set rs = Nothing Set adoCommand = Nothing End Sub The bold text is new
|
|
|
|
|
nvierros
Groupie
Joined: 02 July 2006 Location: Australia Status: Offline Points: 44 |
Post Options
Thanks(0)
Quote Reply
Posted: 21 April 2008 at 7:16pm |
|
|
|
Baldur
Senior Member
Joined: 22 November 2006 Location: Germany Status: Offline Points: 244 |
Post Options
Thanks(0)
Quote Reply
Posted: 22 April 2008 at 7:45am |
|
If you use a lot of fields and records, it will be faster in two ways:
For Each fld In rs.Fields
XtremeRecord.AddItem fld.Value Next fld And faster:
dim Index as integer
For Index=0 to rs.field.count-1
XtremeRecord.AddItem rs.Collect(Index)
Next
Collect(Index) is a hidden property to get/set only the Value.
If you get NULL-Values from Database, the reportcontrol don't draw the entire row !
Solution:
For Index=0 to rs.field.count-1
with XtremeRecord.AddItem(rs.Collect(Index))
if isnull(.value) then
.value = empty
end if
end with
Next |
|
|
|
|
Aaron
Senior Member
Joined: 29 January 2008 Status: Offline Points: 2192 |
Post Options
Thanks(0)
Quote Reply
Posted: 22 April 2008 at 9:58am |
|
Hi,
I did test the 2 options and the option Baldur gave us was faster, good job
  I did ten runs for each option (slow machine 800Mhz and about 40000 items) but you can see the difference. I think Nick will have to change the sample again
Baldur option:
1: 9,191
2: 8,530 3: 8,691 4: 8,968 5: 8,781 6: 8,701 7: 9,197 8: 8,708 9: 8,676 10: 8,786 Aaron option:
1: 10,772 sec
2: 10,740 sec 3: 10,789 sec 4 : 10,594 sec 5: 10,918 sec 6: 10,411 sec 7: 10,742 sec 8: 10,403 sec 9: 10,608 sec 10: 10,370 sec |
|
|
|
|
nvierros
Groupie
Joined: 02 July 2006 Location: Australia Status: Offline Points: 44 |
Post Options
Thanks(0)
Quote Reply
Posted: 22 April 2008 at 11:04am |
Done
 |
|
|
|
|
Lodep59
Senior Member
Joined: 03 April 2008 Status: Offline Points: 203 |
Post Options
Thanks(0)
Quote Reply
Posted: 22 April 2008 at 12:14pm |
|
You can also do it like this (the ADO command is not needed in the sample).
Private Sub LoadRecordSet()
Dim rs As adodb.Recordset
Dim fld As adodb.Field Dim XtremeRecord As XtremeReportControl.ReportRecord Dim Index As Integer Dim colCount As Integer Set rs = New Recordset
rs.Open "SELECT ComputerName As [Computer Name],ComputerSerial as [Computer Serial], ComputerLocation as [Computer Location] FROM tblComputers ORDER BY ComputerName", Con rptControl.Columns.DeleteAll For Each fld In rs.Fields rptControl.Columns.Add colCount, fld.Name, 1000, True colCount = colCount + 1 Next fld rptControl.Records.DeleteAll
While Not rs.EOF Set XtremeRecord = rptControl.Records.Add() For Index = 0 To colCount - 1 XtremeRecord.AddItem rs.Fields(Index).Value Next rs.MoveNext Wend rptControl.Populate
rs.Close Set rs = Nothing End Sub I've check with null values in the database, the item is correctly drawn ? |
|
|
|
|
Aaron
Senior Member
Joined: 29 January 2008 Status: Offline Points: 2192 |
Post Options
Thanks(0)
Quote Reply
Posted: 22 April 2008 at 12:47pm |
|
Dear @Lodep59
The how is possible in 1000 ways, we know that
 . The speed with loading is what counts. Do the test with:Your solution (and myself included):
For Index = 0 To colCount - 1
XtremeRecord.AddItem rs.Fields(Index).Value Next and with:
Baldur solution: (seems about 2 seconds faster, look my post above)
For Index = 0 To rs.Fields.Count - 1
With XtremeRecord.AddItem(rs.Collect(Index)) If IsNull(.Value) Then .Value = Empty End If End With Next |
|
|
|
|
Lodep59
Senior Member
Joined: 03 April 2008 Status: Offline Points: 203 |
Post Options
Thanks(0)
Quote Reply
Posted: 23 April 2008 at 5:10am |
|
Dear Aaron,
I was talking about the use of adocommand not rs.collect.
You're right, the collect method is faster because it doesn't need a reference to the field object. Why this method is hidden is an other subject...
Sorry to have disturbed you.
|
|
|
|
|
younicke
Senior Member
Joined: 11 March 2005 Status: Offline Points: 107 |
Post Options
Thanks(0)
Quote Reply
Posted: 23 April 2008 at 6:10am |
hi aaron, what object is being referenced in the ".value"? i didnt see any WITH statement. thanks |
|
|
|
|
Aaron
Senior Member
Joined: 29 January 2008 Status: Offline Points: 2192 |
Post Options
Thanks(0)
Quote Reply
Posted: 23 April 2008 at 7:08am |
|
Hi,
With XtremeRecord.AddItem(rs.Collect(Index))
If IsNull(.Value) Then .Value = Empty End If End With Collect is returning the value Field(index) in the recordset
|
|
|
|
|
Aaron
Senior Member
Joined: 29 January 2008 Status: Offline Points: 2192 |
Post Options
Thanks(0)
Quote Reply
Posted: 23 April 2008 at 7:33am |
You can always disturb me
But you were right about that. Keep the code as simple as can be. Keep "bugging" me and the other members
|
|
|
|
|
Baldur
Senior Member
Joined: 22 November 2006 Location: Germany Status: Offline Points: 244 |
Post Options
Thanks(0)
Quote Reply
Posted: 23 April 2008 at 1:41pm |
|
I have Version 12.2 and if an item contains NULL instead of Empty, the hole row will be ignored.
Thats, why i change it to empty.
|
|
|
|
|
younicke
Senior Member
Joined: 11 March 2005 Status: Offline Points: 107 |
Post Options
Thanks(0)
Quote Reply
Posted: 23 April 2008 at 1:49pm |
sorry my bad. i was blind at that moment.. i didnt see it.. hehehe 
|
|
|
|
|
braian87b
Groupie
Joined: 01 April 2008 Location: Argentina Status: Offline Points: 35 |
Post Options
Thanks(0)
Quote Reply
Posted: 24 April 2008 at 11:41am |
Yes, but i think that XtremeRecord.AddItem is who returning the Item object and the .Value Property corresponds to the Item,
Is'n it? or Im Wrong?
Because youre passing rs.Collect to the AddItem Method as an argument.
dont you?
|
|
|
|
|
Aaron
Senior Member
Joined: 29 January 2008 Status: Offline Points: 2192 |
Post Options
Thanks(0)
Quote Reply
Posted: 24 April 2008 at 3:11pm |
|
Hi,
That is correct
|
|
|
|
|
Aaron
Senior Member
Joined: 29 January 2008 Status: Offline Points: 2192 |
Post Options
Thanks(0)
Quote Reply
Posted: 31 May 2008 at 3:02pm |
Hi,
The test I did was with the sample @nvierros provided. Someone else asked a sample for the same thing and I made one with two possible options (DAO and ADODB) and it seems that DAO is faster.
Loading about 200.000 records:
For anyone who is still interested see post:
|
|
|
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows XP (32bit) - SP 2 Language: Visual Basic 6.0 Zero replies is not an option.... |
|
|
|
|
Post Reply
|
|
Tweet
|
| Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |
Topic Options
ackshun wrote: