Using DataSource.InsertParameters.Add to Insert Image into MS SQL

I have a insert stored procedure "AGProfileSetup"
Parameters -----
   @UserId uniqueidentifier,
    @ProfilePic image,
    @ProfilePicMIME varchar(50)

used to insert an image for a user into a database.  The problem is from my understanding sql type "Image" is some kind of binary format :D  I don't know how to pass a binary through the InsertParameters.Add function.   Whats the best way to do this?  By the way I am using ASP.NET 3.5 and MS SQL 2008
Dim imageBytes(Picture.PostedFile.InputStream.Length) As Byte
        Picture.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
 
        Dim AGDataSource As New SqlDataSource()
 
 
        AGDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("AGConnectionString").ToString()
 
        AGDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
        AGDataSource.InsertCommand = "AGProfileSetup"
 
        AGDataSource.InsertParameters.Add("UserId", CurrentUser)
        AGDataSource.InsertParameters.Add("@ProfilePic", TypeCode.Byte, imageBytes.ToString())
        AGDataSource.InsertParameters.Add("@ProfilePicMIME", MIMEType)
 
AGDataSource.Insert()
AGDataSource = Nothing

Open in new window

LVL 1
HarleySkaterAsked:
Who is Participating?
 
Bob LearnedConnect With a Mentor Commented:
Aah, yes, I missed that you are using an SqlDataSource.  

Try this instead:

Storing Binary Files Directly in the Database Using ASP.NET 2.0
http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/120606-1.aspx

Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ImageGalleryConnectionString").ConnectionString)
 
    Const SQL As String = "INSERT INTO [Pictures] ([Title], [MIMEType], [ImageData]) VALUES (@Title, @MIMEType, @ImageData)"
    Using myCommand As New SqlCommand(SQL, myConnection)
      myCommand.Parameters.AddWithValue("@Title", PictureTitle.Text.Trim())
      myCommand.Parameters.AddWithValue("@MIMEType", MIMEType)
 
      'Load FileUpload's InputStream into Byte array
      Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte
      UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
      myCommand.Parameters.AddWithValue("@ImageData", imageBytes)
 
      myConnection.Open()
      myCommand.ExecuteNonQuery()
    End Using
  End Using
End Sub 

Open in new window

0
 
HarleySkaterAuthor Commented:
oh yah.  my error message is!!!!!!!!!!!


Input string was not in a correct format. hehe
0
 
Bob LearnedCommented:
You need to pass a byte array from the image that you are working with, not a string, so remove the .ToString() from imageBytes.ToString().
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
HarleySkaterAuthor Commented:
Im sorry, I should have said that.   But, I put ToString() because imageBytes was throwing an error.  I will post that error to, I tried that on another attempt.
0
 
HarleySkaterAuthor Commented:
ERROR: Error      1      Overload resolution failed because no accessible 'Add' can be called with these arguments:
    'Public Function Add(name As String, dbType As System.Data.DbType, value As String) As Integer': Value of type '1-dimensional array of Byte' cannot be converted to 'String'.
    'Public Function Add(name As String, type As System.TypeCode, value As String) As Integer': Value of type '1-dimensional array of Byte' cannot be converted to 'String'.      C:\Documents and Settings\Administrator.ACFUNDING.000\My Documents\AG\AG Website\Settings\Profile\Profile.aspx.vb      58      9      C:\...\AG Website\

Thats why I added the .ToString()

Is there an easier way to do this?
0
 
Bob LearnedCommented:
Try this:

AGDataSource.InsertParameters.AddWithValue("@ProfilePic", imageBytes)
0
 
HarleySkaterAuthor Commented:
also if i take out the TypeCode.Byte I get this error -  

Error      1      Value of type '1-dimensional array of Byte' cannot be converted to 'String'.      C:\Documents and Settings\Administrator.ACFUNDING.000\My Documents\AG\AG Website\Settings\Profile\Profile.aspx.vb      58      58      C:\...\AG Website\

:D
0
 
HarleySkaterAuthor Commented:
AddWithValue gave me this error
Error:      1      'AddWithValue' is not a member of 'System.Web.UI.WebControls.ParameterCollection'.      C:\Documents and Settings\Administrator.ACFUNDING.000\My Documents\AG\AG Website\Settings\Profile\Profile.aspx.vb      58      9      C:\...\AG Website\
0
 
HarleySkaterAuthor Commented:
all I want to do is upload a picture from asp.net 3.5 to my sql 2008 db table hehehe

I have never inserted objects into a database before from asp.net, I didn't it would be this tricky, because its so easy to insert everything else ;D
0
 
HarleySkaterAuthor Commented:
So I am guessing this is an example of when you should not use a stored procedure : )

I'll give it a try : )!
0
 
Bob LearnedCommented:
I try to use stored procedures as much as possible, so if that is what you would like to do, it is a simple fix:


Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ImageGalleryConnectionString").ConnectionString)
 
    Using myCommand As New SqlCommand("InsertImageData", myConnection)
      myCommand.CommandType = CommandType.StoredProcedure
      myCommand.Parameters.AddWithValue("@Title", PictureTitle.Text.Trim())
      myCommand.Parameters.AddWithValue("@MIMEType", MIMEType)
 
      'Load FileUpload's InputStream into Byte array
      Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte
      UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
      myCommand.Parameters.AddWithValue("@ImageData", imageBytes)
 
      myConnection.Open()
      myCommand.ExecuteNonQuery()
    End Using
  End Using
End Sub 

Open in new window

0
 
HarleySkaterAuthor Commented:
I got it to compile and run!  but it didn't actually work hahaha

I am going to throw a try catch in there and see what i can find ; )
0
 
HarleySkaterAuthor Commented:
i got this message this time :D

Your Profile Is Setup Is Confirmed!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

THANK YOUUUUUUUUUUUU  
0
 
Bob LearnedCommented:
Ur velcome :D
0
 
HarleySkaterAuthor Commented:
thank youuu
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.