can't save picture to database

Question

Database is .mdf, datatype of column is varchar << must be used as project requirement

I input textFilePath.text that I generate from txtFilePath.Text = open.FileName;, but it makes an error when I press button input. The error that is displayed on the line cmd.CommandText is

Sqlexception is unhandled : String or binary data would be truncated. The statement has been terminated.

But if I replace the textFilePath.text with other string it will be success to input to the database.

This is the code, thanks for helping me.

private void Save()
{
    txtFilePath.Text.ToString();
    cn.Open();
    cmd.CommandText = "insert into Inventory (ID,Item,Gender,Qty,Price,FilePath) values('" + txtID.Text + "','" + txtItem.Text + "','" + Gender + "','" + numQty.Value + "','" + numPrice.Value + "','" + txtFilePath.Text + "')";
    cmd.ExecuteNonQuery();
    cmd.Clone();
    cn.Close();
}

private void ButtonChoose_Click(object sender, EventArgs e)
{
    try
    {
        // open file dialog
        OpenFileDialog open = new OpenFileDialog();
        // image filters
        open.Filter = "Image Files(*.jpg; *.jpeg; *.gif; *.bmp)|*.jpg; *.jpeg; *.gif; *.bmp";

        if (open.ShowDialog() == DialogResult.OK)
        {
            // display image in picture box
            pictureBox1.Image = new Bitmap(open.FileName);
            // image file path
            txtFilePath.Text = open.FileName;

        }
    }
    catch (Exception)
    {
        throw new ApplicationException("Image loading error....");
    }
}

private void buttonSave_Click(object sender, EventArgs e)
{
    if (txtID.Text != "" & txtItem.Text != "" & Gender != "" & numPrice.Value > 0 & numQty.Value > 0 & txtFilePath.Text != "")
    {
        Save();
        MessageBox.Show("Recorded");
        gridViewLoad();

    }
    else
    {
        MessageBox.Show("all field must be filled");
    }
}

possible duplicate of [c# store a picture to database](http://stackoverflow.com/questions/5961071/c-sharp-store-a-picture-to-database) — Ňɏssa Pøngjǣrdenlarp, Jan 08 '15 at 15:24
What part of the error message is confusing? It says that the data you are trying to put in the column in larger than the column can hold. — Golden Dragon, Jan 08 '15 at 15:24
@Plutonix - The title of this post is a little misleading. He's not trying to save a picture to the database. It looks more like he's trying to save a path to the database. That question is not a duplicate. — Icemanind, Jan 08 '15 at 15:30
You are right, sorry for asking this easy problem. i'm a newbe. thanks for the guidance — Eka Soedono, Jan 08 '15 at 15:32
Check the column size of `FilePath` and then check the `txtFilePath.Text` values length and you shoul see why the exception is thrown — Mahesh, Jan 08 '15 at 15:32
possible duplicate of [error, string or binary data would be truncated when trying to insert](http://stackoverflow.com/questions/5591473/error-string-or-binary-data-would-be-truncated-when-trying-to-insert) — C-Pound Guru, Jan 08 '15 at 15:42

Álvaro Menezes · Answer 1 · 2015-01-08T15:44:44.017

The error as Golden Dragon said is that you are trying to INSERT INTO the table Inventory a string bigger than the allowed size of the table field.

To solve it you should increase the size of the field FilePath in the database.

It's also important to point out that running a query with string concatenation is a huge security flaw, see SQL Injection for more information.

You should always use parameterized querys as in the example:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    command.Parameters.Add("@ID", SqlDbType.Int);
    command.Parameters["@ID"].Value = customerID;

    // Use AddWithValue to assign Demographics.
    // SQL Server will implicitly convert strings into XML.
    command.Parameters.AddWithValue("@demographics", demoXml);

    try
    {
        connection.Open();
        Int32 rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine("RowsAffected: {0}", rowsAffected);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

From: MSDN - SqlCommand.Parameters Property

The example also shows how to use a connection with using (avoiding not closing the connection if an error occurs).

In your case it would be:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    string commandText = "insert into Inventory (ID,Item,Gender,Qty,Price,FilePath) values(@ID, @Item, @Gender, @Qty, @Price, @FilePath)";

    SqlCommand command = new SqlCommand(commandText, connection);

    command.Parameters.Add("@ID", SqlDbType.Int).Value = txtID.Text;
    ...
    command.Parameters.Add("@Price", SqlDbType.Decimal, 11, 4).Value = numPrice.Value;
    ...

    connection.Open();
    command.ExecuteNonQuery();
}

score 0 · Answer 2 · answered Jan 08 '15 at 15:28

0

@Eka Soedono Please try the below code and let me know if it works.

cmd.CommandText = "insert into Inventory (ID,Item,Gender,Qty,Price,@path); 
cmd.Parameters.Add("@path",txtFilePath.Text);

answered Jan 08 '15 at 15:28

TheGaME

443
2
8
21

can't save picture to database

2 Answers2