473,386 Members | 1,644 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Create foxpro DBF table in C#

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();
Nov 16 '05 #1
2 22071
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();

Nov 16 '05 #2
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();

Nov 16 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Kate | last post by:
Hi... I have a foxpro program which is for producing excel reports back ended to foxpro table(.dbf). Since the table is getting bigger and bigger, it slows down my program. I need a program...
13
by: Simon Bailey | last post by:
I am a newcomer to databases and am not sure which DBMS to use. I have a very simplified knowledge of databases overall. I would very much appreciate a (simplifed) message explaining the advantages...
2
by: Salad | last post by:
OS = WinXP & Win98. Access = A97 & AXP Q1) Where can I find the VFP ODBC driver at Microsoft. I have been working developing an app in Access that will link to some DOS FoxPro tables. I...
1
by: David Sorber via AccessMonster.com | last post by:
Hello, I've got two questions. Im writing an Access 2000 database to collect data from multiple Visual Foxpro Databases, total some figures, save the data into a table for archiving purposes, and...
5
by: Roy Gourgi | last post by:
Hi, I am used to working in Visual FoxPro and I would like to be able to create a database and store and retrieve information from it. What is the simplest way to do it and what should I be...
3
by: Amar | last post by:
I have a abc.PRG file in visual foxpro 8.0. I can run this file using visual foxpro environment and it creates a table X.dbf in the same folder where this program file is and populates some data...
2
by: Toco | last post by:
Hello. I have app written in C# that we are testing. The executable is shared on our network. The method we are testing is a call to a foxpro table. The call is made to query the table, then...
2
by: cj | last post by:
We have a legacy accounting system (not developed in house) here that happens to be written in Visual FoxPro. One of the tables has an index that is actually a coded function COMPANY1 ...
7
by: z71mdridin | last post by:
I have an asp.net website that uses Form authentication to authenticate users. I need to provide users with a report based on FoxPro data that resides on a remote server. When I attempt to...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.