Looping through SQL data

[Jat421]

Hi, I am trying to build a asset tracking application with Mysql. I have selected all from the tables but how can I loop through the columns?

Func Search()
   Local $sql = Connect()
   $string = "select * from assets"
   $i = _Query($sql, $string)
  
  
With $i
$a = .Fields("asset_no").value
MsgBox(0, "test", $a)
EndWith

Will I have to use multiple with statements and assign them to each variable or is their a easier way to loop through them all?

[jdelaney]

good place to start:

http://www.w3schools.com/ado/ado_ref_recordset.asp

example of a query that may/may not return some records

expandcollapse popup

Func WGe_SQLStatement($sCallersConnectionString, $sCallersSQLStatement)
$ado = ObjCreate("ADODB.Connection")
If @error = 1 Then
;Call("Var_SetLogAndActOnState", 0, 1, "WGe_SQLStatement()", "Func=[ObjCreate]: Unable to create ADODB.Connetion Object.", False, False)
Return False
EndIf
$adors = ObjCreate("ADODB.RecordSet")
If @error = 1 Then
;Call("Var_SetLogAndActOnState", 0, 1, "WGe_SQLStatement()", "Func=[ObjCreate]: Unable to create ADODB.RecordSet Object.", False, False)
Return False
EndIf
$ado.Open($sCallersConnectionString)
$adors.Open($sCallersSQLStatement, $ado)
; Only get records if there are records to receive
If $adors.Fields.Count > 0 Then
Dim $aReturn[1][1]
$iField = 0
; Create the First record of the array with the Field names
For $Field In $adors.Fields
ReDim $aReturn[1][$iField + 1]
$aReturn[0][$iField] = $Field.name
$iField += 1
Next
$iRow = 1
While Not $adors.EOF
$iField = 0
For $Field In $adors.Fields
    $sValue = $adors.Fields($Field.name).value
    ReDim $aReturn[$iRow + 1][UBound($aReturn, 2)]
    $aReturn[$iRow][$iField] = $sValue
    ;Call("Var_SetLogAndActOnState", 2, 2, "SqlAndSubmitExtRpt()", "Submitted data fro patient#=[" & $iPatient & "]; MRN=[" & $sPID & "]; PatientName=[" & $sLast & " ," & $sFirst & "].", False, False)
    $iField += 1
Next
$adors.MoveNext
$iRow += 1
WEnd
; Close the recordset
$adors.Close
$adors = 0
;Call("Var_SetLogAndActOnState", 1, 2, "WGe_SQLStatement()", "Func=[WGe_SQLStatement]: Returning Record(s)=[" & UBound($aReturn) - 1 & "], each containing Fileds=[" & UBound($aReturn, 2) & "].", False, False)
Else
;Call("Var_SetLogAndActOnState", 1, 2, "WGe_SQLStatement()", "Func=[Insert|Update]: Statement ran successfully.", False, False)
$aReturn = True
EndIf
; Close ADODB connection
$ado.Close
$ado = 0
If IsArray($aReturn) Then
If UBound($aReturn) > 1 Then
$giSubFunctionCounter -= 1
Return $aReturn
EndIf
EndIf
Return True
EndFunc ;==>WGe_SQLStatement

specifically, to answer your question:

For $Field In $adors.Fields
    $sValue = $adors.Fields($Field.name).value
 
   Consolewrite ( $sValue & @crlf )
Next

Edited September 7, 2012 by jdelaney

[jchd]

Good example. OTOH, you can also retrieve a 2D array containing all columns of all rows by using:

Func Search()
Local $sql = Connect()
$string = "select * from assets"
$i = _Query($sql, $string)
With $i
Local $a = .GetRows()         ; <=== here
_ArrayDisplay($a)
EndWith

BTW, you should probably not have to make a brand new connection for every invokation of Search().

[Jat421]

Thanks that does the trick!

Jdelany, the w3 school link that you posted are all the properties and methods supported that are on the website. I was trying to get the "Find" method working but that no luck. Thanks!

[jchd]

You can spend some time reading MSDN about ADO to see what methods/properties are available for which objects. Still, there is no firm guarantee that everything will be implemented in a given ADO layer. Experiments will tell you what mileage you can expect running a given ADO interface.