Why can't I save a database transaction using SubSonic?

Question

I have a table called jobs and I can get data out of the table with no problems but saving is causing issues. Here is the code and the error:

        Job job = new Job(JobId);

        job.Name = txtName.Text;
        job.SimsCustCode = txtSimsCustCode.Text;
        job.Mode = cboMode.Text;
        job.Interval = Convert.ToInt32(nudInterval.Text);
        job.Enabled = Convert.ToBoolean(chkEnabled.Checked);

        job.SourceHost = txtSourceHostName.Text;
        job.SourceType = cboSourceType.Text;
        job.SourceUsername = txtSourceUsername.Text;
        job.SourcePassword = txtSourcePassword.Text;
        job.SourceDirectory = txtSourceDirectory.Text;
        job.SourceIgnoreExtension = txtSourceIgnoreExtension.Text;

        job.TargetHost = txtTargetHostName.Text;
        job.TargetType = cboTargetType.Text;
        job.TargetUsername = txtTargetUsername.Text;
        job.TargetPassword = txtTargetPassword.Text;
        job.TargetDirectory = txtTargetDirectory.Text;
        job.TargetTempExtension = txtTargetTempExtension.Text;

        job.Save();

Here is the error:

A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
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 'interval) VALUES('adf','adsf','inbound','ftp','','','','','','ftp','','','','','' at line 1

To clarify, if I edit an existing job it works fine, it's only saving new jobs that fails.

Here is the schema:

Table Create Table

---

jobs CREATE TABLE jobs (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL,
sims_cust_code varchar(10) NOT NULL,
mode varchar(10) NOT NULL,
source_type varchar(10) NOT NULL,
source_host varchar(100) default NULL,
source_username varchar(50) default NULL,
source_password varchar(50) default NULL,
source_directory varchar(100) default NULL,
source_ignore_extension varchar(10) default NULL,
target_type varchar(10) NOT NULL,
target_host varchar(100) default NULL,
target_username varchar(50) default NULL,
target_password varchar(50) default NULL,
target_directory varchar(100) default NULL,
target_temp_extension varchar(10) default NULL,
enabled tinyint(1) NOT NULL,
interval int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

Answer 1

It looks like a value isn't being parsed right - I need to see the SQL schema and also the SQL that's being generated - do you have a profiler up? Something that allows you to watch what's happening?

Interval is a MySQL reserved word :) - can you enter a bug on this? We should catch that...