[RESOLVED]Upload image to SQL Compact 4 db

Hi guys,

I’m trying to upload an image to a specific users db row (type=image)

Using SQL Compact 4 – I’m getting the error below. Read up that there is a hot fix for the issue in Compact 3.5 but I have Version 4 – so thinking that this hot fix SHOULD be included in V4.

Unless something else is the problem – any assistance would really be appreciated.

Many thanks

An SqlCeParameter with ParameterName ’1′ is not contained by this SqlCeParameterCollection.

Description: An
unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.IndexOutOfRangeException: An SqlCeParameter with ParameterName ’1′ is not contained by this SqlCeParameterCollection.

Source Error: 

Line 64:     //var db = Database.Open("MyDB");
Line 65:     var ProfileImg = "UPDATE UserProfile SET ProfileImage=@0 WHERE LOWER(Email) = LOWER(@1)";
Line 66:     db.Execute(ProfileImg, newFileName);
Line 67: }
Line 68: 

I’ve tested a normal updating a normal String Value to another char field in the database and get the same error – only when commenting out the db.Execute line loads the page successfully – a bit more confused now.

This does not explain anything. The error says there is no parameter @1. Did you set it somewhere?

hi smirnov – im having a look into it now.

Feeling a bit stupid now – made a few changes – left out the reference to the user accounts email…also changed it to db.Execute

db.Execute("UPDATE UserProfile SET CopyofID=@0 WHERE LOWER(Email) = LOWER(@1)", @photo, @WebSecurity.CurrentUserName);

Now getting error below – the db type is set to Image and when retrieving the image from the web page I use. The actual image is a .jpg which uploads fine to the site directory – just not the DB.

Thanks,

The UPDATE works if i test and change the value and db column to a char – just when reverting to image and uploading an actual image that i get this error – do i need to make a change in the image type?

No mapping exists from DbType System.Web.Helpers.WebImage to a known SqlCeType

@{  
    WebImage photo = null;
    var newFileName = "";
    var imagePath = "";
    var imageThumbPath  = "";
    var db = Database.Open("StarterSite");
 
    if(IsPost){
 
        photo = WebImage.GetImageFromRequest();
        if(photo != null){
             newFileName = Guid.NewGuid().ToString() + "_" +
                 Path.GetFileName(photo.FileName);
             imagePath = @"images" + newFileName;
             photo.Save(@"~" + imagePath);
 
            imageThumbPath = @"imagesthumbs" + newFileName;
            photo.Resize(width: 60, height: 60, preserveAspectRatio: true,
               preventEnlarge: true);
            photo.Save(@"~" + imageThumbPath); 
            db.Execute("UPDATE UserProfile SET CopyofID=@0 WHERE LOWER(Email) = LOWER(@1)", photo, @WebSecurity.CurrentUserName);
            }
    }
db.Execute("UPDATE UserProfile SET CopyofID=@0 WHERE LOWER(Email) = LOWER(@1)", @photo, @WebSecurity.CurrentUserName);

In db.Execute(…) instead of just photo (which is System.Web.Helpers.WebImage) try to use 

photo.GetBytes("image/jpeg")

which should return a byte array.

thanks smirnov – worked 100%.

Awesome – 1 last question.

How would I attach the image to an outgoing email.  I suspect I’d have to give 2 instructions (1.) retrieve and store in correct variable type. (2.) attach in outgoing email.

(1.) var CopyofID = db.QueryValue("Select CopyofID from UserProfile WHERE LOWER(Email) = LOWER(@0)", WebSecurity.CurrentUserName);

(2.) Really not sure what how to go about attaching the actual image to the email.

Yes, you can it do it as described in 2 steps. See example here http://geekswithblogs.net/SanjayU/archive/2008/01/31/119155.aspx 

Another way is to save the file temporarily somewhere on server and then attach it: http://stackoverflow.com/questions/2317012/attaching-image-in-the-body-of-mail-in-c-sharp 

Thanks – but don’t you know of an example using asp.net razor – not too familiar with the code in above links – I will keep trying to google it – haven’t had proper luck till now. 

Why do you need it using razor? You can send it from controller which will not differ from the above examples. If you need it from view/razor then first make a code which could send any email, just without any attachment but with other data from the database
and then just add the code with MemoryStream as described in one of the links above.

Guess i dont fully understand it that good yet – but at the moment i have code that successfully sends an email with the specific users data being pulled and populated in the email. I will look at the links again. thanks - 

So you would just need to add some code based on the following example

Dim strMem As System.IO.MemoryStream = New System.IO.MemoryStream(CType(dt.Rows(0)("ImageColumnyte()))
Dim strWriter As System.IO.StreamWriter = New System.IO.StreamWriter(strMem)
strWriter.Flush()

'this is very important..wont work without it
strMem.Position = 0

'Filename and content type are hardcoded here, but I assume you get them from GetYourStuff() above
Dim attachment As System.Net.Mail.Attachment = New System.Net.Mail.Attachment(strMem, "myFile.txt", "text/plain")
msg.Attachments.Add(attachment)

which is vb but on c# it will not be very different

System.IO.MemoryStream strMem = new System.IO.MemoryStream(...your db column here ...);
System.IO.StreamWriter strWriter = new System.IO.StreamWriter(strMem);
strWriter.Flush();
strMem.Position = 0;

System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(strMem, "myFile.JPG", "image/jpg");
msg.Attachments.Add(attachment);

Thanks for the quick reply – I haven’t used memory stream before – but i will give it my best shot.

(I’m using webmatrix asp.net c# (with razor), sqlce db) – busy reading up and trying out the code – thanks, will post back on results

Just  a few questions:

1. DB Column CopyofID (column type is image + storing a byte array) – is QueryValue correct in getting the CopyofID image and storing it in var type – not getting any errors when running the page.

var CopyofID = db.QueryValue("Select CopyofID from UserProfile WHERE LOWER(Email) = LOWER(@0)", WebSecurity.CurrentUserName);

2.  Not sure where in which section to insert the code that gets the stream and does the Attachment.


 string selectedAccountEmail = db.QueryValue("SELECT AccEmail FROM Accounts WHERE Account_Name = @0", selectedAccount);
               
                WebMail.Send(
                            to: "selectedAccountEmail",
                            subject: "Notification subject by user" + " - ",
                                            body: "Message")
                                                  
                                                  
                                                  ;
 
                                           System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
                                           System.IO.MemoryStream strMem = new System.IO.MemoryStream(CopyofID); //column containing byte array of image
                                           System.IO.StreamWriter strWriter = new System.IO.StreamWriter(strMem);
                                           strWriter.Flush();
                                           strMem.Position = 0;
 
                                           System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(strMem, "UserID.JPG","image/jpg");
                                           msg.Attachments.Add(attachment);

Ok, I see. WebMail does not support attachments from streams. It only supports file paths, which means you need to save the file on the server and then use filesToAttach of the Send method. Check this tutorial http://www.asp.net/web-pages/overview/getting-started/11-adding-email-to-your-web-site 

If you do not want to save files on server. Then you need to use the MailMessage and SmtpClient directly (as per given links).

Thanks again for your help smirnov – having you guys here to help really saves time.

I will explore both of their capabilities to determine which one is best suited for the environmnet.

Just as a general rule – which is the preferred method between Webmail and MailMessage in terms of better features?

I will mark both options as answers – for anyone looking at this thread in the future.

Quoted from http://forums.asp.net/t/1929033.aspx?The+difference+about+using+WebMail+and+MailMessage+

[WebMail is] designed to be a simple-to-use helper. You get more functionality if you use the underlying MailMessage class directly. For example, the WebMail helper doesn’t allow you to specify a From name or alternative parts
to a message.

In other words, it’s just a helper method that includes most often used functions but not e.g. attaching binary streams (which is not a very usual thing).

Awesome = -

Thanks !!

Leave a Reply