CODEWITHSUNDEEP
.netvb.netdatedatetime
Darren
Darren

Reputation: 53

Conversion From String To Type 'Date' Is Not Valid

I have 2 datetimepicker that allow user to choose the start date and end date. So after choosing the dates, user must click a button to run the sql query. So the output is filtered based on the chosen date.

However there are error when i declare the date as follows (this code below is put under the button click event) :

Dim startdate As DateTime = Format(DateTimePicker1.Value, "dd/MM/yyyy")
Dim enddate As DateTime = Format(Microsoft.VisualBasic.DateAdd(DateInterval.Day, 1, DateTimePicker2.Value), "dd/MM/yyyy")

query = "Select * from records where recorddate between '" & startdate & "' and '" & enddate & "'"

The error is here : 

Dim startdate As DateTime = Format(DateTimePicker1.Value, "dd/MM/yyyy")

The error is : 

Conversion from string "13/02/2016" to type 'Date' is not valid.

The sample date of my records are like this:

12/2/2016 7:28:26 PM

I don't know what i missed here. Please help. Thanks

Upvotes: 3

Views: 40951

Answers (2)

Ňɏssa Pøngjǣrdenlarp
Ňɏssa Pøngjǣrdenlarp

Reputation: 38875

There are several issues in your code.

Dim startdate As DateTime = Format(DateTimePicker1.Value, "dd/MM/yyyy")

If you hold the mouse over Format Intellisense will tell you that it returns a string. Since you cant assign a string result to a variable declared as DateTime, you get the error. If you turn on Option Strict the compiler will tell you about these.

Secondly, the query converts the dates to string also:

"...between '" & startdate & "' and '" & enddate & "'"

Ticks are not all-purpose SQL delimiters, they are a way of designating text/string data. SQL Parameters are safer and prevent accidental data type changes as well as SQL injection attacks. They also make the code easier to read. If you have a name column in your db, try to INSERT a name like Tim O'Brien, D'Angelo Barksdale or Betty's Cupcake Factory. The query will crash. SQL Parameters prevent this.

I have no idea which database, so I guessed Access. That doesn't matter because the DB Providers all work much the same: 

' Dim startdate As DateTime = DateTimePicker1.Value
Dim enddate As DateTime = DateTimePicker2.Value.AddDays(1)

Dim SQL = "Select * from records where recorddate between @p1 AND @p2"

Using dbCon As New OleDbConnection(connstr)
    Using cmd As New OleDbCommand(SQL, dbCon)
        dbCon.Open()
        cmd.Parameters.Add("@p1", OleDbType.DBDate).Value = startdate
        cmd.Parameters.Add("@p2", OleDbType.DBDate).Value = DateTimePicker1.Value

        dt = New DataTable
        dt.Load(cmd.ExecuteReader)
    End Using
End Using

Note This assumes the column type in the DB is Date and not string. The BETWEEN clause is unlikely to work with dates as string. If you want them to act as dates, save them as Date. You do not "need to convert to String in order to use it in SQL". Ever.

  • No processing is required for the Date variables. The NET providers know how to pass a DateTime type to the data base.
  • There is no real need to assign the DateTimePicker1.Value to a new variable, because .Value is a DateTime type.
  • The Using blocks declare and create the DB objects, then close and dispose of them to free resources
  • When defining the parameter, the code tells it to expect a date (OleDbType.DBDate), then the Value set is an actual date type.

With Access/OleDB it is important to set the parameter values in the exact same order as they appear in the SQL. OleDB allows named parameters but assigns the values in the order they appear in the SQL.

To emphasize that, MSDN (and others) encourage the use of ? in place of other forms ("@p1", "@firstname", "@DateOfBirth"). Especially in queries with more than a few columns, I like "@p1" because the numeral helps index the related column in the SQL and helps make sure they are in order (visually).

Finally, whether your BETWEEN clause works as you want will depend on several things including that DataType parameter. The dates from the DateTimePicker will include the time. In order to not to exclude some rows because the time portion is earlier than whatever you got from the DateTimePicker, you will need to use the correct one.

Upvotes: 2

John Pick
John Pick

Reputation: 5650

DateTimePicker.Value is of type DateTime, which you need to convert to String in order to use it in SQL:

Dim startdate as String = DateTimePicker1.Value.ToString("dd/MM/yyyy")

However, your database server may not be configured for that format. It is better to use the default format instead:

Dim startdate As String = DateTimePicker1.Value.ToString()
Dim enddate As String = Microsoft.VisualBasic.DateAdd(DateInterval.Day, 1, DateTimePicker2.Value).ToString()
query = "Select * from records where recorddate between '" & startdate & "' and '" & enddate & "'"

Upvotes: 1

Related Questions