Sunday, 23 June 2002

Back to SQL as usual

Heh; I finally seem to have the database backend working.  Previously I was attempting to bend a DataSet to do what I wanted, but it wasn't too co-operative, so now I'm doing it the 'traditional' way, building up my own SQL 'INSERT' and 'UPDATE' statements.

Note to self: OLEDB seems very picky about types.  When you give it a parameter, you need to specify its type in great detail, otherwise it will die with an OleDbException saying "Data type mismatch in criteria expression".

In Perl:

$dbh->do( "INSERT INTO myTable (row1, row2, row3) VALUES (?, ?, ?)", undef, $a, $b, $c );

In C#:

OleDbParameter MakeParameter( string name, object val, OleDbType typ )
{
 OleDbParameter par = new OleDbParameter( name, typ );
 par.Value = val;
 return par;
}

OleDbCommand cmd = new OleDbCommand( "INSERT INTO myTable (col1, col2, col3) VALUES( @a, @b, @c )", dbh );
cmd.Parameters.Add( MakeParameter( "@a", a, OleDbType.VarChar ) );
cmd.Parameters.Add( MakeParameter( "@b", b, OleDbType.VarChar ) );
cmd.Parameters.Add( MakeParameter( "@c", c, OleDbType.VarChar ) );
cmd.ExecuteNonQuery();

There's a lesson to be learned in there somewhere ;-)

posted by Phillip Pearson at 23/06/2002 12:01:36 a.m.