Page 1 of 1 1
Topic Options
#88110 - 2002-09-20 11:39 AM Clear Table in DB
Vig Offline
Starting to like KiXtart

Registered: 2001-11-14
Posts: 166
Loc: Saudi Arabia
I'm able to delete the first record but I cannot delete more than one at a time.

code:
Function Cleardatabase()
$RC=SetOption("WrapAtEOL","On")
$DSN="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBPath$DBFile"
$Connection = CreateObject("ADODB.Connection")
$Command = CreateObject("ADODB.Command")
$Recordset = CreateObject("ADODB.Recordset")

$Connection If @error = 0
? "Connected"
$Connection.ConnectionString = $DSN
$Connection.Open()
$Command.ActiveConnection = $Connection
$Recordset.CursorType = 3
$Recordset.LockType = 3
$Recordset.ActiveCommand = $Command
$DELETE_ALL = "SELECT * FROM TBL_PROGRAM WHERE COMPUTER <> '*';"

$Command.CommandText = $DELETE_ALL
$Recordset.Open($Command)
If $Recordset.RecordCount > 1
For $count = 1 to $Recordset.RecordCount
$Recordset.Delete
Next
EndIf
$Recordset.Update
$Recordset.Close()
?"Completed Successfully"
$Connection.Close()
$Connection = 0
$Recordset = 0
$Command = 0
EndFunction

Thanks

Top
#88111 - 2002-09-20 01:10 PM Re: Clear Table in DB
Breaker Offline
Hey THIS is FUN
*****

Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
Why not try changing your SQL?

code:
$DELETE_SQL = "delete from table where criteria='delete this one';"
$x = $Connection.Execute($DELETE_SQL)

I use the SQL DELETE statement several times in the code for an Access application I developed earlier this year and it works well. I've not used the $Recordset.Delete method at all, and I'd feel happier just using Execute to run a predefined SQL statement when working with databases, as occasionally ADO's data manipulation functions seem a little flaky - it's also easier to debug and test SQL with test data without running the Kix at all.

Also, in the above code, your For...Next construct is doing nothing at all - you never initialise or increment the $count variable. The $recordset object contains all the records returned by your SQL, so just calling $Recordset.Delete ought to do the trick. I'd recommend using a SQL DELETE statement instead, though.

[ 20. September 2002, 13:20: Message edited by: Breaker ]
_________________________
================================================
Breaker


Top
#88112 - 2002-09-20 02:19 PM Re: Clear Table in DB
Vig Offline
Starting to like KiXtart

Registered: 2001-11-14
Posts: 166
Loc: Saudi Arabia
Thanks Breaker,

You suggestion worked great.

As you may have noticed I'm using a slightly modified version a script you posted a while back. Me and COM don't get along, I feel like I'm trying to shove the square block in the round hole. Takes alot of pounding but I'll get it in there.

Thanks for the help.

Top
#88113 - 2002-09-20 03:43 PM Re: Clear Table in DB
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11162
Loc: Boston, MA, USA
Why not use the DB...() UDFs in the UDF Forum. They are pre-packaged UDFs for database interaction via SQL. Your script would look like this with those UDFS:
code:
$rc=DBConnOpen(...)  ;insert correct parameters to open database
$rc=DBExecute($sqlcommand) ; execute SQL command
$rc=DBConnClose(...) ;close database

_________________________
There are two types of vessels, submarines and targets.

Top
#88114 - 2002-09-20 04:03 PM Re: Clear Table in DB
Vig Offline
Starting to like KiXtart

Registered: 2001-11-14
Posts: 166
Loc: Saudi Arabia
I would use those, but I preffer to actually learn how to do something before I take the easier route.

Thanks for the suggestion, those will come in handy later.

[ 20. September 2002, 16:05: Message edited by: Vig ]

Top
Page 1 of 1 1


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 182 anonymous users online.
Newest Members
vertage, dimi575, Drecksnacken, Nlill, webtools321
17735 Registered Users

Generated in 0.049 seconds in which 0.032 seconds were spent on a total of 12 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org