Insert timestamp using datareader to oracle date

kerek2

Hi sir, I'm trying to insert datetime value from firebird database into oracle database but got "

ORA-01861: literal does not match format string

i'm using this command :

oracmd.CommandText = "INSERT INTO ASIS.TIMECLOCK_DATA(TD_CARDHOLDER_ID,TD_EVENT_DATE) " & _
" VALUES ('" + IBSSDR.Item("CARDHOLDERID").ToString + "',to_date('" + IBSSDR.Item("EVENTDATE") + "','YYYY-MM-DD HH24:MI:SS'))"
'

When i'm using this command

oracmd.CommandText = "INSERT INTO ASIS.TIMECLOCK_DATA(TD_CARDHOLDER_ID,TD_EVENT_DATE) " & _
" VALUES ('" + IBSSDR.Item("CARDHOLDERID").ToString + "',('" + IBSSDR.Item("EVENTDATE") + "'))"

I got this error :-

ORA-01843: not a valid month

Please anyone can guide me? Plssss, tq

Richard Deeming

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query. Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^] How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^] Query Parameterization Cheat Sheet | OWASP[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Victor Nijegorodov

Did you look at the debug/watch window to see how the SQl text contained in oracmd.CommandText looks like? Did you try to test this SQL expression in the Oracle DB?

DerekT P

I don't know Oracle so can only assume your date format string is correct; but:

to_date('" + IBSSDR.Item("EVENTDATE") + "','YYYY-MM-DD HH24:MI:SS')

is taking a (presumably) date object IBSSDR.Item("EventDate") and putting it directly into that function via string concatenation. As usual, put breakpoints in place and inspect the value of .CommandText - this will show you how VB has formatted that date field, using its default (local) formatting, as input to the function. You will most likely need to use some explicit formatting on the VB side to create a string that Oracle will be able to parse into a date. But, as Richard always says, don't build your SQL by string concatenation - parameterise it.

kerek2

ooooo i get it, already try to other database like SQL db it can work... maybe oracle have restricted....thank you for idea

kerek2

Ok sir thank for the idea it's done by this

Dim touch As String = DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss tt")
touch = ("" & IBSSDR.Item("EVENTDATE").ToString & "")

                    oracmd.CommandText = "INSERT INTO ASIS.TIMECLOCK\_DATA(TD\_CARDHOLDER\_ID,TD\_EVENT\_DATE,TD\_FIRST\_IN,TD\_IN\_READER,TD\_IP\_CONTROLLER,TD\_TYPE) " & \_
                                         " VALUES  ('" & IBSSDR.Item("CARDHOLDERID") & "',TO\_DATE('" & touch & "','dd/mm/yyyy hh:mi:ss am')," & \_
                                        "TO\_DATE('" & touch & "','dd/mm/yyyy hh:mi:ss am'),'" & IBSSDR.Item("DEVICEID") & "','" & IBSSDR.Item("WINSOCKIPADDRESS") & "','CHECKIN') "

Thank you for yuor help ;) ;)

Dave Kreskowiak

It may work now, but DO NOT get into the habit of using string concatenation to build SQL queries like you have. Google for "SQL Injection Attack" to find out why what you're doing is so bad. I refer you back to Re: Insert timestamp using datareader to oracle date - Visual Basic Discussion Boards[^]

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

DerekT P

As others have said, avoid string concatenation, use parameterised queries (which in this instance would also avoid all the date formatting). Your thanks and confirming you've got the code working is appreciated! :-)

kerek2

Thank you for advised. :)

kerek2

Hi Sir, I'm try to use Parameter but got this error :

Failed to convert parameter value from a String to a DateTime.

My coding like this :

 oracmd.CommandText = " INSERT INTO ASIS.TIMECLOCK\_DATE(TD\_CARDHOLDER\_ID,TD\_EVENT\_DATE,TD\_FIRST\_IN,TD\_IN\_READER,TD\_IP\_CONTROLLER,TD\_TYPE) " & \_
                                " VALUES (@CARDHOLDERID,(@EVENTDATE),(@FIRSTIN),@READER,@IP,@TYPE)"



            oracmd.Parameters.Add("@CARDHOLDERID", OracleType.VarChar).Value = "" & IBSSDR.Item("ID") & ""
            oracmd.Parameters.Add("@EVENTDATE", OracleType.DateTime).Value = "TO\_DATE('06/10/2020 09:30:00','dd/mm/yyyy hh:mi:ss am')"
            oracmd.Parameters.Add("@FIRSTIN", OracleType.DateTime).Value = "TO\_DATE('06/10/2020 09:30:00','dd/mm/yyyy hh:mi:ss am')"
            oracmd.Parameters.Add("@READER", OracleType.VarChar).Value = "" & IBSSDR.Item("DEVICE\_ID") & ""
            oracmd.Parameters.Add("@TYPE", OracleType.VarChar).Value = "CHECKIN"

      

            oracmd.CommandType = CommandType.Text

My oracle using format like this : dd/mm/yyyy hh:mi:ss am'

anyone got idea to hlep me?..Plzz

Richard Deeming

Why are you converting all of your parameter values to strings? Use the appropriate .NET type instead:

oracmd.CommandText = " INSERT INTO ASIS.TIMECLOCK_DATE(TD_CARDHOLDER_ID,TD_EVENT_DATE,TD_FIRST_IN,TD_IN_READER,TD_IP_CONTROLLER,TD_TYPE) " & _
" VALUES (@CARDHOLDERID, @EVENTDATE, @FIRSTIN, @READER, @IP, @TYPE)"

oracmd.Parameters.Add("@CARDHOLDERID", OracleType.VarChar).Value = IBSSDR.Item("ID")
oracmd.Parameters.Add("@EVENTDATE", OracleType.DateTime).Value = New DateTime(2020, 10, 6, 9, 30, 0)
oracmd.Parameters.Add("@FIRSTIN", OracleType.DateTime).Value = New DateTime(2020, 10, 6, 9, 30, 0)
oracmd.Parameters.Add("@READER", OracleType.VarChar).Value = IBSSDR.Item("DEVICE_ID")
oracmd.Parameters.Add("@TYPE", OracleType.VarChar).Value = "CHECKIN"

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Dave Kreskowiak

DateTime values do not have a format while stored in the database. The format is only applied when the value is displayed, either by the query tool you're using to test the query or by your own application. There is no format when stored by the database and that's what's tripping you up.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

kerek2

Sir, The datetime value is taking form another database (Firebird) using Datareader(IBSDDR.item("")), so i don't need to convert to_date when insert into ORacle sir?...Plz guide me, thanks in advanced :)

kerek2

Hi Sir When i'm using Parameter got this error :-

ORA-01008: not all variables bound

Can u advise me what wrong..

oracmd.CommandText = " INSERT INTO ASIS.TIMECLOCK_DATE(TD_CARDHOLDER_ID,TD_EVENT_DATE,TD_FIRST_IN,TD_IN_READER,TD_IP_CONTROLLER,TD_TYPE) " & _
" VALUES (:CARDHOLDERID,to_date(:EVENTDATE,'dd/mm/yyyy hh:mi:ss am'),to_date(:FIRSTIN,'dd/mm/yyyy hh:mi:ss am'),:READER,:IP,:TYPE)"

oracmd.Parameters.Add(":CARDHOLDERID", OracleType.VarChar).Value = "" & IBSSDR.Item("ID") & ""
oracmd.Parameters.Add(":EVENTDATE", OracleType.DateTime).Value = ("" & IBSSDR.Item("EVENT_DATE").ToString & "")
oracmd.Parameters.Add(":FIRSTIN", OracleType.DateTime).Value = ("" & IBSSDR.Item("EVENT_DATE").ToString & "")

            oracmd.Parameters.Add(":READER", OracleType.VarChar).Value = "" & IBSSDR.Item("DEVICE\_ID") & ""
            oracmd.Parameters.Add(":TYPE", OracleType.VarChar).Value = "CHECKIN"

      

            oracmd.CommandType = CommandType.Text

...thank you

Dave Kreskowiak

You're trying to convert date strings being passed to the parameters to date values in the SQL VALUES clause. Don't do that because all you have to do is pass in DateTime values WITHOUT using "to_date" in the SQL. And what's with the "" & garbage? Get rid of stuff. It's useless, unless you want to make the code harder to read and debug.

oracmd.CommandText = " INSERT INTO ASIS.TIMECLOCK_DATE(TD_CARDHOLDER_ID,TD_EVENT_DATE,TD_FIRST_IN,TD_IN_READER,TD_IP_CONTROLLER,TD_TYPE) " & _
" VALUES (:CARDHOLDERID, :EVENTDATE, :FIRSTIN, :READER, :IP, :TYPE)"

oracmd.Parameters.Add(":CARDHOLDERID", OracleType.VarChar).Value = IBSSDR.Item("ID") oracmd.Parameters.Add(":EVENTDATE", OracleType.DateTime).Value = IBSSDR.Item("EVENT_DATE")
oracmd.Parameters.Add(":FIRSTIN", OracleType.DateTime).Value = IBSSDR.Item("EVENT_DATE")
oracmd.Parameters.Add(":READER", OracleType.VarChar).Value = IBSSDR.Item("DEVICE_ID")
oracmd.Parameters.Add(":TYPE", OracleType.VarChar).Value = "CHECKIN"

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

Victor Nijegorodov

Where is the

oracmd.Parameters.Add(":IP", ...)

:confused:

kerek2

Sir,

The datetime value is taking form another database (Firebird) using Datareader(IBSDDR.item("")), so i don't need to convert to_date when insert into ORacle sir?...Plz guide me, thanks in advanced Smile | :)

Dave Kreskowiak

OK, one last time. THERE IS NO SUCH THING AS A FORMAT FOR A DATETIME VALUE IN A DATABASE. ALWAYS use parameters to pass values into a query string. NEVER use string concatenation to build SQL queries.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak