|
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
|
|
|
|
|
|
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?
|
|
|
|
|
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.
|
|
|
|
|
ooooo i get it, already try to other database like SQL db it can work... maybe oracle have restricted....thank you for idea
|
|
|
|
|
Ok sir thank for the idea it's done by this
<pre> 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 
|
|
|
|
|
|
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 :
<pre> 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
|
|
|
|
|
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
|
|
|
|
|
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 
|
|
|
|
|
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.
|
|
|
|
|
Hi Sir
When i'm using Parameter got this error :-
ORA-01008: not all variables bound
Can u advise me what wrong..
<pre> 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)"
<pre>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
|
|
|
|
|
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"
|
|
|
|
|
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 | :)
|
|
|
|
|
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.
|
|
|
|
|
Where is the
oracmd.Parameters.Add(":IP", ...) 
|
|
|
|
|
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!
|
|
|
|
|
Thank you for advised.
|
|
|
|
|
Sorry, this always seems to happen after I post but I think I get it now. I'm trying to assign a value to a class. It compiles if i do this:
Private Sub ab(Optional ByVal val As EncryptKey = Nothing)
end Sub
I was confused by this:
Private Sub ab(Optional ByVal val As String = "")
end Sub
I don't know how String is defined to be able to accept this but what I'm doing won't work.
Sorry again for the ugly post.
Sorry about the subject line; didn't know how to describe my problem.
Through searching around the internet i've come up with this class to be able to process a string value.
Class EncryptKey
Private _string As String = String.Empty
Public Sub New(ByVal value As String)
' Remove white space
Me._string = value.Trim()
End Sub
Public Shared Widening Operator CType(ByVal value As String) As EncryptKey
Return New EncryptKey(value)
End Operator
Public Overrides Function ToString() As String
Return _string
End Function
Public Function Length() As Integer
Return Me._string.Length
End Function
Public Function IsEmpty() As Boolean
Return (_string.Length = 0)
End Function
Public Shared Operator +(ByVal s1 As EncryptKey, ByVal s2 As EncryptKey) As EncryptKey
' Concat and remove white space
Dim temp As String = (s1._string + s2._string).Trim()
Return New EncryptKey(temp)
End Operator
End Class
The class is working except for this case:
Private Sub ab(Optional ByVal val As EncryptKey = "")
' Results in this error for the Optional assigment:
'error BC30060: Conversion from 'String' to 'EncryptKey' cannot occur in a constant expression.
End Sub
Is there a way to make the class able to accept this assignment as an Optional parameter?
Thank you
-- modified 30-Sep-20 12:34pm.
|
|
|
|
|
Hi sir
I'm try to fetch the early date value from my event list using MIN but cannot.
Here my code :
<pre>IBSSCMD.CommandText = "SELECT c.NAME AS NAME,c.cardholderid AS cardholderid, c.EMAIL AS EMAIL,A.PRIORITY, MIN(a.eventdate) AS EVENTDATE " & _
"FROM EVENT a , CARDHOLDER c , COMMCHANNEL h" & _
" WHERE(a.CARDID = c.CARDID And a.CARDHOLDERID = c.CARDHOLDERID)" & _
" AND a.COMMCHANNELID = h.COMMCHANNELID AND a.EVENTDATE >= CAST('TODAY' AS DATE) AND a.EVENTMSG = 'Access Granted' " & _
" AND a.PRIORITY IS NULL " & _
"group by c.NAME,c.cardholderid,c.email,A.PRIORITY ;"
After select I will update rows for Flag :
<pre> Dim FBCMD As New FbCommand
FBCMD = New FbCommand(" UPDATE EVENT SET PRIORITY='1' " & _
"WHERE CARDHOLDERID ='" + IBSSDR.Item("CARDHOLDERID") + "' " & _
"AND EVENTDATE >= CAST('TODAY' AS DATE) " & _
"AND EVENTMSG = 'Access Granted'", IBSSCN)
Using Console Application, the problem is when running / repeating the command, the result still get the lastest eventdate not MIn(eventdate).
anyone can teach n guide me?
Tq
|
|
|
|
|
|
ok tq sir, this not sql injection, tq for support
|
|
|
|
|
Yes it is. You are concatenating a parameter value directly into the query string.
If you think it's not a SQL Injection vulnerability, then you haven't understood what SQL Injection is, nor how dangerous it is.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Although you're using VB.Net, this is not a VB.Net question. Suggest you post under Database & Sysadmin / Database as your issue is with SQL. You should also give us some idea as to what IBSSDR may contain (presumably a datareader from another query), and probably the table definitions and example data too. For instance, will your query (IBSSCMD) be returning a single row or multiple rows?
|
|
|
|
|
You're selecting where a.PRIORITY IS NULL and also grouping on PRIORITY. Code smell.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
