KiXforms Forum Index KiXforms
The Forum for the KiXforms Community
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 Quick Links 
Site News
Downloads
Documentation
Donations
Script Archive
Tracking Systems

insert data into a Access database

 
Post new topic   Reply to topic    KiXforms Forum Index -> Discussion
View previous topic :: View next topic  
Author Message
prinand
KiXforms Novice
KiXforms Novice


Joined: 15 Sep 2003
Posts: 3
Location: netherlands

PostPosted: Mon Sep 15, 2003 11:11 am    Post subject: insert data into a Access database Reply with quote

I have found several script which do work, but now I want to combine 2 script and in a "proper" way

below is an the working script

$CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DBpath;persist security info=false"
$CMDtxt = "select * from OVERVIEW where servername = '$svrname' "
$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.RecordSet")

$cn.connectionstring = $CNstring
$cn.Open
$cmd.activeconnection = $cn
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd
$cmd.commandtext = $CMDtxt $rs.Open ($cmd)

$rs.fields.item("last_result").value = $result
$rs.fields.item("alertno").value = $alertno
$rs.fields.item("date_time").value = $date_time
$rs.update

Above is working fine


Now I want to add the script part below (creates another table in the
same database and every time insert a new line) :

$accDB = CreateObject("Access.Application")
$cn = CreateObject("ADODB.Connection")
$cn.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ="+ $DBPath)
$ah = DecToHex($alertno)

$sSQL = "CREATE TABLE $svrname (id COUNTER PRIMARY KEY,Servername TEXT(255), Alertno TEXT(255),Result TEXT(255),Date_time TEXT(255))"
$table= $cn.Execute($sSQL)
$sSQL = "INSERT INTO $svrname (ServerName, Alertno, Result, Date_time) VALUES ('$svrname', '$ah', '$result', '$date_time')"
$table = $cn.Execute($sSQL)

$accDB.Quit

I would like to insert only the bottom lines to insert the line into a new table, but that does not work together with the selections made in the top script, because that make some selection needed to update some fields in a separate database. How can I make the above working ? the only way I can make it to work now is running the top script and "close" the access instance and then the bottom script, but that seems reaching the goal the hard way, and requires more time to execute...
Confused
Back to top
View user's profile Send private message
prinand
KiXforms Novice
KiXforms Novice


Joined: 15 Sep 2003
Posts: 3
Location: netherlands

PostPosted: Mon Sep 15, 2003 1:17 pm    Post subject: Reply with quote

Embarassed just tried it again, and I may have tried this before while there was en error somewhere because now it works......

;*********** open database and select column for that server ***********
$CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DBpath;persist security info=false"
$CMDtxt = "select * from OVERVIEW where servername = '$svrname'"
$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.RecordSet")

$cn.Open ($CNstring)
$cmd.activeconnection = $cn
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd

$cmd.commandtext = $CMDtxt $rs.Open ($cmd)


If $rs.eof = -1 ; addnew is only needed if a record for this workstation was not found.
$rs.addnew
EndIf

:Nextalert

;*********** read last alert
$alertno = $rs.fields.item("alertno").value
$alertpath = build_path("$alertno","$svrname")
If Exist("$alertpath") = 0 AND $alertno > 0 ; if it does not exist anymore, assume the log was cleared
? "eventlog was cleared"
$rs.fields.item("alertno").value = 0
$rs.fields.item("last_result").value = ""
$rs.update
GoTo "dbclose"
EndIf

$alertno = $alertno + 1 ; increase by 1 (check if there is a new alert since the last scan)
$alertpath = build_path("$alertno","$svrname")
If Exist("$alertpath") = 0
GoTo "DBclose"
EndIf

$rs.fields.item("alertno").value = "$alertno"

$result = Read_file("$alertpath")
$succes = determine_succes("$result")
If $succes = 5 ; if true, the it was no backup alert
GoTo "Nextalert"
EndIf
If $succes = 0 ; increase the succes/failed counter
$rs.fields.item("succes_backups").value = $rs.fields.item("succes_backups").value + 1
$rs.fields.item("succes_boolean").value = 1
Else
$rs.fields.item("failed_backups").value = $rs.fields.item("failed_backups").value + 1
$rs.fields.item("succes_boolean").value = 0
EndIf

$date_time = GetFileTime("$alertpath")

$x = InStr("$result", "backup") ; strip leading rubbish from result string
$y = Len("$result")
$result = Right("$result", $y-$x+1)

;**************** record results in database ****************
$rs.fields.item("last_result").value = $result
$rs.fields.item("alertno").value = $alertno
$rs.fields.item("date_time").value = $date_time
$rs.update


$ah = DecToHex($alertno)
$sSQL = "CREATE TABLE $svrname (id COUNTER PRIMARY KEY,Servername TEXT(255), Alertno TEXT(255),Result TEXT(255),Date_time TEXT(255))"
$table= $cn.Execute($sSQL)
$sSQL = "INSERT INTO $svrname (ServerName, Alertno, Result, Date_time) VALUES ('$svrname', '$ah', '$result', '$date_time')"
$table = $cn.Execute($sSQL)


? "Alert $alertno processed "

goto "nextalert"

:dbclose
$rs.Close
$cn.Quit

GoTo "End"
Back to top
View user's profile Send private message
Chris S.
KiXforms Enthusiast
KiXforms Enthusiast


Joined: 05 Mar 2003
Posts: 241

PostPosted: Mon Sep 15, 2003 4:03 pm    Post subject: Reply with quote

This forum is for KiXforms your post should be directed to the COM forum at http://www.kixtart.org/cgi-bin/ultimatebb.cgi?category=1 , affectionately called the mothership around these parts.
Back to top
View user's profile Send private message MSN Messenger
Sealeopard
KiXforms Aficionado
KiXforms Aficionado


Joined: 05 Mar 2003
Posts: 436
Location: Boston, MA

PostPosted: Mon Sep 15, 2003 9:24 pm    Post subject: Reply with quote

Additionally, you might want to use DBCommand ( http://www.kixtart.org/board/ultimatebb.php?ubb=get_topic;f=12;t=000328 ) and it's related UDFs.
_________________
Jens

'There are two kinds of vessels, submarines and targets.'
Back to top
View user's profile Send private message Send e-mail MSN Messenger
prinand
KiXforms Novice
KiXforms Novice


Joined: 15 Sep 2003
Posts: 3
Location: netherlands

PostPosted: Tue Sep 16, 2003 6:26 pm    Post subject: Reply with quote

ah sorry. did not see this forum before. thanks for the links. they could be very handy as I am still trying to get more grip on COM scripting.

Any idea, are there good books which could help me ?
Back to top
View user's profile Send private message
Sealeopard
KiXforms Aficionado
KiXforms Aficionado


Joined: 05 Mar 2003
Posts: 436
Location: Boston, MA

PostPosted: Tue Sep 16, 2003 6:29 pm    Post subject: Reply with quote

KiXtart Manual
KiXForms CHM Help
The Start to Finish Guide to Scripting With KiXtart at http://www.shareit.com/product.html?productid=186560&languageid=1&affiliateid=70594

_________________
Jens

'There are two kinds of vessels, submarines and targets.'
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    KiXforms Forum Index -> Discussion All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


Powered by phpBB © 2001, 2005 phpBB Group