Adding text and image to database with C# and SQL

The following windows form application takes an input for name, age, gender, description, and an image.

However when clicking the “Save Information” button the following exception error occurs:

System.Data.SqlClient.SqlException: " Operand type clash: nvarchar is incompatible with image"

Not particularly sure why the script is interpreting the image as a nvarchar datatype.

Here is the stored procedure for the function.

ALTER PROCEDURE [dbo].[usp_Students_InsertNewStudent]
(
    @StudentName NVARCHAR(200)
    ,@Age NVARCHAR(50)
    ,@Gender NVARCHAR(50)
    ,@Description NVARCHAR(MAX)
    ,@Image IMAGE
    ,@CreatedBy NVARCHAR(50)
)
AS
    BEGIN
        INSERT INTO [dbo].[Students]
           ([StudentName]
           ,[Age]
           ,[Gender]
           ,[Description]
           ,[Image]
           ,[CreatedBy]
           ,[CreatedDate])
     VALUES
        (
            @StudentName
            ,@Age
            ,@Gender
            ,@Description
            ,@Image
            ,@CreatedBy
            ,GETDATE()
        )

    END

And the relevant code.

        private void SaveButton_Click(object sender, EventArgs e)
        {
            if(IsFormValid())
            {
                //Do Update Process
                using (SqlConnection con = new SqlConnection(AppConnection.GetConnectionString())) 
                {
                    using (SqlCommand cmd = new SqlCommand("usp_Students_InsertNewStudent", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.AddWithValue("@StudentName", StudentNameTextBox.Text.Trim());
                        cmd.Parameters.AddWithValue("@Age", AgeTextBox.Text.Trim());
                        cmd.Parameters.AddWithValue("@Gender", GenderTextBox.Text.Trim());
                        cmd.Parameters.AddWithValue("@Description", DescriptionTextBox.Text.Trim());
                        cmd.Parameters.AddWithValue("@Image", IdPictureBox.ImageLocation);
                        cmd.Parameters.AddWithValue("@CreatedBy", LoggedInUser.UserName);


                        if (con.State != ConnectionState.Open)
                            con.Open();

                        cmd.ExecuteNonQuery();

                        MessageBox.Show("Student is successfully updated in the database.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        ResetFormControl();
                    }
                }
            }

        private bool IsFormValid()
        {
            if (StudentNameTextBox.Text.Trim() == string.Empty)
            {
                MessageBox.Show("Student name is Required.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                StudentNameTextBox.Focus();
                return false;
            }

            if (StudentNameTextBox.Text.Length >= 200)
            {
                MessageBox.Show("Student Name length should be less than or equal to 200 characters.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                StudentNameTextBox.Focus();
                return false;
            }
            return true;
        }

        private void UploadButton_Click(object sender, EventArgs e)
        {
            String ImageLocation = "";
            try
            {
                OpenFileDialog dialog = new OpenFileDialog();
                dialog.Filter = "Image Files(*.jpeg;*.bmp;*.png;*.jpg)|*.jpeg;*.bmp;*.png;*.jpg";

                if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                   ImageLocation = dialog.FileName;
                   IdPictureBox.ImageLocation = ImageLocation;
                }
            }
            catch (Exception)
            {
                MessageBox.Show("An Error Occured", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

Answer

Your issue is this line:

cmd.Parameters.AddWithValue("@Image", IdPictureBox.ImageLocation);

This is a string of where the image is located. Which is getting translated from C# string to SQL nvarchar.

You want to insert the actual image. I suspect this may work alone:

cmd.Parameters.AddWithValue("@Image", IdPictureBox.Image);

But I always try to avoid AddWithValue because it infers the type.

Here’s another way:

cmd.Parameters.Add("@Image", SqlDbType.Image).Value = IdPictureBox.Image;

Note this explicitly defines the SQL type, where AddWithValue infers it.

For this to work make sure you specify the SqlDbType that matches the schema.

Here’s how to do the same, for example, using a byte array:

var image = IdPictureBox.Image;
using (var ms = new MemoryStream())
{
    image.Save(ms, image.RawFormat);
    cmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = ms.ToArray();
}

In either case the data type is explicit using this method.

Advertisement

Answer