Insert Record into Access
-
I’m trying to insert a new record into an MS Access (2002-2003) database using Perl. My instructor's example has me getting the last record ID and using that to generate the next ID. Two things: 1- When I do this the way the instructor illustrates (based on existing ID) using this code:
if($db->Sql("SELECT MAX(ID) lastID FROM Test")) { print "SQL Error: " . $db->Error() . "\n"; $db->Close(); exit; }Can anybody tell me why I'm getting this error: SQL Error: [-3100] [1] [0] "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'MAX(ID) lastID'." 2- If my ID field is set to “AutoNumber” shouldn’t I be able to simply insert and new record (with no ID)? I’ve attempted that with no success. -
I’m trying to insert a new record into an MS Access (2002-2003) database using Perl. My instructor's example has me getting the last record ID and using that to generate the next ID. Two things: 1- When I do this the way the instructor illustrates (based on existing ID) using this code:
if($db->Sql("SELECT MAX(ID) lastID FROM Test")) { print "SQL Error: " . $db->Error() . "\n"; $db->Close(); exit; }Can anybody tell me why I'm getting this error: SQL Error: [-3100] [1] [0] "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'MAX(ID) lastID'." 2- If my ID field is set to “AutoNumber” shouldn’t I be able to simply insert and new record (with no ID)? I’ve attempted that with no success.hi, 1. I think you need an as:
SELECT MAX(ID) AS lastID FROM Test
2. I should think so. And having two separate DB operations to first fetch then use MAX(ID) seems like a recipe for failure as soon as other users are also operating the DB. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
I’m trying to insert a new record into an MS Access (2002-2003) database using Perl. My instructor's example has me getting the last record ID and using that to generate the next ID. Two things: 1- When I do this the way the instructor illustrates (based on existing ID) using this code:
if($db->Sql("SELECT MAX(ID) lastID FROM Test")) { print "SQL Error: " . $db->Error() . "\n"; $db->Close(); exit; }Can anybody tell me why I'm getting this error: SQL Error: [-3100] [1] [0] "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'MAX(ID) lastID'." 2- If my ID field is set to “AutoNumber” shouldn’t I be able to simply insert and new record (with no ID)? I’ve attempted that with no success.Hi For the first question: You could try adding the keyword AS for the column:
if($db->Sql("SELECT MAX(ID) AS lastID FROM Test"))
And for the second question. Yes if the field is autonumbered, you don't have to mention it in an INSERT statement just like you don't have to mention any field that has a default value. However, if you're later going to add something for this row to a child table of this table you need to know the value for the foreign key in the child table.
The need to optimize rises from a bad design.My articles[^]
-
hi, 1. I think you need an as:
SELECT MAX(ID) AS lastID FROM Test
2. I should think so. And having two separate DB operations to first fetch then use MAX(ID) seems like a recipe for failure as soon as other users are also operating the DB. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
Thanks Luc. Aliasing did the trick.
-
hi, 1. I think you need an as:
SELECT MAX(ID) AS lastID FROM Test
2. I should think so. And having two separate DB operations to first fetch then use MAX(ID) seems like a recipe for failure as soon as other users are also operating the DB. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Hi For the first question: You could try adding the keyword AS for the column:
if($db->Sql("SELECT MAX(ID) AS lastID FROM Test"))
And for the second question. Yes if the field is autonumbered, you don't have to mention it in an INSERT statement just like you don't have to mention any field that has a default value. However, if you're later going to add something for this row to a child table of this table you need to know the value for the foreign key in the child table.
The need to optimize rises from a bad design.My articles[^]
Thanks Mika. As both you and Luc suggested, using AS worked by referencing the existing ID's. I'd much rather not do it that way but when I try to insert a record with no ID my table does not update. I'll try to figure that out latter since I can get by with Aliasing for now. ;)