Need Example Of Database Usage
Printed From: Codejock Forums
Category: Codejock Products
Forum Name: Report Control
Forum Description: Topics Related to Codejock Report Control
URL: http://forum.codejock.com/forum_posts.asp?TID=10243
Printed Date: 24 November 2024 at 12:09pm
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com
Topic: Need Example Of Database Usage
Posted By: ackshun
Subject: Need Example Of Database Usage
Date Posted: 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 |
Replies:
Posted By: nvierros
Date Posted: 15 April 2008 at 7:40pm
|
Hey! Please see attached zip file. I hope this helps :) uploads/20080415_193955_testdbload.zip - uploads/20080415_193955_testdbload.zip Nick |
Posted By: ackshun
Date Posted: 15 April 2008 at 8:00pm
| sweet thanks alot! |
Posted By: nvierros
Date Posted: 15 April 2008 at 8:09pm
You're welcome  |
ackshun wrote:Posted By: Aaron
Date 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.
|
Posted By: nvierros
Date 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 |
Posted By: Aaron
Date 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 |
Posted By: nvierros
Date 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? |
Posted By: Aaron
Date 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
|
Posted By: nvierros
Date Posted: 21 April 2008 at 7:16pm
Thanks for the advice Aaron, much appreciated  Revised code: uploads/20080421_193249_testdbload.zip - uploads/20080421_193249_testdbload.zip |
Posted By: Baldur
Date 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 |
Posted By: Aaron
Date 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 |
Posted By: nvierros
Date Posted: 22 April 2008 at 11:04am
Done
 https://forum.codejock.com/uploads/20080422_110422_testdbload.zip - uploads/20080422_110422_testdbload.zip
|
Posted By: Lodep59
Date 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 ? |
Posted By: Aaron
Date 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 |
Posted By: Lodep59
Date 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.
|
Posted By: younicke
Date Posted: 23 April 2008 at 6:10am
hi aaron, what object is being referenced in the ".value"? i didnt see any WITH statement. thanks |
Posted By: Aaron
Date 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
|
Posted By: Aaron
Date 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
|
Posted By: Baldur
Date 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.
|
Posted By: younicke
Date Posted: 23 April 2008 at 1:49pm
sorry my bad. i was blind at that moment.. i didnt see it.. hehehe 
|
Posted By: braian87b
Date 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?
|
Posted By: Aaron
Date Posted: 24 April 2008 at 3:11pm
|
Hi,
That is correct
|
Posted By: Aaron
Date 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:
http://forum.codejock.com/forum_posts.asp?TID=10814 - http://forum.codejock.com/forum_posts.asp?TID=10814
------------- 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.... |