使用带参数的存储过程将图像从图片框保存并更新到数据库。 [英] Save and update image from picturebox to database using stored procedures with parameters.

问题描述

当我单击更新按钮而不更改图片时，会引发错误，值不能为空。即使我插入一个新行也会出现同样的错误。

when I click update button without changing the picture, it raises an error, "Value can not be null". even if am inserting a new row it gives the same error.

private void UpdateButton_Click(object sender, EventArgs e)
        {
            connect.Open();
            //byte[] image;

           SqlCommand cmd = connect.CreateCommand();
           cmd = new SqlCommand("sp_UpdateDepartStaff", connect);
           cmd.CommandType = CommandType.StoredProcedure;

           //Read Image Bytes into a byte array
           byte[] imageData = ReadFile(profilePicPictureBox.ImageLocation);

            try
            {

                cmd.Parameters.AddWithValue("@StaffID", SqlDbType.Int).Value = logInIDTextBox.Text.ToString();
                cmd.Parameters.AddWithValue("@LogInID", SqlDbType.Int).Value = staffIDTextBox.Text.ToString();
                cmd.Parameters.AddWithValue("@StaffRoleID", SqlDbType.Int).Value = staffRoleIDTextBox.Text.ToString();
                cmd.Parameters.AddWithValue("@FirstName", SqlDbType.NVarChar).Value = firstNameTextBox.Text;
                cmd.Parameters.AddWithValue("@LastName", SqlDbType.NVarChar).Value = lastNameTextBox.Text;
                cmd.Parameters.AddWithValue("@UserName", SqlDbType.NVarChar).Value = userNameTextBox.Text;
                cmd.Parameters.AddWithValue("@Password", SqlDbType.NVarChar).Value = passwordTextBox.Text;
                cmd.Parameters.AddWithValue("@Email", SqlDbType.NVarChar).Value = emailTextBox.Text;
                cmd.Parameters.AddWithValue("@Phone", SqlDbType.NVarChar).Value = phoneTextBox.Text;

                cmd.Parameters.AddWithValue("@StaffRole", SqlDbType.NVarChar).Value = staffRoleTextBox.Text;
                cmd.Parameters.AddWithValue("@Department", SqlDbType.NVarChar).Value = departmentTextBox.Text;
                cmd.Parameters.Add(new SqlParameter("@ProfilePic", (object)imageData));


                cmd.ExecuteNonQuery();
                MessageBox.Show("Updated Successfully!");
                connect.Close();


            }

            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void InsertButton_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = connect.CreateCommand();
            cmd = new SqlCommand("sp_InsertDepartStaff", connect);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
              
                //Read Image Bytes into a byte array
                byte[] imageData = ReadFile(profilePicPictureBox.ImageLocation);

                connect.Open();


                //connect.Open();
                SqlParameter parm = new SqlParameter("@StaffID", SqlDbType.Int);
                parm.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(parm);
                SqlParameter parm1 = new SqlParameter("@LoginID", SqlDbType.Int);
                parm1.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(parm1);
                SqlParameter parm4 = new SqlParameter("@StaffRoleID", SqlDbType.Int);
                parm4.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(parm4);


                cmd.Parameters.AddWithValue("@FirstName", SqlDbType.NVarChar).Value = firstNameTextBox.Text;
                cmd.Parameters.AddWithValue("@LastName", SqlDbType.NVarChar).Value = lastNameTextBox.Text;
                cmd.Parameters.AddWithValue("@UserName", SqlDbType.NVarChar).Value = userNameTextBox.Text;
                cmd.Parameters.AddWithValue("@Password", SqlDbType.NVarChar).Value = passwordTextBox.Text;
                cmd.Parameters.AddWithValue("@Email", SqlDbType.NVarChar).Value = emailTextBox.Text;
                cmd.Parameters.AddWithValue("@Phone", SqlDbType.NVarChar).Value = phoneTextBox.Text;

                cmd.Parameters.AddWithValue("@StaffRole", SqlDbType.NVarChar).Value = staffRoleTextBox.Text;
                cmd.Parameters.AddWithValue("@Department", SqlDbType.NVarChar).Value = departmentTextBox.Text;
                cmd.Parameters.Add(new SqlParameter("@ProfilePic", (object)imageData));

                //clear();
                cmd.ExecuteNonQuery();
                MessageBox.Show("Saved Successfully!");
                connect.Close();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
        }

        private void BrowseButton_Click(object sender, EventArgs e)
        {
           
            //Ask user to select file.
            OpenFileDialog dlg = new OpenFileDialog();
            DialogResult dlgRes = dlg.ShowDialog();
            if (dlgRes != DialogResult.Cancel)
            {
                //Set image in picture box
                profilePicPictureBox.ImageLocation = dlg.FileName;

                //Provide file path in txtImagePath text box.
                //txtImagePath.Text = dlg.FileName;
           }

        }

             //Open file in to a filestream and read data in a byte array.
        byte[] ReadFile(string sPath)
        {
            //Initialize byte array with a null value initially.
            MemoryStream mstr = new MemoryStream();

            byte[] data = mstr.GetBuffer();    

            //Use FileInfo object to get file size.
           FileInfo fInfo = new FileInfo(sPath);  // error on this code(value cannot be null)
           // long numBytes = fInfo.Length;
            
            //Open FileStream to read file
            FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

            //Use BinaryReader to read file stream into byte array.
            BinaryReader br = new BinaryReader(fStream);

            //When you use BinaryReader, you need to supply number of bytes to read from file.
            //In this case we want to read entire file. So supplying total number of bytes.
            //data = br.ReadBytes((int)numBytes);
            return data;
       }

推荐答案

首先，我建议你像这样重写AddWithValue代码：

First of all I suggest you to rewrite your AddWithValue code like this:

// Why to use SqlDbType.[sometype] at AddWithValue method, it pointless?
cmd.Parameters.AddWithValue("@FirstName", SqlDbType.NVarChar).Value = firstNameTextBox.Text;
cmd.Parameters.AddWithValue("@FirstName", firstNameTextBox.Text);

请参阅 SqlParameterCollection.AddWithValue Method [ ^ ]了解详情。 AddWithValue期望值，而不是SqlDbType。

您还可以将值转换为参数的相应数据类型（文本到整数等），并将此值作为第二个参数。



其次。你调试了代码吗？你确定ReadFile方法真正从文件中读取任何内容吗？

我认为不是。您应该修改ReadFile方法的代码。此代码不读取任何内容，并且数据缓冲区始终保持0字节长，并且存储过程始终接收null。考虑重写此方法如下：

Please see SqlParameterCollection.AddWithValue Method[^] for details. AddWithValue expects value, not SqlDbType.
You also could convert values to respective data types of parameters (text to int integer etc) and put this value as second argument.

Secondly. Have you debugged your code? Are you sure that ReadFile method really reads anything from file?
I think not. You should modify your code of ReadFile method. This code does not read anything and your data buffer stays always with 0 bytes long and your stored procedure always receives 'null'. Consider to rewrite this method like following:

 byte[] ReadFile(string sPath)
 {
     //Initialize byte array with a null value initially.
     MemoryStream mstr = new MemoryStream();
     byte[] data = mstr.GetBuffer();

     // Why to use FileInfo?
     FileInfo fInfo = new FileInfo(sPath);  // error on this code(value cannot be null)
     //Open FileStream to read file
     FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

     //Use BinaryReader to read file stream into byte array.
     BinaryReader br = new BinaryReader(fStream);

     mstr.SetLength(fStream.Length);
     fStream.Read(mstr.GetBuffer(), 0, (int)fStream.Length);
     byte[] data = mstr.GetBuffer();

     return data;
}

祝你好运：）

Good luck :)

这篇关于使用带参数的存储过程将图像从图片框保存并更新到数据库。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！