MS Access Syntax error for insert into statement
Posted: Mon Mar 01, 2004 7:54 am
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
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;
}
}
?>