1

I have an odd problem about VB6.0 programming when I add a value to database firebird from DtPicker or Calendar. When I add a date where the day is 1 to 12 it can add it into the database, but when I try to add a date where the day is the 13th or above it shows error message

Run-time error '-2147467259'(80004005)':
[ODBC Firebird Driver][Firebird]conversion error from string "13/08/2017"

The type in database is "DATE", but when I turn the type in database into "VARCHAR" everything is fine, but "VARCHAR" cannot do the function Date.

Image

YowE3K
  • 23,852
  • 7
  • 26
  • 40
SETYO
  • 39
  • 4
  • 2
    It is probably expecting a `"mm/dd/yyyy"` date format. I'm used to VBA and not VB6, but it might be something like using `Format(whatever_date, "mm/dd/yyyy")` – YowE3K Aug 25 '17 at 04:04
  • how is the syntax at SQL? I use this code to put it into database : – SETYO Aug 25 '17 at 04:16
  • how is the syntax at SQL? I use this code to put it into database : insert into TBL_KAL values ('" & Cal1.Value & "','" & cmbKet.Text & "','" & txtHour.Text & "') – SETYO Aug 25 '17 at 04:17
  • As I said, I'm not used to VB6 (it is **many** years since I last used it) but it might be something as simple as `... insert into TBL_KAL values ('" & Format(Cal1.Value, "mm/dd/yyyy") & "','" & cmbKet.Text & "','" & txtHour.Text & "')` (assuming that `Cal1.Value` is what I described as `whatever_date`) – YowE3K Aug 25 '17 at 04:21
  • ok I just realize that nothing such 13 month..so the database date format is mm/dd/yyyy but I mak an input such like dd/mm/yyyy – SETYO Aug 25 '17 at 04:23
  • Yes, you are passing a "dd/mm/yyyy" format and it wants a "mm/dd/yyyy" format. P.S. Check out [this question](https://stackoverflow.com/q/801156/6535336). So I think you will need to use `Format(Cal1.Value, "MM/dd/yyyy")` (with uppercase `MM`) or possibly `Format(CDate(Cal1.Value), "MM/dd/yyyy")`. – YowE3K Aug 25 '17 at 04:25
  • it works, thanks alot – SETYO Aug 25 '17 at 04:31
  • 1
    @SETYO: your problem is because you should use 'Command with Parameters' to avoid any Format problem. Your code is totally wrong! Google with: "DAO command parameters" (if you use DAO) or "ADODB Command parameters" if you use ADO. – Giorgio Brausi Aug 25 '17 at 07:05
  • You should post the actual code that produces the error. Also note that anything other than ISO-8601 date format (YYYY-MM-dd) might be confusing, see [my answer here](https://stackoverflow.com/a/23857635/466862) for an overview of valid formats – Mark Rotteveel Aug 25 '17 at 07:39
  • you should not be putting datetime or any other values into SQL text, you have to use parameters with labguage-native datatypes. For other languages and libraries you can find examples here: http://bobby-tables.com/ – Arioch 'The Aug 25 '17 at 08:09
  • https://www.firebirdsql.org/en/firebird-date-literals/ for dangerous method of misplacing datatime variables into SQL text. – Arioch 'The Aug 25 '17 at 08:11

1 Answers1

0

If you pass date value as a string or in context of a concatenated statement, make sure you set the value in 'YYYY-MM-DD' format (ISO_8601).

If you have prepared statements with parameters, then driver itself will handle safely data type conversions automagically. At least it should.

Last option is recommended.

Marcodor
  • 4,578
  • 1
  • 20
  • 24