Create foxpro DBF table in C#

[Maverick]

If i try to create foxpro table by the following "sql" statment, the C#
compiler will only return an error "xxxx not support in non-dbc version". The
"index on" command statement return some kind of syntex error too. How can i
create a DBF table with proper indexing ability ?? The "UNIQUE" keyword has
already been proved to be failed to perform normally. Please help

string sql = "CREATE TABLE datafile (field1 C(10) PRIMARY KEY, field2 C(10))";

System.Data.Odbc.OdbcConnection dbConn = new
System.Data.Odbc.OdbcConnection();

dbConn.ConnectionString = "dsn=TestMsgTables;";

System.Data.Odbc.OdbcCommand cmdCreate = new
System.Data.Odbc.OdbcCommand(sql, dbConn);

cmdCreate.CommandType = System.Data.CommandType.Text;

System.Data.Odbc.OdbcCommand cmdIdx = new
System.Data.Odbc.OdbcCommand("INDEX ON field1 TO datafile.idx UNIQUE",
dbConn);

cmdIdx.CommandType = System.Data.CommandType.Text;

int retVal = 0;
try
{
dbConn.Open();

retVal = cmdIdx.ExecuteNonQuery();
}
catch (Exception ex)

System.Diagnostics.Debug.WriteLine(ex.Message); System.Diagnostics.Debug.WriteLine("RetVal => " + retVal);
}
dbConn.Close();

[Bob Grommes]

My VFP embedded SQL is getting a little rusty as it hasn't been my primary
platform in about 6 or 7 years, but somehow you are creating a free table,
rather than a table attached to a VFP database container (that is what the
"non-dbc" refers to). Primary and Candidate key indexes are only supported
within a DBC; that is why it's objecting to the PRIMARY KEY clause. I'm not
sure what the complaint about the index creation is about since you didn't
give the exact error message and I don't offhand see anything wrong with
your syntax -- but be aware that you're trying to create a stand-alone index
rather than a compound index, and if you're going to do that you would
probably want to add the COMPACT keyword to make it more efficient unless
you need to allow that index to be shared with a very old Fox product, in
fact pre-FoxBase 2.x circa 1986 or so.

If there is in fact a DBC for the table to be created within, you'll want to
find the correct syntax for doing that ... if I recall correctly it's just a
matter of issuing OPEN DATABASE DBCName sometime before the CREATE TABLE.
There might also be an IN DATABASE clause in the CREATE TABLE syntax that
will do the same thing.

By the way, since you are using the Odbc driver rather than OLE DB, I assume
you're talking to an old version of VFP -- 7.0 or earlier. Or possibly
you're even talking to the desktop FoxPro driver. You will want to use the
latest OLE DB driver and talk to it via OleDb libraries for best performance
if at all possible.

Your best bet is to take the detailed error messages to a Visual FoxPro
newsgroup. A lot of those people are getting quite conversant with .NET
anyway, but your core problem is that you're not fully understanding the VFP
environment and need to fix your use of VFP's SQL dialect.

--Bob

"Maverick" <Ma******@discussions.microsoft.com> wrote in message
news:BE**********************************@microsof t.com...

If i try to create foxpro table by the following "sql" statment, the C#
compiler will only return an error "xxxx not support in non-dbc version".
The
"index on" command statement return some kind of syntex error too. How can
i
create a DBF table with proper indexing ability ?? The "UNIQUE" keyword
has
already been proved to be failed to perform normally. Please help

string sql = "CREATE TABLE datafile (field1 C(10) PRIMARY KEY, field2
C(10))";

System.Data.Odbc.OdbcConnection dbConn = new
System.Data.Odbc.OdbcConnection();

dbConn.ConnectionString = "dsn=TestMsgTables;";

System.Data.Odbc.OdbcCommand cmdCreate = new
System.Data.Odbc.OdbcCommand(sql, dbConn);

cmdCreate.CommandType = System.Data.CommandType.Text;

System.Data.Odbc.OdbcCommand cmdIdx = new
System.Data.Odbc.OdbcCommand("INDEX ON field1 TO datafile.idx UNIQUE",
dbConn);

cmdIdx.CommandType = System.Data.CommandType.Text;

int retVal = 0;
try
{
dbConn.Open();

retVal = cmdIdx.ExecuteNonQuery();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine("RetVal => " + retVal);
}
dbConn.Close();

[Cindy Winegarden]

Hi Maverick,

Download and install the FoxPro and Visual FoxPro OLE DB data provider from
http://msdn.microsoft.com/vfoxpro/do...s/default.aspx and use
that instead of ODBC.
The Unique keyword does in fact perform as by design. From the VFP Help on
the Unique clause in the Index Command:

"[UNIQUE | CANDIDATE]
Creates a unique or candidate index. UNIQUE stores the matching index key
only for the first record that matches the specified index expression. The
index key is stored as the only key in a standalone (.idx) file or as an
index tag in a compound index (.cdx) file. Any other index keys for records
that match the index expression are excluded from the index file."
The Index command is supported by the VFP OLE DB data provider for "Stored
Procedures, Rules, Triggers, and Default Values" only, so you can not use it
in an SQL Pass-through command string.
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
ci**************@msn.com www.cindywinegarden.com
"Maverick" <Ma******@discussions.microsoft.com> wrote in message
news:BE**********************************@microsof t.com...

If i try to create foxpro table by the following "sql" statment, the C#
compiler will only return an error "xxxx not support in non-dbc version".
The
"index on" command statement return some kind of syntex error too. How can
i
create a DBF table with proper indexing ability ?? The "UNIQUE" keyword
has
already been proved to be failed to perform normally. Please help

string sql = "CREATE TABLE datafile (field1 C(10) PRIMARY KEY, field2
C(10))";

System.Data.Odbc.OdbcConnection dbConn = new
System.Data.Odbc.OdbcConnection();

dbConn.ConnectionString = "dsn=TestMsgTables;";

System.Data.Odbc.OdbcCommand cmdCreate = new
System.Data.Odbc.OdbcCommand(sql, dbConn);

cmdCreate.CommandType = System.Data.CommandType.Text;

System.Data.Odbc.OdbcCommand cmdIdx = new
System.Data.Odbc.OdbcCommand("INDEX ON field1 TO datafile.idx UNIQUE",
dbConn);

cmdIdx.CommandType = System.Data.CommandType.Text;

int retVal = 0;
try
{
dbConn.Open();

retVal = cmdIdx.ExecuteNonQuery();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine("RetVal => " + retVal);
}
dbConn.Close();