ODBC query to modify DBF table structure

[Patrice]

What if you try VARCHAR(3) instead ?

 

[Andrzej Kaczmarczyk]

Hi,

I have a DBF table that I can access using from VisualStudio 2005 via ODBC

OdbcConnection conn;
string connectionString = @"Dsn=Pliki programu
dBase;defaultdir=c:\DBFFiles;driverid=533;maxbuffersize=2048;pagetimeout=5";
conn = new OdbcConnection(connectionString);
conn.Open();

I can then for example query it with select or import a schema
DataTable dt = conn.GetSchema(OdbcMetaDataCollectionNames.Columns);


The problem is when I try to alter or add the column to the table;

I am following this page (mind the wrap):
http://msdn.microsoft.com/library/d...y/en-us/odbc/htm/vfplngcreate_table___sql.asp

but trying something as simple (the table exists, isn't open by other
program) as

query = "ALTER TABLE testTable ADD COLUMN testColumn C (3)";
OdbcCommand cmd = new OdbcCommand(query, this.conn);
cmd.ExecuteNonQuery();

or

query = "CREATE TABLE testTable (testColumn C (3))";
OdbcCommand cmd = new OdbcCommand(query, this.conn);
cmd.ExecuteNonQuery();

always yields [ERROR][42000 ][Microsoft][ODBC Driver] Invalid field
definition

can anyone tell me what's wrong with those queries?
I've tried quoting, bracketing the column name,putting additional zeros,
using date, and eliminating the field width entirely.



CUIN Kaczy

 

[Andrzej Kaczmarczyk]

What if you try VARCHAR(3) instead ?
There is a decription that the filedtypes must be one letter.
beside there is no such thing as varchar in dbf

anyone has any other idea?

CUIN kaczy

 

[Cindy Winegarden]

Hi Andrzej,

Have you considered using the FoxPro and Visual FoxPro ODBC driver or OLE DB
data provider, both downloadable from
http://msdn.microsoft.com/vfoxpro/downloads/updates ?

There may be a limit on the number of characters in dBase field names (try
8). While Visual FoxPro 9 DBFs have a VarChar data type, using a VarChar
data type in an older table will convert it to the VFP9 format and it won't
be readable via older data access interfaces such as VFP ODBC, etc.

'-- Here's some VB code to play with a table.
'-- Note that FoxPro accepts ', ', and [] as string delimiters.

Imports System
Imports System.Data
Imports System.Data.OleDb

Module Module1

Sub Main()
Try
Dim cn As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn.Open()

Dim cmd1 As New OleDbCommand( _
"Create Table Test (Field1 C(10))", cn)
Dim cmd2 As OleDbCommand = New OleDbCommand( _
"Alter Table Test Add Column testColumn C(10)", cn)
Dim cmd3 As New OleDbCommand( _
"Insert Into Test Values ([Hello ], [World])", cn)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()

Dim da As New OleDbDataAdapter( _
"Select * From Test", cn)
Dim ds As New DataSet()
da.Fill(ds)

Console.WriteLine(ds.Tables(0).Rows(0).Item(0).ToString & _
ds.Tables(0).Rows(0).Item(1).ToString)
Console.ReadLine()

cn.Close()

Catch e As Exception
MsgBox(e.ToString())
End Try

End Sub

End Module

--
Cindy Winegarden MSCD, Microsoft Visual FoxPro MVP
(e-mail address removed) www.cindywinegarden.com

Hi,

I have a DBF table that I can access using from VisualStudio 2005 via ODBC

OdbcConnection conn;
string connectionString = @"Dsn=Pliki programu
dBase;defaultdir=c:\DBFFiles;driverid=533;maxbuffersize=2048;pagetimeout=5";
conn = new OdbcConnection(connectionString);
conn.Open();

I can then for example query it with select or import a schema
DataTable dt = conn.GetSchema(OdbcMetaDataCollectionNames.Columns);


The problem is when I try to alter or add the column to the table;

I am following this page (mind the wrap):
http://msdn.microsoft.com/library/d...y/en-us/odbc/htm/vfplngcreate_table___sql.asp

but trying something as simple (the table exists, isn't open by other
program) as

query = "ALTER TABLE testTable ADD COLUMN testColumn C (3)";
OdbcCommand cmd = new OdbcCommand(query, this.conn);
cmd.ExecuteNonQuery();

or

query = "CREATE TABLE testTable (testColumn C (3))";
OdbcCommand cmd = new OdbcCommand(query, this.conn);
cmd.ExecuteNonQuery();

always yields [ERROR][42000 ][Microsoft][ODBC Driver] Invalid field
definition

can anyone tell me what's wrong with those queries?
I've tried quoting, bracketing the column name,putting additional zeros,
using date, and eliminating the field width entirely.



CUIN Kaczy

 

[Andrzej Kaczmarczyk]

Hi Andrzej,

Have you considered using the FoxPro and Visual FoxPro ODBC driver or OLE
DB data provider

thx, I'll try it on Monday

CUIN Kaczy