Trying to insert date from DateTimePicker1.text

[mikejs]

Hi I am trying to submit a date value from a DateTimePicker1 using short date format 05/02/2011 I get an error on submission

.NetSqlClient Data Provider:conversion failed when converting date and/or time from charecter to string

this is my insert

cmd1 = conn.CreateCommand
                        cmd1.CommandText = "INSERT INTO tenant(firstName, lastName, Email, phone, dob, rentPaid, rent, dateIn, deposit, service, rentDueDate, property,notes, previous) VALUES('" & Trim(TextBoxFirstName.Text) & "','" & Trim(TextBoxLastName.Text) & "','" & Trim(MaskedEditBoxEmail.Text) & "','" & Trim(TextBoxPhone.Text) & "','" & Trim(MaskedEditBoxDob.Text) & "','" & Trim(MaskedEditBoxRentPaid.Text) & "','" & Trim(TextBoxRent.Text) & "','" & (DateTimePicker1.Text) & "','" & Trim(MaskedEditBoxDeposit.Text) & "','" & Trim(MaskedEditBoxService.Text) & "','" & (TextBoxRentDue.Text) & "','" & Trim(RadDropDownList1.Text) & "','" & Trim(TextBoxNotes.Text) & "','" & "True" & "')"
                        check = cmd1.ExecuteReader.RecordsAffected()

my db table

[t_id] [int] IDENTITY(1,1) NOT NULL,
	[firstName] [varchar](20) NOT NULL,
	[lastName] [varchar](20) NOT NULL,
	[email] [varchar](250) NULL,
	[phone] [numeric](18, 0) NULL,
	[dob] [date] NOT NULL,
	[rentPaid] [numeric](18, 0) NULL,
	[rent] [numeric](18, 0) NULL,
	[dateIn] [date] NOT NULL,
	[deposit] [numeric](18, 0) NULL,
	[service] [numeric](18, 0) NULL,
	[rentDueDate] numeric (18,0) NULL,
	[property] [varchar](10) NULL,
	[notes] [text] NULL,
	[existing] [bit] NULL,
	[previous] [bit] NULL,
 CONSTRAINT [PK_tennant] PRIMARY KEY CLUSTERED

cannot figure out why its not seeing it as a date?

[debasisdas]

You need to format the DateTimePicker1.Text before passing the same into database for inserting.

[Netcode]

the datatype on your dbase does not match that of the datetimepicker

[mikejs]

So I now have

cmd1.CommandText = "INSERT INTO tenant(firstName, lastName, Email, phone, dob, rentPaid, rent, dateIn, deposit, service, rentDueDate, property, notes, previous) VALUES('" & Trim(RadTextBoxFirstName.Text) & "','" & Trim(TextBoxLastName.Text) & "','" & Trim(MaskedEditBoxEmail.Text) & "','" & Trim(TextBoxPhone.Text) & "','" & Trim(RadTextBox4.Text) & "','" & Trim(MaskedEditBoxRentPaid.Text) & "','" & Trim(TextBoxRent.Text) & "','" & Trim(TextBox5.Text) & "','" & Trim(MaskedEditBoxDeposit.Text) & "','" & Trim(MaskedEditBoxService.Text) & "','" & (TextBoxRentDue.Text) & "','" & Trim(DropDownList1.Text) & "','" & Trim(TextBoxNotes.Text) & "','" & "True" & "')"

I am using

Private Sub RadDateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadDateTimePicker1.ValueChanged 
Dim ctrl As UI.DateTimePicker = CType(sender, UI.DateTimePicker)

        TextBoxRentDue.Text = ctrl.Value.Day.ToString()

        TextBox4.Text = ctrl.Value.Date.ToString()

    End Sub

    Private Sub DateTimePickerDob_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePickerDob.ValueChanged
        Dim ctrl As UI.DateTimePicker = CType(sender, UI.DateTimePicker)
        TextBox5.Text = ctrl.Value.Date.ToString()
    End Sub

still getting an error Conversion failed when converting date/and or time from character to string

[debasisdas]

you need to convert the string to date format as defined in your database table column.

[mikejs]

Thanks for the reply I am using this

Private Sub DateTimePickerDob_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePickerDob.ValueChanged
        DateTimePickerDob.CustomFormat = "MM/dd/yyyy"
        Dim ctrl As UI.RadDateTimePicker = CType(sender, UI.DateTimePicker)
        TextBox5.Text = ctrl.Value.Date()
    End Sub

is this what you mean?

how do I se the format in SQL 2008 I have set it to date how can I see what format it is looking for?

thanks

M

[mikejs]

Private Sub DateTimePickerDob_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePickerDob.ValueChanged
        DateTimePickerDob.CustomFormat = "MM/dd/yyyy"
        Dim ctrl As UI.RadDateTimePicker = CType(sender, UI.DateTimePicker)
        TextBox5.Text = ctrl.Value.Date()
    End Sub

I removed

Private Sub DateTimePickerDob_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePickerDob.ValueChanged
        DateTimePickerDob.CustomFormat = "MM/dd/yyyy"
        
    End Sub

works a treat thanks guys