Date PROBLEM in VB.NET

[kayclink]

Hello, i am getting a problem to generate a report based on a date. iam using vb.net 2005 and SQL 2000. here is my code;

myConnection = New SqlConnection("Data Source=CALVIN2009\DEV;Initial Catalog=TBFMS;Integrated Security=True;database = TBFMS")
            MyCommand.Connection = myConnection
 
            'If Main.ManifestDatagrid.SelectedRows.Count > 0 Then
 
            MyCommand.CommandText = "select *from Booking2 where BookingDate between convert(datetime,'" & Format(Me.DateTimePicker1.Value, "dd/MM/yyyy") & "',103) AND ""convert(datetime,'" & Format(Me.DateTimePicker1.Value.AddDays(1), "dd/MM/yyyy") & "',103"
 
 MyCommand.CommandType = CommandType.Text
            myDA.SelectCommand = MyCommand
            myDA.Fill(myDS, "Booking2")
 

it gives an error: "Unclosed quotation mark before the character string 'convert(datetime....and so on..

please help

[Plater]

Well you are doing a lot of ' and " and "" in that string.
I recomend switching to either a StringBuilder or a Parameter-ized command

EDIT:
Actually, just going through it I see a lot of missing characters.


Try this:

MyCommand.CommandText = string.Format("select * from Booking2 where BookingDate between convert(datetime,'{0}',103) AND convert(datetime,'{1}',103)", Me.DateTimePicker1.Value.ToString("dd/MM/yyyy"),Me.DateTimePicker1.Value.AddDays(1).ToString("dd/MM/yyyy"))