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?

Recommended Answers

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

Jump to Post

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

Jump to Post

All 6 Replies

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

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

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

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.