Add a week to a date without generating random values.

Question

1.00/5 (2 votes)

See more:

I'm currently making an event program in VB.NET where the user can add daily/weekly/monthly/yearly events to a database.
Their are two options :
-Adding the occurrence x times to a StartDate.
(http://i.imgur.com/LiRrZYy.png[^])
-Adding the occurrence between a StartDate and an EndDate.
(http://i.imgur.com/gC6bcSt.png[^])
When i'm adding the weeks from 2 april until 29 of may / 2 april x times it generates some random values in it. Is there any way of fixing this ?
This is my code :

VB

Case rbnWeekly.Checked 'Weekly mode with Enddate and StartDate
                    If rbnStartEnd.Checked = True Then
                        Dim startDate As Date = dtpStartDate.Text
                        Dim endDate As Date = dtpEndDate.Text
                        Dim currDate As Date = startDate
                        Do While (currDate < endDate)
                            SQL = String.Format("INSERT INTO tblManual (StartDate, EndDate, UniqueID) VALUES (#{0}#, #{1}#, '{2}')", currDate, endDate, id)
                            scmdRecurrence.CommandText = SQL
                            Valueee += 1
                            scmdRecurrence.ExecuteNonQuery()
                            currDate = currDate.AddDays(7)
                        Loop
                        MsgBox(Valueee.ToString & " records toegevoegd")
                    Else 'Weekly mode with x times
                        Dim aantal As Integer = txtHerhaling.Text
                        Dim startDate As Date = Date.Today
                        Dim endDate As Date = startDate.AddDays(aantal * 7)
                        Dim currDate As Date = startDate
                        Do While (currDate < endDate)
                            SQL = String.Format("INSERT INTO tblManual (StartDate, EndDate, UniqueID) VALUES (#{0}#, #{1}#, '{2}')", currDate, endDate, id)
                            scmdRecurrence.CommandText = SQL
                            Valueee += 1
                            scmdRecurrence.ExecuteNonQuery()
                            currDate = currDate.AddDays(7)
                        Loop
                        MsgBox(Valueee.ToString & " records toegevoegd")
                    End If

Kind regards !

Posted 23-Apr-15 10:09am

Member 11385046

Add a Solution

Comments

Richard Deeming 23-Apr-15 16:29pm

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Philippe Mori 5-May-15 19:19pm

It is not random values but values where the day and month are swapped.

2 solutions

Add a Solution

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

Gideon van Dyk · Answer 1 · 2015-04-23T16:05:00

As mentioned in the comments, your code is vulnerable to SQL injections, recommend you use parameters.

As for your problem, I think you need to adjust the code just slightly:

VB

Else 'Weekly mode with x times
     Dim aantal As Integer = txtHerhaling.Text
     Dim gebruik As Integer = 0
     Dim startDate As Date = Date.Today
     Dim endDate As Date = dtpEndDate.Text
     Dim currDate As Date = startDate
     Do While (currDate < endDate AndAlso gebruik < aantal)
         SQL = String.Format("INSERT INTO tblManual (StartDate, EndDate, UniqueID) VALUES (#{0}#, #{1}#, '{2}')", currDate, endDate, id)
         scmdRecurrence.CommandText = SQL
         Valueee += 1
         scmdRecurrence.ExecuteNonQuery()
         currDate = currDate.AddDays(7)
         gebruik += 1
     Loop
     MsgBox(Valueee.ToString & " records toegevoegd")
 End If

Philippe Mori · Answer 2 · 2015-05-05T13:18:00

Solution 2

Looking at the picture, we can easily see that the problem is related to the order MM/DD/YY vs DD/MM/YY. It look like the bad format is used whenever a date is valid in both cases.

By the way, if yoy would follows the recommendation given by other to use parameters, it might fix the problem.

Does the tables shown in images are written in the database by this code and displayed by a program that allows to view SQL table. Because otherwise, if it is your code that display the data, then the problem could also be a load time.

What is the type of your columns in the database?

Also check which culture is set for your database, for your computer and for the application.

You might have to explicitly set the proper culture or use specific format.

Update
If multiple threads are parsing or formatting dates, you also need to be sure that the same culture is used in all cases.

By the way, you are probably using one order while the database use another one and some code try to guess the format and it it is not valid try something else.

Posted 5-May-15 13:18pm

Philippe Mori

Updated 6-May-15 8:55am

v2

Comments

Member 11385046 6-May-15 1:51am

I'm not used to working with parameters, so I would like to stick with what I know so far. I'm using an access database and the screenhots are taken from MS Access. The type is set to text though, but as the handeling of the dates is taken care of within VB I don't see the problem there, or could it be ?I'll take a look to sync all the cultures together if it might be bugging because of that.
Current code :
<pre lang="vb">
Case rbnWeekly.Checked 'Weekly mode with Enddate and StartDate
If rbnStartEnd.Checked = True Then
Dim aantal As Integer = txtHerhaling.Text
Dim gebruik As Integer = 0
Dim startDate As Date = Date.Today
Dim endDate As Date = dtpEndDate.Text
Dim currDate As Date = startDate
Do While (currDate < endDate AndAlso gebruik < aantal)
SQL = String.Format("INSERT INTO tblManual (StartDate, EndDate, UniqueID) VALUES (#{0}#, #{1}#, '{2}')", currDate, endDate, id)
scmdRecurrence.CommandText = SQL
Valueee += 1
scmdRecurrence.ExecuteNonQuery()
currDate = currDate.AddDays(7)
gebruik += 1
Loop
MsgBox(Valueee.ToString & " records toegevoegd")
Else 'Weekly mode with x times
Dim aantal As Integer = txtHerhaling.Text
Dim gebruik As Integer = 0
Dim startDate As Date = Date.Today
Dim endDate As Date = startDate.AddDays(aantal * 7)
Dim currDate As Date = startDate
Do While (currDate < endDate AndAlso gebruik < aantal)
SQL = String.Format("INSERT INTO tblManual (StartDate, EndDate, UniqueID) VALUES (#{0}#, #{1}#, '{2}')", currDate, endDate, id)
scmdRecurrence.CommandText = SQL
Valueee += 1
scmdRecurrence.ExecuteNonQuery()
currDate = currDate.AddDays(1)
gebruik += 1
Loop
MsgBox(Valueee.ToString & " records toegevoegd")
End If</pre>

Philippe Mori 6-May-15 14:47pm

Don't put code in a comment like that. It is unreadable. Modify your question to add more precision.

Add a week to a date without generating random values.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Add a week to a date without generating random values.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us