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 "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 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 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... 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?
-
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 "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 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 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... 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?
- Do not store dates as strings. 1) Use a parameterized query.
-
- Do not store dates as strings. 1) Use a parameterized query.
so, something like this then... 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
-
so, something like this then... 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
Yes.
-
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 "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 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 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... 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?
moordoom wrote:
connection.ConnectionString = "Data Source=SQL01_2000;database=App_DB;uid=sa;pwd=sa"
These notes may be a little "off topic", but you should consider them: - Are you still using SQL Server 2000? (maybe I was mislead by the server name...). - using the password "sa" for the user "sa" is easy to remember, but a fun for an intruder. - do not access the database as sa, unless you really need to do so. Better create a user for the tasks required by your application.
-
moordoom wrote:
connection.ConnectionString = "Data Source=SQL01_2000;database=App_DB;uid=sa;pwd=sa"
These notes may be a little "off topic", but you should consider them: - Are you still using SQL Server 2000? (maybe I was mislead by the server name...). - using the password "sa" for the user "sa" is easy to remember, but a fun for an intruder. - do not access the database as sa, unless you really need to do so. Better create a user for the tasks required by your application.
Bernhard Hiller wrote:
- using the password "sa" for the user "sa" is easy to remember, but a fun for an intruder.
I hope he replaced the password before he posted the code here. At least this is what I'd suggest to everyone, because anything else would be plain stupid :~:~
The busier I am, the more I procrastinate. - Chris "The Mighty" Maunder
-
Bernhard Hiller wrote:
- using the password "sa" for the user "sa" is easy to remember, but a fun for an intruder.
I hope he replaced the password before he posted the code here. At least this is what I'd suggest to everyone, because anything else would be plain stupid :~:~
The busier I am, the more I procrastinate. - Chris "The Mighty" Maunder
Yes, sa is not my user name and password. I just change all the info for the public post. And unfortunately yes, we are still using SQL 2000 SP 4. Because a previous implementer changed several stored procedures. But I will hopefully be changing that soon.
-
Yes, sa is not my user name and password. I just change all the info for the public post. And unfortunately yes, we are still using SQL 2000 SP 4. Because a previous implementer changed several stored procedures. But I will hopefully be changing that soon.
moordoom wrote:
Yes, sa is not my user name and password. I just change all the info for the public post.
:thumbsup:
moordoom wrote:
And unfortunately yes, we are still using SQL 2000 SP 4. Because a previous implementer changed several stored procedures. But I will hopefully be changing that soon.
I'd recommend it - Because otherwise things tend to go wild if you are going to be forced into a change with a schedule which is set by Microsoft.
Veni, vidi, caecus