I cannot insert data from asp.net into SQL Server

Question

I am trying to insert data from ASP.net webforms into SQL Server but I get an error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I don't understand this error, please help me find out what is what is this error.

Protected Sub Myinsert_Click(sender As Object, e As EventArgs) Handles Myinsert.Click
    Dim dt As New Date
    dt = DateAndTime.Now.ToString
    p = "Image/" + ImgPath.FileName.ToString
    Try
        cn.Open()
        cmd.Connection = cn
        cmd.CommandText = "INSERT INTO MyWebSite( img, type, title, short_para, full_para, datepost) VALUES('" & p & "','" & txtType.Text & "','" & txtTitle.Text & "','" & ParaTT.Text & "','" & Para.Text & "','" & dt & "')"

        cmd.ExecuteNonQuery()
        cn.Close()
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

End Sub

You should use parametrized query and you can specify the types. — Zaki
– Zaki, Commented Feb 20, 2015 at 16:42
That's usually a mismatch between the date format you're using and the date format the server expects. E.g. you might be sending 'dd/mm/yyyy', where the server interprets it as 'mm/dd/yyyy', resulting in invalid dates. As @Zaki mentioned, using a parameterized query is a much better way to handle this. — Alan
– Alan, Commented Feb 20, 2015 at 16:42
The message is pretty obvious - the data passed was wrong, definitely because you created the SQL statement using string concatenation instead of using a parameterized query. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 20, 2015 at 16:43

Tim Schmelter · Accepted Answer · 2015-02-20 16:50:06Z

You are open for sql injection if you use string concatenation to build your sql query. Instead always use sql-parameters. That also fixes other issue(f.e. date conversion).

Also, don't declare Dim dt As New Date and initialize it with a string. I strongly recommend to set Option Strict to On. That won't fix anything and also causes many compiler errors. But by fixing them you'll learn a lot about the .NET type system and you'll learn to write safer code which doesn't rely on implicit conversions (or is vulnerable to undesired conversions).

Protected Sub Myinsert_Click(sender As Object, e As EventArgs) Handles Myinsert.Click
    Try
        Using cn = New SqlConnection("Connection String")
            Dim insertSQL = "INSERT INTO MyWebSite(img, type, title, short_para, full_para, datepost) VALUES(@img, @type, @title, @short_para, @full_para, @datepost)"
            Using cmd = New SqlCommand(insertSQL, cn)
                cmd.Parameters.Add(New SqlParameter("@img", SqlDbType.VarChar))
                cmd.Parameters(cmd.Parameters.Count - 1).Value = "Image/" + ImgPath.FileName.ToString()
                cmd.Parameters.Add(New SqlParameter("@type", SqlDbType.VarChar))
                cmd.Parameters(cmd.Parameters.Count - 1).Value = "your type"
                ' ....... other parameters .....
                cmd.Parameters.Add(New SqlParameter("@datepost", SqlDbType.DateTime))
                cmd.Parameters(cmd.Parameters.Count - 1).Value = Date.Now

                cn.Open()
                Dim inserted As Int32 = cmd.ExecuteNonQuery()
            End Using ' no need to close the connection due to the using
        End Using
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Note that i've used the Using statement to ensure that all unmanaged resources are disposed and the connection gets closed even on error.

Collectives™ on Stack Overflow

I cannot insert data from asp.net into SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related