Click here to Skip to main content
15,921,793 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
hi there Pros,

My question:
how to insert multiple data from variables and insert into only one (DateTime)field in a table?

My case:
I got separate declared datetime variables each of them carried (Year, Month, Day, Hour, Minute, & Second) and how to combine these variable's values into a field with datatype(datetime)
e.g.:
VB
Dim idwYear As Integer
Dim idwMonth As Integer
Dim idwDay As Integer
Dim idwHour As Integer
Dim idwMinute As Integer
Dim idwSecond As Integer



Please guide me!!
Posted
Comments
Sergey Alexandrovich Kryukov 25-Nov-13 21:25pm    
And what is the problem? I could not figure out what part may seem to be a problem... http://www.whathaveyoutried.com so far?
—SA

This would be trivial enough but why? If you want to store date/time, use data types related to time, not integers:
http://technet.microsoft.com/en-us/library/ms186724.aspx[^],
http://www.w3schools.com/sql/sql_datatypes.asp[^].

—SA
 
Share this answer
 
Comments
donaldliaw87 25-Nov-13 21:34pm    
i was getting values from a device and this device's sdk (datetime) designed to get date and time separately, therefore i have to declare and get the values separately as well!
Sergey Alexandrovich Kryukov 26-Nov-13 0:07am    
You tagged SQL and did not mention device at all. Anyway, pack this data in System.DateTime before you put to to database...
—SA
self solved: load values to a hidden label and insert into table~!

VB
If bIsConnected = False Then
            MsgBox("Please connect the device first", MsgBoxStyle.Exclamation, "Error")
            Return
        End If
        Dim sdwEnrollNumber As String = ""
        Dim idwVerifyMode As Integer
        Dim idwInOutMode As Integer
        Dim idwYear As Integer
        Dim idwMonth As Integer
        Dim idwDay As Integer
        Dim idwHour As Integer
        Dim idwMinute As Integer
        Dim idwSecond As Integer
        Dim idwWorkcode As Integer
        Dim idwErrorCode As Integer
        Dim iGLCount = 0
        Dim lvItem As New ListViewItem("Items", 0)
        Cursor = Cursors.WaitCursor
        lvLogs.Items.Clear()
        axCZKEM1.EnableDevice(iMachineNumber, False)
        If axCZKEM1.ReadGeneralLogData(iMachineNumber) Then
            While axCZKEM1.SSR_GetGeneralLogData(iMachineNumber, sdwEnrollNumber, idwVerifyMode, idwInOutMode, idwYear, idwMonth, idwDay, idwHour, idwMinute, idwSecond, idwWorkcode)
                iGLCount += 1
                lvItem = lvLogs.Items.Add(iGLCount.ToString())
                lvItem.SubItems.Add(sdwEnrollNumber)
                lvItem.SubItems.Add(idwVerifyMode.ToString())
                lvItem.SubItems.Add(idwInOutMode.ToString())
                lvItem.SubItems.Add(idwYear.ToString() & "-" + idwMonth.ToString() & "-" & idwDay.ToString() & " " & idwHour.ToString() & ":" & idwMinute.ToString() & ":" & idwSecond.ToString())
                Label4.Text = idwYear.ToString() & "-" + idwMonth.ToString() & "-" & idwDay.ToString() & " " & idwHour.ToString() & ":" & idwMinute.ToString() & ":" & idwSecond.ToString()
                lvItem.SubItems.Add(idwWorkcode.ToString())
                Dim sele As String = "SELECT * FROM datetimeframesubtbl WHERE datetimeframe = '" & Label4.Text & "'"
                Dim cmd1 As New SqlCommand(sele, connection)
                If connection.State = ConnectionState.Closed Then
                    connection.Open()
                End If
                Dim reader As SqlDataReader = cmd1.ExecuteReader
                If reader.HasRows = False Then
                    reader.Read()
                    Dim insert1 As String = "INSERT INTO attlog (userid, inoutindicator, workcode) VALUES (@userid, @inoutindicator, @workcode)"
                    Dim insert2 As String = "INSERT INTO datetimeframesubtbl (datetimeframe, userid) VALUES (@datetimeframe, @userid)"
                    Dim insert3 As String = "INSERT INTO inoutmodetbl (mode, userid) VALUES (@mode, @userid)"
                    Dim cmd As New SqlCommand(insert1, connection)
                    cmd.Parameters.AddWithValue("@userid", sdwEnrollNumber)
                    cmd.Parameters.AddWithValue("@inoutindicator", idwVerifyMode)
                    cmd.Parameters.AddWithValue("@workcode", idwWorkcode)
                    Dim cmd2 As New SqlCommand(insert2, connection)
                    cmd2.Parameters.AddWithValue("@datetimeframe", Label4.Text)
                    cmd2.Parameters.AddWithValue("@userid", sdwEnrollNumber)
                    Dim cmd3 As New SqlCommand(insert3, connection)
                    cmd3.Parameters.AddWithValue("@mode", idwInOutMode)
                    cmd3.Parameters.AddWithValue("@userid", sdwEnrollNumber)
                    cmd.ExecuteNonQuery()
                    cmd2.ExecuteNonQuery()
                    cmd3.ExecuteNonQuery()
                    reader.Close()
                    connection.Close()
                Else
                    reader.Close()
                End If
            End While
        Else
            Cursor = Cursors.Default
            axCZKEM1.GetLastError(idwErrorCode)
            If idwErrorCode <> 0 Then
                MsgBox("Reading data from terminal failed,ErrorCode: " & idwErrorCode, MsgBoxStyle.Exclamation, "Error")
            Else
                MsgBox("No data from terminal returns!", MsgBoxStyle.Exclamation, "Error")
            End If
        End If
        axCZKEM1.EnableDevice(iMachineNumber, True)
        Cursor = Cursors.Default
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900