uploading a text or doc file to a database

[Christopher_Arm]

Does anyone have an example of how to upload a text file or doc file into a SQL database from a web application ? So far I have completed the rist part of my program: the code to upload the file from a directory and store the file in a data folder within the application.

Can anyone help me out here?


Thanks in advance.

[mendhak]

upload a text file or doc file into a SQL database from a web application ?

...and store the file in a data folder within the application.

Two different things.

http://support.microsoft.com/kb/323245

http://www.csharpfriends.com/Article...?articleID=112

[Christopher_Arm]

Two different things.

http://support.microsoft.com/kb/323245

http://www.csharpfriends.com/Article...?articleID=112

Question: is there a visual basic.net equivalent out there( and I have googled to no avail) because c-sharp has some marked differences that make it a little hard to follow ?

[Grimfort]

Theres a number of converters out there, its not that different when you look, heres one anyway: http://www.kamalpatel.net/ConvertCSharp2VB.aspx

[Christopher_Arm]

Ok with what I 've cobbled together from the kind information.

Will this work guys ?

    Private Sub Submit1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick
        Dim intDocLen As Integer = File1.PostedFile.ContentLength
        Dim Docbuffer() As Byte = New Byte(intDocLen) {}
        Dim objStream As Stream
        objStream = File1.PostedFile.InputStream
        objStream.Read(Docbuffer, 0, intDocLen)
        'Dim Conn As SqlConnection
        Dim cmdUploadDoc As SqlCommand
        If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then

            Dim fn As String = System.IO.Path.GetFileName(File1.PostedFile.FileName)
            Dim SaveLocation As String = Server.MapPath("Data") & "\" & fn
            Try
                File1.PostedFile.SaveAs(SaveLocation)
                Response.Write("The file has been uploaded.")
            Catch Exc As Exception
                Response.Write("Error: " & Exc.Message)
            End Try
        Else
            Response.Write("Please select a file to upload.")
        End If

        Dim Conn As SqlConnection = New _
               SqlConnection("Server =blah ; database = blaise_blah; userid = yakety;password = smackety")

        cmdUploadDoc = New SqlCommand("uSP_BooksUploadFile", Conn)
        cmdUploadDoc.CommandType = CommandType.StoredProcedure

        cmdUploadDoc.Parameters.Add("@Filename ", SqlDbType.VarBinary, 200)
        cmdUploadDoc.Parameters.Add("@FileLength", SqlDbType.BigInt)
        cmdUploadDoc.Parameters.Add("@FileType", SqlDbType.VarChar, 20)


        cmdUploadDoc.Parameters(0).Value = File1.PostedFile.FileName

        cmdUploadDoc.Parameters(1).Value = Docbuffer
        cmdUploadDoc.Parameters(2).Value = File1.PostedFile.ContentType
        Conn.Open()
        cmdUploadDoc.ExecuteNonQuery()
        Conn.Close()

    End Sub

[Christopher_Arm]

Ok. running the code I got the following error:

Invalid cast from System.String to System.Byte[].

and it stops right around here......

cmdUploadDoc.ExecuteNonQuery()

[Grimfort]

Well, one thing stands out, the parameters dont match up:
@filename is a varbinary and you set it to a string (nvarchar instead maybe?)
@filelength is a bigint and you are passing it the docbuffer

I think you want something like this

cmdUploadDoc.Parameters.Add("@Filename ", File1.PostedFile.FileName)
cmdUploadDoc.Parameters.Add("@Filedata*fix*, Docbuffer)
cmdUploadDoc.Parameters.Add("@FileLength", Docbuffer.Length)  
cmdUploadDoc.Parameters.Add("@FileType", File1.PostedFile.ContentType)

[Christopher_Arm]

Ok so then this...

cmdUploadDoc.Parameters.Add("@Filename ", SqlDbType.NVarChar, 200)
        cmdUploadDoc.Parameters.Add("@FileData ", SqlDbType.VarBinary)
        cmdUploadDoc.Parameters.Add("@FileLength", SqlDbType.BigInt)
        cmdUploadDoc.Parameters.Add("@FileType", SqlDbType.VarChar, 20)


        cmdUploadDoc.Parameters(0).Value = File1.PostedFile.FileName
        cmdUploadDoc.Parameters(1).Value = Docbuffer
        cmdUploadDoc.Parameters(2).Value = Docbuffer.Length
        cmdUploadDoc.Parameters(3).Value = File1.PostedFile.ContentType
        Conn.Open()
        cmdUploadDoc.ExecuteNonQuery()

[Grimfort]

Assuming your storedprocedure is setup like that as well, how does it work now?

[Christopher_Arm]

I am getting this..The EXECUTE permission was denied on the object 'uSP_blah blah', database 'blah blah', schema 'blah'.

[Christopher_Arm]

I needed permssion for my SP to execute. (Smacks head)

With this process I can do one file at a time in a given directory how this process being modified to upload multiple files from an entire directory ?

[Grimfort]

Well, you can use the System.IO.Directory.GetFiles ("c:\") type function to get a list of the files as an array, then just look through each item in the list against the main section of your code.

[Christopher_Arm]

Ok. How would you read an element say a string of numbers in the sample file be it a text file and reproduce them in a column and if there are a list of numbers in the file to show the same filename, filedata, filelength and filetype with the different number in the list reflected ?

[Grimfort]

Think your gonna have to say all that again. Try it one step at a time .

[Christopher_Arm]

Right now I have a program that succesfully uploads a file one at a time to a SQL database. Fine.
Now I want to have that same program read the contents of the file in this case a sequence of numbers ex 12234334.

The program will then take that sequence of unique numbers and upload this information into the database in an added column to the table we have in this exercise..let call it FileNumber.

If there are different numbers( that represent the FileNumber) in say the one text file the program would loop reading all of the numbers perhaps stored them in an array structure and then when the program begins uploading you will see in each individual rowset.

FileName, FileData, FileType, FileLength, FileNumber

The Filename FileData Filetype and Filelength values would remain the same until it finished importing all of the individual FileNumbers that exist in the file.

Then when you selected a different file in the same directory this process would repeat again and so on.

I hope this is clearer somewhat.

[Christopher_Arm]

Writing this down logically:

I would need to

a) Create a loop structure in the existing code that read the contents of the file for the unique number using streamreader

b) Put the file content(numbers) into an array

c)Stream this array result for output to the database. (I would need something similiar to what is going in docbuffer which is an stored array of bytes for the filedata.)

d) Place this result in the Filenumber column.

[Christopher_Arm]

cmdUploadDoc.Parameters.Add("@Filename ", SqlDbType.NVarChar)
        cmdUploadDoc.Parameters.Add("@FileData ", SqlDbType.VarBinary)
        cmdUploadDoc.Parameters.Add("@FileLength", SqlDbType.BigInt)
        cmdUploadDoc.Parameters.Add("@FileType", SqlDbType.VarChar, 20)
        'cmdUploadDoc.Parameters.add("@FileNumber",SqlDbType.BigInt)


        cmdUploadDoc.Parameters(0).Value = File1.PostedFile.FileName
        cmdUploadDoc.Parameters(1).Value = Docbuffer
        cmdUploadDoc.Parameters(2).Value = Docbuffer.Length
        cmdUploadDoc.Parameters(3).Value = File1.PostedFile.ContentType
        'cmdUploadDoc.Parameters(4).Value = NumberBuffer
        Conn.Open()
        cmdUploadDoc.ExecuteNonQuery()
        Conn.Close()
        Response.Write("Upload Succesful")

something like this but I need the number that is grabbed from inside the text file to be stored in an array of large order sequenced numbers(ex: 12234456) called Numberbuffer.

that way if the file has this:

12223445
22324456
32356768
45454667

It would show the same filename, filetype, filellength and filedata for each rowset( 4 of them) but when we get to the filenumber column we should see 12223445 for row 1
22324456 for row 2 etcetera on down

[mendhak]

Not entirely clear, but it seems to me that after you have read the file and saved it, you will need your ASP.NET code to read the file again. You will go with the assumption that the user always uploads a text file. Use a StreamReader for this. Then it's a matter of creating new database entries per array you extract from the text.

[Christopher_Arm]

Mendhak,

Thanks. You were right not only did I need a streamreader that program but I needed to place my insert stored procedure there as well along with its parameters and values. The crucial part was to place this in my code...
strline = objStreamReader.ReadLine so that the program would know to go into the file and read line by line of what was inside it.

[mendhak]

So I assume you've accomplished what you needed then?

[Christopher_Arm]

You would be correct in your assumption sir. Now if you would so kind as to give me a point.

[mendhak]

I wonder why it was red for you in the first place. You haven't come across as condescending as far as I've seen.

[Christopher_Arm]

I wonder why it was red for you in the first place. You haven't come across as condescending as far as I've seen.

Thanks Mendhak.