I am writing an application that must: a.) Open an existing DBF file, then, b.) Add a new empty record (row), then c.) Add some data to one or more of the fields in the new record, d.) Close the DBF file
I already have the code working to open and read the dbf file and display contents in a datagridview. That works perfectly. But I have searched for 3 days now, several hours a day, and cannot find anywhere any guidance or example on how to simply add a single blank record.
Note: the new record should simply be a new empty record, appended to the existing file (the file is automatically created by another application - so all the columns are established and do not need to be defined by my app)
Here is the code I am using to open and read the table, then count the rows.
OleDbConnection oConn = new OleDbConnection(
"Provider=VFPOLEDB.1;SourceType=DBF;Data Source=.\\;");
oConn.Open();
System.Data.OleDb.OleDbCommand oCmd = oConn.CreateCommand();
string dbname = "SELECT * FROM C:\\rmwin\\poslink.dbf";
oCmd.CommandText = dbname;
DataTable emp = new DataTable();
emp.Load(oCmd.ExecuteReader());
oConn.Close();
dg1.DataSource = emp;
int rowcount = emp.Rows.Count;
The data displays properly in dg1 (my datagridview control) So now what I want it to simply add a new record to POSLINK.DBF. Any guidance will be greatly appreciated.
One sample showing SQL Update, but the principles are the same
To start, your OleDb connection source should point to the path where the data resides instead of just a relative path of .\ which might not always be a good choice. Then, all your queries for insert, update, delete are all operating from that folder so you don't have to be explicit of all paths. Just the table name.
OleDbConnection oConn = new OleDbConnection(
"Provider=VFPOLEDB.1;SourceType=DBF;Data Source=C:\\rmwin\\;");
Now, your command. To add, update and delete, you can do with relatively common sql statements. However, do not concatenate values you are trying to insert or update, that exposes you to SQL-Injection. Instead, the OleDb uses a "?" character as a place-holder for the parameter added to the command, and all "?" need parameters added in the same order.
So, to get a select, start as you have
System.Data.OleDb.OleDbCommand oCmd = oConn.CreateCommand();
oCmd.CommandText = "select * from PosLink";
for an insert, build the command out, identify all the fields you are trying to insert... obviously don't know the content of your table
oCmd.CommandText =
@"insert into PosLink
( TryColumn1,
TryColumn2,
TryColumn3 )
values
( ?,
?,
? ) ";
Now, add the parameters which come from wherever you have them from your screen/source
oCmd.Parameters.AddWithValue( "parmTryColumn1", yourCSharpClassOrPropertyStringField );
oCmd.Parameters.AddWithValue( "parmTryColumn2", DateTime.Now );
oCmd.Parameters.AddWithValue( "parmTryColumn3", 12345 );
Notice that the parameter naming I have prefixed with "parm" just for purposes to know its a parameter and not the actual column of the insert. They do have to be in the same order as the insert.
Then you can execute it... Since it is an INSERT, nothing is returned, so considered a non-query, but will return a count of how many records impacted... would expect 1 if all is ok, 0 or negative if a failure for anything else.
var recordCountInserted = oCmd.ExecuteNonQuery();
Hope this helps you get started and on your way.
As for doing an append blank, that would require a script. In VFP, you would do something like
use SomeTable
append blank
So, build a string for those commands (NOT ALL COMMANDS are supported within the VfpOleDb, but the most common you would expect data, date, string, common functions do work)
oCmd.CommandText =
@"execScript('use SomeTable
append blank
use' )";
oCmd.ExecuteNonQuery();
Yes, you can do VFP code like this, but not everything is allowed