• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1066
  • Last Modified:

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

I am close but I have one last problem in my file upload procedure using asp.net 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.

1 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 http://msdn2.microsoft.com/en-us/library/system.data.sqldbtype.aspx    -  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????


jholmes724Author Commented:
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now