Uploading files to SQL Server 2012 with ASP.NET/VB.NET

Question

I followed a tutorial an ran the below code without any errors. The file "uploads", however no data is inserted into my SQL Server table.

Data should be inserted into the content table.

Content Table:

enter image description here

Document.aspx

Imports System.Data.SqlClient
Imports System.Data
Imports System.IO

Partial Class Documents
    Inherits System.Web.UI.Page

    Protected Sub btnUploadContent_Click(sender As Object, e As EventArgs) Handles btnUploadContent.Click

        Dim filePath As String = FileUpload.PostedFile.FileName

        Dim filename As String = Path.GetFileName(filePath)

        Dim ext As String = Path.GetExtension(filename)

        Dim contenttype As String = String.Empty



        Select Case ext

            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 ".jpg"

                contenttype = "image/jpg"

                Exit Select

            Case ".png"

                contenttype = "image/png"

                Exit Select

            Case ".gif"

                contenttype = "image/gif"

                Exit Select

            Case ".pdf"

                contenttype = "application/pdf"

                Exit Select

        End Select

        If contenttype <> String.Empty Then

            Dim fs As Stream = FileUpload.PostedFile.InputStream

            Dim br As New BinaryReader(fs)

            Dim bytes As Byte() = br.ReadBytes(fs.Length)



            'insert the file into database

            Dim strQuery As String = "INSERT INTO content (content_name, content_type, content_file) VALUES (@Name, @ContentType, @Data)"

            Dim cmd As New SqlCommand(strQuery)

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

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

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

            InsertUpdateData(cmd)

            lblMessage.ForeColor = System.Drawing.Color.Green

            lblMessage.Text = "File Uploaded Successfully"

        Else

            lblMessage.ForeColor = System.Drawing.Color.Red

            lblMessage.Text = "File format not recognised." + " Upload Image/Word/PDF/Excel formats"

        End If

    End Sub




    Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnStringDb1").ConnectionString()

        Dim conn As New SqlConnection("Data Source=BRIAN-PC\SQLEXPRESS;Initial Catalog=master_db;Integrated Security=True;")

        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

Can anyone tell me what's going on ?

EDIT: Debug Breakpoint @ InsertUpdateData(cmd) :

        SqlDbType.Binary    Binary {1}  System.Data.SqlDbType
+       bytes   {Length=4136752}    Byte()
+       cmd {System.Data.SqlClient.SqlCommand}  System.Data.SqlClient.SqlCommand
+       cmd.Parameters  {System.Data.SqlClient.SqlParameterCollection}  System.Data.SqlClient.SqlParameterCollection

1) Check your connection string, I see you took info from config but then just ignore it and use another hardcoded connection string. 2) It won't help, but piece of optimization: you shouldn't call conn.Close() and conn.Dispose() together, Close method calls Dispose internally. — Ihor Deyneka, Apr 09 '13 at 10:17
@IhorDeyneka I removed the conn.dispose and change these lines: `Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnStringDb1").ConnectionString() Dim conn As New SqlConnection(strConnString)`, no errors but still doesn't work — Brian, Apr 09 '13 at 10:21
Did you debug that? cmd.ExecuteNonQuery() executes successfully or exception occurs? During debug check all important properties of cmd just before executing ExecuteNonQuery — Ihor Deyneka, Apr 09 '13 at 10:25
No exception, everything works fine, just no data is put into the db table ! — Brian, Apr 09 '13 at 10:37

Ihor Deyneka · Accepted Answer · 2013-04-09T12:59:33.660

1

I have created empty database and added table content just like you have and I used code almost the same as you and it worked fine.

Again, if no exception occurs, please check your connection string and see whether the rows been added to the table in the db specified in connection string. Here is my code (which is working fine), a bit modified from yours:

Imports System.Data.SqlClient
Imports System.IO

Public Class _Default
Inherits System.Web.UI.Page

Protected Sub btnUploadContent_Click(sender As Object, e As EventArgs) Handles btnTest1.Click

    Dim fs As Stream = FileUpload.PostedFile.InputStream

    Dim br As New BinaryReader(fs)

    Dim bytes As Byte() = br.ReadBytes(fs.Length)


    'insert the file into database

    Dim strQuery As String = "INSERT INTO content (content_name, content_type, content_file) VALUES (@Name, @ContentType, @Data)"

    Dim cmd As New SqlCommand(strQuery)

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

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

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

    InsertUpdateData(cmd)

End Sub




Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

    Dim conn As New SqlConnection("Data Source=(local);Initial Catalog=test;Integrated Security=True;")

    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

I add sample of SQL to test on DB:

 INSERT INTO [master_db].[dbo].[content]
       ([content_name]
       ,[content_type]
       ,[content_file])
 VALUES
       ('test'
       ,'png'
       ,0x111111111111111)

 SELECT * FROM [master_db].[dbo].[content]

edited Apr 09 '13 at 12:59

answered Apr 09 '13 at 12:04

Ihor Deyneka

1,326
1
19
37

I don't know what's wrong, tried the code and still the same.. http://i.imgur.com/zKqjJ0u.jpg – Brian Apr 09 '13 at 12:21
What's content size? I didn't see that column before. – Ihor Deyneka Apr 09 '13 at 12:23
Ahh I just added that so I will code the size of the file in it later, but currently it's not in use. – Brian Apr 09 '13 at 12:37
but is it nullable? If not then that SQL script should be crashing and you should get an exception – Ihor Deyneka Apr 09 '13 at 12:37
Yes, it's set to allow NULL taking 'bit' as a value. – Brian Apr 09 '13 at 12:40
I would localize the error like this: 1) Execute your SQL script with hardcoded parameters directly on the DB and confirm that data is added 2) Execute the same SQL script using your SqlCommand but don't use any parameters (instead use inserted values hardcoded in the script) and confirm it is working 3) Add parameters but with hardcoded values and confirm that. 4) Return to your starting code. Let me know which step is failing for you. – Ihor Deyneka Apr 09 '13 at 12:44
OK I will try that. How do I add a file directly through SQL though? – Brian Apr 09 '13 at 12:46
Ahhh again! Rows 'updated' successfully but still no data in the table. http://i.imgur.com/gBfxDNr.jpg – Brian Apr 09 '13 at 12:53
I can't believe that is possible :) Try closing your Management Studio and reopen it, after that execute script again, but add SELECT at bottom and execute it as one script (refer to my post again please). – Ihor Deyneka Apr 09 '13 at 13:00
It worked.. what was wrong ?? The code still doesn't work (ASP.NET) – Brian Apr 09 '13 at 13:08
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/27862/discussion-between-ihor-deyneka-and-brian) – Ihor Deyneka Apr 09 '13 at 13:10

score 1 · Answer 2 · answered Jun 06 '13 at 23:14

I came across this post looking looking for an example. I used the example code you posted and had the same problem. I found and resolved the following issues and got it working:

I created the db table as pictured. content_type of nchar(5) was the first problem since you were inserting something like "application/vnd.ms-word" which was too big.
The next error was because I had not defined the content_id to be an identity column and since it wasn't listed in the insert statement it failed.
Next I had an error as my db user didn't have insert privileges.
The biggest problem is that the return message was always a success message because even though the InsertUpdateData function was catching errors it was not notifying the calling code. This made me think things were okay. doh! Using a breakpoint on the ExecuteNonQuery allowed me to see the errors.

Hope that helps the next person that stops by....

Uploading files to SQL Server 2012 with ASP.NET/VB.NET

2 Answers2