Question Open File from MySQL

sarahxiaori

Member
Joined
Apr 29, 2012
Messages
6
Programming Experience
Beginner
Hi, i am a newbie to programming and i have google long time solution for my program.
I want to upload files to MYSQL database, then open the file by its default application (pdf with adobe reader, doc with MS word etc etc.. )
but i still have an error, please help

Upload:
VB.NET:
Private Sub cmdUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpload.Click
        lblUploadStatus.Text = ""
 
        If LTrim(RTrim(txtFilePath.Text)) = "" Then
            lblUploadStatus.Text = "Please select Files!"
            lblUploadStatus.ForeColor = Color.Red
            txtFilePath.Focus()
            Exit Sub
        End If
 
        Dim sFileToUpload As String = ""
        sFileToUpload = LTrim(RTrim(txtFilePath.Text))
        Dim Extension As String = System.IO.Path.GetExtension(sFileToUpload)
        upLoadImageOrFile(sFileToUpload, Extension)
 
        isFirst = False
        GetFilesFromDatabase()
    End Sub
    Private Sub upLoadImageOrFile(ByVal sFilePath As String, ByVal sFileType As String)
 
        Dim builder As New MySqlConnectionStringBuilder
        builder.Server = "localhost"
        builder.UserID = "root"
        builder.Password = "12345"
        builder.Database = "misdatabase"
        Dim str As String = builder.ConnectionString
        Dim conn As New MySqlConnection(str)
        conn.Open()
 
        Dim SqlCom As MySqlCommand
        Dim imageData As Byte()
        Dim sFileName As String
        Dim qry As String
 
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
 
            imageData = ReadFile(sFilePath)
            sFileName = System.IO.Path.GetFileName(sFilePath)
            qry = "insert into eFilling (upddate,updUser,fileName,ImageData,fileType,dateUpload,uploadBy) values(@UpdDate,@updUser,@FileName, @ImageData,@FileType,@DateUpload,@UploadBy)"
            SqlCom = New MySqlCommand(qry, conn)
            SqlCom.Parameters.Add(New MySqlParameter("@UpdDate", Now()))
            SqlCom.Parameters.Add(New MySqlParameter("@updUser", frmMain.sEmpID))
            SqlCom.Parameters.Add(New MySqlParameter("@FileName", sFileName))
            SqlCom.Parameters.Add(New MySqlParameter("@ImageData", DirectCast(imageData, Object)))
            SqlCom.Parameters.Add(New MySqlParameter("@FileType", sFileType))
            SqlCom.Parameters.Add(New MySqlParameter("@DateUpload", Now()))
            SqlCom.Parameters.Add(New MySqlParameter("@UploadBy", frmMain.sEmpID))
            SqlCom.ExecuteNonQuery()
 
            lblUploadStatus.ForeColor = Color.Green
            lblUploadStatus.Text = "File uploaded successfully"
            txtFilePath.Text = ""
 
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
            lblUploadStatus.Text = "File could not uploaded"
        End Try
 
 
    End Sub

Open:
VB.NET:
Private Function ReadFile(ByVal sPath As String) As Byte()
        Dim data As Byte() = Nothing
        Dim fInfo As New FileInfo(sPath)
        Dim numBytes As Long = fInfo.Length
        Dim fStream As New FileStream(sPath, FileMode.Open, FileAccess.Read)
        Dim br As New BinaryReader(fStream)
        data = br.ReadBytes(CInt(numBytes))
        Return data
    End Function
 
    Private Sub GetFilesFromDatabase()
        Dim builder As New MySqlConnectionStringBuilder
        builder.Server = "localhost"
        builder.UserID = "root"
        builder.Password = "12345"
        builder.Database = "misdatabase"
        Dim str As String = builder.ConnectionString
 
        Dim conn As New MySqlConnection(str)
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
 
            Dim strSql As String = "Select id,fileName,fileType,dateUpload from eFilling  where uploadby =  '" & frmMain.sEmpID & "'"
            Dim ADAP As New MySqlDataAdapter(strSql, conn)
            Dim DS As New DataSet()
 
            ADAP.Fill(DS, "eFilling")
            dgdFiles.DataSource = DS.Tables("eFilling")
            Dim dgButtonColumn As New DataGridViewButtonColumn
            dgButtonColumn.HeaderText = "View File"
            dgButtonColumn.UseColumnTextForButtonValue = True
            dgButtonColumn.Text = "View File"
            dgButtonColumn.Name = "ViewFile"
            dgButtonColumn.ToolTipText = "View File"
            dgButtonColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCellsExceptHeader
            dgButtonColumn.FlatStyle = FlatStyle.System
            dgButtonColumn.DefaultCellStyle.BackColor = Color.Gray
            dgButtonColumn.DefaultCellStyle.ForeColor = Color.White
            If isFirst = True Then
                dgdFiles.Columns.Add(dgButtonColumn)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
            MessageBox.Show("Could not load the File")
        End Try
    End Sub
 
    Private Sub dbGridView_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgdFiles.CellContentClick
 
        Dim strSql As String = ""
 
 
        Try
            Select Case e.ColumnIndex
                Case Is > -1
                    If sender.Columns(e.ColumnIndex).Name = "ViewFile" Then
 
 
                        downLoadFile(dgdFiles.Rows(e.RowIndex).Cells("id").Value, dgdFiles.Rows(e.RowIndex).Cells("FileName").Value, dgdFiles.Rows(e.RowIndex).Cells("FileType").Value)
                    End If
            End Select
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
 
    End Sub
 
    Private Sub downLoadFile(ByVal iFileId As Long, ByVal sFileName As String, ByVal sFileExtension As String)
        Dim builder As New MySqlConnectionStringBuilder
        builder.Server = "localhost"
        builder.UserID = "root"
        builder.Password = "12345"
        builder.Database = "misdatabase"
        Dim str As String = builder.ConnectionString
 
        Dim conn As New MySqlConnection(str)
        Dim strSql As String
        Try
            strSql = "Select ImageData from eFilling WHERE id=" & iFileId
            Dim sqlCmd As New MySqlCommand(strSql, conn)
            Dim fileData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())
            Dim sTempFileName As String = Application.StartupPath & "\" & sFileName
 
            If Not fileData Is Nothing Then
                Using fs As New FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write)
                    fs.Write(fileData, 0, fileData.Length)
                    fs.Flush()
                    fs.Close()
                End Using
                Process.Start(sFileName)
            End If
 
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
 
    End Sub
 
End Class


view.png
db.png
error.png
 
Top Bottom