Insert timestamp using datareader to oracle date

[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