And the saga continues

### Question for MODS ### - Not sure if you want this in a new topic given the code has completely changed? If so then this topic could be deleted.

The original code that I posted failed to connect to the database. After spending loads of time trying to work out why I decided to connect using a different method. Obviously all I have done is cut and paste from other peoples scripts and I can finally output a result to the screen.

The problem is that the value returned to the screen is the title of the column. It does return the same number of results to the screen as is shown when viewing the table "Person" / column "Email Address" in the MS Access GUI.

I have included the new code including all the error checking and minus closing connections and clearing down of the objects. I would appreciate any feedback on why the tile is being returned not the value in each of the rows.

 Code:

$= SetOption("WrapAtEol", "On")

; #### Set Variables ####

$DBPath = "@scriptdir\ActinicCatalog.mdb"
$CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DBpath;persist security info=false"
$CMDtxt = 'SELECT "Email Address" FROM Person'



; #### Create ADO Objects ####

$cn = CreateObject ("ADODB.Connection")
? "ADO Conn       ERROR @ERROR - @SERROR"

$cmd = CreateObject ("ADODB.Command")
? "ADO Cmd        ERROR @ERROR - @SERROR"

$rs = CreateObject ("ADODB.RecordSet")
? "ADO RS         ERROR @ERROR - @SERROR"



; #### Open Database connection ####

$cn.connectionstring = $CNstring
? "ADO CN String  ERROR @ERROR - @SERROR"

$cn.Open
? "ADO CN Open    ERROR @ERROR - @SERROR"



; #### Set Active commands ####

$cmd.activeconnection = $cn
? "ADO ActConn    ERROR @ERROR - @SERROR"

;$rs.activecommand = $cmd       Is this needed? It errors if left in!
;? "ADO ActCmd     ERROR @ERROR - @SERROR"



; #### Open Recordset and Populate with results of Query ####

$cmd.commandtext = $CMDtxt $rs.Open ($cmd)
? "ADO Query      ERROR @ERROR - @SERROR"



; #### Move to the first record ####

$rs.Movefirst()
? "ADO Movefirst  ERROR @ERROR - @SERROR"
? ""



; #### Read all data in the recordset ####

$L=0
$Field = $rs.Fields.count -1
do
for $i=0 to $Field ; count through each column
$rs.Fields.item($i).Value ; spit out the value in that column
if $i<>$Field ", " endif ; comma separate fields
$L = $L +1
? "Number of times rs.Fields.Item(i).Value has been called:" $L
next

$rs.MoveNext() ; goto the next record
? "ADO Movenext  ERROR @ERROR - @SERROR"
? ; CRLF separate records

until $rs.EOF ; until we reach the End Of the file


Edited by madboyo (2007-03-31 10:40 PM)