Trouble inserting a datetime into a foxpro dbf file from C#

I am writing a small program that reads in a csv file and parses out some data and writes it back to a foxpro dbf file. I have everything working fine, except for writing a datetime value to the dbf. I have changed the format around to test it and taken out the 2 datetime fields and it works, but when I put back in the fields, I get a syntax error. See the attached code snippet

I've also tried hardcoding a value into the INSERT INTO statement in the format of DToT{^4/14/2010} as well as {^4/14/2010 03:31:12} and both give me a sytnax error.
I am using Visual Studio 2010 Professional in C# and have tried in Visual Studio 2008 Professional in C# and I'm using the latest Microsoft OLE DB Provider for Visual Fox Pro.

using (OleDbCommand iCom = conn.CreateCommand())
          {
            iCom.CommandText = "INSERT INTO prxpst30 (xseqno, paytype, prempl, units, begdate, enddate, taxterr, dayswrk, totlpay, payrate, intime, outtime, tmeth, deptid, pexpacc, pliaacc, project, phase, catg, valflag, valmsg, jobid, item, taxcode, currid, exchrat, bpayrate) VALUES (@xseqno, @paytype, @prempl, @units, '', @begdate, @enddate, 13, @totlpay, @payrate,'00:00', '00:00', 'H', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0.0000)"; 
            iCom.Parameters.AddWithValue("@xseqno", OleDbType.Numeric).Value = lastXseqno;
            iCom.Parameters.AddWithValue("@paytype", OleDbType.Double).Value = payType;
            iCom.Parameters.AddWithValue("@prempl", OleDbType.Numeric).Value = employeeID;
            iCom.Parameters.AddWithValue("@units", OleDbType.Double).Value = hours;
            iCom.Parameters.AddWithValue("@begdate", OleDbType.DBDate).Value = begDate;
            iCom.Parameters.AddWithValue("@enddate", OleDbType.DBDate).Value = endDat;
            iCom.Parameters.AddWithValue("@totlpay", OleDbType.Double).Value = totPay;
            iCom.Parameters.AddWithValue("@payrate", OleDbType.Double).Value = payRate;


            //iCom.CommandType = CommandType.Text;
            iCom.Connection = conn;
            iCom.Connection.Open();
            iCom.ExecuteNonQuery();
            iCom.Connection.Close();
          }

Open in new window

z28power4uAsked:
Who is Participating?
 
pcelbaCommented:
Looking at Tusharkan's example from duplicate question the problem is solved. Parameters in VFP OLE DB provider must be ? (question marks) not @...  Now it is your responsibility to properly create datetime parameter values.

Following code works for me:
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            OleDbConnection dbConnection1 = new OleDbConnection("Provider=vfpoledb.1;Data Source=D:\\LinkFox\\");
            string sql = "INSERT INTO netinsert (ID, dtm, popis) VALUES (5, ?, 'pop')";
            OleDbCommand scCommand = new OleDbCommand(sql,dbConnection1);
            OleDbParameter scParam = new OleDbParameter();

            DateTime cdtm = new DateTime(2010, 4, 21, 15, 55, 15);
            scParam.ParameterName = "dtm";
            scParam.Value = cdtm;
            scParam.OleDbType = OleDbType.DBTimeStamp;
            //scParam.Size = 5;

            scCommand.Parameters.Add(scParam);
            
            Console.WriteLine(DateTime.Now.ToString("yyyymmdd HH:mm:ss"));
            //scCommand.Parameters.AddWithValue("@DatePar3", DateTime.Now);
 
            try
            {
                dbConnection1.Open();
                //scCommand.Connection = dbConnection1;
                scCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //dbu.SQL_FailureHandler(ex);
                Console.WriteLine("Exception: {0}", ex);
            }
            finally
            {
                dbConnection1.Close();
            }      
        }
    }
}

Open in new window

0
 
Daniel Van Der WerkenIndependent ConsultantCommented:
I don't really know FoxPro, but for this I would use a string like:

'04-22-2010'

so my insert into would be:

INSERT INTO TB_NAME
COL_DATETIME = '04-22-2010'
WHERE X = Y

or something like that.  Try just a string though.
0
 
pcelbaCommented:
Does it mean you cannot assign C# datetime value as query parameter?

Correct datetime constant is {^YYYY/MM/DD HH:MM:SS}

You may also test    EVALUATE('{^YYYY/MM/DD HH:MM:SS}')   which should evaluate the datetime from text string at FoxPro side.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
pcelbaCommented:
Correct date and time literals for ODBC are described here: http://msdn.microsoft.com/en-us/library/ms710282(VS.85).aspx   
It could work in OLE DB also.
0
 
pcelbaCommented:
Question:  Do parameters work for you in INSERT command?  Following code reports syntax error when @IDpar parameter is used. If I remove it, everything works. I cannot find the way how to use parameters in insert command...
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            OleDbConnection dbConnection1 = new OleDbConnection("Provider=vfpoledb.1;Data Source=D:\\LinkFox\\");
            string sql = "INSERT INTO netinsert (ID, dtm, popis) VALUES (5,{^2010.04.21 15:55},@IDpar)";
            OleDbCommand scCommand = new OleDbCommand(sql,dbConnection1);
            OleDbParameter scParam = new OleDbParameter();

            scParam.ParameterName = "@IDpar";
            scParam.Value = "popis";
            scParam.OleDbType = OleDbType.Char;
            scParam.Size = 5;

            scCommand.Parameters.Add(scParam);
            
            Console.WriteLine(DateTime.Now.ToString("yyyymmdd HH:mm:ss"));
 
            try
            {
                dbConnection1.Open();
                scCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: {0}", ex);
            }
            finally
            {
                dbConnection1.Close();
            }      
        }
    }
}

Open in new window

0
 
tusharkanvindeCommented:
This is a copy of what was there in the duplicate thread.

use {^2010/4/14 03:31:12} instead

Or you can try command like

OleDbConnection con = new OleDbConnection(
   @"Provider=VFPOLEDB;Data Source=c:\my folder\mydatabase.dbc");
OleDbCommand cmd = new OleDbCommand(
   "insert into myTable (myDateColumn) values (?) where myPkID = ?", con);
cmd.Parameters.AddWithValue("mydate", DateTime.Today );
cmd.Parameters.AddWithValue("myId", 1 );
con.Open();
cmd.ExecuteNonQuery();
con.Close();
0
 
Olaf DoschkeSoftware DeveloperCommented:
Waht type are the DBF fields "begdate" and "enddate"? OleDbType.DBDate may only be valid for T (DateTime) fields and not for D (Date only) fields, or the DBF fields might simply rather be Char fields.

Maybe your VFPOLEDB PRovider is just to old. The Provider for VFP7 SP1 had this error:
http://support.microsoft.com/?scid=kb%3Ben-us%3B321631&x=14&y=11

Bye, Olaf.
0
 
Tom KnowltonWeb developerCommented:
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
pcelbaCommented:
This question should not be deleted because it was answered correctly. I would recommend points split.
0
 
pcelbaCommented:
My recommendation: Points split between ID:31858865 and ID:32034704
0
 
South ModModeratorCommented:
All,
 
Following an 'Objection' by pcelba (at http://www.experts-exchange.com/Q_27083714.html) to the intended closure of this question, it has been reviewed by at least one Moderator. In its present state, it is our concensus there is no PAQ-worthy content in this question.
 
Please note this is by no means implying the suggestion provided is invalid. The nature of EE is such that awarding points must only be done for tested and verified solutions. In many cases, without a suitable response from the Author to indicate this, we cannot be sure a solution is indeed working as originally requested.
 
At this point I am going to re-start the procedure of deleting this question.
 
Thank you,
 
SouthMod
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.