vb.net 如何使用文件上传插入到数据库 sql server 这个

how to use fileupload insert to database sql server this

提问by arif pratama

Protected Sub btnUpload_Click(ByVal sender As Object, e As EventArgs) Handles btnUpload.Click
    Dim filePath As String = FileUpload.PostedFile.FileName
    Dim filePath2 As String = FileUploadImage.PostedFile.FileName
    Dim fileName As String = Path.GetFileName(filePath)
    Dim ext As String = Path.GetExtension(fileName)
    Dim contenttyppe As String = String.Empty
    Select Case ext
        Case ".pdf"
            ContentType = "application/pdf"
            Exit Select
        Case ".jpg"
            ContentType = "image/jpg"
            Exit Select
        Case ".pfx"
            ContentType = "image/pfx"
            Exit Select
        Case ".png"
            ContentType = "image/png"
            Exit Select
        Case ".gif"
            ContentType = "image/gif"
            Exit Select
        Case ".doc"
            ContentType = "application/vnd.ms-word"
            Exit Select
        Case ".docx"
            ContentType = "application/vnd.ms-word"
            Exit Select
        Case ".xls"
            ContentType = "application/vnd.ms-excel"
            Exit Select
        Case ".xlsx"
            ContentType = "application/vnd.ms-excel"
            Exit Select
        Case ".pfx"
            ContentType = "image/pfx"
            Exit Select
    End Select
    If contenttyppe <> String.Empty Then
        Dim fs As Stream = FileUpload.PostedFile.InputStream
        Dim fi As Stream = FileUploadImage.PostedFile.InputStream
        Dim br As New BinaryReader(fs)
        Dim br2 As New BinaryReader(fi)
        Dim bytes As Byte() = br.ReadBytes(fs.Length)
        Dim bytes2 As Byte() = br2.ReadBytes(fi.Length)
        Dim strQuery As String = "insert into APPUSERDTL (UID,APPCODE,APPEXPIRED,SIGNIMAGE, PFKFILE,HEADUID,PRINCIPALFROM,PRINCIPALTO,EXCEPTIONUSER,LastUpdate)" _
                            & "VALUES('',' ','','  @FileUpload  ','  @FileUploadImage','','','','','')"

        Dim cmd As New SqlCommand(strQuery)
        cmd.Parameters.Add("@UID", Data.SqlDbType.Int).Value = bytes
        cmd.Parameters.Add("@APPCODE", Data.SqlDbType.VarChar, 50).Value = bytes
        cmd.Parameters.Add("@APPEXPIRED", Data.SqlDbType.DateTime).Value = bytes
        cmd.Parameters.Add("@FileUpload", Data.SqlDbType.Image).Value = FileUpload
        cmd.Parameters.Add("@FileUploadImage", Data.SqlDbType.VarBinary).Value = FileUploadImage
        cmd.Parameters.Add("@HEADUID", Data.SqlDbType.Int).Value = bytes
        cmd.Parameters.Add("@PRINCIPALFROM", Data.SqlDbType.Money).Value = bytes
        cmd.Parameters.Add("@PRINCIPALTO", Data.SqlDbType.Money).Value = bytes
        cmd.Parameters.Add("@EXCEPTIONUSER", Data.SqlDbType.Bit).Value = bytes
        cmd.Parameters.Add("@LastUpdate", Data.SqlDbType.DateTime).Value = bytes
        InsertUpdateData(cmd)

        lblMsgUpload.ForeColor = System.Drawing.Color.Green
        lblMesgImage.ForeColor = System.Drawing.Color.Green
        lblMsgUpload.Text = "File Upload Successfully"
        lblMesgImage.Text = "File Upload Successfuly"

    Else
        lblMesgImage.ForeColor = System.Drawing.Color.Red
        lblMesgImage.Text = "File format not recognised." _
         & " Upload Image/Word/PDF/Excel formats"
    End If

    ' delete file upload
    Dim fullPath = MapPath("~/upload/") + fileName
    If System.IO.File.Exists(fullPath) Then
        System.IO.File.Delete(fullPath)
    End If
End Sub

==================================================

==================================================

Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean
    '  Dim strConnString As New DBX
    Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("connection").ConnectionString
    Dim conn As New SqlConnection(strConnString)
    cmd.CommandType = CommandType.Text
    cmd.Connection = conn
    Try
        conn.Open()
        cmd.ExecuteNonQuery()
        Return True
    Catch ex As Exception
        Response.Write(ex.Message)
        Return False
    Finally
        conn.Close()
        conn.Dispose()

    End Try
End Function
End Class

采纳答案by Steve

You have only two parameters placeholders in the query text (@FileUploadand @FileUploadImage). Then you define parameters for every field with various datatype, but then set them all with the same value (bytes). Finally the commas that should separe the values are placed wrongly in the query text. No wonder that you ExecuteNonQuery doesn't work

查询文本中只有两个参数占位符 (@FileUpload和@FileUploadImage)。然后您为具有各种数据类型的每个字段定义参数，然后将它们全部设置为相同的值 ( bytes)。最后，应该分隔值的逗号在查询文本中被错误地放置。难怪你 ExecuteNonQuery 不起作用

If you don't know the other values and your record could be inserted leaving that fields to their default value you could try with this trimmed down version of your query above. Otherwise you need to know the actual values to insert in the other fields and initialize the parameters value with the appropriate variables.

如果您不知道其他值并且可以插入您的记录，而将这些字段保留为默认值，您可以尝试使用上述查询的精简版本。否则，您需要知道要插入其他字段的实际值并使用适当的变量初始化参数值。

Dim strQuery As String = "insert into APPUSERDTL (SIGNIMAGE,PKFILE,LastUpdate) " & _ "VALUES(@FileUpload,@FileUploadImage, GetDate())"

Dim strQuery As String = "插入 APPUSERDTL (SIGNIMAGE,PKFILE,LastUpdate)" & _ "VALUES(@FileUpload,@FileUploadImage, GetDate())"

Dim cmd As New SqlCommand(strQuery)
cmd.Parameters.Add("@FileUpload", Data.SqlDbType.Image).Value = FileUpload
cmd.Parameters.Add("@FileUploadImage", Data.SqlDbType.VarBinary).Value = FileUploadImage
InsertUpdateData(cmd)

As a last note, when you have errors it is always good to add the error message to your question. It is very helpfull to understand what happens in your code.

最后要注意的是，当您遇到错误时，最好将错误消息添加到您的问题中。了解代码中发生的事情非常有帮助。