TerranRich
asked on
Inserting dates into FoxPro DBF table using SQL INSERT INTO statement
Everything works just fine, except that changing the DATE() part to an actual date value, or even an integer (using a calculation), does not work. What must I put into a date field for a FoxPro DBF file?
<% response.expires = 0 %>
<!-- #include file="../_function.ascx" -->
<%
' Open connection for DBF file
Dim DBConn
DBConn = OpenDBFConn(Server.Mappath("../giftcert"))
' Insert some row to the table
'Dim Results
'Results = DBConn.Execute("SELECT * FROM GIFTCERT.DBF")
DBConn.Execute("USE GIFTCERT.DBF")
DBConn.Execute("INSERT INTO GIFTCERT.DBF (GIFTNO, PURCHDATE, EXPDATE, ORDERNO, ORDERID, ITEMNO, SUBNO, CUSTNO, SHIPNO, LINENO, STARTAMT, CURRENTAMT, EMAIL, MESSAGE, HISTORY, STATUS) VALUES('11111111AA', DATE(), DATE(), 0, '', '', '', 0, 0, 0, 100, 100, 'TEST EMAIL', 'TEST MSG', '', '')")
' Output the recordset in CSV format
'Response.Write(Results.GetString(,-1, ", ", "<br />"))
Response.Redirect("admin.aspx")
%>
And the "function" file only contains one function that I use...
<%
'...
Function OpenDBFConn(Path)
Dim Conn
Conn = CreateObject("ADODB.Connection")
'Conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";Extended Properties='FoxPro 3.0;';")
Conn.Open("Provider=vfpoledb.1;Data Source=" & Path & ";Collating Sequence=machine;")
OpenDBFConn = Conn
End Function
'...
%>
ASKER
But say I want to set the PURCHDATE and EXPDATE to custom dates... for example, the PURCHDATE to 4/5/07 and the EXPDATE to 8/9/08. How would I set actual date values?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah-hah! Enclose them in braces ({ }). Thank you very much!!
You are welcome. Which version of the braces worked out for you? VFP 9.0 uses the latter.
I never used ADODB but here is one trial or
DBConn.Execute("INSERT INTO GIFTCERT.DBF (GIFTNO, PURCHDATE, EXPDATE, ORDERNO, ORDERID, ITEMNO, SUBNO, CUSTNO, SHIPNO, LINENO, STARTAMT, CURRENTAMT, EMAIL, MESSAGE, HISTORY, STATUS) VALUES('11111111AA', {" + DTOC(DATE()) + "}, {" + DTOC(DATE()) + "}, 0, '', '', '', 0, 0, 0, 100, 100, 'TEST EMAIL', 'TEST MSG', '', '')")