none
Insert image to a mysql table RRS feed

  • Question

  • I have used below code to insert student id and picture to my sql database and got an error message and appreciate your assistance.

    …….MySQL table creation...

    create table mst_image (student_id varchar(11) not null,foreign key(student_id) references mst_student(student_id),image longblob not null);

    …..error...... 

    MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'student_mgt.insert into mst_image(student_id,image) values ('04190319003',_binar' at line 1'

    …..c# coding.....

    private void button4_Click(object sender, EventArgs e)
            {
                MySqlCommand cmd;
                FileStream fs;
                BinaryReader br;
               
                        string FileName = txtStudentImage.Text;
                        byte[] ImageData;
                        fs = new FileStream(FileName, FileMode.Open, FileAccess.Read);
                        br = new BinaryReader(fs);
                        ImageData = br.ReadBytes((int)fs.Length);
                        br.Close();
                        fs.Close();
                        string CmdString = "student_mgt.insert into mst_image(student_id,image) values (@id,@image)";
                        cmd = new MySqlCommand(CmdString, conn);
                        cmd.Parameters.Add("@id", MySqlDbType.VarChar, 11);
                        cmd.Parameters.Add("@image", MySqlDbType.LongBlob);
                        cmd.Parameters["@id"].Value = txtbx_prntid.Text;
                        cmd.Parameters["@image"].Value =picbx_vwid.Image;
                    conn.Open();
                        int RowsAffected = cmd.ExecuteNonQuery();
                        if (RowsAffected > 0)
                        {
                            MessageBox.Show("Image saved sucessfully!");
                        }
                        conn.Close();

            }

    Thursday, January 30, 2020 6:51 AM

Answers

  • Hi Sampath Gamaarchchi,

    Thank you for posting here.

    Your 'CmdString' is incorrect, and you need to delete 'student_mgt.'.

    string CmdString = "insert into mst_image(student_id,image) values (@id,@image)";

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 30, 2020 9:32 AM
    Moderator
  • Hi Sampath Gamaarchchi,

    Thanks for your feedback.

    It seems that you have not given DataBase name in your connection string.

    Try the following code to provide your databasename in your connection string.

    string connstring = "datasource=localhost;port=3306;Initial Catalog='your DBname';username=root;password= your password";
     

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 31, 2020 3:56 AM
    Moderator

All replies

  • Hi Sampath Gamaarchchi,

    Thank you for posting here.

    Your 'CmdString' is incorrect, and you need to delete 'student_mgt.'.

    string CmdString = "insert into mst_image(student_id,image) values (@id,@image)";

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 30, 2020 9:32 AM
    Moderator
  • I tried with that. But it says 'No database selected'. Pls refer the attachment for further details.

    Friday, January 31, 2020 3:23 AM
  • Hi Sampath Gamaarchchi,

    Thanks for your feedback.

    It seems that you have not given DataBase name in your connection string.

    Try the following code to provide your databasename in your connection string.

    string connstring = "datasource=localhost;port=3306;Initial Catalog='your DBname';username=root;password= your password";
     

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 31, 2020 3:56 AM
    Moderator
  • Hi Xingyu Zhao,

    It is working now. Thank you for your immediate response.

    Friday, January 31, 2020 4:03 AM