Android Question Save Date and Time in MSSQL

DataProtec

Member
Licensed User
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 & ")")
 

OliverA

Expert
Licensed User
Longtime User
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))
 
Upvote 0

rraswisak

Active Member
Licensed User
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)
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
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)
But does that hold true for parameterized queries to? Using parameterized queries often eliminates the need for quoting.
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Upvote 0

DataProtec

Member
Licensed User
Ok, i Will public how i did it finaly and Works perfect
This is to store in MSSQL DB
B4X:
    Dim CheckInOut 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)
    CheckInOut = sCheckIn_Day & " " & sCheckIn_Time
    
    Log("UserID " & aktUserID)
    Log("CheckInOut  " & CheckInOut)
        
        sSQL = "insert into CheckInOut_Table (UserID, RegisterType_ID, CheckIn)"
        sSQL = sSQL & " Values('" & aktUserID & "','1','" &  CheckInOut & "')"
        msSQL.ExecNonQuery(sSQL)
 
Upvote 0

sorex

Expert
Licensed User
Longtime User
is this some kind of attendance loggin?

then you're leaving options for cheaters as they can tweak their phone times before the insert.

you better use it like this if no time zone differences can happen

B4X:
'Dim CheckInOut 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)
    'CheckInOut = sCheckIn_Day & " " & sCheckIn_Time
   
    Log("UserID " & aktUserID)
    'Log("CheckInOut  " & CheckInOut)
       
        sSQL = $"insert into CheckInOut_Table (UserID, RegisterType_ID, CheckIn) Values('${aktUserID}',1,CURRENT_TIMESTAMP)"$
        msSQL.ExecNonQuery(sSQL)

you can even set the default value of CheckIn to CURRENT_TIMESTAMP then you can remove it from the insert query
 
Upvote 0

DataProtec

Member
Licensed User
Hi sorex
Thanks, i did not now how to do this with V4B, Will implement in my programm
Is there a way to get a wait for, i tried with but no exit
B4X:
Wait For msSQL_Ready (Success As Boolean)
        If Success = True Then
            Log("True")
        Else
            Log("False")
        End If
'** but no result
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Hi sorex
Thanks, i did not now how to do this with V4B, Will implement in my programm
Is there a way to get a wait for, i tried with but no exit
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.
 
Upvote 0

DataProtec

Member
Licensed User
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
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
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.
 
Upvote 0

DataProtec

Member
Licensed User
if i try as i found in the fórum
B4X:
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
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
ExecNonQuery
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.
 
Upvote 0
Top