(Starting to like KiXtart)

2002-09-20 11:39 AM

Clear Table in DB

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

Breaker

(Hey THIS is FUN)

2002-09-20 01:10 PM

Re: Clear Table in DB

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 ]

Vig

(Starting to like KiXtart)

2002-09-20 02:19 PM

Re: Clear Table in DB

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.

Sealeopard

(KiX Master)

2002-09-20 03:43 PM

Re: Clear Table in DB

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

Vig

(Starting to like KiXtart)

2002-09-20 04:03 PM

Re: Clear Table in DB

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 ]