Search code examples
c#oledbdbf

OleDbDataAdapter Update To dbf [Free Table] -Syntax Error()

When I insert through the OleDbCommand with direct values no problem, it's working fine

OleDbCommand OleCmd1 = new OleDbCommand("Insert into My_Diary (sl_no,reminder) values("+a1+",'CHECK VALUE')", OleCon1);
OleCmd1->ExecuteNonQuery();

But when I like to update through parameter its showing "Syntax Error"....I can't identify my mistake...

string MyConStr = "Provider=VFPOLEDB.1; Data Source='C:\\For_Dbf'; Persist Security Info=False";    
InsSavDiaryCmd = "Insert into My_Table1 (sl_no,reminder) values (@sl_no,@reminder) ";

VFPDAp=gcnew OleDbDataAdapter();
VFPDApMy_Table1InsertCommand = gcnew OleDbCommand(InsSavDiaryCmd, OleCon1);

WithInsVar = VFPDAp.InsertCommand.Parameters;
WithInsVar.Add("@sl_no", OleDbType.Integer, 10, "sl_no");
WithInsVar.Add("@reminder", OleDbType.Char, 250, "reminder");

OleCon1.ConnectionString = MyConStr;
OleCon1.Open();

OleDbTransaction Trans=OleCon1.BeginTransaction();

//VFPDAp.DeleteCommand.Transaction = Trans;
//VFPDAp.UpdateCommand.Transaction = Trans;
VFPDAp.InsertCommand.Transaction = Trans;

VFPDAp.Update(MyDataTbl);
Trans.Commit();
OleCon1.Close();
Solution

  • The OleDbCommand doesn't use named parameters. You need to change the insert statement so that it uses questions. 

    InsSavDiaryCmd = "Insert into My_Table1 (sl_no,reminder) values (?, ?) ";

    You need to make sure that you have a parameter for each question mark and make sure that the parameters are inserted in order of their use in the insert statement.

    ** If you'd like to use name parameters... you can try using VfpClient which is a project that I'm working on to make data access a little nicer from .Net.