redsleeves Posted April 10, 2008 Share Posted April 10, 2008 I can't read the ALIAS values from a query. The query returns the data correctly, I've checked, but it creates a derived table with ALIASES that I can't get the values from. Here is what I have: expandcollapse popupDim $My_Database = "Dummy.mdb" $oConn = ObjCreate("ADODB.Connection") $oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq="&$My_Database&"") $oRS_recordcount = ObjCreate("ADODB.Recordset") $oRS_recordcount.Open("Select username FROM pt_users", $oConn, 1,3) While not $oRS_recordcount.EOF $oRS_recordcount.MoveNext WEnd $numusers = $oRS_recordcount.RecordCount $oRS_recordcount.Close Dim $MyArray[$numusers+1] $MyArray[0] = $numusers $oRS = ObjCreate("ADODB.Recordset") $SQL = "" $SQL &= "Select Max(pt_records.recordstatus) As USERSTATUS, " $SQL &= "Max(pt_records.recordaddon) As ADDDATE, " $SQL &= "pt_users.userlastname As LASTNAME, " $SQL &= "Max(pt_users.userfirstname) As FIRSTNAME " $SQL &= "FROM pt_records " $SQL &= "INNER JOIN pt_users On pt_records.recordaddby = pt_users.username " $SQL &= "GROUP By pt_users.userlastname" ;MsgBox(0,"debug",""""& $SQL & """") $oRS.Open(""& $SQL & "", $oConn, 1,3) For $l = 1 to $numusers $UserLastName = $oRS.Fields( "userlastname" ).value $UserFirstName = $oRS.Fields( "userfirstname" ).value $UserRecordStatus = $oRS.Fields( "recordstatus" ).value $UserRecordAddon = $oRS.Fields( "recordaddon" ).value $MyArray[$l] = $UserLastName & ", " & $UserFirstName & "|" & $UserRecordStatus & "|" & $UserRecordAddon $oRS.MoveNext Next $oRS.Close $oConn.Close GuiCreate("Who", 420, 600, -1, -1, $WS_POPUPWINDOW, $WS_EX_TOOLWINDOW + $WS_EX_TOPMOST) GUISetState (@SW_SHOW) $listview = GUICtrlCreateListView ("",10, 30, 400, 500,$LVS_REPORT) GUICtrlSetBkColor($listview,0xece9d8) _GUICtrlListView_InsertColumn ($LISTVIEW, 0, "Who", 130) _GUICtrlListView_InsertColumn ($LISTVIEW, 1, "Status", 78) _GUICtrlListView_InsertColumn ($LISTVIEW, 2, "Time", 75) _GUICtrlListView_InsertColumn ($LISTVIEW, 3, "Date", 90) For $w = 1 To $numusers $item1=GUICtrlCreateListViewItem("|||",$listview) GUICtrlSetData($item1,_ArrayPop ( $MyArray )) Next $okay_button = GuiCtrlCreateButton("Okay", 170, 550, 80, 30) While 1 $guimsg = GUIGetMsg() Select Case $guimsg = $okay_button GUIDelete("Who") ExitLoop EndSelect WEnd I have also tried using the aliases like this: $UserLastName = $oRS.Fields( "LASTNAME" ).value $UserFirstName = $oRS.Fields( "FIRSTNAME" ).value $UserRecordStatus = $oRS.Fields( "USERSTATUS" ).value $UserRecordAddon = $oRS.Fields( "ADDDATE" ).value What I would like to do is read the values from the aliases. Do I need to open another recordset to access the derived table? Any ideas or suggestions would be welcome. Also, I can provide the dummy database that I am using. Thanks in advance. Link to comment Share on other sites More sharing options...
ptrex Posted April 10, 2008 Share Posted April 10, 2008 @redsleevesMaybe this can give you some tips.Aliases When joining Tables you can give a table an alias name which you can then use throughout the SQL statement. This is another way of reducing the size on an SQL statement that a join. When joining the Employee and Training Table instead of typing the whole table name in front of every column, you could alias Employee as E and Training as T Which would give you: Select E.EMPLOYEENAME, T.DATE, T.DESCRIPTION From EMPLOYEE E, TRAINING T where E.EMPLOYEENO = T.EMPLOYEENOSQL For Accessregardsptrex Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM - New Link to comment Share on other sites More sharing options...
redsleeves Posted April 11, 2008 Author Share Posted April 11, 2008 Thank you for the tip.I can access the tables and fields with the following code but the returned data is not sorted correctly:$oRS = ObjCreate("ADODB.Recordset") $SQL = "" $SQL &= "SELECT pt_records.recordstatus, " $SQL &= "pt_records.recordaddon, " $SQL &= "pt_users.userlastname, " $SQL &= "pt_users.userfirstname " $SQL &= "FROM pt_records " $SQL &= "INNER JOIN pt_users On pt_records.recordid = pt_users.userid " $oRS.Open(""& $SQL & "", $oConn, 3,3)As soon as I add the 'GROUP By' function (code below) everything falls apart. This is the error I get: $oRS.Open(""& $SQL & "", $oConn, 3,3)^ ERRORError: The requested action with this object has failed.$oRS = ObjCreate("ADODB.Recordset") $SQL = "" $SQL &= "SELECT pt_records.recordstatus, " $SQL &= "pt_records.recordaddon, " $SQL &= "pt_users.userlastname, " $SQL &= "pt_users.userfirstname " $SQL &= "FROM pt_records " $SQL &= "INNER JOIN pt_users On pt_records.recordid = pt_users.userid " $SQL &= "GROUP By pt_users.userlastname" < ---------------------- this breaks me $oRS.Open(""& $SQL & "", $oConn, 3,3)I have tested the SQL above with the 'GROUP By' inside of Access and it works fine. But using the 'GROUP By' creates a new temporary recordset and I haven't been able to get to that. I was ALIASING the fields earlier because they show up in the derived table as Expr1000, Expr1001, Expr1002, etc., and aliasing them makes the recordset easier to read. I was also aliasing the tables within the SQL statement itself but I have not found a way to declare that the derived recordset should be called 'MyNewTable,' or something similar, and then connect to it directly. I have also looked at CursorType and LockType to see if I am just not 'seeing' the derived recordset.I am using CursorType adOpenStatic, 3, which should allow me to process a local cached version of the database. I don't care about any changes being made while I am running my query so my LockType is also 3, adLockOptimistic, which allows the database to be updated while I'm running my query.I am trying to find some vbscript that uses ADO with a group like this so that I can take it part. I haven't found anything useful so far. Any ideas or suggestions are welcome. Link to comment Share on other sites More sharing options...
redsleeves Posted April 13, 2008 Author Share Posted April 13, 2008 I wrote this in vbscript and it worked fine. The only difference was in the vbscript code I echoed the data out onto the screen instead of pumping it into an array. So I tried this with AutoIt as well and it, too, worked. So, something is going on with my array when I add the GROUP function to my SQL query. This works, For $l = 1 to $numusers $UserLastName = $oRS.Fields( "LASTNAME" ).value $UserFirstName = $oRS.Fields( "FIRSTNAME" ).value $UserRecordStatus = $oRS.Fields( "STATUS" ).value $UserRecordAddon = $oRS.Fields( "ADDON" ).value _GetLoginDate($UserRecordAddon) MsgBox(0,"",$UserLastName &", "&$UserFirstName &" "&$UserRecordStatus &" "&$RecordDate) $oRS.MoveNext Next ...but this does not: For $l = 1 to $numusers $UserLastName = $oRS.Fields( "LASTNAME" ).value $UserFirstName = $oRS.Fields( "FIRSTNAME" ).value $UserRecordStatus = $oRS.Fields( "STATUS" ).value $UserRecordAddon = $oRS.Fields( "ADDON" ).value _GetLoginDate($UserRecordAddon) $MyArray[$l] = $UserLastName & ", " & $UserFirstName & "|" & $UserRecordStatus & "|" & $RecordDate $oRS.MoveNext Next Any ideas? Possibly timing? I was using the array to populate a listview by popping each element off but I could use the string directly by calling a function from within the for loop as I run my query. I'll play with that for awhile. Here is the vbscript for anyone who may be interested: Dim my_database my_database = "DLECINOUT.mdb" Dim QueryString QueryString = "" QueryString = QueryString & "SELECT MAX(pt_records.recordstatus) AS STATUS, " QueryString = QueryString & "MAX(pt_records.recordaddon) AS ADDON, " QueryString = QueryString & "pt_users.userlastname AS LASTNAME, " QueryString = QueryString & "MAX(pt_users.userfirstname) AS FIRSTNAME " QueryString = QueryString & "FROM pt_records " QueryString = QueryString & "INNER JOIN pt_users On pt_records.recordaddby = pt_users.username " QueryString = QueryString & "GROUP By pt_users.userlastname" Set objConnection = CreateObject("ADODB.Connection") Set objRecordSet = CreateObject("ADODB.Recordset") objConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0; " & "Data Source = " & my_database objRecordSet.Open QueryString, objConnection, 3, 3 While not objRecordSet.EOF WScript.Echo objRecordSet.Fields( "LASTNAME" ).Value & " " &_ objRecordSet.Fields( "FIRSTNAME" ).Value & " " &_ objRecordSet.Fields( "STATUS" ).Value & " " &_ objRecordSet.Fields( "ADDON" ).value objRecordSet.MoveNext WEnd objRecordSet.Close objConnection.Close Link to comment Share on other sites More sharing options...
redsleeves Posted April 14, 2008 Author Share Posted April 14, 2008 I got this working. Finally. Probably not the most elegant way to achieve this but it does work. I just dumped all of the returned fields into a string, appended an @CR onto the end of each recursion then StringSplit the whole thing into an array. That worked. Here is the chunk of code that I am using. This is actually a part of something much larger. expandcollapse popup#include <GUIConstants.au3> #Include <GuiListView.au3> #include <Array.au3> Dim $My_Database = "dummy.mdb" Dim $MyString = "" If FileExists($My_Database) Then $oConn = ObjCreate("ADODB.Connection") $oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq="&$My_Database&"") If $oConn.State = 1 Then ;MsgBox(0,"","Connection successfully opened.") $oRS = ObjCreate("ADODB.Recordset") $SQL = "" $SQL &= "SELECT MAX(pt_records.recordstatus) AS STATUS, " $SQL &= "MAX(pt_records.recordaddon) AS ADDON, " $SQL &= "pt_users.userlastname AS LASTNAME, " $SQL &= "MAX(pt_users.userfirstname) AS FIRSTNAME " $SQL &= "FROM pt_records " $SQL &= "INNER JOIN pt_users On pt_records.recordaddby = pt_users.username " $SQL &= "GROUP By pt_users.userlastname" $oRS.Open(""& $SQL & "", $oConn,2,3) While Not $oRS.EOF $MyString &= $oRS.Fields( "LASTNAME" ).value & ", " $MyString &= $oRS.Fields( "FIRSTNAME" ).value & "|" $MyString &= $oRS.Fields( "STATUS" ).value & "|" $MyString &= $oRS.Fields( "ADDON" ).value & @CR $oRS.MoveNext WEnd $MyArray = StringSplit($MyString, @CR, 0) $oRS.Close $oConn.Close _ArrayDelete( $MyArray,0) GuiCreate("Who", 420, 600, -1, -1, $WS_POPUPWINDOW, $WS_EX_TOOLWINDOW + $WS_EX_TOPMOST) GUISetState (@SW_SHOW) $listview = GUICtrlCreateListView ("",10, 30, 400, 500,$LVS_REPORT) GUICtrlSetBkColor($listview,0xece9d8) GUICtrlSetState($listview,$GUI_FOCUS) _GUICtrlListView_InsertColumn ($LISTVIEW, 0, "Who", 130) _GUICtrlListView_InsertColumn ($LISTVIEW, 1, "Status", 78) _GUICtrlListView_InsertColumn ($LISTVIEW, 2, "Time", 75) _GUICtrlListView_InsertColumn ($LISTVIEW, 3, "Date", 90) For $w = 1 To UBound($MyArray) $item1=GUICtrlCreateListViewItem("|||",$listview) GUICtrlSetData($item1,_ArrayPop ( $MyArray )) Next $okay_button = GuiCtrlCreateButton("Okay", 170, 550, 80, 30) While 1 $guimsg = GUIGetMsg() Select Case $guimsg = $okay_button GUIDelete("Who") ExitLoop EndSelect WEnd Else MsgBox(0,"",$oConn.State) EndIf EndIf Link to comment Share on other sites More sharing options...
ResNullius Posted April 16, 2008 Share Posted April 16, 2008 I got this working. Finally. Probably not the most elegant way to achieve this but it does work. I just dumped all of the returned fields into a string, appended an @CR onto the end of each recursion then StringSplit the whole thing into an array. That worked.redsleeves, Might also want to look at the ADO Getrows and Getstring function. The Getrows will return an array with out the need for creating a string and splitting it. So replace your String creation/spilt code with $oRS.Open(""& $SQL & "", $oConn,2,3) $MyArray = $oRS.GetRows $oRS.Close $oConn.Close Should work the same... Link to comment Share on other sites More sharing options...
redsleeves Posted April 20, 2008 Author Share Posted April 20, 2008 Thank you ResNullius. Your example is much quicker than what I had been using. I am using it like this: $oRS.Open(""& $SQL & "", $oConn,2,3) Dim $GetRows[4] $GetRows[0] = "userlastname" $GetRows[1] = "userfirstname" $GetRows[2] = "recordstatus" $GetRows[3] = "recordaddon" $MyArray = $oRS.GetRows(-1,0,$GetRows) $oRS.Close For anyone who happens to stumble across this in the future, the '-1' is 'adGetRowsRest' which retrieves all records, '0' is the starting position and '$GetRows' is an array of the fields I want to retrieve in the order I want them in. I am connecting to a database that lives on a remote machine and this ended up being much quicker than retrieving all records and processing the data client side or running recursive queries with ugly, fat SQL on the server. (The connection is slow.) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now