VB.NET Upload File (any type) to MySQL Question.. not ASP

Hi everyone!!

I Have the following code to upload files in ASP, but I need this code for vb.net application.
My idea is the same, put the path root in textbox and upload this file to MySQL Database.

Code:

---

Imports System.IO
Imports System.Data
Imports System.Data.mySqlClient
Imports System.Configuration

---

Code:

---

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

    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(constr)

                Dim query As String = "insert into tblFiles values (@Name, @ContentType, @Data)"

                Using cmd As New SqlCommand(query)

                    cmd.Connection = con

                    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename

                    cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contentType

                    cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes

                    con.Open()

                    cmd.ExecuteNonQuery()

                    con.Close()

                End Using

            End Using

        End Using

    End Using

    Response.Redirect(Request.Url.AbsoluteUri)

End Sub

---

This is mysql database (same that the example..)
Attachment 118975
My Questions is:
1. for any type of files, it's the correct table type (varbinary)?
2. I try with blob type, but only upload a .bin file..


Thanks a lot for your help!
Best Regards,

That code is already in VB.NET. Do you actually mean that you have an ASP.NET Web Forms application written in VB.NET and now you want to write a Windows Forms application in VB.NET? If so then your job is simple. You simply call File.ReadAllBytes to read a file into a Byte array and then save that to the database in exactly the same way as you already are. Data access is the same no matter the presentation technology you're using.

By the way, your title says MySQL but your code is using a SqlCommand, which is for SQL Server. This namespace import:

Code:

---

Imports System.Data.mySqlClient

---

is completely meaningless because there is no such namespace. Which database are you actually using? If it's SQL Server then you need to import System.Data.SqlClient. If it's MySQL then you need to import MySql.Data.MySqlClient but then only after installing Connector/Net from MySQL. You also need to use types from that namespace for MySQL, e.g. MySqlCommand.

Hi jmcilhinney!

Thanks for your reply.
Maybe, I won't explain correct. I Have a Windows Forms VB.NET 2010 aplication with MySQL. My idea is upload a excel/pdf/word uploader in the application. I rewrite the code but the file uploaded is a .bin file , this is my code:

Code:

---

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim cmd As New MySqlCommand
        Dim SQL As String
        Dim FileSize As UInt32
        Dim rawData() As Byte = IO.File.ReadAllBytes("c:\Tt.xlsx")
        Dim fs As FileStream
        If sconnection.State = ConnectionState.Closed Then
            sconnection.ConnectionString = "Data Source=XX;Database=TT;User ID=xx;Password=ok;"



            Try
                fs = New FileStream("c:\Tt.xlsx", FileMode.Open, FileAccess.Read)
                FileSize = fs.Length

                rawData = New Byte(FileSize) {}
                fs.Read(rawData, 0, FileSize)
                fs.Close()

                sconnection.Open()

                SQL = "INSERT INTO id_text (FileName) VALUES(?FileName)"

                cmd.Connection = sconnection
                cmd.CommandText = SQL
                cmd.Parameters.AddWithValue("?FileName", rawData)


                cmd.ExecuteNonQuery()

                MessageBox.Show("File Inserted into database successfully!", _
                "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

                sconnection.Close()
            Catch ex As Exception
                MessageBox.Show("There was an error: " & ex.Message, "Error", _
                    MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If

    End Sub

---

The fileupload correct, but in the MySQL show a .bin File (why not excel/pdf/word file extension?)

This is MySQL Design:

Attachment 119017

it's correct?

Thanks for your comments

Because like a good DBMS it's storing the binary data outside of the table data - it doesn't give one wit if it's a DOCX or XLSX or a Text file... it's binary as far as the ... does that code even work? It shouldn't ... you're calling the field FileName, but the table says strFileName ... and then you're not adding the file name, but the raw binary data ... either way, the wrong field name should have caused an exception.

Your field names and types don't make a lot of sense to me. Why is fileSize a blob and not a number? Why is strFileName a blob as well instead of a varchar? whay is it strFileName and not just fileName?

-tg