Android Question Save Date and Time in MSSQL

[DataProtec]

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

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 & ")")

 

[rraswisak]

just my guess, try to change the date format with "yyyy-mm-dd"

and when inserting to database use apostrophe:

msSQL.ExecNonQuery("insert into CheckInOut_Table (UserID, CheckIn) Values(51, '" & CheckIn & "')")

 

[DataProtec]

Hi
Yes thats it, what makes the difference, why don't work without apostrophe.
many thanks again

 

[OliverA]

I use the following setup to store dates (with times) in MS SQL. All other attempts seem to cause issues:

       '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

msSql.ExecNonQuery2("insert into CheckInOut_Table (UserID, CheckIn) Values(?,?)", Array As Object(56, timeString))

 

[rraswisak]

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)

 

[OliverA]

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.

 

[rraswisak]

But does that hold true for parameterized queries to? Using parameterized queries often eliminates the need for quoting.

Agree and that was correct, i just following OP code

 

[DonManfred]

@DataProtec needs to learn how sql in b4x works. Again i suggest to watch some videos from erels collection even if he told he did watched them all.

Parametrized queries should be prefered and is the suggested way to do it.

Another treasure are Klaus Booklets:
B4X Booklets V1.4: Getting Started, Basic Language, IDE, Visual Designer, CustomViews etc.

 

[DataProtec]

Ok, i Will public how i did it finaly and Works perfect
This is to store in MSSQL DB

    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)

 

[DonManfred]

You should use ExecNonQuery2. No need to care about any quotes

 

[sorex]

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

'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

 

[DataProtec]

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

Wait For msSQL_Ready (Success As Boolean)
        If Success = True Then
            Log("True")
        Else
            Log("False")
        End If
'** but no result

 

[OliverA]

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.

 

[DataProtec]

Hi OliverA
Sorry, i have 2 different statments, Insert and Update

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

 

[OliverA]

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.

 

[DataProtec]

if i try as i found in the fórum

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

 

[OliverA]

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.

 

[DataProtec]

Ok, Will do it
Thanks