Hi mm.netters,

Does any one know how to trick the Data Adapter Configuration Wizard to recognize which
column in a fpw26 free table is the unique identifer? In my case, *custid* is the unique key. The vfp data provider cannot automatically generate the update and delete statements from my free table because there is no associated dbc to get the necessary info. Kevin suggested that I add a custom data access class to overcome the limitation but now I find the Adapter Configuration Wizard, used in this process, is not happy without the dbc either.

Below please find the code that the Adapter Configuration Wizard did generate. Please notice that it not generate delete or update code for my table, arypat01.

using System;
using System.Data;
using OakLeaf.MM.Main.Data;
namespace eCentric.gmg.Business
{
	/// <summary>
	/// Summary description for PatientDataAccessOledb.
	/// </summary>
	public class PatientDataAccessOledb : mmDataAccessOleDb
	{
		private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
		private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
		private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
		private System.Data.OleDb.OleDbConnection oleDbConnection1;

		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.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
			// 
			// oleDbDataAdapter1
			// 
			this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
			this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
			this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
																										new System.Data.Common.DataTableMapping("Table", "arypat01", new System.Data.Common.DataColumnMapping[] {


																																															new System.Data.Common.DataColumnMapping("custid", "custid"),
																																																					new System.Data.Common.DataColumnMapping("custphon", "custphon"),
																																																					new System.Data.Common.DataColumnMapping("w_phone", "w_phone"),
																																																					new System.Data.Common.DataColumnMapping("custname", "custname"),
																																																					new System.Data.Common.DataColumnMapping("title", "title"),
																																																					new System.Data.Common.DataColumnMapping("company", "company"),
																																																					new System.Data.Common.DataColumnMapping("custadd", "custadd"),
																																																					new System.Data.Common.DataColumnMapping("add", "add"),
																																																					new System.Data.Common.DataColumnMapping("custcity", "custcity"),
																																																					new System.Data.Common.DataColumnMapping("st", "st"),
																																																					new System.Data.Common.DataColumnMapping("zip", "zip"),
																																																					new System.Data.Common.DataColumnMapping("type", "type"),
																																																					new System.Data.Common.DataColumnMapping("shipname", "shipname"),
																																																					new System.Data.Common.DataColumnMapping("con", "con"),
																																																					new System.Data.Common.DataColumnMapping("rec", "rec"),
																																																					new System.Data.Common.DataColumnMapping("acai", "acai"),
																																																					new System.Data.Common.DataColumnMapping("br", "br"),
																																																					new System.Data.Common.DataColumnMapping("na", "na"),
																																																					new System.Data.Common.DataColumnMapping("dob", "dob"),
																																																					new System.Data.Common.DataColumnMapping("entered", "entered"),
																																																					new System.Data.Common.DataColumnMapping("ref", "ref"),
																																																					new System.Data.Common.DataColumnMapping("dds", "dds"),
																																																					new System.Data.Common.DataColumnMapping("status", "status"),
																																																					new System.Data.Common.DataColumnMapping("comment", "comment"),
																																																					new System.Data.Common.DataColumnMapping("comment1", "comment1"),
																																																					new System.Data.Common.DataColumnMapping("comment2", "comment2"),
																																																					new System.Data.Common.DataColumnMapping("comment3", "comment3"),
																																																					new System.Data.Common.DataColumnMapping("xnotes", "xnotes"),
																																																					new System.Data.Common.DataColumnMapping("appts", "appts"),
																																																					new System.Data.Common.DataColumnMapping("statnote", "statnote")})});
			// 
			// oleDbSelectCommand1
			// 
			this.oleDbSelectCommand1.CommandText = "SELECT custid, custphon, w_phone, custname, title, company, custadd, `add`, custc" +
				"ity, st, zip, type, shipname, con, rec, acai, br, na, dob, entered, ref, dds, st" +
				"atus, comment, comment1, comment2, comment3, xnotes, appts, statnote FROM arypat" +
				"01";
			this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
			// 
			// oleDbInsertCommand1
			// 
			this.oleDbInsertCommand1.CommandText = @"INSERT INTO arypat01(custid, custphon, w_phone, custname, title, company, custadd, `add`, custcity, st, zip, type, shipname, con, rec, acai, br, na, dob, entered, ref, dds, status, comment, comment1, comment2, comment3, xnotes, appts, statnote) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("custid", System.Data.OleDb.OleDbType.VarChar, 6, "custid"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("custphon", System.Data.OleDb.OleDbType.VarChar, 13, "custphon"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("w_phone", System.Data.OleDb.OleDbType.VarChar, 13, "w_phone"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("custname", System.Data.OleDb.OleDbType.VarChar, 16, "custname"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("title", System.Data.OleDb.OleDbType.VarChar, 16, "title"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("company", System.Data.OleDb.OleDbType.VarChar, 1, "company"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("custadd", System.Data.OleDb.OleDbType.VarChar, 35, "custadd"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("add", System.Data.OleDb.OleDbType.VarChar, 35, "add"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("custcity", System.Data.OleDb.OleDbType.VarChar, 20, "custcity"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("st", System.Data.OleDb.OleDbType.VarChar, 4, "st"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("zip", System.Data.OleDb.OleDbType.VarChar, 5, "zip"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("type", System.Data.OleDb.OleDbType.VarChar, 6, "type"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("shipname", System.Data.OleDb.OleDbType.VarChar, 6, "shipname"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("con", System.Data.OleDb.OleDbType.VarChar, 8, "con"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("rec", System.Data.OleDb.OleDbType.VarChar, 8, "rec"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("acai", System.Data.OleDb.OleDbType.VarChar, 8, "acai"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("br", System.Data.OleDb.OleDbType.VarChar, 8, "br"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("na", System.Data.OleDb.OleDbType.VarChar, 8, "na"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("dob", System.Data.OleDb.OleDbType.VarChar, 8, "dob"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("entered", System.Data.OleDb.OleDbType.VarChar, 8, "entered"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ref", System.Data.OleDb.OleDbType.VarChar, 25, "ref"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("dds", System.Data.OleDb.OleDbType.VarChar, 25, "dds"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("status", System.Data.OleDb.OleDbType.VarChar, 1, "status"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("comment", System.Data.OleDb.OleDbType.VarChar, 40, "comment"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("comment1", System.Data.OleDb.OleDbType.VarChar, 40, "comment1"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("comment2", System.Data.OleDb.OleDbType.VarChar, 40, "comment2"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("comment3", System.Data.OleDb.OleDbType.VarChar, 40, "comment3"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("xnotes", System.Data.OleDb.OleDbType.VarChar, 2147483647, "xnotes"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("appts", System.Data.OleDb.OleDbType.VarChar, 50, "appts"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("statnote", System.Data.OleDb.OleDbType.VarChar, 2147483647, "statnote"));
			// 
			// oleDbConnection1
			// 
			this.oleDbConnection1.ConnectionString = "User ID=;DSN=;Cache Authentication=False;Data Source=\"C:\\GMGWIN\\MAIN\";Provider=\"V" +
				"FPOLEDB.1\";Collating Sequence=MACHINE;Mask Password=False;persist security info=" +
				"False;Mode=Share Deny None;Extended Properties=;Encrypt Password=False";

		}
	
		public PatientDataAccessOledb()
		{
			//
			// TODO: Add constructor logic here
			//
		}
	}
}


 

If we cannot trick the Wizard, what code could be added to the code, that wizard did generate, to satisfy updating and deleting of data from my free table. A small example is vey much appreciated.


Thanx,
Neil