2 replies to this topic

[it01y2]

I am curently quarying a mysql database, however I am struggling retrieving variables from the script I have downloaded from vbmysqldirect. Here is the code:

'---------------------------------------------------------------------------------------
' Module	: mListviewLoader
' DateTime  : 03/07/04 14:06
' Author	: Robert Rowe
' Purpose   : Modified to work with VBMySQLDirect
'---------------------------------------------------------------------------------------
'
'	Copyright 2003 Mike Hillyer (www.vbmysql.com)
'
' Module	: mListviewLoader
' DateTime  : November 27, 2003
' Author	: MIKE HILLYER
' Purpose   : See ListViewLoad Sub

Option Explicit
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Const LVM_SETITEMCOUNT As Long = 4096 + 47
 
Public Sub ListViewLoad(lvwData As Control, rs As MYSQL_RS, Optional Owner As Variant)
'---------------------------------------------------------------------------------------
' Procedure : ListViewLoad
' DateTime  : 03/07/04 14:09
' Author	: Robert Rowe
' Purpose   : Modified to work with VBMySQLDirect
'---------------------------------------------------------------------------------------

'THIS FUNCTION IS USED TO TAKE DATA FROM A ADODB RECORDSET AND LOAD IT INTO
'A LISTVIEW CONTROL. PLACING A PROGRESS BAR NAMED pbrProgress ON OWNER FORM
'WILL ALLOW FOR AN UPDATING PROGRESS BAR TO SHOW HOW FAR ALONG YOU ARE WHEN
'OWNER PARAMETER IS SPECIFIED DURING CALL.
'
'NOTE: LISTVIEWS ARE NOT VERY EFFICIENT WITH MEMORY, AND THIS FUNCTION
'IS NOT RECCOMENDED FOR RECORDSETS WITH > 10,000 RECORDS
'
'AUTHOR: MIKE HILLYER
'
'USAGE: ListViewLoad lvwMyListView, rsMyRecordset[, me]
 
	On Error Resume Next
	 
	Dim lngCounter As Long
	 
	Dim FirstColumn As Boolean
 
	lvwData.View = lvwReport					'THESE PARAMETERS CREATE
	lvwData.LabelEdit = lvwManual			   'A DATAGRID-LIKE APPEARANCE
	lvwData.GridLines = True
	lvwData.FullRowSelect = True
	 
	lvwData.ListItems.Clear
	lvwData.ColumnHeaders.Clear
	
	'POPULATE HEADERS
	For lngCounter = 0 To rs.FieldCount - 1
		lvwData.ColumnHeaders.Add , , rs.Fields(lngCounter).Name
	Next
	
	'PREALLOCATE MEMORY FOR ROWS
	SendMessage lvwData.hwnd, LVM_SETITEMCOUNT, rs.RecordCount, 0&
	 
	Dim myitems As MSComctlLib.ListItems
	Dim test As String
	Dim Text1 As TextBox
	
	Set myitems = lvwData.ListItems
	 
	Do Until rs.EOF
		FirstColumn = True	  'FIRST COLUMN IS A LISTITEM, REST ARE LISTSUBITEMS
		For lngCounter = 0 To rs.FieldCount - 1
			If FirstColumn Then
				If Not IsNull(rs.Fields(lngCounter).Value) Then
				
				'insert data into the table
				
				myitems.Add , , rs.Fields(lngCounter).Value
				
			 Else
				myitems.Add , , ""  'NULL FIELDS NEED A BLANK ITEM
				End If				  'TO KEEP DATA FROM SHIFTING LEFT
				FirstColumn = False
			Else
				If Not IsNull(rs.Fields(lngCounter).Value) Then
				   myitems(myitems.Count).ListSubItems.Add , , rs.Fields(lngCounter).Value
				Else
					myitems(myitems.Count).ListSubItems.Add , , ""
				End If
			End If
		Next
		If Not IsMissing(Owner) Then Owner.pbrProgress.Value = (rs.AbsolutePosition / rs.RecordCount) * 100
		rs.MoveNext
	Loop
End Sub

This script basically displays the results in a table however I need to edit this script to give me set variables for later use. The loop loops the query intill each row has been displayed in the table.

For example I want to create a variable with the queryed information from the mysql database:

(example)
First column Second column Third column (header)
this result will be in a variable called first column 1 this result will be in a variable called second column 1
this result will be in a variable called first column 2 this result will be in a variable called second column 1
this result will be in a variable called first column 3 etc...
this result will be in a variable called first column 4
etc...

Please I need help, i an send you the project if you want, many thanks-
Rob

[Galahad]

Ok, if I understood you correctly, you could do it something like this... I'm writing this blindly, since I don't use VB anymore, I switched over to Linux

Dim Headers() As String
Dim Columns() As String

Public Sub FillVariables(ByRef Headers() As String, ByRef Columns() As String, rs As MYSQL_RS, Optional Owner As Variant)
	On Error Resume Next
	Dim lngCounter As Long
	Dim FirstColumn As Boolean

	ReDim Headers(rs.FieldCount - 1)
	For lngCounter = 0 To rs.FieldCount - 1
		Headers(lngCounter) = rs.Fields(lngCounter).Name
	Next
	
	'PREALLOCATE MEMORY FOR ROWS
	SendMessage lvwData.hwnd, LVM_SETITEMCOUNT, rs.RecordCount, 0&

	ReDim Columns(rs.RecordCount, rs.FieldCount,rs - 1)
	Dim lngCols As Long
	
	Do Until rs.EOF
		lngCols = lngCols + 1
		For lngCounter = 0 To rs.FieldCount - 1
			Columns(lngCols, lngCounter) = rs.Fields(lngCounter).Value
		Next
		rs.MoveNext
	Loop
End Sub

This is ofcourse only the idea how to try, I haven't actually tried it...

The whole idea is to use two-dimensional array for row data, so that each row, can contain column data, for example
Columns(1,1) = Column 1, Row 1
Columns(1,2) = Column 2, Row 1
Columns(1,3) = Column 3, Row 1
Columns(2,1) = Column 1, Row 2

etc...

[iGuest]

label names in a for next loop
Vb Variable Help

I have a set of labels (dia1, dia2, dia3...Dia21, area1, area2, area3...Area21)
I have tried to reset all the captions to "" using a for next loop but it won't let me. Is there an easier way to do this.

Thank you all in advance for your kind help.

Steve

-question by Steve