Forums

Database error problem with block add.

Permalink
I have been working on blocks successfully since the C5 classes I took 4 months go but I haven't used an add or edit file with a block table until now. I reviewed my class videos again before proceeding. I started out by creating the db.xml and add.php files, I set up the controller, and then installed the block and it created the table successfully. I, then for the first time, tried to use this block in a page. When I filled in my data fields on the add window that were to be inserted into the form, I got this database error;
An unexpected error occurred. mysqlt error: [1064: 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 '' at line 1] in EXECUTE("UPDATE btMessageBar SET rcID=167,Color=#fcc,Message='This is a message for the Reg_Game_Host Page' WHERE bID=370")

Why is Concrete5 trying to UPDATE bID=370 instead of doing an INSERT to bID=1 to an empty table?
db.xml;
<?xml version="1.0"?>
<schema version="0.3">
    <table name="btMessageBar">
        <field name="bID" type="I">
            <key />
            <unsigned />
        </field>
        <field name="rcID" type="I"><unsigned /></field>
        <field name="Color" type="I"><unsigned /></field>
        <field name="Message" type="X2"></field>
    </table>
</schema>
Controller;
<?php defined('C5_EXECUTE') or die("Access Denied.");
Viewing 15 lines of 39 lines. View entire code block.
ThomasJ
 
ThomasJ replied on at Permalink Best Answer Reply
ThomasJ
I figured out what is going on. First of all, the error message is very misleading. Concrete5 and ADOdb may have tried to update a record that didn't exist instead of inserting but the error is originally caused by c5 attempting to convert the string "#fcc" from the <input> field to an integer which the table field expects. I changed the "#fcc" to "&#fcc" and I guess that c5 was then successful in converting it into an Integer because that error went away. But then, something really strange happened. When I pressed add, the whole block object was printed out in an error window instead of displaying the view.

At this point, I have decided that it is best to just store all data as text and convert it myself as needed to avoid any of these database mishaps.