Paul,
I am hard at work (thank you very much) and I have a quick quesiton.
What System.Data.OleDb.OleDbType is appropriate for a fpw2.6 free table "MEMO" field? And... please point me in the direction of where I can find all of the System.Data.OleDb.OleDbType eqivalents for the fpw and vfp table fields.
Thank you once again for this work,
Neil
>Since this has come up a few times, I figured I'd spend a little bit of time last night/this morning to figure out what a sample subclass of mmDataAccessOleDb might look like. In English, you'll need to do this when you're trying to Select/Insert/Delete/Update from Fox tables inside of MM.NET.
>
>I copied a 2.6 table from one of our older systems and dropped it into a C:\TestMM26 directory. It's named Dealers.DBF. I actually removed a bunch of the columns to make this a little simpler - I wasn't interested in filling in 150 columns <g>. The primary key field is named "dlr_pk" and it's an integer.
>
>In the app.config file, I specified the "database"
>
>
><databases>
> <add key="MyData\Connection" value="Provider=vfpoledb.1;Data Source=C:\TestMM26\" />
></databases>
>
>
>I did this with a WinForm app, but this should work just fine from an ASP.NET app. Just realize you'll use the web.config file instead.
>
>I created a business object and in the constructor method I added this code:
>
>
>this.TableName = "Dealers";
>this.PrimaryKey = "dlr_pk";
>this.DatabaseKey = "MyData";
>
>
>I had to override the CreateDataAccessObject() method in the business object to return my new subclass of the mmDataAccessOleDb class.
>
>
>protected override mmDataAccessBase CreateDataAccessObject(string dataAccessClassName)
>{
> DealersDataAccessOleDb da = new DealersDataAccessOleDb();
> return da;
>}
>>
>Finally, I created a new class based on mmDataAccessOleDb named "DealersDataAccessOleDb.cs"
>
>
>using System;
>using System.Data;
>
>using OakLeaf.MM.Main.Data;
>
>namespace Kirtland.SampleApp.Business
>{
> /// <summary>
> /// Summary description for DealersDataAccessOleDb.
> /// </summary>
> public class DealersDataAccessOleDb : mmDataAccessOleDb
> {
> private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
> private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
> private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
> private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
> private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
>
> private void InitializeComponent()
> {
> this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
> this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
> this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
> this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
> this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
>
> //
> // oleDbDataAdapter1
> //
> this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
> this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
> this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
> this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
> this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
> new System.Data.Common.DataTableMapping("Table", "Dealers", new System.Data.Common.DataColumnMapping[] {
>new System.Data.Common.DataColumnMapping("dlr_pk", "dlr_pk"),
>new System.Data.Common.DataColumnMapping("company", "company"),
>new System.Data.Common.DataColumnMapping("dealer", "dealer"),
>new System.Data.Common.DataColumnMapping("dlr_type", "dlr_type"),
>new System.Data.Common.DataColumnMapping("legalname", "legalname"),
>new System.Data.Common.DataColumnMapping("addr1", "addr1"),
>new System.Data.Common.DataColumnMapping("addr2", "addr2"),
>new System.Data.Common.DataColumnMapping("city", "city"),
>new System.Data.Common.DataColumnMapping("st", "st"),
>new System.Data.Common.DataColumnMapping("zip", "zip")})});
>
> //
> // oleDbSelectCommand1
> //
> this.oleDbSelectCommand1.CommandText = "SELECT dlr_pk, company, dealer, dlr_type, legalname, addr1, addr2, city, st, zip FROM Dealers";
> //
> // oleDbInsertCommand1
> //
> this.oleDbInsertCommand1.CommandText = "INSERT INTO Dealers(dlr_pk, company, dealer, dlr_type, legalname, addr1, addr2, city, st, zip) "
> + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
> this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("dlr_pk", System.Data.OleDb.OleDbType.Integer, 0, "dlr_pk"));
> this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("company", System.Data.OleDb.OleDbType.VarChar, 2, "company"));
> this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("dealer", System.Data.OleDb.OleDbType.VarChar, 5, "dealer"));
> this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("dlr_type", System.Data.OleDb.OleDbType.VarChar, 3, "dlr_type"));
> this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("legalname", System.Data.OleDb.OleDbType.VarChar, 35, "legalname"));
> this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("addr1", System.Data.OleDb.OleDbType.VarChar, 35, "addr1"));
> this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("addr2", System.Data.OleDb.OleDbType.VarChar, 35, "addr2"));
> this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("city", System.Data.OleDb.OleDbType.VarChar, 25, "city"));
> this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("st", System.Data.OleDb.OleDbType.VarChar, 2, "st"));
> this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("zip", System.Data.OleDb.OleDbType.VarChar, 10, "zip"));
>
> // oleDbUpdateCommand1
>
> this.oleDbUpdateCommand1.CommandText = "UPDATE Dealers SET dlr_pk = ?, company = ?, dealer = ?, dlr_type = ?, legalname = ?, addr1 = ?"
> + ", addr2 = ?, city = ?, st = ?, zip = ? WHERE dlr_pk = ? " ;
>
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?dlr_pk", System.Data.OleDb.OleDbType.Integer, 0, "dlr_pk"));
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?company", System.Data.OleDb.OleDbType.VarChar, 2, "company"));
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?dealer", System.Data.OleDb.OleDbType.VarChar, 5, "dealer"));
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?dlr_type", System.Data.OleDb.OleDbType.VarChar, 3, "dlr_type"));
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?legalname", System.Data.OleDb.OleDbType.VarChar, 35, "legalname"));
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?addr1", System.Data.OleDb.OleDbType.VarChar, 35, "addr1"));
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?addr2", System.Data.OleDb.OleDbType.VarChar, 35, "addr2"));
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?city", System.Data.OleDb.OleDbType.VarChar, 25, "city"));
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?st", System.Data.OleDb.OleDbType.VarChar, 2, "st"));
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?zip", System.Data.OleDb.OleDbType.VarChar, 10, "zip"));
> this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?dlr_pk", System.Data.OleDb.OleDbType.Integer, 0, "dlr_pk"));
>
> // oleDbDeleteCommand1
>
> this.oleDbDeleteCommand1.CommandText = "DELETE FROM Dealers WHERE dlr_pk = ?";
>
> this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("?dlr_pk", System.Data.OleDb.OleDbType.Integer, 0, "dlr_pk"));
>
> }
>
> /// <summary>
> /// Constructor
> /// </summary>
> public DealersDataAccessOleDb()
> {
> this.InitializeComponent();
> }
>
> /// <summary>
> /// Returns an instance of the custom data adapter
> /// </summary>
> /// <returns></returns>
> public override System.Data.IDataAdapter CreateDataAdapter()
> {
> return this.oleDbDataAdapter1;
> }
> }
>}
>
>>
>The real trick to making this work (or, in a lot of cases, not work) is that the VFP OLE-DB provider doesn't support named parameters. So all those examples showing code with the "@SomeField" won't work. The other way to do this is with positional parameters. You'll notice in the code I add the parameters in the same order as I've specified them in the Command.
>
>This is just a sample, I didn't try to cover every possible scenario (or even spend a ton of time testing - sorry! So if it doesn't work for you, I'll give you the stock, "Well, it works for me" answer <g>).
>
>Hope that helps.
