I have been following a number of threads i.e. Shawn who has illuminated a number of various issues re: Kix and OLE automation with respect to Access databases. All of the examples posted here are read-only. That part works fine. What I am unable to do is WRITE to a database. This is what works in VB but not KIX:

$x = OLEPutProperty ($cn, "Mode", "I", "3")
$x = OLECallProc ($cn, "OPEN", "s", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\MachineData\Machines.mdb")

$x = OLECallProc ($cn, "Execute", "s", "Insert into tbl_Machines (MachineName) Values ('Test')")

Please Help

errmm , with your permission Shawn !?

code:

---

break on
$engine = "dao.dbengine.35"
$database = "%0\..\mydatabase.mdb" 
;========================
; Connect to database ...
;========================
$dbe = olecreateobject ( "$engine" )
if @error goto finish endif
;======================
; Open the database ...
;======================
$dbs = val ( "&" + olecallfunc ( $dbe, "opendatabase", "s", "$database" ))
if @error goto finish endif
;==================================
; Create a new table and schema ...
;==================================
$rs = olecallfunc ( $dbs, "execute", "s", 
"CREATE TABLE JOCHEN (FULLNAME TEXT, BBSNAME TEXT);" )
if @error goto finish endif
;===========================
; Add some data to table ...
;===========================
$rs = olecallfunc ( $dbs, "execute", "s",
"INSERT INTO JOCHEN (FULLNAME,BBSNAME) VALUES ('Jochen Polster', 'jpols');")
if @error goto finish endif
;================================
; Add some more data to table ...
;================================
$rs = olecallfunc ( $dbs, "execute", "s",
"INSERT INTO JOCHEN (FULLNAME,BBSNAME) VALUES ('Shawn Tassie', 'Shawn');")
if @error goto finish endif
;======
:finish
;======
if @error
 ?
 ?"System error @error occurred."
 ?"@serror"
endif
if $dbe
 $= olereleaseobject ( $dbe )
endif
if $dbs
 $= olereleaseobject ( $dbs )
endif
?
exit

---

Jochen

_________________________

Merry Christmas my friend - and a Happy New Year !

What have you been up to ? You've sort of been popping in and out of the board lately.

Work been keeping you busy ?

Me and sbehrns (Steve) have been working off-line (email) to get these KIX OLE/ADODB database read/writes happening - it's similar to DAO (above) but there are subtle differences !

Personally - I'm going to use only ADO going forward - only because (I think) this is Microsofts recommened long-term OLEDB product.

By the way, working together with Steve - we finally managed to "crack" the ADO Recordset Object in KiX !!!.

This is great (for me anyway) becuase without the recordset object - one has to use a bunch of hacks and work-arounds.

Now it's nice and clean !

Shawn.

yep it was work i think
Merry Christmas and a happy new Year to you too, my Friend !!!
Any sample code using ADO ?

Jochen

_________________________

Here's where I am right now - next step is to update the database through the recordset...

I'm trying to think of a generic application for this stuff - a KIX DB script that might be of general interest to the board and would excerise all the DB functions... any ideas ?

Give this script a try and let me know if it works !

code:

---

break on

; Everytime this script is run - it updates the
; database and displays the results...

; Create a blank database called logon.mdb
; then set the following variable as required...

$database= "m:\msaccess\logon.mdb"

$dsn= "data source= $database; provider= microsoft.jet.oledb.4.0;"

; Create connection...

$connection= olecreateobject("adodb.connection")

if @error
 ?"mdac:adodb not insalled on this machine" 
 quit(0)
endif

; Set the connect string ...

$= oleputproperty($connection,"connectionstring","s","$dsn")

; Open connection ...

$= olecallfunc ($connection,"open")

; Create a table ...

$= olecallfunc($connection,"execute","s",
     "create table logon (stamp char(50) primary key, userid char(50))")

; Add a record ...

$= olecallfunc($connection,"execute","s",
     "insert into logon (stamp,userid) values ('@time', '@userid')")

; Create a recordset...

$recordset= val("&"+olecallfunc($connection,"execute","s",
     "select * from logon"))

; Get the fields...

$fields= val("&"+olegetproperty($recordset,"fields"))
$stamp= val("&"+olecallfunc($fields,"item","s","stamp"))
$userid= val("&"+olecallfunc($fields,"item","s","userid"))

; Enumerate the recordset...

while olegetproperty($recordset,"eof")= "0" and @error= 0

 ?
 ?"stamp  : " olegetproperty($stamp,"value")
 ?"userid : " olegetproperty($userid,"value")

 ; Next record please ...

 $= olecallfunc($recordset,"movenext")

loop

?

; Closeup (important!) ...

$= olecallfunc($recordset,"close")
$= olecallfunc($connection,"close")

; Cleanup (not so important!) ...

$= olereleaseobject($stamp)
$= olereleaseobject($userid)
$= olereleaseobject($fields)
$= olereleaseobject($recordset)
$= olereleaseobject($connection)

exit

---

Shawn.

[This message has been edited by Shawn (edited 20 December 2000).]

back from a short holiday ....
it works as announced , just one little thing : The values for the recordset won't get displayed ... (but... who cares !)

good work !
Jochen

_________________________

The data already exist. The open with an open.database.8 (MS Access) works but not with adodb. I want to modify my script to works with adodb for run faster.

Is someone have an web adress with explanation of dao or dsn script ?
Thanks for your help

Might want to make sure you've got MDAC/ADODB installed... one way to check this is by verifying you've got this key...

HKEY_CLASSES_ROOT\ADODB.Connection

defined in your registry...

May want to post your script as well...

For more ADODB info go here but to really get a good grounding in ADODB, read this...

Shawn.

[This message has been edited by Shawn (edited 12 March 2001).]

HTH

cj

------------------
For more scripts goto my website and click the hammer and spanner icon.

chrismat@ozemail.com.au

Your script is great and it work great on my network!

I am not familiar with ADO Object, DAO, OLE,

You have very clear command on your script,

; Create connection...
; Set the connect string ...
; Open connection ...
; Create a table ...
; Add a record ...
; Create a recordset...
; Get the fields...
; Enumerate the recordset...

I have also visit the microsoft link you posted. I would like to know where can I get the function or property that I can execute all of the above steps.

Thanks a lot.

Gilbert Ng

Information on ADODB has always been sketchy and scattered, but I just found this link ...

ADO API Reference

Check it out !

Shawn.

Any insight?

Cory T.

We ended-up resolving all our ADO recordset issues. Best thing to do is post your code and describe what's happening. Might want to stick a few of these...

?"@serror"

after your ole functions to see what's going on...

Shawn.

What I did to research was to echo the values of the recordset, fields, stamp, user id and they all show "0" and thus do not display.

Long story short, I am trying to use your code as a base to understand how to get where I want to go. That simply is a database of user info that can be updated as changes occur, for example add a field called SP to your code above, gather that data and add it Example 09:22:20 CTGreen SP4. Then I would like to find that record next time the script is ran and update if it has changed, SP5 for example.

I have not fully got the syntax of the ADO calls. Looked at the www site and the VB sytntax, but missed the transition thus far ;-(.

Let me know if you have any words of wisdom.

Also, thanks again...

CTG

You running Win9x by any chance ?

Shawn.

Here is what I have thus far, any suggestions?

I am concerned at the way the update allows only one at a time, is there a better way?

BTW: Thanks again for the assistance!!

; ***** Lookup Section, find info in registry and declare variable to be used by scripts

; ESD Exception List ***** Set Variable to show PC hardware type Laptop, Dell, Compaq or Unknown *****
SETL "MACHINE_TYPE=Unknown"
SELECT
CASE ExistKey("HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\Root\LEGACY_PCMCIA")=0
$MACHINE_TYPE=Laptop
CASE ExistKey("HKEY_LOCAL_MACHINE\SOFTWARE\Compaq")=0
$MACHINE_TYPE=Compaq
CASE ExistKey("HKEY_LOCAL_MACHINE\SOFTWARE\Dell Computer Corporation")=0
$MACHINE_TYPE=Dell
ENDSELECT

; ESD Exception List ***** Set Variable to show PC GDMS hardware platform Ex. dp6350 *****
$lookup=ReadValue("HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Windows NT\CurrentVersion", "GDMS_Hw_Platform")
$GDMS_Hw_Platform=$lookup

; ESD Exception List ***** Set Variable to show current SP version *****
$lookup=ReadValue("HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Windows NT\CurrentVersion", "CSDVersion")
SELECT
CASE INSTR($lookup, "3")>0
$SPVersion=SP3
CASE INSTR($lookup, "4")>0
$SPVersion=SP4
CASE INSTR($lookup, "5")>0
$SPVersion=SP5
CASE INSTR($lookup, "6")>0
$SPVersion=SP6
ENDSELECT

; ESD Exception List ***** Set Variable to show if user is in Administrators group *****
$ReturnCode = INGROUP("\\@WKSTA\Administrators")
If $ReturnCode < 2
$AdminEquiv=No
ELSE
$AdminEquiv=Yes
ENDIF

; *********************************************************************************************************************

break on

$database= "F:\Working\logonv2.mdb"

$dsn= "data source= $database; provider= microsoft.jet.oledb.4.0;"

; Create connection...

$connection= olecreateobject("adodb.connection")

if @error
?"mdac:adodb not insalled on this machine"
quit(0)
endif

; Set the connect string ...

$= oleputproperty($connection,"connectionstring","s","$dsn")

; Open connection ...

$= olecallfunc ($connection,"open")

; Create a table ...
; ***** $= olecallfunc($connection,"execute","s","create table logon (COMPUTERNAME char(50) primary key, USERID char(50))")

; Add a record for the PCNAME, note this field is primary will allow no duplicates...
; $= olecallfunc($connection,"execute","s","insert into logon (COMPUTERNAME, USERID) values ('@WKSTA', '@USERID')")
$= olecallfunc($connection,"execute","s","insert into logon (COMPUTERNAME) values ('@WKSTA')")

; Update USERID
$= olecallfunc($connection,"execute","s","update logon set USERID = '@USERID' where COMPUTERNAME = '@WKSTA'")

; Update PCTYPE
$= olecallfunc($connection,"execute","s","update logon set PCTYPE = '$MACHINE_TYPE' where COMPUTERNAME = '@WKSTA'")

; Update HWTYPE
$= olecallfunc($connection,"execute","s","update logon set HWTYPE = '$GDMS_Hw_Platform' where COMPUTERNAME = '@WKSTA'")

; Update SPVER
$= olecallfunc($connection,"execute","s","update logon set SPVER = '$SPVersion' where COMPUTERNAME = '@WKSTA'")

; Update ADMINEQUIV
$= olecallfunc($connection,"execute","s","update logon set ADMINEQUIV = '$AdminEquiv' where COMPUTERNAME = '@WKSTA'")

; *********************************************************************************************************************

; Closeup (important!) ...
$= olecallfunc($connection,"close")

Shawn.

Also I am trying to find if the COMPUTERNAME entry exists, but using I always get back 0. ANy ideas....

; Look for COMPUTERNAME in DB ...
$recordset=val("&"+olecallfunc($connection,"execute","s","select count(COMPUTERNAME) as total from logon where COMPUTERNAME = '@WKSTA'"))
? "$recordset"
? "$total"

; If COMPUTERNAME does not exist create record ...
if $recordset.wkstatotal=0
?"mdac:error - user not in table"
; Add a record for this COMPUTERNAME, note this field is primary will allow no duplicates...
$= olecallfunc($connection,"execute","s","insert into logon (COMPUTERNAME) values ('@WKSTA')")
endif

CTG

Give this script a whirl - first change it to your specific settings, etc...

It basically checks to see if a host record already exists, if not, it creates one. Then it proceeds to update the database directly through the recordset object. I still think there is room for more performance improvements here... give it a shot...

code:

---

break on

$db = "m:\msaccess\logon.mdb"
$dsn = "data source= $db; provider= microsoft.jet.oledb.4.0;"
$table = "logon"
$select = "SELECT * FROM LOGON WHERE COMPUTERNAME='@WKSTA'"

$adcmdtext = "1"
$adlockoptimistic = "3"
$adopenstatic = "3"
$adopendynamic = "2"
$adopenkeyset = "1"

$connection = olecreateobject("adodb.connection")
$xconnection = dectohex($connection)
$recordset = olecreateobject("adodb.recordset")

if $connection and $recordset

 $= olecallfunc($connection,"open","s","$dsn")

 if @error = 0

  $= olecallfunc($recordset,"open","sosss","$select","$xconnection",
                  "$adopenkeyset","$adlockoptimistic","$adcmdtext")

  if @error = 0

   $fields = val("&"+olegetproperty($recordset,"fields"))
   $computername = val("&"+olecallfunc($fields,"item","s","computername"))
   $stamp = val("&"+olecallfunc($fields,"item","s","stamp"))
   $userid = val("&"+olecallfunc($fields,"item","s","userid"))
   $pctype = val("&"+olecallfunc($fields,"item","s","pctype"))
   $hwtype = val("&"+olecallfunc($fields,"item","s","hwtype"))
   $spver = val("&"+olecallfunc($fields,"item","s","spver"))
   $adminequiv = val("&"+olecallfunc($fields,"item","s","adminequiv"))
  
   if olegetproperty($recordset,"recordcount") = 0
   
    $= olecallfunc($recordset,"addnew")
    $= oleputproperty($computername,"value","s","@WKSTA")

   endif

   $admin = ingroup("\\@wksta\administrators")

   $= oleputproperty($stamp,"value","s","@TIME")
   $= oleputproperty($userid,"value","s","@USERID")
   $= oleputproperty($pctype,"value","s","@INWIN")
   $= oleputproperty($hwtype,"value","s","HP")
   $= oleputproperty($spver,"value","s","@DOS")
   $= oleputproperty($adminequiv,"value","s","$admin")

   $= olecallfunc($recordset,"update")

   $= olereleaseobject($fields)
   $= olereleaseobject($computername)
   $= olereleaseobject($stamp)
   $= olereleaseobject($userid)
   $= olereleaseobject($pctype)
   $= olereleaseobject($hwtype)
   $= olereleaseobject($spver)
   $= olereleaseobject($adminequiv)

  endif

 endif

 $= olecallfunc($recordset,"close")
 $= olecallfunc($connection,"close")
 $= olereleaseobject($recordset)
 $= olereleaseobject($connection)

endif

exit

---

Shawn.

just for info, change the following to get MS SQL server support:

code:

---

$dsn="PROVIDER=SQLOLEDB;Server=sqlserver;Database=database;User ID=userid;pwd=kixtart;trusted_connection=no"

---

And you are rocking on a SQL server

------------------
Cheers....

David