1. 

   michelles, Member

   I created a procedure to create a new record in tables through an inline form. Right now there's an ID field that I need to fill out in order to add the record. How can I make this value automatic instead of having to enter it manually?

   This is my code for the form (I have 2 tables - one for People and one for Email Addresses):

   DROP PROCEDURE IF EXISTS sp_DBR_master_create $$ CREATE PROCEDURE sp_DBR_master_create( inID int, inFirst varchar(255), inLast varchar(255), inEmail varchar(255) ) BEGIN

   INSERT INTO People (People.FirstName, People.LastName) VALUES (inFirst, inLast);

   INSERT INTO Email (Email.PeopleID, Email.Email) VALUES (inID, inEmail);

   select 'dbr.refresh', 0;

   END $$
2. 

   myDBR Team, Key Master

   Hi,
   you can use MySQL's AUTO_INCREMENT attribute, which takes care of the ID field automatically:

   http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

   --
   myDBR Team
3. 

   michelles, Member

   Thanks. I should've mentioned that I've tried that, but I keep getting an error that says:

   You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_INCREMENT

   Is there possibly another solution or a reason this is happening?
4. 

   myDBR Team, Key Master

   AUTO_INCREMENT is the easiest solution. Please check the code you were using for the syntax error. If you show the code, we shoud be able to help.

   --
   myDBR Team
5. 

   michelles, Member

   Here's my code:

   DROP PROCEDURE IF EXISTS sp_DBR_master_create $$ CREATE PROCEDURE sp_DBR_master_create( inID int NOT NULL AUTO_INCREMENT, inFirst varchar(255), inLast varchar(255), inEmail varchar(255) ) BEGIN

   INSERT INTO People (People.PeopleID, People.FirstName, People.LastName) VALUES (NULL, inFirst, inLast);

   SELECT * FROM People; SELECT LAST_INSERT_ID();

   INSERT INTO Email (Email.PeopleID, Email.Email) VALUES (NULL, inEmail);

   select 'dbr.refresh', 0;

   END $$

   And this is the error I get:

   You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL AUTO_INCREMENT, inFirst varchar(255), inLast var' at line 2

   Maybe it has something to do with how I'm linking this procedure in my report? Here's my code for the report:

   DROP PROCEDURE IF EXISTS sp_DBR_master $$ CREATE PROCEDURE sp_DBR_master() BEGIN

   select 'dbr.report', 'sp_DBR_master_edit', 'inline', 'inID=id', 'inFirst<=firstname', 'inLast<=lastname', 'inEmail<=email'; select 'dbr.report', 'sp_DBR_master_create', 'inline', 'ID=1'; select 'dbr.hdr', 'id';

   SELECT People.PeopleID as 'ID[id]', People.FirstName as 'First Name[firstname]', People.LastName as 'Last Name[lastname]', Email.Email as 'Email[email]' FROM People JOIN Email ON People.PeopleID = Email.PeopleID ORDER BY People.PeopleID ASC; END $$
6. 

   myDBR Team, Key Master

   The AUTO_INCREMENT is an attribute for the table column, not the procedure paramerer. When you create table People you should mark the PeopleID column with AUTO_INCREMENT-attribute.

   When you insert new row, the PeopleID is populated automatically. Take a look at the example from the MySQL documentation:

   http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

   --
   myDBR Team
7. 

   michelles, Member

   Hi,
   Sorry to be a bother, but is there a way to remove the ID field completely from the form (see above) so it just automatically gets set as the next number? No matter what number I put, it's set as a the next increment anyway.

Reply

You must log in to post.