How to pass a date time value to SQL without a conversion error-CodePudding

I'm doing a windows form using Visual Basic.NET, SQL Server 2016, Visual Studio 2017.

I have been trying to fix this problem and already tried set dateformat mdy in SQL Server Management Studio query, but the dates on the table I have are still in this format: 2022-07-17 00:00:00.000. Does this have anything to do with this error when trying to insert something while running the project?

Everyone says stuff along the line "datetime doesn't work with yy/mm/dd or dd/mm/yy, use mm/dd/yy instead". But nobody says how you actually change/fix it in the database or Visual Studio.

I never found this error while using MySQL when I was studying and doing stuff on other languages, so this datetime thing is really getting desperate. Any insight on how to actually fix this error is greatly appreciated.

Code:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim someid As Integer = TextCode.Text
            Dim descri As String = TextDescription.Text
            Dim somedate As DateTime = DateTimePickerinsert.Text
            Dim value As String = TextValue.Text
            Dim stock As String = TextStock.Text
            Dim warehouse As String = ComboWarehouse.Text
    
            con.Open()
            Dim command As New SqlCommand("Insert into Item values('" & someid & "','" & descri & "','" & somedate & "','" & value & "','" & stock & "','" & warehouse & "')", con)
            command.ExecuteNonQuery()
            con.Close()
            MessageBox.Show("Inserted succesfully")
            LoadDataInGrid()
        End Sub

I get

System.Data.SqlClient.SqlException: 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.' error on the line: command.ExecuteNonQuery()

CodePudding user response：

You should use proper parameterization, keeping your dates as actual DateTime values, rather than strings, likewise for numbers. Otherwise you will get SQL injection problems, this is not just a security issue but also about correctness.

The parameter values should be cast to the correct type before you send them, and the parameter objects should be declared with the correct SqlDbType and precision/length also.

You should also create and dispose your connection object, rather than keeping a global connection open, which is wasteful. Automatic connection pooling will ensure efficient usage of available connections.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim query As String = "
INSERT INTO Item (someid, descri, somedate, value, stock, warehouse)
VALUES (@id, @description, @date, @value, @stock, @warehouse)
"
    Dim someid As Integer = Integer.Parse(TextCode.Text)
    Dim somedate As DateTime = DateTimePickerinsert.Value
    Dim value As Decimal = Decimal.Parse(TextValue.Text)
    Dim stock As Integer = Integer.Parse(TextStock.Text)
    
    Using con As new SqlConnection("YourConnectionString"),
        command As New SqlCommand(query, con)
        command.Parameters.Add("@id", SqlDbType.Int).Value = someid
        command.Parameters.Add("@description", SqlDbType.VarChar, 100).Value = TextDescription.Text
        command.Parameters.Add("@date", SqlDbType.DateTime).Value = somedate
        command.Parameters.Add("@value", SqlDbType.Decimal).Value = value
        command.Parameters.Add("@stock", SqlDbType.Int).Value = stock
        command.Parameters.Add("@warehouse", SqlDbType.VarChar, 50).Value = ComboWarehouse.Text
        con.Open()
        command.ExecuteNonQuery()
    End Using
    MessageBox.Show("Inserted succesfully")
    LoadDataInGrid()
End Sub

As far as viewing the results in SSMS: datetime values don't have an inherent format. SSMS will have a default way of displaying them, but you can show them any way you like by converting them using CONVERT, or in VB using ToString

CodePudding user response：

You may run into many issues while using strings as dates. If you are connecting to a stored procedure or just executing SQL via SqlClient or ODBC, one way to fix this error is to use Cast in your SQL string to convert the date string to something that the server will understand. ex:

Insert Into MyTable (MyID, MyDate) Values (@MyID, Cast(@MyDate as datetime));

or,

Insert Into MyTable (MyID, MyDate) Values (123, Cast('2022-03-14 14:12:00' as datetime));

It will be more forgiving on different formats that you might use.