Can't get record to insert into Access

[Drew Burlingame via .NET 247]

I have a windows service that will insert records into an Accessdatabase. Or at least that's the idea. The insert isn'thappening, but the code runs without raising any errors. When Irun the generated sql statement from a query window in Access,the record inserts just fine. Can anyone help me?

string sqlInsert = "INSERT INTO checks( PageNo, Template,OriginFile, TargetFile, MICR, Account, Routing, CheckNumber,CheckDate, CheckAmount, ScanBatchNum ) VALUES( '{0}', '{1}','{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}')";
StringBuilder sb = new StringBuilder();
using( OleDbConnection cn = new OleDbConnection( m_ConnString ))
{
cn.Open();
using( OleDbCommand cmd = new OleDbCommand( "", cn ) )
{
foreach( ChecksDS.ChecksRow row in checkRecords )
{
sb.AppendFormat( sqlInsert, row.PageNo, row.Template,row.OriginFile, row.TargetFile, row.MICR, row.Account,row.Routing, row.CheckNum, row.CheckDate, row.CheckAmount,row.ScanBatchNum );
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
sb.Remove( 0, sb.Length );
}
}
cn.Close();
}

Here's a generated sql statement.

INSERT INTO checks( PageNo, Template, OriginFile, TargetFile,MICR, Account, Routing, CheckNumber, CheckDate, CheckAmount,ScanBatchNum ) VALUES( '3', 'Check', 'C:\ProgramFiles\FormStorm\urm\Queues\0120040901160314.tif', 'C:\ProgramFiles\FormStorm\URM\ImageExp\090920049091443253.tif','A021204005A 0001538C 0404', '021204005', '0001538', '0404','', '20.00', '09092004909144325' );

I've tried with and without the ending semi-colon. What am Imissing?

Thanks

 

[Mary Chipman]

First of all, put your ExecuteNonQuery in a try/Catch block and write
out the exception.message so you can see why Access doesn't like the
insert. If it's the query syntax, open a copy of Access and copy the
INSERT INTO statement into the query designer SQL pane and try to
execute it. That's the best way to catch SQL syntax errors.

--Mary

 

[Ron Allen]

Drew,
Since your insert command isn't a literal string all the items after the
backslashes are control codes. You should either double the the backslashes
in the values being inserted or do something to the generated string later.
I'd suggest setting the command up with parameters and then assigning the
values to the parameters. i.e.
cmd = new OleDbCommand("INSERT INTO checks( PageNo, Template, OriginFile,
TargetFile, MICR, Account, Routing, CheckNumber, CheckDate, CheckAmount,
ScanBatchNum ) VALUES( '?', '?', '?', '?', '?', '?', '?', '?', '?', '?',
'?}' )", cn);
cmd.Parameters.Add("@pn", OleDbType.?????); and for the rest as well
putting in the correct OleDbTypes for the parameters. Remember that
parameters are position based so add them in the correct order. Then just
assign the parameter values during the loop and execute the command. This
will also save building a lot of strings

cmd.Paramters["@pn"].Value = row.PageNo;
....

Ron Allen

I have a windows service that will insert records into an Access database.
Or at least that's the idea. The insert isn't happening, but the code runs
without raising any errors. When I run the generated sql statement from a
query window in Access, the record inserts just fine. Can anyone help me?

string sqlInsert = "INSERT INTO checks( PageNo, Template, OriginFile,
TargetFile, MICR, Account, Routing, CheckNumber, CheckDate, CheckAmount,
ScanBatchNum ) VALUES( '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}',
'{7}', '{8}', '{9}', '{10}' )";
StringBuilder sb = new StringBuilder();
using( OleDbConnection cn = new OleDbConnection( m_ConnString ) )
{
cn.Open();
using( OleDbCommand cmd = new OleDbCommand( "", cn ) )
{
foreach( ChecksDS.ChecksRow row in checkRecords )
{
sb.AppendFormat( sqlInsert, row.PageNo, row.Template, row.OriginFile,
row.TargetFile, row.MICR, row.Account, row.Routing, row.CheckNum,
row.CheckDate, row.CheckAmount, row.ScanBatchNum );
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
sb.Remove( 0, sb.Length );
}
}
cn.Close();
}

Here's a generated sql statement.

INSERT INTO checks( PageNo, Template, OriginFile, TargetFile, MICR, Account,
Routing, CheckNumber, CheckDate, CheckAmount, ScanBatchNum ) VALUES( '3',
'Check', 'C:\Program Files\FormStorm\urm\Queues\0120040901160314.tif',
'C:\Program Files\FormStorm\URM\ImageExp\090920049091443253.tif',
'A021204005A 0001538C 0404', '021204005', '0001538', '0404', '', '20.00',
'09092004909144325' );

I've tried with and without the ending semi-colon. What am I missing?

Thanks