VB.NET Insert Error into old version of Foxpro

I'm trying to connect to an accounts package (Pegasus Opera) which uses an
old version of Foxpro as the back end database. I've tried 3 different driver
methods to connect to the data:-

1) Using the Oledb for foxpro driver within .NET
"User ID=;DSN=;Cache Authentication=False;Data
Source='{Freetablepath}';Password=;Provider='VFPOLEDB.1';Collating
Sequence=MACHINE;Null=No;Mask Password=False;Mode=Share Deny None;Extended
Properties=;Encrypt Password=False"

2) Using the 'OLEDB driver for ODBC' driver within .NET (As in the example
below) and with the ODBC connection set up using the 'Microsoft Dbase for
VFP' driver

3) Using the'OLEDB driver for ODBC' driver within .NET and with the ODBC
connection set up using the 'Microsoft Visual Foxpro' drivers

The VB.NET code to insert a row into sname is as below.

Dim cmdFox As New OleDb.OleDbCommand
Dim conFox As New
OleDb.OleDbConnection("BackgroundFetch=Yes;DSN={DSNName};UID=;SourceType=DBF;Collate=Machine;SourceDB={free table directory};Exclusive=No"")
Dim strsql As String

conFox.Open()

cmdFox.CommandText = "insert into T_sname (sn_account, sn_name) values " _
& " ('Test1', 'Test Client')"

cmdFox.Connection = conFox

Try
cmdFox.ExecuteNonQuery()
Catch obje As Exception
MsgBox(obje.Message)
End Try

conFox.Close()


In all cases the error is the same, '{fieldname} cannot be a null value'

I have tried inserting default values into all the rest of the fields to try
and get around this ('' for character fields, 0 for numeric fields, .F. for
boolean fields and 'yyyy-mm-dd hh:mm:ss' for datetimes) but I then get a
invalid data type error instead.

Some dbase/foxpro user groups suggest that earlier versions of Foxpro do not
support null values in which case the above should work and may just be
throwing an error because I've got the format of the default values
incorrect. The two fields filled in, in the above example are the only ones
that are required to add a record.

I hope someone can shed some light on this. Thanks in advance.

Sietse
Mon Aug 09 07:38:22 CDT 2004

Hi,

The table you're trying to insert the record in has the 'Allow null'
disabled for one or more fields. In this case you'll have to insert the full
values for each field (unless it's a database-table where the default value
is specified)

You're on the right track with inserting the default values by hand, but the
type-mismatch error does indead occur because of formatting problems of the
different types
BEcause of the fact that the ODBC or OLEDB driver is of VFP, VFP syntax
rules apply:
strings have to be encapsulated in quote ('), Double qoute (") or brackets
([string])
nummerics are just left as-is (mark for the dot for decimal-separation).
Date and datetime fields can be specified in multiple manners:
for dates:
DATE(nyear, nmonth, nday) (this is actually a function returning a
date-value)
{^1998-10-31}
for datetimes
DATETIME(nYear, nmonth, nday, nhours, nminutes, nseconds) (this is
actually a function returning a datetime-value)
{^1998-10-31 14:32:01}

the "^" in the last statement makes sure you're using yyy-mm-dd ordering
otherwise the ordering is the current 'set date' ordering.

HTH,
Sietse Wijnker

"Big Kahunna" <BigKahunna@discussions.microsoft.com> wrote in message
news:D91CB3C8-C294-4B7F-B3AE-03611BD07B05@microsoft.com...
> I'm trying to connect to an accounts package (Pegasus Opera) which uses an
> old version of Foxpro as the back end database. I've tried 3 different
driver
> methods to connect to the data:-
>
> 1) Using the Oledb for foxpro driver within .NET
> "User ID=;DSN=;Cache Authentication=False;Data
> Source='{Freetablepath}';Password=;Provider='VFPOLEDB.1';Collating
> Sequence=MACHINE;Null=No;Mask Password=False;Mode=Share Deny None;Extended
> Properties=;Encrypt Password=False"
>
> 2) Using the 'OLEDB driver for ODBC' driver within .NET (As in the example
> below) and with the ODBC connection set up using the 'Microsoft Dbase for
> VFP' driver
>
> 3) Using the'OLEDB driver for ODBC' driver within .NET and with the ODBC

> connection set up using the 'Microsoft Visual Foxpro' drivers
>
> The VB.NET code to insert a row into sname is as below.
>
> Dim cmdFox As New OleDb.OleDbCommand
> Dim conFox As New
>
OleDb.OleDbConnection("BackgroundFetch=Yes;DSN={DSNName};UID=;SourceType=DBF
;Collate=Machine;SourceDB={free table directory};Exclusive=No"")
> Dim strsql As String
>
> conFox.Open()
>
> cmdFox.CommandText = "insert into T_sname (sn_account, sn_name) values " _
> & " ('Test1', 'Test Client')"
>
> cmdFox.Connection = conFox
>
> Try
> cmdFox.ExecuteNonQuery()
> Catch obje As Exception
> MsgBox(obje.Message)
> End Try
>
> conFox.Close()
>
>
> In all cases the error is the same, '{fieldname} cannot be a null value'
>
> I have tried inserting default values into all the rest of the fields to
try
> and get around this ('' for character fields, 0 for numeric fields, .F.
for
> boolean fields and 'yyyy-mm-dd hh:mm:ss' for datetimes) but I then get a
> invalid data type error instead.
>
> Some dbase/foxpro user groups suggest that earlier versions of Foxpro do
not
> support null values in which case the above should work and may just be
> throwing an error because I've got the format of the default values
> incorrect. The two fields filled in, in the above example are the only
ones
> that are required to add a record.
>
> I hope someone can shed some light on this. Thanks in advance.