Converting from Access to MySQL: Insert Error

[teedoff]

ok I had an insert query that worked fine when using Access as my datasource, but I wanted to start using MySQL, so now when I run the insert statement, I get an error that says Filed "bulbID" doesn't have a default value. This error didnt occur with Access, so not sure what changed or is different now in MySQL. Here is my INSERT INTO query:

<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<cfDump var="#FORM#">
  <cfquery datasource="rlbulbs">   
    INSERT INTO rlbbulbs (item, brandID, bulbDesc, cost, price, qtyPerPrice, qtyInStock, wattage, voltage, acDC, base, glass, filament, avgLife, beamAngle, oldPage)
VALUES (
	'#FORM.item#', 
	'#FORM.brandID#', 
	'#FORM.description#', 
    #FORM.cost#, 
	#FORM.price#, 
	#FORM.qtyPerPrice#, 
	#FORM.qtyInStock#, 
	'#FORM.wattage#', 
	'#FORM.voltage#', 
	'#FORM.acDC#', 
	'#FORM.base#', 
	'#FORM.glass#', 
	'#FORM.filament#', 
	'#FORM.avgLife#', 
	'#FORM.beamAngle#', 
	'#FORM.oldPage#'
)
  </cfquery>
  <cflocation url="bulbView.cfm" addtoken="no">
</cfif>
<cfquery name="rsBrand" datasource="rlbulbs">
SELECT *
FROM rlbbrand
ORDER BY brandName ASC 
</cfquery>

If anyone needs me to post my MySQL table structure, I will do so. Thanks in advance for any help!

[rch1231]

Do you have the field "bulbID" set as not null in your table definition?

[teedoff]

Do you have the field "bulbID" set as not null in your table definition?

Hi rch! thanks for the reply. I think I had bulbID set as primary key, but as you said...it was also set as not NULL. I checked the autoincrement button and that seems to work. I do have a question though about data types in MySQL. Most of the data types that are simple strings are set as varChar. Which I think has some limits on size... i think? One field in particular, bulbDesc, can sometimes be long, and when I run my search query, it seems just a tad slow. I have 1500 bulbs in the table so far..and I think that will triple or more in size before its done. I know I read something about some data types being slower and some faster. What would you recommend as data types to speed up my web page when users are searching?

[rch1231]

Hello,

To make the searches faster index the field. If possible use a unique index provided none of your descriptions are exactly the same. As far as limits on the size it depends on the version of MySQL.

From my reference manual.

CHAR(n)    character string with specified length, maximum 255 characters
VARCHAR(n) character string with variable length, maximum 255 characters (MySQL
           through 4.1: n<256; MySQL from 5.0.3: n<65,535)
TINYTEXT   character string with variable length, maximum 255 bytes 
TEXT           character string with variable length, maximum (2 to the 16 power)-1 characters
MEDIUMTEXT     character string with variable length, maximum (2 to the 24 power)-1 characters
LONGTEXT           character string with variable length, maximum (2 to the 32 power)-1 characters

New Aspects of VARCHAR: In MySQL 5.0 there are two significant innovations for the data type VARCHAR.
• The maximal column size for tables is now 65,535 bytes (it was previously
255 bytes). The maximum number of characters depends on the character set, since
many character sets require more than 1 byte per character.
• Spaces at the beginning and end of VARCHAR values are now stored in the table. Thus
INSERT INTO table (varcharcolumn) VALUES (‘ abc ’) actually stores ‘ abc ’ in the col-
umn, that is, a space, the characters a, b, and c, and finally another space character.
(Previously, MySQL deleted spaces at the end of VARCHAR values, which was in viola-
tion of the ANSI standard.)

[teedoff]

Hello,

To make the searches faster index the field. If possible use a unique index provided none of your descriptions are exactly the same. As far as limits on the size it depends on the version of MySQL.

From my reference manual.

CHAR(n)    character string with specified length, maximum 255 characters
VARCHAR(n) character string with variable length, maximum 255 characters (MySQL
           through 4.1: n<256; MySQL from 5.0.3: n<65,535)
TINYTEXT   character string with variable length, maximum 255 bytes 
TEXT           character string with variable length, maximum (2 to the 16 power)-1 characters
MEDIUMTEXT     character string with variable length, maximum (2 to the 24 power)-1 characters
LONGTEXT           character string with variable length, maximum (2 to the 32 power)-1 characters

New Aspects of VARCHAR: In MySQL 5.0 there are two significant innovations for the data type VARCHAR.
• The maximal column size for tables is now 65,535 bytes (it was previously
255 bytes). The maximum number of characters depends on the character set, since
many character sets require more than 1 byte per character.
• Spaces at the beginning and end of VARCHAR values are now stored in the table. Thus
INSERT INTO table (varcharcolumn) VALUES (‘ abc ’) actually stores ‘ abc ’ in the col-
umn, that is, a space, the characters a, b, and c, and finally another space character.
(Previously, MySQL deleted spaces at the end of VARCHAR values, which was in viola-
tion of the ANSI standard.)

Thanks again rch. I did set an index on item. Really the only time it seems a little slow is when all the search fields are left blank, in which case every record is displayed in the query. Guess I need to set up some validations or required fields to not allow no entries. I do hope I did the index right. I named it item and then the filed name was item and set it to a normal index. Thanks again!

[rch1231]

Any time. Don't forget to mark the thread solved.