Please help me debug about datetimepicker. I think, the code is written correctly but its not. The program kee[s prompting ORA-01858: a non-numeric character was found where a numeric was expected

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim cmd As New OleDb.OleDbCommand
        'cnn.ConnectionString = ConfigurationManager.ConnectionStrings("mysys.My.MySettings.ConnectionString").ConnectionString()
        cnn.ConnectionString = "Provider=OraOLEDB.Oracle; Data Source=TRAVELMATE-PC/XE;User Id=cj;Password=me;"

        If Not cnn.State = ConnectionState.Open Then
            cnn.Open()
        End If

        cmd.Connection = cnn
        cmd.CommandText = "INSERT INTO room(tenant_id, room_number, room_name, bed_number)" & _
                          "VALUES ('" & Me.TextBox1.Text & "','" & Me.ComboBox4.Text & "','" & Me.ComboBox5.Text & "','" & Me.ComboBox6.Text & "')"

        cmd.CommandText = "INSERT INTO rental(date_to, date_from)" & _
                          "VALUES (TO_DATE('" & Me.DateTimePicker1.Text & "','dd-mon-yy'), TO_DATE('" & Me.DateTimePicker2.Text & "','dd-mon-yy'))"
        cmd.ExecuteNonQuery()
        cnn.Close()


        'MessageBox.Show("Data has been Added!")
        MsgBox("Data has been Added!", vbInformation + vbOKOnly, "Saved!")

        Dim intResponse As Integer
        If intResponse = vbOKOnly Then
            Me.Close()
            Form3.Show()
        End If



    End Sub

Instead of

 cmd.CommandText = "INSERT INTO rental(date_to, date_from)" VALUES (TO_DATE('" & Me.DateTimePicker1.Text & "','dd-mon-yy'), TO_DATE('" & Me.DateTimePicker2.Text & "','dd-mon-yy'))"

Try this:

cmd.CommandText = "INSERT INTO rental(date_to, date_from) VALUES (#" & DateTimePicker2.Text & "#, #" & DateTimePicker1.Text & "#)"

Edited 4 Years Ago by mikeybware

This article has been dead for over six months. Start a new discussion instead.