Page 1 of 1

MS Access Syntax error for insert into statement

Posted: Mon Mar 01, 2004 7:54 am
by bianster
Hi All,
Would someone be so kind to assist with a problem Im having?

I have some code that inserts rows into either an empty table or carries on from an existing row record. The problem I'm having right now is that I keep getting the SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement., SQL state 37000 in SQLExecDirect when the script is run.

I did an echo for the SQL string, it checks out alright and the values are what they are supposed to be
=> insert into booking(bookingID, date, slot, bookedby, vehno, available) values(3, '3/1/2004', 3, 'none', 'none', 1)

bookingID(LONGINT)
date(STRING)
slot(INT)
bookedby(STRING)
vehno(STRING)
available(INT)

here's my code

Code: Select all

<?php
include '.\includedScripts\databaseFunctions.inc.php';
define("DAY", 60*60*24);
//MS Access date format: mm/dd/yyyy
//process booking request
if($_GET['action'] == "book"){
}

//display booking calendar
$currentDateArray = getdate(time());
//retrieve the latest date in the booking table
$query = "select bookingID, date from booking order by bookingID desc";
$rs = executeQuery($query);
//record found in database
if($rs){
	if(getResultRow($rs)){
		//$lastDate is currently MS Access date format: mm/dd/yyyy
		$lastBookingID = getRowData($rs, 1);
		$latestDateString = getRowData($rs, 2);
		$latestDate  = explode("/", $latestDateString);
		$latestDateTimestamp = mktime(0,0,0,(int) $latestDate['0'],(int) $latestDate['1'],(int)$latestDate['2']);
	}
	else{
		//empty table, initialise date string to today and bookingID to 1
		$lastBookingID = 0;
		$latestDateTimestamp = mktime(0,0,0,(int)$currentDateArray['mon'],(int)$currentDateArray['mday'],(int)$currentDateArray['year']);
	}
	freeResult($rs);
	//$latestSlot = getRowData($query, 2);
	//checks whether latest slot date in booking is at least 21 days ahead
	while(bcdiv($latestDateTimestamp - time(), 86400, 0) < 21){
		$latestDateArray = getdate($latestDateTimestamp);
		$accessDateArray = array($latestDateArray['mon'], $latestDateArray['mday'] , $latestDateArray['year']);
		$date = implode("/", $accessDateArray);
		//insert 4 slots for each day
		for($i=1;$i<5;$i++){
			//increment the bookingID to next record
			$lastBookingID += 1;
			$query = "insert into booking(bookingID, date, slot, bookedby, vehno, available) values($lastBookingID, '$date', $i, 'none', 'none', 1)";
			if($rs = executeQuery($query)){
				echo "Slot $lastBookingID inserted into table<br>";
				freeResult($rs);
			}
			//unable to insert record
			else{
				echo "unable to execute query => $query<br>";
			}
		}
		$latestDateTimestamp += DAY;
	}
}	



?>