-
Aug 7th, 2005, 10:42 AM
#1
Thread Starter
Lively Member
recordset to msflexgrid
Hi all.
I have a problem in retrieve data from database column and display the data in msflexgrid.
The attached code below can only display the first row data in recordset.
Can anyone here help me to fix this problem?
Thanks in advance.
VB Code:
Private Sub FG_ShowRecordset(myFG As MSFlexGrid, myRST As ADODB.Recordset)
Dim iField As Integer, iNofFields As Integer
Dim lRow As Long
Screen.MousePointer = vbHourglass
myFG.Redraw = False
myFG.AllowUserResizing = flexResizeColumns
myFG.ScrollTrack = True
'column header
myFG.TextMatrix(0, 0) = "DocNo"
myFG.TextMatrix(0, 1) = "ItemNo"
myFG.TextMatrix(0, 2) = "Item"
myFG.TextMatrix(0, 3) = "Occurances"
With myRST
'loop for displaying the document id in DocNo column
'fields(0) = Id
For x = 1 To myFG.Rows - 1
myFG.TextMatrix(x, 0) = .Fields(0).Value
.MoveNext
x = x + 1
Next
End With
myFG.Redraw = True
Screen.MousePointer = vbNormal
End Sub
-
Aug 7th, 2005, 10:55 AM
#2
Re: recordset to msflexgrid
Also where do you tell the grid how many rows you will have. I normally will perform an if not recordset.BOF and not recordset.EOF then do your loop. Also before posing the first row into the grid perform a recordset.movelast and recordset.MoverFirst and set the grid.rows to recordset.recordcount + 1.
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Aug 7th, 2005, 11:00 AM
#3
Re: recordset to msflexgrid
Do not set the .ROWS property of a FLEXGRID - use .ADDITEM to add a whole row at once...
See this thread:
http://www.vbforums.com/showthread.p...light=.additem
-
Aug 7th, 2005, 11:05 AM
#4
Addicted Member
Re: recordset to msflexgrid
HI,
Szlamany:
I like that additem method. Neat. There is nothing wrong with using .Rows. I would say additem would be easier to use.
Have a good one!
BK
-
Aug 7th, 2005, 01:06 PM
#5
Thread Starter
Lively Member
Re: recordset to msflexgrid
Why I got this error : subscript out of range ?
VB Code:
With myRST
'loop for displaying the document id in DocNo column
'fields(0) = Id
Do While Not .EOF
myFG.Rows = myFG.Rows + 1
myFG.TextMatrix(myFG.Rows, 0) = .Fields(0).Value
.MoveNext
Loop
End With
-
Aug 7th, 2005, 01:14 PM
#6
Thread Starter
Lively Member
Re: recordset to msflexgrid
Do I need to set the first value for myFG.rows?
-
Aug 7th, 2005, 01:19 PM
#7
Re: recordset to msflexgrid
I agree - you can increment .ROWS if you would like.
Then you have to remember how to deal with the "offset-of-0" issue and the FIXED rows issues.
Doing a .MOVELAST which will cause the entire recordset to be transferred to the client side, just to find out the row count is wasteful.
Since we use forward-only-read-only recordsets, that's not possible anyway.
-
Aug 7th, 2005, 01:25 PM
#8
Hyperactive Member
Re: recordset to msflexgrid
VB Code:
Set rs = New ADODB.Recordset
myFG.ColHeaderCaption(0, 0) = "DocNo"
myFG.ColHeaderCaption(0, 1) = "ItemNo"
myFG.ColHeaderCaption(0, 2) = "Item"
myFG.ColHeaderCaption(0, 3) = "Occurances"
Do While Not .EOF
myFG.AddItem rs!DocNo & vbTab & rs!ItemNo & vbTab & rs!Item & vbTab & rs!Occurances
rs.MoveNext
Loop
-
Aug 7th, 2005, 01:26 PM
#9
Re: recordset to msflexgrid
Originally Posted by Rina
Do I need to set the first value for myFG.rows?
Is that first row a fixed row?
Usually myFG.Rows = myFG.FixedRows-1 will set the row count to be just the header row, or no header row if you have no FIXEDROWS.
We use .FORMATSTRING to build the header...
Something like
Code:
myFG.FormatString = "Column1" & vbTab & "^Centered Col" & vbTab & ">Right Col"
-
Aug 7th, 2005, 01:28 PM
#10
Thread Starter
Lively Member
Re: recordset to msflexgrid
I try this code which I put .additem syntax in the for loop.
VB Code:
With myRST
'loop for displaying the document id in DocNo column
'fields(0) = Id
For x = 1 To myFG.Rows - 1
myFG.TextMatrix(x, 0) = .Fields(0).Value
.MoveNext
Next
myFG.AddItem strGridLine
End With
But the result is..I have to click the button everytime I want to increase the row plus display the data in .Fields(0).
Why?
-
Aug 7th, 2005, 01:30 PM
#11
Thread Starter
Lively Member
Re: recordset to msflexgrid
What is vbTab for? Im sorry if this is such a silly question. Im new in vb programming so dont have much idea.
-
Aug 7th, 2005, 01:31 PM
#12
Re: recordset to msflexgrid
It's one or the other - either use .ADDITEM, after setting .ROWS=0 (or .ROWS=.FIXEDROWS-1)
or...
use .TEXTMATRIX to fill the entire recordset.
.ADDITEM adds the row for you - all at once, and increments the row counter - all at once. You do this just like the example in the thread I posted.
-
Aug 7th, 2005, 01:32 PM
#13
Re: recordset to msflexgrid
Originally Posted by Rina
What is vbTab for? Im sorry if this is such a silly question. Im new in vb programming so dont have much idea.
vbTab is a constant for a TAB character - recognized by the flexgrid as a column separator.
That's how you can pass one string for the heading and one string for each row and have the flexgrid know where the columns are.
-
Aug 7th, 2005, 01:33 PM
#14
Re: recordset to msflexgrid
Here's the commented example of loading the data rows into a flex grid from that other thread...
Code:
Dim strWhat as String, strGridLine as String
Dim z as Long
Do While rs_e.EOF = False ' Loop through the recordset now
For z = 0 To rs_e.Fields.Count - 1
strWhat = rs_e(z) & "" ' The data element for this row/column
' This assumes that the recordset has columns in order compared to the list you showed
' in your post - EnqNo, FirstName, LastName and so on
' Using & "" will make sure that a null value is turned into an empty string
If strGridLine <> "" Then strGridLine = strGridLine & vbTab
' Add a tab character to the string - separates the columns
strGridLine = strGridLine & strWhat
' Put the column onto the string
Next z
MSFlexGrid1.AddItem strGridLine
' This adds the row to the grid - all in one shot
strGridLine = ""
rs_e.MoveNext
Loop
-
Aug 7th, 2005, 02:04 PM
#15
Hyperactive Member
Re: recordset to msflexgrid
There is by the way an undocumented bug with .additem for hierarchical recordsets. According to microsoft .additem should accept an optional third parameter defining the band to which the row should be added. However it does not, and it is in fact apparently impossible to use .additem (or .removeitem) with hierarchical recordsets. This has proved a major pain to me, and I've still got nowhere with resolving it.
-
Aug 7th, 2005, 02:22 PM
#16
New Member
Re: recordset to msflexgrid
You might also consider that MSHFlexGrid, which can be data-bound using ADO. If you're grid is read-only, might be simpler.
Dave
-
Aug 7th, 2005, 02:23 PM
#17
Re: recordset to msflexgrid
You can use the shape command to load a MSHFlexgrid.
-
Aug 8th, 2005, 01:01 AM
#18
Hyperactive Member
Re: recordset to msflexgrid
Here is my code , where i developed a class to accomodate data from any table or recordset. I do not say that there codes are very fine, but once u add in your project you do not have to write codes for adding in each and every program.
VB Code:
Option Explicit
Private m_GroupBy, m_SumOn, m_OmitZero As String
Private m_TotalCaption As Long
Private TotalFields As Long
Private m_isAppend As Boolean
Property Get FieldGroupBy() As String
FieldGroupBy = m_GroupBy
End Property
Property Let FieldGroupBy(ByVal newGroupBy As String)
m_GroupBy = newGroupBy
End Property
Property Get SumOnField() As String
SumOnField = m_SumOn
End Property
Property Let SumOnField(ByVal newSumOn As String)
m_SumOn = newSumOn
End Property
Property Get ColoumnNameToSkipRecordIfZero() As String
ColoumnNameToSkipRecordIfZero = m_OmitZero
End Property
Property Let ColoumnNameToSkipRecordIfZero(ByVal newOmitZero As String)
m_OmitZero = newOmitZero
End Property
Property Get TotalCaptionAtColumnNumber() As Long
TotalCaptionAtColumnNumber = m_TotalCaption
End Property
Property Let TotalCaptionAtColumnNumber(ByVal newTotalCaption As Long)
m_TotalCaption = newTotalCaption
End Property
Property Get isAppend() As Boolean
isAppend = m_isAppend
End Property
Property Let isAppend(ByVal newIsAppend As Boolean)
m_isAppend = newIsAppend
End Property
Public Sub AddinGrid(pRsName As ADODB.Recordset, FlexGrid As MSFlexGrid, Optional CloseCursor As Boolean)
If pRsName.EOF = True Or pRsName.BOF = True Then
MsgBox "Recordset either EOF or BOF"
pRsName.Close
Exit Sub
End If
If pRsName.RecordCount >= 5555 Then
MsgBox "Too bit table to fit in FlexGrid"
pRsName.Close
Exit Sub
End If
FlexGrid.AllowUserResizing = flexResizeColumns
Dim i, NewRow, FieldNumber, gFieldNumber As Long
Dim yesGroupFound As Boolean
Dim yesSumFound As Boolean
Dim yesOmitFound As Boolean
Dim LastFieldValue As Variant
Dim mTotal As Variant
Dim RecordSkip As Boolean
TotalFields = pRsName.Fields.Count
If m_GroupBy <> "" Then
For i = 0 To TotalFields - 1
If UCase(pRsName.Fields(i).Name) = UCase(m_GroupBy) Then
yesGroupFound = True
gFieldNumber = i
Exit For
End If
Next i
If yesGroupFound = False Then
MsgBox "Invalid field entered for grouping"
Exit Sub
End If
End If
If m_SumOn <> "" Then
For i = 0 To TotalFields - 1
If UCase(pRsName.Fields(i).Name) = UCase(m_SumOn) And (pRsName.Fields(i).Type = adDouble _
Or pRsName.Fields(i).Type = adNumeric _
Or pRsName.Fields(i).Type = adLongVarChar) Then
yesSumFound = True
FieldNumber = i
Exit For
End If
Next i
If yesSumFound = False Then
MsgBox "Field entered for sum invalid or non numeric."
Exit Sub
End If
End If
If m_OmitZero <> "" Then
For i = 0 To TotalFields - 1
If UCase(pRsName.Fields(i).Name) = UCase(m_OmitZero) And (pRsName.Fields(i).Type = adDouble _
Or pRsName.Fields(i).Type = adNumeric _
Or pRsName.Fields(i).Type = adLongVarChar) Then
yesOmitFound = True
Exit For
End If
Next i
If yesOmitFound = False Then
MsgBox "Field entered for Omit zero value is invalid or non numeric."
Exit Sub
End If
End If
i = 0
FlexGrid.Cols = TotalFields
If m_isAppend = False Then
FlexGrid.Rows = 2
End If
FlexGrid.RowHeight(0) = 400
With FlexGrid
For i = 0 To TotalFields - 1
If Not IsNull(pRsName.Fields(i)) Then
.CellFontBold = True
.TextMatrix(0, i) = UCase(pRsName.Fields(i).Name)
End If
Next i
i = 0
pRsName.MoveFirst
If m_GroupBy <> "" Then
LastFieldValue = pRsName.Fields("" & m_GroupBy & "")
End If
If m_isAppend = False Then
NewRow = 0
End If
Do While pRsName.EOF = False
If m_OmitZero <> "" Then
If pRsName.Fields("" & m_OmitZero & "") = 0 Then
RecordSkip = True
Else
RecordSkip = False
End If
End If
If RecordSkip = False Then
If m_SumOn <> "" And m_GroupBy = "" Then
mTotal = mTotal + pRsName.Fields("" & m_SumOn & "")
End If
If m_GroupBy <> "" Then
If LastFieldValue = pRsName.Fields("" & m_GroupBy & "").Value Then
If m_SumOn <> "" Then
mTotal = mTotal + pRsName.Fields("" & m_SumOn & "")
End If
Else
If NewRow > 1 Then
.Rows = .Rows + 1
NewRow = .Rows - 1
If m_SumOn <> "" Then
If m_TotalCaption > 0 Then
.TextMatrix(NewRow, m_TotalCaption) = "Total"
.Col = m_TotalCaption
.Row = .Rows - 1
.CellFontUnderline = True
.CellFontBold = True
.CellBackColor = vbWhite
.CellForeColor = vbMagenta
Else
.TextMatrix(NewRow, 1) = "Total"
End If
.TextMatrix(NewRow, FieldNumber) = Format(Round(mTotal, 0), "#######.00")
mTotal = 0
mTotal = mTotal + pRsName.Fields("" & m_SumOn & "")
End If
.Col = gFieldNumber
.Row = .Rows - 1
.CellBackColor = vbWhite
.Col = TotalFields - 1
'.Cols = TotalFields
.Row = .Rows - 1
.ColSel = FieldNumber
.FillStyle = flexFillRepeat
.CellBackColor = vbWhite
.CellForeColor = vbBlue
.CellFontBold = True
End If
End If
End If
End If
If RecordSkip = False Then
.Rows = .Rows + 1
NewRow = .Rows - 1
For i = 0 To TotalFields - 1
If Not IsNull(pRsName.Fields(i)) Then
If pRsName.Fields(i).Type = adDouble Then
.TextMatrix(NewRow, i) = Format(pRsName.Fields(i).Value, "#######.00")
Else
.TextMatrix(NewRow, i) = pRsName.Fields(i).Value
End If
Else
.TextMatrix(NewRow, i) = ""
End If
Next i
i = 0
If m_GroupBy <> "" Then
LastFieldValue = pRsName.Fields("" & m_GroupBy & "")
End If
End If
pRsName.MoveNext
Loop
If mTotal > 0 And m_GroupBy = "" And m_SumOn <> "" Then
.Rows = .Rows + 1
NewRow = .Rows - 1
If m_TotalCaption > 0 Then
.TextMatrix(NewRow, m_TotalCaption) = "Total"
Else
.TextMatrix(NewRow, 1) = "Total"
End If
.TextMatrix(NewRow, FieldNumber) = Format(Round(mTotal, 0), "#######.00")
mTotal = 0
.Col = TotalFields - 1
.Row = .Rows - 1
.ColSel = FieldNumber
.FillStyle = flexFillRepeat
.CellBackColor = vbWhite
.CellForeColor = vbBlue
.CellFontBold = True
End If
If CloseCursor = True Then
pRsName.Close
Set pRsName = Nothing
End If
End With
End Sub
add this class in your project , set it in your form from where u wand to add data in grid.
VB Code:
Set newAddintoGrid = New clsAddInFlexGrid
Call newAddintoGrid.AddinGrid(AppRateRs, FxGrid)
fxgrid is that where u want to add data.
-
Aug 8th, 2005, 01:10 AM
#19
Re: recordset to msflexgrid
Can you post your class, along with how to use the flags? It looks very interesting.
Last edited by dglienna; Aug 8th, 2005 at 01:36 AM.
-
Aug 8th, 2005, 01:25 AM
#20
Re: recordset to msflexgrid
The flexgrid control is not data-aware. Instead replace it with a MSHFlexgrid control. You can use all the methods/properties of flexgrid in this along with the one-liner to fill it with the recordset.
VB Code:
Set MSHFlexGrid1.Recordset = rsData
-
Aug 8th, 2005, 01:30 AM
#21
Hyperactive Member
Re: recordset to msflexgrid
VB Code:
newAddintoGrid.ColoumnNameToSkipRecordIfZero
newAddintoGrid.FieldGroupBy
newAddintoGrid.isAppend
newAddintoGrid.SumOnField
newAddintoGrid.TotalCaptionAtColumnNumber
If you want to skip record if value zero of any particular colour for example we do not need that item which stock value is 0, simply name = fieldname
if you want to grouping on grid on particular field = fieldname
if you want to add data from different records set append = true
if you want to sum on numeric field = fieldname
u can place "Total" caption near to sum result.
many other feature can be added.
-
Aug 8th, 2005, 01:35 AM
#22
Hyperactive Member
Re: recordset to msflexgrid
But here i we have un limited options , can MshFlexgrid allow you to add data from different recordset?. Can MshFlexGrid allow you to place any heading or caption at run time. And most important is that it release the record set after retreiving the data.
-
Aug 8th, 2005, 01:38 AM
#23
Re: recordset to msflexgrid
zubairkhan:
I got a few errors. Can you post a small project along with the class?
Thanks.
-
Aug 8th, 2005, 01:48 AM
#24
Hyperactive Member
Re: recordset to msflexgrid
David
I have not small project, but i am sending you recordset and its fields set by class
VB Code:
.Open "SELECT b.JobNo,b.JobDate,b.JobDesc,b.PartyName,b.OrderQty, " & _
"iif(isnull(b.Issued),0,b.issued)-iif(isnull(e.rejected),0,e.rejected) as Issued,b.Billed, " & _
"b.BilledRate,b.RateUnit,b.BilledAmt,b.GstAmt, " & _
"b.TotValue,b.ToBill " & _
"FROM billedquery b,enquiry e " & _
"WHERE e.jobno = b.jobno " & _
"AND mid(e.jobno,3,1) = '" & txtType.Text & "' " & _
"ORDER BY b.partyname"
this will might help you to rectify my error i really appreciate you if you identified my mistakes .
VB Code:
Set NewAddinGrid = New clsAddInFlexGrid
NewAddinGrid.ColoumnNameToSkipRecordIfZero = "tobill"
NewAddinGrid.FieldGroupBy = "partyname"
NewAddinGrid.SumOnField = "billedamt"
NewAddinGrid.TotalCaptionAtColumnNumber = 7
Call NewAddinGrid.AddinGrid(ViewRs, FxGrid)
Thanks
-
Aug 8th, 2005, 02:01 AM
#25
Re: recordset to msflexgrid
OK. I've resolved the errors. You also have to declare NewAddinGrid as a object. I'll use some data tomorrow. I just worked on adding a flexgrid to a new project. This would have saved some time. I may replace my code with your class tomorrow. Thanks.
EDIT: You should place a copy in the CodeBank. I'll let you know if there are any more problems.
-
Aug 8th, 2005, 02:04 AM
#26
Hyperactive Member
Re: recordset to msflexgrid
Thanks for your co-operation, i will add in code bank after your reply.
-
Aug 8th, 2005, 02:08 AM
#27
Re: recordset to msflexgrid
Originally Posted by zubairkhan
But here i we have un limited options , can MshFlexgrid allow you to add data from different recordset?. Can MshFlexGrid allow you to place any heading or caption at run time. And most important is that it release the record set after retreiving the data.
You'll be able to do all these. You just need to change the approach. An MSHFlexGrod can do everything a FlexGrid can do plus more.
It just populates itself with the recordset, doesn't bind to it. So after that you can close the recordset or do whatever else. And changes to the recordset or MSHFlexGrid cell after that won' be reflected in the other one either.
Pradeep
-
Aug 8th, 2005, 02:27 AM
#28
Thread Starter
Lively Member
Re: recordset to msflexgrid
Why do I always get this error?
subscript out of range
Can anyone here help me correct this code.
Thanks in advance.
VB Code:
Private Sub FG_ShowRecordset(myFG As MSFlexGrid, myRST As ADODB.Recordset)
Screen.MousePointer = vbHourglass
myFG.Redraw = False
myFG.AllowUserResizing = flexResizeColumns
myFG.ScrollTrack = True
'column header
myFG.TextMatrix(0, 0) = "DocNo"
myFG.TextMatrix(0, 1) = "ItemNo"
myFG.TextMatrix(0, 2) = "Item"
myFG.TextMatrix(0, 3) = "Occurances"
With myRST
'loop for displaying the document id in DocNo column
'fields(0) = Id
x = 0
Do
x = x + 1
myFG.TextMatrix(x, 0) = .Fields(0).Value 'error points to this line
.MoveNext
Loop Until .EOF
End With
myFG.Redraw = True
Screen.MousePointer = vbNormal
End Sub
-
Aug 8th, 2005, 02:41 AM
#29
Re: recordset to msflexgrid
I've adapted szlamany's method
VB Code:
Sub DisplayRecordset(x As ADODB.Recordset)
Dim rs As String, fld As String, z As Long
Dim str As String
flx.Rows = 1
str = "ID" & vbTab & "Last Name " & vbTab & "First Name " & vbTab
str = str & "Address " & vbTab & "City " & vbTab & "State " & vbTab
flx.FormatString = str
Do While x.EOF = False
For z = 0 To x.Fields.Count - 1
fld = x(z) & ""
If rs <> "" Then rs = rs & vbTab
rs = rs & fld
Next z
flx.AddItem rs
rs = ""
x.MoveNext
Loop
Set x = Nothing
End Sub
It adds the autonumber to col(0). You could use formatstrint to load the row headings, also. I had to set .rows to 1 before I loaded the grid as it was adding a blank row to the beginning of the grid each time I populated it.
-
Aug 9th, 2005, 02:28 PM
#30
Re: recordset to msflexgrid
I was getting a blank row on top, so I made this change to your class, which fixed things. I like the bold KEY field name.
VB Code:
If m_isAppend = False Then
FlexGrid.Rows = 1 ' Instead of 2
End If
Also, the columns weren't wide enough in a few cases. I have some long and some short fieldnames. Also, it would be nice if you could set the alignment of fields and the format.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|