更新 Sql 服务器 2008年数据库中的图像后出现错误
本文关键字:图像 错误 Sql 服务器 2008年 数据库 更新 | 更新日期: 2023-09-27 18:35:41
我正在用VS 2010 C#开发一个winform 应用程序。我开发了一个表单来插入和更新其中的用户详细信息。
我的更新用户表单如下图所示
![更新用户屏幕][1]
https://i.stack.imgur.com/iZaAJ.png
并且编码要更新是
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.VisualBasic;
using System.Drawing.Imaging;
using System.IO;
namespace SampleApplication
{
public partial class UserUpdate : Form
{
public UserUpdate()
{
InitializeComponent();
}
SqlDataAdapter da;
SqlConnection con = new SqlConnection("user id=sa; password=123;initial catalog=Inventory;data source=Aniket-PC");
SqlCommand cmd;
MemoryStream ms;
byte[] photo_array;
DataSet ds;
int rno = 0;
string str;
private void nameTxt_Validating(object sender, CancelEventArgs e)
{
if (nameTxt.Text.Trim().Length == 0)
{
namewarning.Visible = true;
}
else
{
namewarning.Visible = false;
}
}
private void Update_Load(object sender, EventArgs e)
{
contTxt.MaxLength = 10;
retriveData();
retriveImg();
}
void retriveImg()
{
con.Open();
cmd = new SqlCommand("Select Logo from Register where UserName='" + uNameTxt.Text + "'", con);
da = new SqlDataAdapter(cmd);
ds = new DataSet("MyImage");
da.Fill(ds, "MyImage");
DataRow myRow;
myRow = ds.Tables["MyImage"].Rows[0];
photo_array = (byte[])myRow["Logo"];
ms = new MemoryStream(photo_array);
profPic.Image = Image.FromStream(ms);
con.Close();
}
void retriveData()
{
con.Open();
cmd = new SqlCommand("Select * from Register where UserName='"+uNameTxt.Text+"'",con);
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
nameTxt.Text = (read["Name"].ToString());
passTxt.Text = (read["Password"].ToString());
conPassTxt.Text = (read["Password"].ToString());
emailTxt.Text = (read["EmailId"].ToString());
addTxt.Text = (read["Address"].ToString());
contTxt.Text = (read["ContactNo"].ToString());
DORTxt.Text = (read["DOR"].ToString());
validity.Text = "Account Valid till "+(read["Validity"].ToString());
}
read.Close();
con.Close();
}
private void AttachBtn_Click(object sender, EventArgs e)
{
// Open image by OpenFiledialog and show it in PicturBox.
try
{
//filter only image format files.
openFileDialog1.Filter = "jpeg|*.jpg|bmp|*.bmp|all files|*.*";
DialogResult res = openFileDialog1.ShowDialog();
if (res == DialogResult.OK)
{
Image img = new Bitmap(openFileDialog1.FileName);
//inserting image in PicturBox
profPic.Image = img.GetThumbnailImage(127, 128, null, new IntPtr());
openFileDialog1.RestoreDirectory = true;
}
}
catch
{
MessageBox.Show("Cannot upload image");
}
}
private void UpdateBtn_Click_1(object sender, EventArgs e)
{
string DOM = dateTimePicker1.Value.ToShortDateString();
if (namewarning.Visible == true || picError.Visible == true || PassError.Visible == true || emailwarningImg.Visible == true)
{
MessageBox.Show("Please correct the marked fields");
}
else
{
//cmd = new SqlCommand("update Register set (Name,Password,EmailId,Address,ContactNo,Logo,DOM) values('" + nameTxt.Text.Trim() + "','" + passTxt.Text.Trim() + "','" + emailTxt.Text.Trim() + "','" + addTxt.Text.Trim() + "','" + contTxt.Text.Trim() + "',@Logo,'" + DOM+ "')", con);
str = string.Format("update Register set Name='{0}', Password='{1}',EmailID='{2}',Address='{3}',ContactNo='{4}',Logo='{5}',DOU='{6}' where UserName='{7}'", nameTxt.Text.Trim(), passTxt.Text.Trim(), emailTxt.Text.Trim(),addTxt.Text.Trim(), contTxt.Text.Trim(), @"Logo" ,DOM,uNameTxt.Text);
con_photo();
//con.Open();
cmd = new SqlCommand(str, con);
int count= cmd.ExecuteNonQuery();
if (count > 0)
MessageBox.Show("Sucsess");
else
MessageBox.Show("need to work");
}
}
void con_photo()
{
if (profPic.Image != null)
{
ms = new MemoryStream();
profPic.Image.Save(ms, ImageFormat.Jpeg);
byte[] photo_array = new byte[ms.Length];
ms.Position = 0;
ms.Read(photo_array, 0, photo_array.Length);
cmd.Parameters.AddWithValue("@Logo", photo_array);
}
}
当我运行应用程序时,它执行得很好并向我显示成功消息,但是当我再次尝试查看更新用户表单时,它显示在屏幕截图错误下方
https://i.stack.imgur.com/7z1Rx.png
在 reriveImg ()
请帮我解决这个问题..
您不是将图像字节传递给 UPDATE
命令,而是将包含单词 Logo
的字符串传递给 。
另外:请避免使用字符串连接或String.Format
创建 SQL 命令。请改用参数化查询!
另外:不要使用 NVARCHAR
字段来存储图像字节(除非先从它们创建 BASE64 字符串),而是改用VARBINARY
或IMAGE
列。
问题出在以下行中:
str = string.Format("update Register set ... ,Logo='{5}' ...", ..., @"Logo", ...);
如您所见,您正在格式化一个字符串,但您没有插入图像中的字节,而是插入单词"Logo"。
假设列Logo
是IMAGE
或VARBINARY
类型,我会写这样的东西:
byte[] photo_array = null;
if (profPic.Image != null)
{
MemoryStream ms = new MemoryStream();
profPic.Image.Save(ms, ImageFormat.Jpeg);
photo_array = ms.GetBuffer();
}
if (photo_array != null)
{
SqlCommand cmd = new SqlCommand("UPDATE Register SET Logo=@logo", connection);
cmd.Parameters.AddWithValue("@logo", imageBytes);
cmd.ExecuteNonQuery();
}
也许我错了,但我会尝试这样做
photo_array = (byte[])myRow["Logo"];
if photo_array.length.trim()>0
{
ms = new MemoryStream(photo_array);
profPic.Image = Image.FromStream(ms);
}
con.Close();
当字符串的长度为 0 时,可能会出现此错误
另一件事是你没有将图像保存在数据库中
//C# 代码
SqlCommand cmdSelect = new SqlCommand("select ImageValue from table where ID=1", ObjCon); ObjCon.Open();
byte[] barrImg = (byte[])cmdSelect.ExecuteScalar();
ObjCon.Close();
string strfn = Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs = new FileStream("C:''Temp''1.txt", FileMode.CreateNew, FileAccess.Write);
fs.Write(barrImg, 0, barrImg.Length);
fs.Flush();
fs.Close();
1.txt 文件将在 c:''temp 文件夹中创建