locked
how to correct use cmd.Parameters RRS feed

  • Question

  • User-1643910501 posted

    I have this code as below trying to upload a pdf file into SQL db.
    The web page resulted as error , error message as 

    "Conversion failed when converting the nvarchar value 'test.pdf' to data type int."

    It pointed to line inside the upload(), around "cmd.ExecuteNonQuery()"


    Need help. Thanks in advance
    . . . . . . table sql . . .
    CREATE TABLE [dbo].[tblFiles](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EmpID] [int] NOT NULL ,
    [Name] [varchar](50) NOT NULL,
    [ContentType] [nvarchar](200) NOT NULL,
    [Description] [varchar](200) ,
    [Data] [varbinary](max) NOT NULL,
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    . . . . vb.aspx.vb . . .

    Private Sub BindGrid()
    'Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    'Dim constr As String = ConfigurationManager.ConnectionStrings("strSqlProviderDBFiles").ConnectionString
    Using con As New SqlConnection(strSqlProvider2)
      Using cmd As New SqlCommand()
        cmd.CommandText = "select Id, Name, ContentType, Description from tblFiles"
        cmd.Connection = con
        con.Open()
        GridView1.DataSource = cmd.ExecuteReader()
        GridView1.DataBind()
        con.Close()
      End Using
    End Using
    End Sub


    Protected Sub Upload(sender As Object, e As EventArgs)
    Dim filename As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
    Dim contentType As String = FileUpload1.PostedFile.ContentType
    'Dim strDesc As String = txtDescription.Text.Trim
    Using fs As Stream = FileUpload1.PostedFile.InputStream
    Using br As New BinaryReader(fs)
    Dim bytes As Byte() = br.ReadBytes(DirectCast(fs.Length, Long))
    'Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(strSqlProvider2)
    Dim query As String = "insert into tblFiles values ( @Name, @EmpID, @ContentType, @Description, @Data )"
    Using cmd As New SqlCommand(query)
    cmd.Connection = con

    cmd.Parameters.AddWithValue("@Name", filename)
    cmd.Parameters.AddWithValue("@EmpID", 20)
    cmd.Parameters.AddWithValue("@ContentType", contentType)
    cmd.Parameters.AddWithValue("@Description", txtDescription.Text.Trim)
    cmd.Parameters.AddWithValue("@Data", bytes)

    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()
    End Using
    End Using
    End Using
    End Using
    Response.Redirect(Request.Url.AbsoluteUri)
    End Sub

    The filename ws "test.pdf" . Shomehow it took that file name as the column "EmpID" (because that was the only INT field in the table)

    Need help.

    Monday, June 10, 2019 3:44 PM

Answers

  • User-1643910501 posted

    Thank you very much for your solution and the advice. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 10, 2019 4:14 PM

All replies

  • User753101303 posted

    Hi,

    This is because the ordering is wrong ie you have EmpID,Name in your table but you provide values in the @Name,@EmpID order (so it  tries to use @Name for the EmpID column).

    A best practice is to always include an explicit field list ie something such as :

    insert into tblFiles(Name,EmpID,ContentType,Description,Data) values (@Name,@EmpID,@ContentType,@Description,@Data)

    So that you don't care about the colums ordering (you can also add a new column with a default value without breaking your code).

    Monday, June 10, 2019 4:06 PM
  • User-1643910501 posted

    Thank you very much for your solution and the advice. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 10, 2019 4:14 PM
  • User61956409 posted

    Hi tsaim,

    Welcome to ASP.NET forums.

    It seems that you solved the problem with the help of PatriceSc. As PatriceSc suggested, if you'd like to [insert data that is not in the same order as the table columns](https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017#c-inserting-data-that-is-not-in-the-same-order-as-the-table-columns), you can use a column list to explicitly specify the values that are inserted into each column.

    Besides, if the solution that PatriceSc shared did help solve your problem, please accept that reply as answer. 

    With Regards,

    Fei Han

    Tuesday, June 11, 2019 9:28 AM