ADO and NULL in VFP tables

Darren Woodford

When I use the AddNew() method of a recordset the new record has all the fields set to .NULL. Unfortunately the tables I am accessing do not allow NULL values in the fields. Can you tell me please is there a simple way to change this behaviour of the recordset so that the fields are set to empty values instead such as "", 0, {} etc Otherwise I am guessing that I would have to write a routine of my own that changes any null values that haven't been set by the client. At the moment when I call the Update() method of the recordsetit throws a pretty general exception saying something went wrong. I am guessing that it is because of the NULLs.

As a bit of background I am trying to use ADO to access VFP native tables so that I can easily use the recordsets as business entities that can be passed between my classes without having to specify dozens of parameters in the method calls. I am hoping to also pass these business entities between my COM server and the calling client application.

Regards

Darren

CetinBasoz

VFPOLEDB provider supports "set null" command which can specify whether to insert nulls or emtpy values for those not specified. ie:

Code Block

Local loRS As ADODB.Recordset, ;

loCon As ADODB.Connection, ;

loCmd As ADODB.Command

loCon = Createobject("ADODB.Connection")

loCmd = Createobject('ADODB.Command')

loRS = Createobject("ADODB.RecordSet")

With loCon

.ConnectionString = "Provider=VFPOLEDB;Data Source="+;

"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\data\TESTDATA.DBC"

.Mode = 16 && adModeShareDenyNone - default

.Open()

Endwith

With loCmd

.ActiveConnection = loCon

.CommandType = 2 && Table

.CommandText = 'customer'

Endwith

With loRS

.CursorType = 2 && ADOPENDYNAMIC

.LockType = 4 && ADLOCKBATCHOPTIMISTIC

.CursorLocation = 3 && ADUSECLIENT

.ActiveConnection = loCon

.Open(loCmd)

Endwith

loCon.Execute("set null off")

loRS.AddNew

loRS.Fields("Cust_ID").Value = "ADO1"

loRS.AddNew

loRS.Fields("Cust_ID").Value = "ADO2"

loRS.UpdateBatch()

loRS.Close()

loCon.Close()

To pass biz entities between classes and sessions you can also pass objects wrapping structure and data, XML and so on (I don't know the background in detail, just ideas Smile

Darren Woodford

That's got it! Thank you very much Cetin.

The error I was getting is "Multiple-step operation generated errors. Check each status value." I also get this if I forget to fill in a mandatory field. Is there a way to get a more specific message back from ADO

WRT the passing of business entities between classes I have started a new thread here http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=2351632&SiteID=1 to ask for some guidance. I have given a bit more background there too.

Regards

Darren

ADO and NULL in VFP tables

Re: ADO and NULL in VFP tables

Re: ADO and NULL in VFP tables