inserting Byte() array into SQL Database Image Field when trying to Upload file using ASP.NET upload control (Language VB.NET)

Posted on 2006-04-26
Last Modified: 2012-05-05
I am close but I have one last problem in my file upload procedure using file upload control.  I have no problem getting it to upload a file to a directory on the web server but when I try to use a TSQL insert command with parameters the Image datatype will not accept a parameter.  So I am trying to figure out how to do an insert in the the SQL database table without using a parameter on the insert commad.  Here is my code so far:

First I get all the values from the "Uploader"(Name of the Upload control) ,and in the GetByteArray Function I pass in the Uploader and convert to byte() array

            uploadFileName = Uploader.PostedFile.FileName  '---- Get full path name ---
            uploadFileType = Uploader.PostedFile.ContentType
            uploadFileSize = Uploader.PostedFile.ContentLength
            theFileName = System.IO.Path.GetFileName(strFullFileName) ' only the attched file name not its path  
            fileData = GetByteArrayFromFileField(Uploader)
            dataManager.WriteFileToDB(issueID, pageDataSet.WebTeamAttachments, Me.txtFileDesc.Text, theFileName, ownerID, uploadFileType, fileData)  '--- Passes variables to Data Layer component to process
            '--------------- Note  I am using a type dataset and passing the "pageDataSet.WebTeamAttachments" table to the data layer

           '------ Code to convert to byte array -----
    Public Function GetByteArrayFromFileField(ByVal FileField As System.Web.UI.HtmlControls.HtmlInputFile) As Byte()
        ' Returns a byte array from the passed
        ' file field controls file
        Dim intFileLength As Integer, bytData() As Byte
        Dim objStream As System.IO.Stream
        If IsFileFieldSelected(FileField) Then  '---------------------- This Function simply validates if  file is valid and not nothing.
            intFileLength = FileField.PostedFile.ContentLength
            ReDim bytData(intFileLength)
            objStream = FileField.PostedFile.InputStream
            objStream.Read(bytData, 0, intFileLength)
            Return bytData
        End If
    End Function

Here is the Data Layer Componet "dataManager" that handles the file insertion into the database
This is where I need the help getting the byte() array into the database.
Here is what I have so far.

    Public Function WriteFileToDB(ByVal issueID As Integer, ByVal resultDataTable As MainDataset.WebTeamAttachmentsDataTable, ByVal strDesc As String, _
    ByVal strName As String, ByVal ownerID As Integer, ByVal strType As String, ByRef Buffer As Byte()) As Integer

        Dim tempDataAdapter As SqlClient.SqlDataAdapter
            tempDataAdapter = New SqlClient.SqlDataAdapter
            tempDataAdapter.SelectCommand = Me.cmdUploadFile
            tempDataAdapter.Fill(resultDataTable) '--- Fill data set
            Dim dbRow As DataRow  '--- create Row object
            dbRow = resultDataTable.NewRow '--- Create new row in typed dataset table
            dbRow("FileName") = strName
            dbRow("FileSize") = Buffer.Length
            dbRow("FileType") = strType
            dbRow("OwnerID") = ownerID
            dbRow("FileObject") = Buffer
            tempDataAdapter.Update(resultDataTable) <------ Here is where I get the error because of course I have no insert command that I can provide the data adapter.  So my question is if I can't used a parameter to pass
        Finally                                                           ------ the Byte() array Named "Buffer" how can I get this field into the database???????
            If Not tempDataAdapter Is Nothing Then
            End If
        End Try

So that is my story...  Maybe you have a better way of doing this, I am open to any suggestions.  I am sure this is a common task however every google search I find only gives part of the answer and does not seem to address
how to do this part of it.  They only address the part up to puttng the file in an IMAGE datatype but never seem to find an answer how to get it there.  Any help is greatly appreciated.

Question by:jholmes724
    LVL 23

    Expert Comment

    LVL 12

    Accepted Solution

    try using a parmaterized sqlcommand and passing the byte(s) to the value
    I have had major issues using a DataAdapter to update a datasource without defining primary keys

    this is c#
    SqlCommand cmd = new SqlCommand("insert into table(FileName,FIleSize,FileType,OwnerID,FileObject) values(?FileName,?FIleSize,?FileType,?OwnerID,?FileObject) ", conn); //conn  is sqlconnection object, table ahould have a primary key, auto increment
    cmd.Parameters.Add("?FileName",SqlDbType.VarChar).Value = strName;
    cmd.Parameters.Add("?FIleSize",SqlDbType.Int32).Value = Buffer.Length;
    cmd.Parameters.Add("?FileName",SqlDbType.VarChar).Value = strType;
    cmd.Parameters.Add("?FileName",SqlDbType.Int32).Value = ownerID;
    cmd.Parameters.Add("?FileName",SqlDbType.Binary).Value = Buffer;   // not sure this is the questionable value check out    -  Binary looks liek a byte array

        open connection
       cmd.execute nonquery
       close conenction
    catch error

    I would try and use ther server explorer and drag the table in question onto an apsx page and do all the automatic data binding things through the toolbox and see what type of field visual studio determines it should use - it might even give you the code for the update sql script needed????



    Author Comment

    Thanks, that did it.  My problem was I was using the query designer to add parameter, which gave error when tried to add in parameter.  Once hand coded the command script and parameters it worked fine.   Thank you very much.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Suggested Solutions

    Title # Comments Views Activity
    State Session High Availibility 2 34
    Effect on button 11 39
    SequenceEqual in C# List 2 29
    Session variables expiration ASP.NET C# 3 37
    AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now