Insert dates into VFP Table via ADODB

[wsobers]

I have to update/insert records into a VFP free table.
Code follows below...

Dim Conn As Variant
Dim sql_cmd As String, qry_cmd As String
'
Set Conn = CreateObject("ADODB.Connection")
Conn.Mode = 16 ' ShareDenyNone
Conn.CursorLocation= 3 'Client Side Cursor
Conn.Open "Provider=VFPOLEDB;Data Source=E:\RETAIL\DATA\"
'
sql_cmd = "insert into xrtlparts (" & cInsertFields & ") values (" & cInsertValues & ")"

Dim objRS As Variant
Set objRS = CreateObject("ADODB.RecordSet")
Set acmd = CreateObject("ADODB.Command")
'
acmd.CommandText = sql_cmd
Set acmd.ActiveConnection = Conn
'
Set objRS = acmd.Execute
*-----End of Code ----

I can update/insert records with numeric or character data, but not with dates.
I get the message "Microsoft OLEDB Provider for Visual Foxpro: Data type mismatch"
I believe the date format I'm using is incorrect ("MM/DD/YYYY"). Can anyone shed any light on this? Thanks for any help
(Yes, its not VB it's Lotus script, but hopefully its a data format problem and not a dev environment problem)
Wayne

 

[Mike Lewis]

Wayne,

Can you clarify: what are the values that you are passing (in other words, what is in your cInsertValues string)? Also, what is the data type of the field that gave rise to the "data type mismatch" error?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[wsobers]

cInserFields is like this:-
[Account,CustName,Rate,Address]
cInsertValues is a string somewhat like:
[1345,"Fred Bloggs",5.87,"1 Random Way"]
so the final string looks like...
"insert into xrtlparts (Account,CustName,Rate,Address) values (1345,"Fred Bloggs",5.87,"1 Random Way")"

This works for numeric and character data but dates are failing.
I've tried "YYYYMMDD","YYYY/MM/DD","YYYY-MM-DD","{YYYY-MM-DD}", "MM/DD/YYYY" but I still get the error.

Wayne

 

[pthalacker]

Hi Wayne,

I suspect that VFP is interpreting your date as a character string. Can you set the Type property of the ADO Recordset field to adDate and then use the recordset directly in the insert command like:

Values(ors.DateField, "Fred Bloggs" etc...

pamela

 

[Mike Lewis]

Waynbe,

I think Pamela is probably right. But I'd suggest one other thing you might try. You mentioned that you used this format:

"{YYYY-MM-DD}"

In fact, the correct syntax in VFP is:

"{^YYYY-MM-DD}"

It might be worth a shot.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[wsobers]

Mike,
I tried "{^YYYY-MM-DD}" as well, but no luck.
Pamela,
Given that I am using an ADODB Command object, I'm not sure how to go about setting this up.
I'll have to do some more research on this.
Thanks for replying...

Wayne

 

[TamarGranor]

To pass a date value with ADO, try hash marks aroudn the date in this format:

#DD-MM-YYYY#

Tamar