insert Date into FoxPro 6 database

[WaZda]

hi everybody,
am developping a vb6 application that uses a foxpro 6 database. insertion of numerique and charactor works fine. but i have to insert a date too. that's where am having a problem. i use the DTP on the vb6 interface and when i insert the '"& dtp.value &"' into the the table, i have an error " data type mismatch ".
Can somebody please help?

WaZda

 

[TheFitz]

When I've programmed with FoxPro, inserting a date in the wrong format will give this error. The right format is shown below

{^2007/07/17}

Note both the { } and the ^. This works fine for me.

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[WaZda]

thanks a lot TheFitz , am giving your code a try. will let u know of any change.

 

[WaZda]

it is giving me a hard time. what am trying to do looks like this

con.Execute ("insert into Garantie values('" & Trim$(txtGarantie) & "','" & Trim$(dtpDate.value) & "'))

the dtpDate.value seems to be in a wrong format, though the value is correct (02/02/2007) which is the format of the date in the table.
please help

 

[TheFitz]

OK, I see, I'm not guaranteeing this will work, but try:

con.Execute ("insert into Garantie values(" & Trim$(txtGarantie) & "','{^" & Trim$(FORMAT(dtpDate.value,"YYYY/MM/DD)) & "})")

Let me know how it goes. . .

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[WaZda]

ok TheFitz,
thanks a lot, will let u know as soon as possible. will try this right away

 

[Andrzejek]

ORACLE takes TO_DATE, so you may want to try:

con.Execute ("insert into Garantie values(" & Trim$(txtGarantie) & "', [blue]TO_DATE[/blue]('" & dtpDate.value & "', 'MM/DD/YYYY'))

Have fun.

---- Andy

 

[WaZda]

thank you Andrzejek, i've use your code but am having an error with it "file 'to_date.prg' does not exist ".

TheFitz, when i use your code, am having an "capacity overflow" error.

am really stuck don't know what to do next. still searching though. anybody comes accross something, kindly let me know.

 

[Andrzejek]

When I have problem like this, I use Data Environment. That's where I build (almost) all my Select, Update, Insert and Delete SQLs.

In criteria I just dump simple date, and SQL is build by data env with correct syntax. Works ilke a dream with ORACLE. Try it. It may work with FoxPro as well.

Have fun.

---- Andy

 

[WaZda]

thanks for your advice andy, i'll give it a try.

 

[TheFitz]

WaZda,

I've used FoxPro 5 and FoxPro 8. I haven't used it for a little while, however, I seem to remember, there was a difference between the date formats of 5 & 8.

I had big problems trying to use the right dates in 8 after 5.

From memory, Access requires dates as #dd/mm/yyyy#, however FoxPro always requires {}. I think the ^ may have come in, in FoxPro Version 8. Try combinations of date formats in {}, starting with {yyyy/mm/dd}. See if that works.

As for the reason why you are getting To_Date.prg, FoxPro is assuming you are trying to call an external program, hence the .prg extension.

Note, MS like using different standards for their code. For example, an executed SQL statement in Access, has a ; at the end. Oracle (obviously non MS!!) use a ; to execute a statement and FoxPro uses ; as a line continuation.

Let me know how this goes, and I'll see if I can advise further.

Can you let me see an example command to insert the date please.

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[WaZda]

Once again, thank you very much TheFitz for your advices. they are helping me understand a lot. well, this is the exact command am using:

If strLetter = "S" Then
con.Execute ("insert into Garantie values('" & Trim$(txtGarantie.Text) & "','" & Trim$(pays) & "','" & Trim$(txtlibelle.Text) & "','" & Trim$(txtCode.Text) & "', '" & dtpDate.value & "' ,'" & Trim$(txtDuree.Text) & "','" & Trim$(txtDiffere.Text) & "'," & txtMontant.Text & ",'" & Trim$(txtDescription.Text) & "','" & Trim$(txtNom.Text) & "','" & Trim$(txtPrenom.Text) & "','" & Trim$(txtEmail.Text) & "','" & Trim$(emprunt) & "')")
MsgBox ("Information saved")
End If

i'll now try using the combinations u gave me, then let you know of what happens. Am not sure of this but it seems to me that vb has to pb to interpret the {} sign in the insert command.

 

[TheFitz]

Does that mean you've tried:

If strLetter = "S" Then
      con.Execute ("insert into Garantie values('" & Trim$(txtGarantie.Text) & "','" & Trim$(pays) & "','" & Trim$(txtlibelle.Text) & "','" & Trim$(txtCode.Text) & "',[b] {" & dtpDate.value & "} [/b],'" & Trim$(txtDuree.Text) & "','" & Trim$(txtDiffere.Text) & "'," & txtMontant.Text & ",'" & Trim$(txtDescription.Text) & "','" & Trim$(txtNom.Text) & "','" & Trim$(txtPrenom.Text) & "','" & Trim$(txtEmail.Text) & "','" & Trim$(emprunt) & "')")
   MsgBox ("Information saved")
End If

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[WaZda]

oh my god!!! it is working. your code is rockin''''''''''''''. thanks a lot TheFitz.
i can finally insert into my table.
One other thing is the format of the table is "MM/DD/YYYY" i've custom the dtpicker to that format but when it is saving, it changes to "DD/MM/YYYY". This implies that at times when the first two digits are more that "12" it saves nothing to the table, but fortunately there is no error in that case. The only thing is, that field is empty in the table.
Can you please help ?

 

[Andrzejek]

Would Format fix your problem?

con.Execute ("insert into Garantie values('" & Trim$(txtGarantie.Text) & "','" & Trim$(pays) & "','" & Trim$(txtlibelle.Text) & "','" & Trim$(txtCode.Text) & "', [b] {" & [blue]Format(dtpDate.value, "MM/DD/YYYY")[/blue] & "} [/b],'" & Trim$(txtDuree.Text) & "','" & Trim$(txtDiffere.Text) & "'," & txtMontant.Text & ",'" & Trim$(txtDescription.Text) & "','" & Trim$(txtNom.Text) & "','" & Trim$(txtPrenom.Text) & "','" & Trim$(txtEmail.Text) & "','" & Trim$(emprunt) & "')")

Have fun.

---- Andy

 

[TheFitz]

Andy,

I was just about to add the same!

Fitz

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[WaZda]

thanks a lot TheFitz and Andrzejek. am through with my problem. At least no "date" is buggin' me no more.
I really appreciate all you did to help me.