hello
i got a small problem to save a Date and Time in the MSSQL DB, exactly when a button is pressed should Save "Now"
If i try to save the App crash with the Error
B4X:
In VB NET i do and Works perfect
"aCmd.Parameters.AddWithValue("@CheckIn", Now) "
'***In V4A i tried many option but don't get the proper result
'***My Code
Dim CheckIn As String
Dim dateNow As Long = DateTime.Now
DateTime.DateFormat = "dd/MM/yyyy"
DateTime.timeFormat = "hh:mm:ss"
Dim sCheckIn_Day As String = DateTime.Date(dateNow)
Dim sCheckIn_Time As String = DateTime.Time(dateNow)
Log("CheckIn " & sCheckIn_Day & " " & sCheckIn_Time) '*** gives the correct data 27/09/2019 02:52:16 as it is in the DB
CheckIn = sCheckIn_Day '& " " & sCheckIn_Time '** only save sCheckIn_Day gives 1900-01-01 00:00:00.000, both together chrash the APP
msSQL.ExecNonQuery("insert into CheckInOut_Table (UserID, CheckIn) Values(51, " & CheckIn & ")")
I use the following setup to store dates (with times) in MS SQL. All other attempts seem to cause issues:
B4X:
'Note: Before changing DateTime's date/time formats, save them
Dim dateFormat As String = DateTime.DateFormat
Dim timeFormat As String = DateTime.TimeFormat
DateTime.DateFormat = "yyyyMMdd"
DateTime.TimeFormat = "HH:mm:ss"
Dim timeNow As Long = DateTime.Now
'Note: Using SmartStrings to create the desired result
Dim timeString As String = $"${DateTime.Date(timeNow)} ${DateTime.Time(timeNow)}"$
Log($"timeString: ${timeString}"$)
'Note: The next two lines reset the DateTime formats to how they were before they were changed above
DateTime.DateFormat = dateFormat
DateTime.TimeFormat = timeFormat
Then, use parameterized query instead of non-parameterized query
B4X:
msSql.ExecNonQuery2("insert into CheckInOut_Table (UserID, CheckIn) Values(?,?)", Array As Object(56, timeString))
IMHO, even you are using B4A, all query you make is following MSSQL rule, if you don't put apostrophe the query will generate error. While using apostrophe database will recognize the value as date time data type (not string)
IMHO, even you are using B4A, all query you make is following MSSQL rule, if you don't put apostrophe the query will generate error. While using apostrophe database will recognize the value as date time data type (not string)
You're not showing enough code. What is before the Wait For? Where is the statement that sets up the Wait For? Plus, this is not related to the original question and needs to be its own thread.
Hi OliverA
Sorry, i have 2 different statments, Insert and Update
B4X:
sSQL = $"insert into CheckInOut_Table (UserID, RegisterType_ID, CheckIn) Values('${aktUserID}',1,CURRENT_TIMESTAMP)"$
msSQL.ExecNonQuery(sSQL)
'*** here i need a Result to act
sSQL = "Update CheckInOut_Table Set RegisterType_ID = '2', CheckOut = '" & CheckInOut & "'"
sSQL = sSQL & " Where ID = '" & nAktCheckInOut_ID & "'"
msSQL.ExecNonQuery(sSQL)
'*** and here as well
Neither are set up for Wait For. For an example (using MS SQL) that uses Wait For's see: https://www.b4x.com/android/forum/threads/microsoft-sql-query.109886/#post-686176. In your case (if you're wanting to use Wait For), you'll also need to use ExecNonQueryBatch, for which documentation is provided here: https://www.b4x.com/b4j/help/jsql.html#sql_execnonquerybatch. Maybe then you will finally use parameterized queries (ExecNonQueryBatch is set up to use parameterized queris) instead of trying to build your SQL statements from scratch. You're asking for SQL injection attacks with the code as it stands. Plus, this should be a different thread. If at all possible (language barrier may make this harder), please review all videos and documentation that @DonManfred has provided you with in several answers in this and other threads.
sSQL = $"insert into CheckInOut_Table (UserID, RegisterType_ID, CheckIn) Values('${aktUserID}',1,CURRENT_TIMESTAMP)"$
Dim Sf As Object = msSQL.ExecNonQuery(sSQL)
Wait For (Sf) SQL_ExecNonQueryComplete (Success As Boolean)
Get the Msg "Cannot asign void Value", in the Row with Dim Sf…. how can i set this
Is not a method that supports Wait For. I gave you the method you need to use and a link to it's documentation in my previous answer. Plus, this is not the same issue as the first post in this thread and should really be handled in a new thread. I will no longer answer questions in this thread unless it pertains to your first question (which is solved). Please watch some videos/read some documentation.