Update Foxpro 2.6 date field

Good Morning All,

I'm using VB.Net 2008, my code looks like this:

 ' Build a connection string to the FoxPro free table folder.
Dim connectionString As String = "Provider=vfpoledb;Data Source=c:\misc\dbf\mach.dbf"

' Create a connection to the tables.
Dim connectFoxPro As OleDbConnection = New OleDbConnection(connectionString)

Dim QueryString As String = ""

Dim command As OleDbCommand = New OleDbCommand()

Dim QueryString as string  = "Update test"
QueryString += "SET field1 = "' & "Hello" & "'"
QueryString += "WHERE acctnum = 108 "
QueryString += "AND test.date = '" & "12/01/2009" & "'"

command.Connection = connectFoxPro
command.CommandType = CommandType.Text
command.CommandText = QueryString

command.ExecuteNonQuery

This does not work!  IF I replace the date line with a different field, it works fine.  What is the trick with dealing with dates in a foxpro dbf file?  I've tried { ' ^ ( #, you name it I've tried everyone but the one that works.  hehehe

Any help would be great!

Thanks in advance!!

Jim
transstatesAsked:
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.

pcelbaCommented:
The question is what means 12/01? December the 1st for you probably, January 12th for me.

You could try:

QueryString += "AND test.date = {^" & "2009/12/01" & "}"
or
QueryString += "AND test.date = DATE(2009,12,1)"
0
transstatesAuthor Commented:
pce,

Tried both, neither worked. :(

hmmmm

jb
0
pcelbaCommented:
I am testing it in VS2008 just now. What is the exact error description? And waht is test.date data type in your DBF?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

transstatesAuthor Commented:
There is no error.  It runs as though it works but my executenonquery returns 0.  When I reload the data, the value has not changed.  

The Date i'm testing for is "12/01/2009".

Thanks for you help!!!!

jb
0
pcelbaCommented:
Following query:
        QueryString = "Update netinsert"
        QueryString += " SET popis = '" & "Hello" & "'"
        QueryString += " WHERE ID < 108 "
        QueryString += " AND jend = DATE(2010, 4, 23)"

returns 1.

You should check " SET popis = '" & "Hello" & "'"  in your code - it has apostroph of invalid place and also the space is missing before SET keyword.
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
pcelbaCommented:
So, the following query should work for you:

Dim QueryString as string  = "Update test "
QueryString += " SET field1 = '" & "Hello" & "'"
QueryString += " WHERE acctnum = 108 "
QueryString += "AND test.date = DATE(2009,12,1)"

And, also, you should open the connection at the beginning:

        connectFoxPro.Open()
 
0
transstatesAuthor Commented:
pceliba,

It does work!!!!  I have to check my sql statment, I'm doing something wrong, because when I run this one you just did, it works fine.  When I run my other sql statement it doesn't..

Thank you for all you help!!!

jb
0
transstatesAuthor Commented:
One more thing... will any other date formats work?

jb
0
pcelbaCommented:
Depends what you mean by "any other"...

{^2009/12/01} will work

Following code will work as well:

        QueryString = "Update netinsert"
        QueryString += " SET popis = '" & "Hello" & "'"
        QueryString += " WHERE ID < 108 "
        QueryString += " AND jend = ?"

        Dim somedate As Date
        somedate = Today
       
        connectFoxPro.Open()

        command.Connection = connectFoxPro
        command.CommandType = CommandType.Text
        command.CommandText = QueryString
        command.Parameters.AddWithValue("jend", somedate)

#12.01.2009#  is not good format
0
transstatesAuthor Commented:
Thank you for your help Pcelba!!

Jim
0
pcelbaCommented:
You are welcome!
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
Visual Basic Classic

From novice to tech pro — start learning today.