Results 1 to 4 of 4

Thread: DateTimePicker syntax error

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2013
    Posts
    27

    DateTimePicker syntax error

    I am trying to do an SQL Select Query by using the dates supplied by two DateTimePickers to export data from a SQL Server to an Excel Spreadsheet.
    When I run the app, I get the error...
    "Line1: Incorrect syntax near 'Tuesday, November 12, 2013 AND Wednesday, November 13, 2013'. "

    The query is being run on an SQL 2000 server, and the DateTime Format on the server is MM/dd/yyyy hh:mm:ss tt in the record.

    I have tried doing a DateTimePickerFormat

    Code:
    DateTimePicker1.Format = DateTimePickerFormat.Custom
    DateTimePicker1.CustomFormat = "MM/d/yyyy hh:mm:ss tt"
    DateTimePicker2.Format = DateTimePickerFormat.Custom
    DateTimePicker2.CustomFormat = "MM/d/yyyy hh:mm:ss tt"
    Which gives the same error in 11/12/2013 02:00:00 PM 11/13/2013 02:00:00 PM

    I have Tried

    Code:
    DatePicker1.Format = DateTimePickerFormat.Custom
    DateTimePicker1.CustomFormat = "MM/d/yyyy"
    DateTimePicker2.Format = DateTimePickerFormat.Custom
    DateTimePicker2.CustomFormat = "MM/d/yyyy"Which gives the same error in 11/12/2013 AND 11/13/2013
    Here is my current Select code...

    Code:
    Dim dataAdapter As New SqlClient.SqlDataAdapter()
    Dim dataSet As New DataSet
    Dim command As New SqlClient.SqlCommand
    Dim datatableMain As New System.Data.DataTable()
    Dim connection As New SqlClient.SqlConnection
    
    DateTimePicker1.Format = DateTimePickerFormat.Custom
    DateTimePicker1.CustomFormat = "MM/d/yyyy"
    DateTimePicker2.Format = DateTimePickerFormat.Custom
    DateTimePicker2.CustomFormat = "MM/d/yyyy"
    
    Dim dt1 As String = DateTimePicker1.Text
    Dim dt2 As String = DateTimePicker2.Text
    
    connection.ConnectionString = "Data Source=SQL01_2000;database=App_DB;uid=sa;pwd=sa"
    command.Connection = connection
    command.CommandType = CommandType.Text
    command.CommandText = "Select * from LabelCheck WHERE [Date1] BETWEEN '" & dt1 & " AND " & dt2 & "'"
    dataAdapter.SelectCommand = command
    What am I missing?

  2. #2
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    Re: DateTimePicker syntax error

    Nothing to do with the dtp, you just don't know how to write sql .

    Have you seen what is actually in this line? command.CommandText
    Quotes would need to go before and after both the date values. Of course it would be suggested you use parameters instead anyway.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2013
    Posts
    27

    Re: DateTimePicker syntax error

    So something like this then...

    Code:
    connection.ConnectionString = "Data Source=SQL01_2000;database=App_DB;uid=sa;pwd=sa"
    command.Connection = connection
    command.CommandType = CommandType.Text
    command.CommandText = "Select * from LabelCheck WHERE Date1 BETWEEN (@date1) AND (@date2)"
    command.Parameters.Add(New SqlParameter("@date1", DateTimePicker1.Value.Date))
    command.Parameters.Add(New SqlParameter("@date2", DateTimePicker2.Value.Date))
    dataAdapter.SelectCommand = command
    I always forget about parameters...

  4. #4
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,593

    Re: DateTimePicker syntax error

    Suggestion: do not use command or connection as your identifiers.

    I think the way you coded it should use command.Parameters.AddWithValue()
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width