• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1609
  • Last Modified:

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
0
transstates
Asked:
transstates
  • 6
  • 5
2 Solutions
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now