4 Replies - 6816 Views - Last Post: 27 September 2011 - 08:30 AM Rate Topic: -----

#1 jepoyy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 08-October 09

INSERT/UPDATE longblob in MySql Database

Posted 27 September 2011 - 07:16 AM

public void UpdateUser(ClientAdmin user, bool edit)
        {
            MySqlTransaction trans = conn.BeginTransaction();
            try
            {
                if (edit)
                {
                    cmd = new MySqlCommand(
                        "UPDATE users SET tabletid=?tabletid, clientname=?clientname, username=?username, password=?password, bankaccount_no=?bankacct, contact_no=?contact, emailadd=?email, pic=COMPRESS(?pic), status=?status WHERE userid=?userid",
                        conn);
                }
                else
                {
                    cmd = new MySqlCommand(
                        "INSERT INTO users(tabletid,clientname,username,password,bankaccount_no,contact_no,emailadd,pic,status,userid) VALUES( ?tabletid, ?clientname, ?username, ?password, ?bankacct, ?contact, ?email, COMPRESS(?pic), ?status, ?userid)",
                        conn);
                }

                String Pic = Convert.ToBase64String(user.Pic);

                cmd.Parameters.AddWithValue("tabletid", user.TabletID);
                cmd.Parameters.AddWithValue("clientname", user.ClientName);
                cmd.Parameters.AddWithValue("username", user.UserName);
                cmd.Parameters.AddWithValue("password", user.Password);
                cmd.Parameters.AddWithValue("bankacct", user.BankAcct);
                cmd.Parameters.AddWithValue("contact", user.ContactNo);
                cmd.Parameters.AddWithValue("email", user.Email);
                cmd.Parameters.AddWithValue("pic", Pic);
                cmd.Parameters.AddWithValue("status", user.Status);
                cmd.Parameters.AddWithValue("userid", user.UserID);

                cmd.ExecuteNonQuery();
                trans.Commit();
                ServerInterface.Update("Admin: Update User Successful\n");
            }
            catch(MySqlException ex)
            {
                trans.Rollback();
                ServerInterface.Update(ex.ToString());
                ServerInterface.Update("Admin: Update User Failed\n");
            }
        }



user.Pic is the array byte(byte[]) which contains the image bytes.
There's no error in the code. The user.Pic is working properly when passed.

BUT, when I view the image in the sql database.. It has error in loading the image.
So saving the image in the database is wrong. Please help, idk what to do now. :/

Is This A Good Question/Topic? 0
  • +

Replies To: INSERT/UPDATE longblob in MySql Database

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5583
  • View blog
  • Posts: 11,943
  • Joined: 02-June 10

Re: INSERT/UPDATE longblob in MySql Database

Posted 27 September 2011 - 07:27 AM

Quote

BUT, when I view the image in the sql database.. It has error in loading the image.
It would help a lot if we didn't have to use telepathic powers to guess what the error on the server is.
Was This Post Helpful? 0
  • +
  • -

#3 jepoyy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 08-October 09

Re: INSERT/UPDATE longblob in MySql Database

Posted 27 September 2011 - 07:39 AM

Oh, err. I'll just ask the ways on how to insert BLOB in MySql Database using INSERT/UPDATE command statements.

The way I save the image to the server above is converting it to base64string and compress it but that's not working. It doesn't say any errors anywhere but the image is not saved properly in the database. More like a corrupted image. :/

Is there any other way to save image in MySql Database? :)

EDIT: BTW, I'm searching for other ways. But I can't find anything that suits my situation.

This post has been edited by jepoyy: 27 September 2011 - 07:40 AM

Was This Post Helpful? 0
  • +
  • -

#4 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5583
  • View blog
  • Posts: 11,943
  • Joined: 02-June 10

Re: INSERT/UPDATE longblob in MySql Database

Posted 27 September 2011 - 08:07 AM

After a second look at your code

 String Pic = Convert.ToBase64String(user.Pic);



That is just not going to work. You can't convert an Image into a string in one line.

Googling "C# image to blob" gets lots of techniques.
Was This Post Helpful? 1
  • +
  • -

#5 jepoyy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 08-October 09

Re: INSERT/UPDATE longblob in MySql Database

Posted 27 September 2011 - 08:30 AM

Oh I get it. Haha. Thnx for the info in the string thingy. It works now. THnx :))
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1