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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.