Using the UPDATE MySQL Command in C#

[bananacat]

I'm a little bit stuck at this moment. As of now, this is the code that I have:

private void btnChangeImage_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialogForImgUser = new OpenFileDialog())
            {
                string location = null;
                string fileName = null;
                openFileDialogForImgUser.Filter = "Image Files (*.jpg, *.png, *.gif, *.bmp)|*.jpg; *.png; *.gif; *.bmp|All Files (*.*)|*.*"; // filtering only picture file types
                openFileDialogForImgUser.ShowDialog(); // show the file open dialog box
                imgUser.Image = new Bitmap(openFileDialogForImgUser.FileName); //showing the image opened in the picturebox
                location = openFileDialogForImgUser.FileName;
                fileName = openFileDialogForImgUser.SafeFileName;

                FileStream fs = new FileStream(location, FileMode.Open, FileAccess.Read); //Creating a filestream to open the image file
                int fileLength = (int)fs.Length; // getting the length of the file in bytes
                byte[] rawdata = new byte[fileLength]; // creating an array to store the image as bytes
                fs.Read(rawdata, 0, (int)fileLength); // using the filestream and converting the image to bits and storing it in an array

                MySQLOperations MySQLOperationsObj = new MySQLOperations("localhost", "root", "myPass");
                MySQLOperationsObj.saveImage(rawdata);
                fs.Close();
            }
        }

The code below is from the MySQLOperations class that I have made:

public void saveImage(byte[] rawdata)
        {
            try
            {
                string myConnectionString = "Data Source = " + server + "; User = " + user + "; Port = 3306; Password = " + password + ";";
                MySqlConnection myConnection = new MySqlConnection(myConnectionString);
                string currentUser = formLogin.userID;
                string useDataBaseCommand = "USE " + dbName + ";";
                string updateTableCommand = "UPDATE tblUsers SET UserImage = @file WHERE Username = \'" + currentUser + "\';";
                MySqlCommand myCommand = new MySqlCommand((useDataBaseCommand + updateTableCommand), myConnection);
                myCommand.Parameters.AddWithValue("@file", rawdata);
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

I can open a file dialog, choose an image, but when I click on the Open button, an error pops up: "You have an error in your SQL syntax; check the manual that correponds to your MySQL server version for the right syntax to use near ';UPDATE tblUsers SET UserImage = _binary'?PNG ... (and so on with some random characters).

I know there may be something wrong with the syntax I'm using.
Thanks.

• I guess I've mistakenly posted this in the Community Center.
• My apologies. I can't somehow delete it.

[arunkumars]

"UPDATE tblUsers SET UserImage = @file"
What does @file hold there ?
probably it should hold the path i suppose, so the patch is usually like "C:\Desktop......", the best way to incluse the escape sequence in the query would be to add "@", you can actually read the file path and do this in your update statement,
SET UserImage = @"C:\Desktop....";

Try this.

[cgeier]

Creating and Using a Database

"...The USE statement is special in another way, too: it must be given on a single line..."

Connection Strings
"...The port 3306 is the default MySql port..."

Try the following:

In "saveImage":

Change line #5 to:

string myConnectionString = "Server = " + server + "; Port = 3306; Database=" + dbName + "; Uid = " + user + "; Port = 3306; Pwd = " + password + ";";

Change line #10 to:

MySqlCommand myCommand = new MySqlCommand(updateTableCommand, myConnection);

Or you can use the following:

Ensure you have a reference to "MySql.Data":

• Select "Project"
• Select "Reference"
• Click ".NET" tab
• Select "MySql.Data"

Add using statement:

• using MySql.Data.MySqlClient

Connection String:

private string connectStr = "Server = " + server + "; Port = 3306; Database=dbName; Uid = " + user + "; Port = 3306; Pwd = " + password + ";";

updateUserImage:

public static string updateUserImage(string currentUser, byte[] userImage, string tblName)
{    
    string status = string.Empty;
    string errMsg = string.Empty;

    try
    {
        using (MySqlConnection cn = new MySqlConnection(connectStr))
        {
            string sqlText = string.Empty;

            sqlText = "Update [" + tblName + "]";
            sqlText += "SET UserImage = @userImage ";
            sqlText += "where Username = @currentUser";

            //open connection to db
            cn.Open();

            using (MySqlCommand sqlCmd = new MySqlCommand(sqlText, cn))
            {

                sqlCmd.Parameters.AddWithValue("@currentUser", currentUser);
                sqlCmd.Parameters.AddWithValue("@userImage", userImage);

                //MySqlParameter paramName1 = new MySqlParameter();
                //paramName1.ParameterName = "@userImage";
                //paramName1.MySqlDbType = MySqlDbType.VarBinary;
                //paramName1.Value = userImage;
                //sqlCmd.Parameters.Add(paramName1);

                //execute
                sqlCmd.ExecuteNonQuery();
            }//using SqlCommand
        }//using SqlConnection

        status = "Image updated for : " + currentUser;
    }//try
    catch (MySqlException ex)
    {
        errMsg = "Error:: updateUserImage (" + currentUser + "): " + ex.Message;
        errMsg += System.Environment.NewLine + System.Environment.NewLine;
        errMsg += "Connection String: " + System.Environment.NewLine + connectStr;
        status = errMsg;
        MessageBox.Show(errMsg, "Error - Update User Image", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }//SqlException
    catch (Exception ex)
    {
        errMsg = "Error:: updateUserImage (" + currentUser + "): " + ex.Message;
        errMsg += System.Environment.NewLine + System.Environment.NewLine;
        errMsg += "Connection String: " + System.Environment.NewLine + connectStr;
        status = errMsg;
        MessageBox.Show(errMsg, "Error - Update User Image", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }//Exception

    return status;

}//updateUserImage

[cgeier]

In my previous post, in the connection string, "Port" should only be in there once.