none
想请教如何做到下图program的方法 RRS feed

  • 问题

  •  想请有关下图program的方法。

    当user click choose file button 时, user 可以upload below 100KB file 进 database 如下图。并存到数据库。请指点upload file进database的方法。谢谢。

    以下是open file dialog 的program,

     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            OpenFileDialog1.Title = "Open File"
            OpenFileDialog1.Filter = "All files (*.*)|*.*"
            If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                filePath = OpenFileDialog1.FileName
                fileName = OpenFileDialog1.SafeFileName
                fileExtension = Path.GetExtension(OpenFileDialog1.FileName)
            End If
        End Sub

    2020年10月6日 3:22

答案

  • Hi christing,

    你可以在Form1_Load事件中添加下列代码:

     DataGridView1.AllowUserToAddRows = False
     DataGridView1.Columns("Column2").DefaultCellStyle.NullValue = "Download"
     DataGridView1.Columns("Column3").DefaultCellStyle.NullValue = "Delete"

    此外我将原本的代码改写成了Access数据库适用版本,并简化了操作步骤以及数据库设计,经过我的测试,它运行成功。如果你有兴趣,你可以参考一下。

    具体的操作步骤:
    1.Access数据库设计

    删除原本默认存在的ID列,添加filename,file

    注意1:经过我的测试,file的存储类型应设置为OLE Object。若设置为longtext, buf = CType(reader("file"), Byte())会导致异常报错:'System.String' to type 'System.Byte[]'

    注意2:文件上传成功后,file列会显示binary data。

    2.窗体设计


    3.详细代码

       

    Imports System.Data.OleDb
    Imports System.IO
    Public Class Form1
        Dim filePath As String
        Dim fileName As String
        Dim buf() As Byte
        Dim str As String

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
            OpenFileDialog1.Title = "Open File"
            OpenFileDialog1.Filter = "All files (*.*)|*.*"
            OpenFileDialog1.InitialDirectory = "F:\testData"
            If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                filePath = OpenFileDialog1.FileName
                fileName = OpenFileDialog1.SafeFileName
                UploadFile(filePath, fileName)
                RefreshData()
            End If
        End Sub

        Sub UploadFile(ByVal filepath As String, ByVal filename As String)
            Dim buffer() As Byte
            Dim result As Integer
            If String.IsNullOrEmpty(filepath) Then
            Else
                buffer = File.ReadAllBytes(filepath)
                If buffer.Length > 100 * 1024 Then
                    MsgBox("文件过大")
                Else
                    Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\..\FileInfo.accdb")
                    Dim cmd As New OleDbCommand("insert into Table1 values(@filename,@file)", connection)
                    cmd.Parameters.AddWithValue("@filename", filename)
                    cmd.Parameters.AddRange({New OleDbParameter("@file", buffer)})
                    connection.Open()
                    result = cmd.ExecuteNonQuery()
                    connection.Close()
                    MsgBox(result)
                End If
            End If
        End Sub


        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DataGridView1.AllowUserToAddRows = False
            DataGridView1.AutoGenerateColumns = False
            RefreshData()
            DataGridView1.Columns("Column2").DefaultCellStyle.NullValue = "Download"
            DataGridView1.Columns("Column2").DefaultCellStyle.ForeColor = Color.Blue
            DataGridView1.Columns("Column2").DefaultCellStyle.Font = New Font(" 宋体 ", 8, FontStyle.Underline)
            DataGridView1.Columns("Column3").DefaultCellStyle.NullValue = "Delete"
            DataGridView1.Columns("Column3").DefaultCellStyle.ForeColor = Color.Blue
            DataGridView1.Columns("Column3").DefaultCellStyle.Font = New Font(" 宋体 ", 8, FontStyle.Underline)
        End Sub 

    Sub RefreshData()
            Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\..\FileInfo.accdb")
            Dim sda As New OleDbDataAdapter("select * from Table1", connection)
            Dim dt As New DataTable
            sda.Fill(dt)
            DataGridView1.DataSource = dt
            DataGridView1.Columns("Column1").DataPropertyName = "filename"
        End Sub

        Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
            Dim name As String
            name = DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString
            If e.ColumnIndex = 1 Then
                str = Path.GetExtension(name)
                Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\..\FileInfo.accdb")
                Dim cmd As New OleDbCommand("select file from Table1 where filename='" + name + "'", connection)
                Dim reader As OleDbDataReader
                connection.Open()
                reader = cmd.ExecuteReader()
                If reader.HasRows Then
                    Do While reader.Read()
                        buf = CType(reader("file"), Byte())
                    Loop
                End If
                connection.Close()
                SaveFileDialog1.Title = "Save File Location"
                SaveFileDialog1.Filter = "All files (*.*)|*.*"
                SaveFileDialog1.InitialDirectory = "F:\saveData"
                SaveFileDialog1.FileName = name
                If SaveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                    File.WriteAllBytes(Path.Combine(SaveFileDialog1.FileName + str), buf)
                    MsgBox("文件下载成功")
                End If
            ElseIf e.ColumnIndex = 2 Then
                Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\..\FileInfo.accdb")
                Dim cmd As New OleDbCommand("delete from Table1 where filename='" + name + "'", connection)
                connection.Open()
                cmd.ExecuteNonQuery()
                connection.Close()
                MsgBox("文件删除成功")
                RefreshData()
            End If
        End Sub

    End Class



    如果你有什么疑问,尽情地提出来。

    Best regards,

    Arya


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.






















    • 已编辑 Arya Ding 2020年10月13日 7:54
    • 已标记为答案 christing 2020年10月13日 8:41
    2020年10月13日 6:28
  • @Arya Ding

    感谢你的指点。

    当user click DATAGRIDVIEW 如下图 时会出现这个 error

     

     

    我放了这个代码后。download and delete 会变成不能按。

    If Me.dgr.Rows.Count > 0 Then
                If Me.dgr.SelectedRows.Count > 0 Then

    End if

    End if

    • 已标记为答案 christing 2020年10月13日 8:41
    2020年10月13日 7:50
  • Hi christing,

    当你点击时,Me.dgr.SelectedRows.Count的值一直为0,它永远无法满足判断条件。

    如果你需要判断你现在点击的单元格行索引:DataGridView1.CurrentCell.RowIndex

                                                          列索引:DataGridView1.CurrentCell.ColumnIndex

    Best Regards,

    Arya


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    • 已标记为答案 christing 2020年10月13日 8:41
    • 已编辑 Arya Ding 2020年10月13日 9:33
    2020年10月13日 8:18

全部回复

  • @Arya Ding

    感谢你的回复...由于我使用OLEDB 的database。想请问有关Imports MySql.Data.MySqlClient 可以用OLED系列那个代码来使用。

    >>想请问 select file from FileInfo1 是指datatable 吗?

    >> 想看看有关你这句的MyServerinfo 的代码 

    Dim connection As New MySqlConnection("MyServerinfo")

    我是这样篇写有关My Serverinfo 的代码

     Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + strFullDBPath + ";Jet OLEDB:Database Password=123456;"
            Dim con As OleDbConnection = New OleDbConnection(constr)

            Dim connection As New OleDbConnection(constr)



      Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + strFullDBPath + ";Jet OLEDB:Database Password=123456;"
            Dim con As OleDbConnection = New OleDbConnection(constr)

    >> 以下图片是我datatable 的设置

    我在下图遇上了以下问题 

    感恩
    2020年10月9日 6:29
  • Hi christing,

    Imports System.Data.OleDb

    select file from FileInfo1.   FileInfo1指的是数据库中的表

    请确保你的DataGridView设计界面有Column1,Column2,Column3.. 具体设计我已更新在回复中。

    Best regards,

    Arya


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.





    2020年10月9日 9:01
  • Hi christing,

    因为我使用的是MySql数据库,所以我的连接信息对你并没有很大的参考意义。

    在你发布的另一个帖子,我知道你遇到了连接异常报错。你可以尝试将oledb驱动改变,看看是否有效果。

    provider=microsoft.ace.oledb.12.0

    如果你不确定你的连接语句是否正确,你可以使用VS工具来构建这个连接字符串。

    具体操作步骤:

    (1)菜单【工具=>连接到数据库=>弹出【选择数据源】窗口=>选择【Microsoft Access数据库文件】=>点击【继续】按钮

    (2)在弹出的【添加连接】页面,数据库文件名项目浏览找到你的数据库文件,之后可以点击【测试连接】按钮,正常应该弹出提示“测试连接成功”,如果这个测试不通过,需要检查其它问题。

    测试连接成功后,点击【高级】按钮,弹出【高级属性】页面,最下面一行即显示了连接字符串,直接复制即可。

    Best regards,

    Arya




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    2020年10月9日 10:00
  • @Arya Ding

    感谢你的回复。

    想请问有关你的FileInfo1 的data table columns有哪些,

    我的program 在user upload 时会出现这个error Number of query values and destination fields are not the same.

    我对这个error 的理解Number of query values and destination fields are not the same.是指insert 的data 于我datatable的 column不对称。

    我尝试以下代码

     Dim cmd As New OleDbCommand("insert into FileInfo1 (filename,file) values (@filename,@file,'DownLoad','Delete')", connection)  Get Error Number of query values and destination fields are not the same.

       Dim cmd As New OleDbCommand("insert into FileInfo1 (filename,file,DownLoad,Delete) values (@filename,@file,'DownLoad','Delete')", connection)   Get Error Syntax error in INSERT INTO statement.

    以下是我完整的代码。

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Me.CenterToScreen()
            strCurrentPath = CurDir()
            strCurrentPath = Replace(CurDir, "\bin\Debug", "")
            strFullDBPath = strCurrentPath + "\My_Data" + "\Database1.mdb"
            cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + strFullDBPath + ";Jet OLEDB:Database Password=123456;"

            DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

            '填充DataGridView1,设置DataGridView1第二列,第三列字体格式
            RefreshData()
            DataGridView1.Columns("Column2").DefaultCellStyle.ForeColor = Color.Blue
            DataGridView1.Columns("Column2").DefaultCellStyle.Font = New Font(" 宋体 ", 8, FontStyle.Underline)
            DataGridView1.Columns("Column3").DefaultCellStyle.ForeColor = Color.Blue
            DataGridView1.Columns("Column3").DefaultCellStyle.Font = New Font(" 宋体 ", 8, FontStyle.Underline)
        End Sub

      Sub RefreshData()

            Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + strFullDBPath + ";Jet OLEDB:Database Password=123456;"
            Dim con As OleDbConnection = New OleDbConnection(constr)

            Dim connection As New OleDbConnection(constr)
            Dim sda As New OleDbDataAdapter("select * from FileInfo1", connection)

           Dim dt As New DataTable
            sda.Fill(dt)
            DataGridView1.AutoGenerateColumns = False
            DataGridView1.DataSource = dt
            DataGridView1.Columns("Column1").DataPropertyName = "filename"
            DataGridView1.Columns("Column2").DataPropertyName = "download"
            DataGridView1.Columns("Column3").DataPropertyName = "delete"

        End Sub

       Sub UploadFile(ByVal filepath As String, ByVal filename As String)
            Dim buffer() As Byte
            Dim result As Integer
            If String.IsNullOrEmpty(filepath) Then
            Else
                buffer = File.ReadAllBytes(filepath)
                If buffer.Length > 100 * 1024 Then
                    MsgBox("文件过大")
                Else
                    Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + strFullDBPath + ";Jet OLEDB:Database Password=123456;"
                    Dim con As OleDbConnection = New OleDbConnection(constr)

                    Dim connection As New OleDbConnection(constr)
                    Dim cmd As New OleDbCommand("insert into FileInfo1 values (@filename,@file,'DownLoad','Delete')", connection)
                    'Dim cmd As New OleDbCommand("insert into FileInfo1 (filename, file, DownLoad, Delete) Values (@filename, @file, @DownLoad, @Delete)", connection)
                    cmd.Parameters.AddWithValue("@filename", filename)
                    cmd.Parameters.AddRange({New OleDbParameter("@file", buffer)})
                    connection.Open()
                    result = cmd.ExecuteNonQuery()
                    connection.Close()
                    MsgBox(result)

                End If

            End If
        End Sub


    2020年10月12日 3:05
  • Hi christing,

    是的,你的理解是正确的。

    插入语句:insert into FileInfo1 (filename,file,DownLoad,Delete) values (@filename,@file,'DownLoad','Delete')

    values前后括号的内容应该相匹配,一一对应。

    同时,数据库设计需要有四列,filename,file,Download,Delete。

    filename,Download,Delete数据类型可设置为shorttext.

    file的数据类型设置为longtext.

    Best regards,

    Arya


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    2020年10月12日 7:36
  • Hi christing,

    你的报错信息是什么?还是一样的报错信息吗?
    你可以不用粘贴很多代码,只提供报错的那一行代码以及报错信息就可以了。

    Best regards,

    Arya


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2020年10月12日 10:07
  • HI Arya Ding

    我更改了一些代码后我成功upload文件,但是不知如何添加download link 与 delete data 的link。

     Dim cmd As New OleDbCommand("insert into FileInfo1(filename,file) values (@filename,@file)", connection)


                    cmd.Parameters.AddWithValue("@filename", filename)

    2020年10月13日 2:34
  • Hi christing,

    你可以在Form1_Load事件中添加下列代码:

     DataGridView1.AllowUserToAddRows = False
     DataGridView1.Columns("Column2").DefaultCellStyle.NullValue = "Download"
     DataGridView1.Columns("Column3").DefaultCellStyle.NullValue = "Delete"

    此外我将原本的代码改写成了Access数据库适用版本,并简化了操作步骤以及数据库设计,经过我的测试,它运行成功。如果你有兴趣,你可以参考一下。

    具体的操作步骤:
    1.Access数据库设计

    删除原本默认存在的ID列,添加filename,file

    注意1:经过我的测试,file的存储类型应设置为OLE Object。若设置为longtext, buf = CType(reader("file"), Byte())会导致异常报错:'System.String' to type 'System.Byte[]'

    注意2:文件上传成功后,file列会显示binary data。

    2.窗体设计


    3.详细代码

       

    Imports System.Data.OleDb
    Imports System.IO
    Public Class Form1
        Dim filePath As String
        Dim fileName As String
        Dim buf() As Byte
        Dim str As String

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
            OpenFileDialog1.Title = "Open File"
            OpenFileDialog1.Filter = "All files (*.*)|*.*"
            OpenFileDialog1.InitialDirectory = "F:\testData"
            If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                filePath = OpenFileDialog1.FileName
                fileName = OpenFileDialog1.SafeFileName
                UploadFile(filePath, fileName)
                RefreshData()
            End If
        End Sub

        Sub UploadFile(ByVal filepath As String, ByVal filename As String)
            Dim buffer() As Byte
            Dim result As Integer
            If String.IsNullOrEmpty(filepath) Then
            Else
                buffer = File.ReadAllBytes(filepath)
                If buffer.Length > 100 * 1024 Then
                    MsgBox("文件过大")
                Else
                    Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\..\FileInfo.accdb")
                    Dim cmd As New OleDbCommand("insert into Table1 values(@filename,@file)", connection)
                    cmd.Parameters.AddWithValue("@filename", filename)
                    cmd.Parameters.AddRange({New OleDbParameter("@file", buffer)})
                    connection.Open()
                    result = cmd.ExecuteNonQuery()
                    connection.Close()
                    MsgBox(result)
                End If
            End If
        End Sub


        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DataGridView1.AllowUserToAddRows = False
            DataGridView1.AutoGenerateColumns = False
            RefreshData()
            DataGridView1.Columns("Column2").DefaultCellStyle.NullValue = "Download"
            DataGridView1.Columns("Column2").DefaultCellStyle.ForeColor = Color.Blue
            DataGridView1.Columns("Column2").DefaultCellStyle.Font = New Font(" 宋体 ", 8, FontStyle.Underline)
            DataGridView1.Columns("Column3").DefaultCellStyle.NullValue = "Delete"
            DataGridView1.Columns("Column3").DefaultCellStyle.ForeColor = Color.Blue
            DataGridView1.Columns("Column3").DefaultCellStyle.Font = New Font(" 宋体 ", 8, FontStyle.Underline)
        End Sub 

    Sub RefreshData()
            Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\..\FileInfo.accdb")
            Dim sda As New OleDbDataAdapter("select * from Table1", connection)
            Dim dt As New DataTable
            sda.Fill(dt)
            DataGridView1.DataSource = dt
            DataGridView1.Columns("Column1").DataPropertyName = "filename"
        End Sub

        Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
            Dim name As String
            name = DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString
            If e.ColumnIndex = 1 Then
                str = Path.GetExtension(name)
                Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\..\FileInfo.accdb")
                Dim cmd As New OleDbCommand("select file from Table1 where filename='" + name + "'", connection)
                Dim reader As OleDbDataReader
                connection.Open()
                reader = cmd.ExecuteReader()
                If reader.HasRows Then
                    Do While reader.Read()
                        buf = CType(reader("file"), Byte())
                    Loop
                End If
                connection.Close()
                SaveFileDialog1.Title = "Save File Location"
                SaveFileDialog1.Filter = "All files (*.*)|*.*"
                SaveFileDialog1.InitialDirectory = "F:\saveData"
                SaveFileDialog1.FileName = name
                If SaveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                    File.WriteAllBytes(Path.Combine(SaveFileDialog1.FileName + str), buf)
                    MsgBox("文件下载成功")
                End If
            ElseIf e.ColumnIndex = 2 Then
                Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\..\FileInfo.accdb")
                Dim cmd As New OleDbCommand("delete from Table1 where filename='" + name + "'", connection)
                connection.Open()
                cmd.ExecuteNonQuery()
                connection.Close()
                MsgBox("文件删除成功")
                RefreshData()
            End If
        End Sub

    End Class



    如果你有什么疑问,尽情地提出来。

    Best regards,

    Arya


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.






















    • 已编辑 Arya Ding 2020年10月13日 7:54
    • 已标记为答案 christing 2020年10月13日 8:41
    2020年10月13日 6:28
  • @Arya Ding

    感谢你的指点。

    当user click DATAGRIDVIEW 如下图 时会出现这个 error

     

     

    我放了这个代码后。download and delete 会变成不能按。

    If Me.dgr.Rows.Count > 0 Then
                If Me.dgr.SelectedRows.Count > 0 Then

    End if

    End if

    • 已标记为答案 christing 2020年10月13日 8:41
    2020年10月13日 7:50
  • Hi christing,

    当你点击时,Me.dgr.SelectedRows.Count的值一直为0,它永远无法满足判断条件。

    如果你需要判断你现在点击的单元格行索引:DataGridView1.CurrentCell.RowIndex

                                                          列索引:DataGridView1.CurrentCell.ColumnIndex

    Best Regards,

    Arya


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    • 已标记为答案 christing 2020年10月13日 8:41
    • 已编辑 Arya Ding 2020年10月13日 9:33
    2020年10月13日 8:18
  • @Arya Ding

    原来如此。感谢你的帮助与答复谢谢。:)

    2020年10月13日 8:41