Error: Unable to cast object of type &#x27;System.String&#x27; to type &#x27;System.Byte[]&#x27;

in following line

Response.BinaryWrite(DirectCast(rdr("File_Name"), Byte()))

you are trying to case "FILENAME" which is a string to byte.

which will not work.
u said trying to upload,
but looking at ur code, it seems you are trying to push the file to user.
is that correct?

also, whats the data type defined for file name in your database?
are u saving files in DB or just the name?

ASKER

Correct. I am trying to read (download) the file not push.

Datatype for file_name is nvarchar(1000)
Datatype for file is varbinary(max)

but, where are you files on the server?
u need to read them first

apologies.
ignore my previous post

Response.BinaryWrite(DirectCast(rdr("File"), Byte()))

above code should work.
have you checked that there is actually data in the DB?

ASKER

excellent point.....I am not even sure if I am uploading the file to the server.

For Upload File I used the FileUploadcontrol and to insert the data I used a stored procedure which gets the following values:

Me.FileUpload1.PostedFile.FileName, GetStreamAsByteArray(Me.FileUpload1.PostedFile.InputStream)

ASKER

Yes it's storing data:

File_name File
C:\Documents and Settings\DL1ITJJR\My Documents\Excel Files\AMD.xls <Binary data>

The directcast should be right in the "File", but you need to ensure that the binary data actually contains the file. Have you validated the process that you use to insert it into the database table?

ASKER

How could I validate that?

Here is a good article that covers all the code that you need for this. It appears that you do not need the Directcast, but you can do a CType() instead:

Dim result as Byte() = Nothing

result = CType(dr.GetValue(0), Byte())

ASKER

the excel file holds one funky character regardless of which one I open up :

Ð

What is the datatype in the database?

varbinary(max)

ASKER

Correct

dbaduck: DirectCast performs better than CType and should be used unless you possibly need to coerce something to another unreleated type. Also dr.GetValue(0) doesn't perform any better and is less readable than rdr("File").

jamesrh: thanks for the clarifications. I know about DirectCast and the differences between the GetValue and just calling the rdr("File").

But that still does not explain why the file is uploaded and ends up garbage.

What does the code look like that uploads it to the database?

One quick sanity check you could do is run a SELECT DATALENGTH(File) FROM files query on the Sql Server to verify that there is a reasonable and most likely slightly different number of bytes being stored for each row in the table.

ASKER

This was asked as a related question. All the code is in the other question. Sorry I am away from my laptop.

dbaduck: 2 related questions back is the code for the upload. FYI, I was a little touchy about the code as the original code the poster started with including Response.BinaryWrite(DirectCast(rdr("File"), Byte())) instead of Response.BinaryWrite(DirectCast(rdr("File_Name"), Byte())) was mine from the immediately preceeding related question.

JessyRobinson1234: In the very first question with respect to the upload, if the web server has framework 3.5 you can directly get a byte array version of the uploaded file using the FileBytes property instead of what you are doing now.

ASKER

Yes it does have framework 3.5. Great tip. How would I need to change my code?

ASKER

Jamesrh: I ran the SELECT DATALENGTH(File) FROM files and it's returning 1 bytes for all the records. There must be something wrong with my insert statement.

that clearly means file was not saved in the DB correctly

ASKER

this is what I use for the insert statement:

Private Function GetStreamAsByteArray(ByVal stream As System.IO.Stream) As Byte()

        Dim streamLength As Integer = Convert.ToInt32(stream.Length)

        Dim fileData As Byte() = New Byte(streamLength) {}

        ' Read the file into a byte array
        stream.Read(fileData, 0, streamLength)
        stream.Close()

        Return fileData

    End Function

    Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim CTAdapter As New CTTableAdapters.CTTableAdapter
        Try
            CTAdapter.Insert_CT(Me.txtCTDate.Text.ToString, Me.ddlShipment.SelectedValue.ToString, Me.txtTrailer.Text.ToString, Me.txtPullRequest.Text.ToString, CDate(DateTime.Now.ToString("d") + ", " + Me.txtPullReqTime.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtPullReqTimeTwo.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtPullReqTimeThree.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtPullReqTimeFour.Text.ToString), Me.txtFill.Text.ToString, CDate(DateTime.Now.ToString("d") + ", " + Me.TxtPull.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtValCompleted.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.TxtManifest.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.TxtInvoiceRcvd.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.TxtSEDStart.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtTrailerLoaded.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtSedCompl.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtDriverDep.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtCustomsClear.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtBorderDepart.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtArrivalFC.Text.ToString), CDate(DateTime.Now.ToString("d") + ", " + Me.txtCT.Text.ToString), Me.txtComments.Text.ToString, Me.cbPr1Inv.Checked, Me.cbPR2Inv.Checked, Me.cbPR3Inv.Checked, Me.cbPR4Inv.Checked, Me.cbIncPull.Checked, Me.cbIncPart.Checked, Me.cbMSSyst.Checked, Me.cbMSIncorrect.Checked, Me.cbIRSyst.Checked, Me.cbIRIncorrect.Checked, Me.cbSEDDelay.Checked, Me.cbSEDSIncorrect.Checked, Me.cbSEDCincorrect.Checked, Me.cbBATraf.Checked, Me.cbBDRedLight.Checked, Me.FileUpload1.PostedFile.FileName, GetStreamAsByteArray(Me.FileUpload1.PostedFile.InputStream))
        Catch ex As SqlClient.SqlException
            Exit Sub
        End Try

    End Sub

Open in new window

this doesnt explains anything.
does it use stored proc at the end?
if it does then paste that

ASKER

Sorry about that. Here's my stored procedure.

ALTER PROCEDURE [dbo].[Insert_CT]
	@CT_Date datetime,
	@Ship_Name nvarchar(50),
	@Trailer_Nbr nvarchar(50),
	@Pull_Request nvarchar(50),
	@Pull_Req_Time datetime,	
	@Pull_Req_Time_Two datetime,	
	@Pull_Req_Time_Three datetime,
	@Pull_Req_Time_Four datetime,
	@Fill_Rate nvarchar(10),
	@Pull_Compl_Time datetime,
	@Val_Compl_Time datetime,
	@Manifest_Time datetime,
	@Invoice_Time datetime,
	@SED_Start_Time datetime,
	@Trailer_Load_Time datetime,
	@SED_Compl_Time datetime,
	@Driver_Depart_Time datetime,
	@Customs_Time datetime,
	@Border_Depart datetime,
	@Trailer_Arrive_Time datetime,
	@Cycle_Time datetime,
	@Comments nvarchar(max),
	@Rc_PR1_Inv bit,
	@Rc_PR2_Inv bit,
	@Rc_PR3_Inv bit,
    @Rc_PR4_Inv bit,
	@Rc_VC_Inc_Pull bit,
	@Rc_VC_Inc_Part bit,
	@Rc_MS_Syst bit,
	@Rc_MS_Incorr bit,
	@Rc_IR_Syst bit,
	@Rc_IR_Incorr bit,
	@Rc_SED_Delay bit,
	@Rc_SED_Incorr bit,
	@Rc_SEDS_Incorr bit,
	@RC_BA_Traffic bit,
	@RC_BD_Red_Light bit,
	@File_Name nvarchar(1000),
	@File varbinary
	

	
AS
BEGIN
SET NOCOUNT ON;
	DECLARE @Msg nvarchar(max)
	DECLARE @Ship_Key int

    SET @Ship_Key = (select dbo.Get_Shipment_Key(@Ship_Name ))


--  Insert Header Values
	BEGIN
	   INSERT INTO Cycle_Time VALUES (@CT_Date, @Ship_Key, @Trailer_Nbr,@Pull_Request,@Pull_Req_Time, @Pull_Req_Time_Two, @Pull_Req_Time_Three,@Pull_Req_Time_Four,@Fill_Rate, @Pull_Compl_Time, @Val_Compl_Time,
		@Manifest_Time, @Invoice_Time, @SED_Start_Time, @Trailer_Load_Time, @SED_Compl_Time, @Driver_Depart_Time, @Customs_Time,@Border_Depart, @Trailer_Arrive_Time, 
		@Cycle_Time, @Comments,0,@Rc_PR1_Inv,@Rc_PR2_Inv,@Rc_PR3_Inv,@Rc_PR4_Inv,@Rc_VC_Inc_Pull,@Rc_VC_Inc_Part,@Rc_MS_Syst,@Rc_MS_Incorr,@Rc_IR_Syst,@Rc_IR_Incorr, @Rc_SED_Delay,@Rc_SED_Incorr,@Rc_SEDS_Incorr, @RC_BA_Traffic, @RC_BD_Red_Light,@File_Name, @File) 
	   GOTO Done
	END

Error:
	RAISERROR(@Msg,11,1)

Done:

END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION