Page 1 of 1
Adding multi dates into a sqlite db?
Posted: Sun Nov 27, 2016 10:28 am
by Dummy1912
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 allDatabase.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 ;)
Re: Adding multi dates into a sqlite db?
Posted: Sun Nov 27, 2016 12:43 pm
by CodenStuff
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?
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.
Re: Adding multi dates into a sqlite db?
Posted: Sun Nov 27, 2016 12:53 pm
by Dummy1912
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.
Re: Adding multi dates into a sqlite db?
Posted: Sun Nov 27, 2016 1:34 pm
by CodenStuff
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?
lol
Re: Adding multi dates into a sqlite db?
Posted: Sun Nov 27, 2016 1:55 pm
by Dummy1912
Hmmm
if you see my loading code i guess it will be a little harder then you let me show
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