updating an image to a database table

pinkpanther27

Member
Joined
Jun 7, 2004
Messages
11
Programming Experience
1-3
I am trying to save .pdf files to a sql server database table, and I am having trouble actually getting the file to save to the table, I think it may have something to do with the datatype I am using to update the field.

This is what my stored procedure looks like:
CREATE proc PractProc
(@var1 varchar(50), @var2 varchar(50), @varFile image)
as
insert into tblPract(fName, fLast, fFile) values(@var1, @var2,@varFile)
GO


Here is the code I am using to update the stored procedure from code:

Try
Dim objConn As New SqlConnection("Initial Catalog=DatabaseName; Data Source=ServerName; uid=UserName; password=Password")
objConn.Open()
Dim objCmd As New SqlCommand("PractProc", objConn)
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@var2", SqlDbType.VarChar).Value = "Value 2!"
objCmd.Parameters.Add("@var1", SqlDbType.VarChar).Value = "Value 1!"
objCmd.Parameters.Add("@varFile", SqlDbType.Image).Value = myData
objCmd.ExecuteNonQuery()
objConn.Close()
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try


Problem and error message:
string or binary data would be truncated the statemnet has been terminated.

This occurs, when the @varFile = mydata parameter is executed.

so this leads me to believe that using image as the datatype is not cutting it, I really dont have a clue to be honest, this is an educated guess. If anyone has any sugguestions please feel free to express them

Thanks,

Pink
 

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
I can't help you with the problem but I made a very similar application that inserts images into a database, essentially it is the same thing because we both turned the files into a byte array. This is my code for converting a file to a byte array and then uploading to a database.

VB.NET:
[size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] ButtonUpload_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] ButtonUpload.Click
 
[/size][size=2][color=#008000]'delcare a filestream variable
 
[/color][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] fs [/size][size=2][color=#0000ff]As[/color][/size][size=2] FileStream
 
[/size][size=2][color=#008000]'selecting the file to use int he filestream
 
[/color][/size][size=2]fs = [/size][size=2][color=#0000ff]New[/color][/size][size=2] FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
 
[/size][size=2][color=#008000]'declare a byte array called 'picData' that is the length of our filestream
 
[/color][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] picData(fs.Length) [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]Byte
 
[/color][/size][size=2][color=#008000]'read the data into the byte array
 
[/color][/size][size=2]fs.Read(picData, 0, fs.Length)
 
[/size][size=2][color=#008000]'declare an integer
 
[/color][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] SQLreturn [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]Integer
 
[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2][color=#008000]'open sql connection
 
[/color][/size][size=2]SqlConnection1.Open()
 
[/size][size=2][color=#008000]'pass data to the sql parameters
 
[/color][/size][size=2]SqlCommand1.Parameters("@pictureData").Value = picData
 
SqlCommand1.Parameters("@pictureTitle").Value = TextBoxTitle.Text
 
SqlCommand1.Parameters("@pictureDescription").Value = TextBoxDescription.Text
 
SqlCommand1.Parameters("@pictureOwner").Value = System.Environment.UserName
 
[/size][size=2][color=#008000]'execute the sqlcommand and retrive the result
 
[/color][/size][size=2]SQLreturn = SqlCommand1.ExecuteNonQuery
 
[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception
 
[/size][size=2][color=#008000]'catch any errors and display in status bar
 
[/color][/size][size=2]StatusBar.Text = "Status: " & ex.Message
 
[/size][size=2][color=#0000ff]Finally
 
[/color][/size][size=2][color=#008000]'close sql connection
 
[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].SqlConnection1.Close()
 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2][color=#008000]'check if the sqlreturn number is higher or lower than 1
 
[/color][/size][size=2][color=#0000ff]If[/color][/size][size=2] SQLreturn <> 1 [/size][size=2][color=#0000ff]Then
 
[/color][/size][size=2]StatusBar.Text = "Status: Could not upload image"
 
[/size][size=2][color=#0000ff]Else
 
[/color][/size][size=2]StatusBar.Text = "Status: INSERT completed"
 
TextBoxTitle.Text = ""
 
TextBoxDescription.Text = ""
 
ButtonUpload.Visible = [/size][size=2][color=#0000ff]False
 
[/color][/size][size=2]utils.loadPicture(Image.FromFile("D:\University\2nd year - resit\VB\assaingment2\FSA\FileSharingApplication\Sunset.jpg"), [/size][size=2][color=#0000ff]Me[/color][/size][size=2].PictureBox)
 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]If
 
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub[/color][/size]
[size=2][color=#0000ff]

[/color][/size]The SQL statment I used was
"INSERT INTO dbo.Pictures (pictureTitle, pictureOwner, pictureDescription, pictureData) VALUES (@pictureTitle, @pictureOwner, @pictureDescription, @pictureData)"
 

pinkpanther27

Member
Joined
Jun 7, 2004
Messages
11
Programming Experience
1-3
Thanks for your reponse levyuk, I was able to upload data to the server


but now I'm having trouble retrieving the .pdf file I uploaded successfully, for some reason the file will not open.

Do you have any download code?

Thanks

Pink
 
Top Bottom