Comversion failed when converting datetime from character string

Question

i made a button that would input the current date and time into a database in sql

Dim curDate As DateTime = DateTime.Now
Dim regDate As String = curDate.ToString("yyyy-MM-dd HH:mm:ss")
qr = "Insert into sales (SaleDate) Values ('" & regDate & "')

it worked on another button that's on another form but i got this error on a diff button that's on a diff form

i tried doing this

qr = "Insert into sales (SaleDate) Values ('" & DateTime.Now & "')

but it didn't work

Don't convert the `Date` to a `String`. Use the `Date`. Learn how to use parameters in ADO.NET code. You should not be converting anything to a `String` unless you need to and you never need to with ADO.NET. — jmcilhinney, Dec 28 '19 at 06:50

score 1 · Accepted Answer · answered Jan 01 '20 at 09:47

This was a good solution in a previous post.

The possible reason is that the date is involved, that is, the data you use to insert the data DateTime.Now () is not the required format. It is Merely because you are trying to store wrong data that is not acceptable to the system. Try using ISO-8601 date format, which is the best option for SQL Sever.

You can see this document for CAST and CONVERT (Transact-SQL).

gkan 12 · Answer 2 · 2019-12-28T07:53:41.870

0

your button

            cmd.CommandText = "insert into sales (Date,Time) values (@Date,@Time)"
            cmd.Parameters.AddWithValue("@Date", DateTime.Now.ToString("dd MMMM,yyyy"))
            cmd.Parameters.AddWithValue("@Time", TimeOfDay.ToString("h:mm:ss tt"))

edited Dec 28 '19 at 07:53

answered Dec 28 '19 at 07:31

gkan 12

39
7

the date doesn't generate from the textbox, it automatically inputs into the database – Jhapz Mendoza Dec 28 '19 at 07:44
i already did, the current date and time would be stored in SaleDate that has a datatype of datetime – Jhapz Mendoza Dec 28 '19 at 07:57
what you need explain in details – gkan 12 Dec 28 '19 at 07:59
1

[Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) – JohnyL Dec 28 '19 at 09:35
@gkan12 Sending anydesk is not the Stack Overflow way of doing things. Private communication is of no value to future readers. – Mary Dec 28 '19 at 13:10
@gkan12 Why did you change a single field `SalesDate` into two fields? You have also arbitrarily changed the format of the date portion. – Mary Dec 28 '19 at 13:16

score 0 · Answer 3 · answered Dec 28 '19 at 13:07

Database objects need to be closed and disposed. Using...End Using blocks take care of this for you even if there is an error.

I had to guess what kind of database you are using. If it is not Sql Server then you need to change the types of the database objects. For example, for Access it would be OleDbConnection etc..

If SalesDate is not a DateTime datatype in the database, it should be.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand("Insert into sales (SaleDate) Values (@regDate);", cn)
        cmd.Parameters.Add("@regDate", SqlDbType.DateTime).Value = DateTime.Now
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

Comversion failed when converting datetime from character string

3 Answers3