• Top Members
    Reps
    Posts
  • 834 Replies
    2585 Replies
  • 716 Replies
    2025 Replies
  • 331 Replies
    1928 Replies

Adding multi dates into a sqlite db?

Please LOCK your topics once you have found the solution to your question so we know you no longer require help with your query.
User avatar
Dummy1912
VIP - Donator
Posts: 1965

Adding multi dates into a sqlite db?

Sun Nov 27, 2016 11:28 am

Hello,

Can anyone helping me with this part
i like to get multi dates into our db

for example i have 2 datetimepickers when i select the first mdtpmultiplyStart 2017-11-19
and the mdtpmultiplyEnd like 2019-11-19

my code:

Code: Select all

Database.modifyDatabase("INSERT INTO scheduler VALUES (null," & Util.quote(SetEmployeeID.ToString) & ", " & Util.quote(mdtpmultiplyStart.Value.ToString("MMMMdyyyy")) & ", " & Util.quote(mdtpmultiplyEnd.Value.ToString("MMMMdyyyy")) & ", " & Util.quote(SetShiftstart) & ", " & Util.quote(SetShiftend) & ", " & Util.quote(GetEventID) & ", " & Util.quote("") & ", " & Util.quote(txtremarks.Text) & ", " & Util.quote(monthid) & ", " & Util.quote(yearid) & ");")
so how can i add the dates from 2017-11-19 till 2019-11-19 into our db?

i don't have a clue... even searched for it but without success

Thank you

Dummy ;)

User avatar
CodenStuff
Site Admin
Posts: 4373

Re: Adding multi dates into a sqlite db?

Sun Nov 27, 2016 1:43 pm

Are you trying to enter every single date between those two dates in to separate fields in the database, example:

2017-11-19 - 2017-11-20 - 2017-11-21 - 2017-11-22 - 2017-11-23 ...and so on until 2019-11-19

If so can you explain why you need to do this? :duh;

That would be a lot of dates to be adding to the database and it would be a lot easier/faster to have just a start date and an end date and work with those when outputting the data to display tot he user ect.
Welcome to CodenStuff.com Learn Code, Love Code. Thank you for being a member of the community.

User avatar
Dummy1912
VIP - Donator
Posts: 1965

Re: Adding multi dates into a sqlite db?

Sun Nov 27, 2016 1:53 pm

Haha you're funny :p

well yes there are 2 columns with start and end date
i think we only need the start and end date 2017-11-19 - 2019-11-19
i have a calendar so when you load the data into it it has to fill all the data from every date on the calendar
from 2017-11-19 till 2019-11-19

when i scroll into the months of the calendar it has to show all the dates and info

so no idea if its possible to work only with 2 dates to fill out

just think of a scheduler :) kind of way.

User avatar
CodenStuff
Site Admin
Posts: 4373

Re: Adding multi dates into a sqlite db?

Sun Nov 27, 2016 2:34 pm

Erm yeah lol I'm not too sure but you could get all the dates between the start and end dates using something similar to:

Code: Select all

        Dim StartDate = New Date(2017, 11, 19)
        Dim EndDate = New Date(2019, 11, 19)
        Do While (StartDate <= EndDate)
            'Do whatever you need to do here to use the date data
            'I guess you would query the database from each date matching: StartDate.Date.ToString("yyyy-MM-dd")
            '
            '
            'Add 1 day to the date and continue to do what you're doing above
            StartDate = StartDate.AddDays(1)
        Loop
Yes/No? :duh; lol
Welcome to CodenStuff.com Learn Code, Love Code. Thank you for being a member of the community.

User avatar
Dummy1912
VIP - Donator
Posts: 1965

Re: Adding multi dates into a sqlite db?

Sun Nov 27, 2016 2:55 pm

Hmmm

if you see my loading code i guess it will be a little harder then you let me show :D

this is the part where we load the calendar

Code: Select all

        us.ReZeroLabels()
        AddHandler us.YearChanged, AddressOf us.YearChange
        AddHandler us.MonthChanged, AddressOf us.MonthChange
        us.CurrentYear = us.dtNow.Year
        us.CurrentMonth = us.dtNow.Month
        us.ReloadCal(us.dtNow)
calendar loading the data without the multidates because no idea how to add:

Code: Select all

 Try
            For Each itm As Control In EnumerateAllControls(Me)
                For Each c As Control In itm.Controls
                    If TypeOf c Is DateLabel Then

                            Dim attributes As String = "scheduler.setdate,scheduler.StartTime,scheduler.EndTime, employees.name,employees.firstname,colors.statuscolor,colors.defaultstatus,scheduler.note Relation"

                            Dim whereClause As String = "scheduler.id='" & SetEmployeeID.ToString & "' AND scheduler.setdate ='" & LabelMonth.Text & DayNumber & LabelYear.Text & "'"

                            Dim fromClause As String = "left JOIN employees ON employees.employeeid = scheduler.id left JOIN colors ON colors.colorid = scheduler.eventid"

                            Dim empls As SQLiteDataReader = Database.executeQuery("SELECT " & attributes & " FROM scheduler " & fromClause & " WHERE " & whereClause & " ;")

                            While empls.Read() = True


                                Dim items As String() = New String(8) {}

                                items(0) = empls.GetValue(0).ToString 'setdate
                                items(1) = empls.GetValue(1).ToString 'StartTime
                                items(2) = empls.GetValue(2).ToString 'EndTime

                                items(3) = empls.GetValue(5).ToString 'color
                                items(4) = empls.GetValue(6).ToString 'defaultevent
                                items(5) = empls.GetValue(7).ToString 'note

                                If Not items(0).ToString = String.Empty Then

                                    Dim str As [String] = items(3).ToString
                                    Dim clrConverter As New ColorConverter()
                                    Class1.newcol = DirectCast(clrConverter.ConvertFromString(str), Color)

                                    CType(c, DateLabel).Description = items(5).ToString
                                    CType(c, DateLabel).EventColor = Class1.newcol
                                End If
                            End While
                            empls.Close()
                        End If
                    End If
                Next
            Next
        Catch ex As Exception
        End Try

Post Reply

Return to “Tutorial Requests”