jholmes724
asked on
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.FileNa me '---- Get full path name ---
uploadFileType = Uploader.PostedFile.Conten tType
uploadFileSize = Uploader.PostedFile.Conten tLength
theFileName = System.IO.Path.GetFileName (strFullFi leName) ' only the attched file name not its path
fileData = GetByteArrayFromFileField( Uploader)
dataManager.WriteFileToDB( issueID, pageDataSet.WebTeamAttachm ents, 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.WebTeamAttach ments" table to the data layer
'------ Code to convert to byte array -----
Public Function GetByteArrayFromFileField( ByVal FileField As System.Web.UI.HtmlControls .HtmlInput File) 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(FileFi eld) Then '---------------------- This Function simply validates if file is valid and not nothing.
intFileLength = FileField.PostedFile.Conte ntLength
ReDim bytData(intFileLength)
objStream = FileField.PostedFile.Input Stream
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.WebTeamAttachm entsDataTa ble, 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
Try
tempDataAdapter = New SqlClient.SqlDataAdapter
tempDataAdapter.SelectComm and = Me.cmdUploadFile
tempDataAdapter.Fill(resul tDataTable ) '--- 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
resultDataTable.Rows.Add(d bRow)
tempDataAdapter.Update(res ultDataTab le) <------ 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
tempDataAdapter.Dispose()
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.
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.FileNa
uploadFileType = Uploader.PostedFile.Conten
uploadFileSize = Uploader.PostedFile.Conten
theFileName = System.IO.Path.GetFileName
fileData = GetByteArrayFromFileField(
dataManager.WriteFileToDB(
'--------------- Note I am using a type dataset and passing the "pageDataSet.WebTeamAttach
'------ Code to convert to byte array -----
Public Function GetByteArrayFromFileField(
' 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(FileFi
intFileLength = FileField.PostedFile.Conte
ReDim bytData(intFileLength)
objStream = FileField.PostedFile.Input
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.WebTeamAttachm
ByVal strName As String, ByVal ownerID As Integer, ByVal strType As String, ByRef Buffer As Byte()) As Integer
Dim tempDataAdapter As SqlClient.SqlDataAdapter
Try
tempDataAdapter = New SqlClient.SqlDataAdapter
tempDataAdapter.SelectComm
tempDataAdapter.Fill(resul
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
resultDataTable.Rows.Add(d
tempDataAdapter.Update(res
Finally ------ the Byte() array Named "Buffer" how can I get this field into the database???????
If Not tempDataAdapter Is Nothing Then
tempDataAdapter.Dispose()
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
http://support.microsoft.com/default.aspx?scid=kb;en-us;326502