Codejock Forums Homepage
Forum Home Forum Home > Codejock Products > ActiveX COM > Report Control
  New Posts New Posts RSS Feed - Need Example Of Database Usage
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Need Example Of Database Usage

 Post Reply Post Reply
Author
Message
ackshun View Drop Down
Newbie
Newbie


Joined: 15 April 2008
Location: United Kingdom
Status: Offline
Points: 9
Post Options Post Options   Thanks (0) Thanks(0)   Quote ackshun Quote  Post ReplyReply Direct Link To This Post Topic: Need Example Of Database Usage
    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
Back to Top
nvierros View Drop Down
Groupie
Groupie
Avatar

Joined: 02 July 2006
Location: Australia
Status: Offline
Points: 44
Post Options Post Options   Thanks (0) Thanks(0)   Quote nvierros Quote  Post ReplyReply Direct Link To This Post Posted: 15 April 2008 at 7:40pm
Hey!

Please see attached zip file. I hope this helps :)


uploads/20080415_193955_testdbload.zip

Nick
Back to Top
ackshun View Drop Down
Newbie
Newbie


Joined: 15 April 2008
Location: United Kingdom
Status: Offline
Points: 9
Post Options Post Options   Thanks (0) Thanks(0)   Quote ackshun Quote  Post ReplyReply Direct Link To This Post Posted: 15 April 2008 at 8:00pm
sweet thanks alot!
Back to Top
nvierros View Drop Down
Groupie
Groupie
Avatar

Joined: 02 July 2006
Location: Australia
Status: Offline
Points: 44
Post Options Post Options   Thanks (0) Thanks(0)   Quote nvierros Quote  Post ReplyReply Direct Link To This Post Posted: 15 April 2008 at 8:09pm
Originally posted by ackshun ackshun wrote:

sweet thanks alot!


You're welcome
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
nvierros View Drop Down
Groupie
Groupie
Avatar

Joined: 02 July 2006
Location: Australia
Status: Offline
Points: 44
Post Options Post Options   Thanks (0) Thanks(0)   Quote nvierros Quote  Post ReplyReply Direct Link To This Post 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

Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
nvierros View Drop Down
Groupie
Groupie
Avatar

Joined: 02 July 2006
Location: Australia
Status: Offline
Points: 44
Post Options Post Options   Thanks (0) Thanks(0)   Quote nvierros Quote  Post ReplyReply Direct Link To This Post 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?
 
 
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
nvierros View Drop Down
Groupie
Groupie
Avatar

Joined: 02 July 2006
Location: Australia
Status: Offline
Points: 44
Post Options Post Options   Thanks (0) Thanks(0)   Quote nvierros Quote  Post ReplyReply Direct Link To This Post Posted: 21 April 2008 at 7:16pm
Thanks for the advice Aaron, much appreciated 

Revised code:
uploads/20080421_193249_testdbload.zip
Back to Top
Baldur View Drop Down
Senior Member
Senior Member


Joined: 22 November 2006
Location: Germany
Status: Offline
Points: 244
Post Options Post Options   Thanks (0) Thanks(0)   Quote Baldur Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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
 
 
Back to Top
nvierros View Drop Down
Groupie
Groupie
Avatar

Joined: 02 July 2006
Location: Australia
Status: Offline
Points: 44
Post Options Post Options   Thanks (0) Thanks(0)   Quote nvierros Quote  Post ReplyReply Direct Link To This Post Posted: 22 April 2008 at 11:04am
Originally posted by Aaron Aaron wrote:

I think Nick will have to change the sample again
 
Done
 
Back to Top
Lodep59 View Drop Down
Senior Member
Senior Member
Avatar

Joined: 03 April 2008
Status: Offline
Points: 203
Post Options Post Options   Thanks (0) Thanks(0)   Quote Lodep59 Quote  Post ReplyReply Direct Link To This Post 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 ?

Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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
 
 
 
 
Back to Top
Lodep59 View Drop Down
Senior Member
Senior Member
Avatar

Joined: 03 April 2008
Status: Offline
Points: 203
Post Options Post Options   Thanks (0) Thanks(0)   Quote Lodep59 Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
younicke View Drop Down
Senior Member
Senior Member
Avatar

Joined: 11 March 2005
Status: Offline
Points: 107
Post Options Post Options   Thanks (0) Thanks(0)   Quote younicke Quote  Post ReplyReply Direct Link To This Post Posted: 23 April 2008 at 6:10am
Originally posted by Aaron Aaron wrote:

 
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


hi aaron,

what object is being referenced in the ".value"?
i didnt see any WITH  statement.

thanks
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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
 
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post Posted: 23 April 2008 at 7:33am
Originally posted by Lodep59 Lodep59 wrote:

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.
 
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
Back to Top
Baldur View Drop Down
Senior Member
Senior Member


Joined: 22 November 2006
Location: Germany
Status: Offline
Points: 244
Post Options Post Options   Thanks (0) Thanks(0)   Quote Baldur Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
younicke View Drop Down
Senior Member
Senior Member
Avatar

Joined: 11 March 2005
Status: Offline
Points: 107
Post Options Post Options   Thanks (0) Thanks(0)   Quote younicke Quote  Post ReplyReply Direct Link To This Post Posted: 23 April 2008 at 1:49pm
Originally posted by Aaron Aaron wrote:

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
 


sorry my bad. i was blind at that moment.. i didnt see it.. hehehe 
Back to Top
braian87b View Drop Down
Groupie
Groupie


Joined: 01 April 2008
Location: Argentina
Status: Offline
Points: 35
Post Options Post Options   Thanks (0) Thanks(0)   Quote braian87b Quote  Post ReplyReply Direct Link To This Post Posted: 24 April 2008 at 11:41am
Originally posted by Aaron Aaron wrote:

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
 
 
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?
 
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post Posted: 24 April 2008 at 3:11pm
Hi,
 
That is correct
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post Posted: 31 May 2008 at 3:02pm
Originally posted by Aaron Aaron wrote:

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
 
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:
  1. ADODB - 22,04 sec. 
  2. DAO - 15,75 sec.
For anyone who is still interested see post:
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....
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.04
Copyright ©2001-2021 Web Wiz Ltd.

This page was generated in 0.188 seconds.