CODEWITHSUNDEEP
asp.netsql-server
Hou Chandara
Hou Chandara

Reputation: 23

I cannot insert data from asp.net into SQL Server

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

Upvotes: 0

Views: 146

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460008

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.

Upvotes: 1

Related Questions