How To Insert Into DBF File (foxpro)

Question

I have the following code in asp.net:

using (OleDbCommand command = dbConnW.CreateCommand())
{
    string CreateTableK = null;
    CreateTableK += "Create Table DSKKAR00 (DSK_ID c(10),DSK_KIND N(1),MON_PYM C(3))";
    OleDbCommand cmdCreateTable = new OleDbCommand(CreateTableK, dbConnW);
    cmdCreateTable.ExecuteNonQuery();
    System.Text.StringBuilder sb = new  System.Text.StringBuilder();
    sb.Append(WorkRoomNo + ","); 
    sb.Append("1,");
    sb.Append(",");
    OleDbCommand cmd3 = new OleDbCommand("Insert into DSKKAR00 (DSK_ID,DSK_KIND,MON_PYM) Values (" + sb.ToString() + ")", dbConnW);
    cmd3.ExecuteNonQuery();

But I have the following error:

Syntax error

Answer 1

In addition to what Chris has offered, you are starting your CREATE TABLE with a NULL string variable, then doing a += to it. From what I remember, a NULL += "anystring" will remain a null value... You might be crashing right there too.

Although VFP is not really suceptible to SQL Injection like other SQL engines, its good habit to do parameterizing. When you do, use "?" as a place-holder for the value you want to insert, and add parameters in the same order sequence as the "?" represent.

string CreateTableK = 
   "Create Table DSKKAR00 (DSK_ID c(10),DSK_KIND N(1),MON_PYM C(3))";     

OleDbCommand cmdCreateTable = new OleDbCommand(CreateTableK, dbConnW);
cmdCreateTable.ExecuteNonQuery();     

string MyInsert = 
   "insert into DSKKAR00 ( dsk_id, dsk_kind, mon_pym ) values ( ?, ?, ? )";
OleDbCommand cmd3 = new OleDbCommand( MyInsert, dbConnW);
cmd3.Parameters.AddWithValue( "parmSlot1", WorkRoomNo );
cmd3.Parameters.AddWithValue( "parmSlot2", 1);
cmd3.Parameters.AddWithValue( "parmSlot3", 'tst' ); // or whatever variable to put
cmd3.ExecuteNonQuery(); 

Answer 2

First off, any time you have an error it's usually best to post the entire error message you get.

Also, when trying to debug a query problem, you should emit the actual query being sent to your server/database and inspect it. This way you can find various problems like too many commas.

Speaking of which, looking at your code, you are concatenating a String and it really looks like you have way too many commas.

The emitted query looks like it will be:

insert into DSKKAR00(DSK_ID, DSK_KIND, MON_PYM) VALUES( X,1, ,)

where X is the value of your WorkRoomNo variable.

Obviously, that isn't valid syntax and would result in the error you've seen. The commas indicate there are 4 values being passed, but the insert query only identifies 3 columns.

The next issue has to do with the column definitions themselves. The first column of that table is a c(10); the third is a c(3). I'm a little rusty, but aren't those character fields?

If so then you need to adjust your string builder to add the appropriate quotes around the values...

Which leads us to the final problem: Don't use String concatentation to build queries. Use Parameterized queries