Hi Lance, I'd recommend that you separate the CGI stuff from the DBI stuff so that you can debug them separately. Make a get_data_from_CGI() method and get_data_from_constants() method that both return a list of values, then feed that list to a do_db_stuff_with_values() mehtod. Make sure it works with the constants ( e.g. $ID = '999' ), then you'll know your DBI stuff is working and you can then check it from CGI input.

My strong suspicion is that you're not getting a $ID value from the form and that you are essentially trying to insert '' into the database which (because of a bug) produces the odd error message you got.

The other thing that is tripping you up is the quoting in SQL. A SQL insert looks something like this: INSERT INTO foo (first_col,second_col) VALUES ('string',7). Notice that string has single quotes around it and the number does not. It works the same way with variables - INSERT INTO foo (first_col,second_col) VALUES ( '$string', $number ). Your INSERT statement has no quotes around anything. The best solution is to never have to care about the quotes: USE PLACEHOLDERS:

    my $sth = $dbh->prepare("
        INSERT INTO foo (first_col,second_col) VALUES (?,?)
    ");
    $sth->execute( $string, $number );
[download]

No quotes, no muss, no fuss. See the DBI docs on placeholders.

Another good way to catch problems is to always (when debugging) print out the SQL before executing it:

    my $DEBUG = 1; # comment out when not debugging
    my $sql = "INSERT INTO foo (first_col,second_col) VALUES (?,?)";
    my @params = ($string,$number);
    print "$sql\n[@params]\n" if $DEBUG;
    my $sth = $dbh->prepare( $sql );
    $sth->execute( @params );
[download]

DBI::trace() will also show you that same information, eventually you might want to check it out (see the DBI docs).

Hopefully all that is enough for you to be able to debug your script. If not, holler.

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.