如何将图像插入数据库 [英] how to insert image into a database

问题描述

我有一个包含42列和一个照片列的表格。

但是当我插入照片时，它会被插入到下一行。



知道出了什么问题吗？



我的桌子有一个自动增加的主键



这是我的代码：

 SqlConnection con =  new  SqlConnection （ @ 数据源=（LocalDB）\ v11.0; AttachDbFilename = C：\ Users \ Brother \\ \\Documents\Visual Studio 2013 \Projects\EMYS \EMYS \EMYS.mdf; Integrated Security = True）; 
 con.Open（）; 
 SqlCommand cmd =  new  SqlCommand（  INSERT INTO [dbo]。[会员]（[Id]，[FirstName]，[LastName]，[DateOfBirth]，[Sex]，[Hometown]，[Region]，[Single]，[已婚]，[学生]，[就业]，[失业]，[姓名出血]，[学校]，[职业]，[电话]，[电子邮件]，[家庭地址]，[主页区域]，[邮政地址]，[洗礼]，[确认]，[ChurchStatus] ，[ClassLeader]，[Brigade]，[ChristLittleBand]，[GirlsFellowship]，[Guild]，[MenFellowship]，[SingingBand]，[SussanaWesley]，[WomenFellowship]，[YouthChoir]，[YouthFellowship]，[编舞]，[戏剧]，[ShiningArmour]，[Ushers]，[EvangelismPrayerTower]，[Organizers]，[PraisesWorship]）VALUES（@ Id，@ FirstName，@ LastName，@ DateOfBirth，@ Sex，@ Hometown，@ Region，@ Single，@已婚，@学生，@ Employed，@ Unemployed，@ NameOfSpouse，@ School，@ Occcupation，@ Telephone，@ Email，@ HomeAddress，@ HomeArea，@ PostalAddress，@洗礼，@ Confirmation，@ MorningStatu s，@ ClassLeader，@ Brigade，@ ChristLittleBand，@ GirlsFellowship，@ Guild，@ MenFellowship，@ SingingBand，@ SussanaWesley，@ WomenFellowship，@ Youngchoir，@ YoungFellowship，@ Choreography，@ Drama，@ ShiningArmour，@ Ushers，@ EvangelismPrayerTower， @Organisers，@ PrasesWorship），con）; 
 cmd.Parameters.AddWithValue（  @ Id，idTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ FirstName，firstNameTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ LastName，lastNameTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ DateOfBirth，dateOfBirthDateTimePicker.Text）; 
 cmd.Parameters.AddWithValue（  @ Sex，sexComboBox.Text）; 
 cmd.Parameters.AddWithValue（  @ Hometown，hometownTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ Region，regionComboBox.Text）; 
 cmd.Parameters.AddWithValue（  @ Single，singleCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ Married，marriedCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ Student，studentCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ Employed，employCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ Unemployed，unemployedCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ NameOfSpouse，nameOfSpouseTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ School，schoolTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ Occupation，occupiteTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ Telephone，telephoneMaskedTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ Email，emailTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ HomeAddress，homeAddressTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ HomeArea，homeAreaTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ PostalAddress，postalAddressTextBox.Text）; 
 cmd.Parameters.AddWithValue（  @ Baptism，baptismCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ Confirmation，confirmationCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ ChurchStatus，churchStatusComboBox.Text）; 
 cmd.Parameters.AddWithValue（  @ ClassLeader，classLeaderComboBox.Text）; 
 cmd.Parameters.AddWithValue（  @ Brigade，brigadeCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ ChristLittleBand，christLittleBandCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ GirlsFellowship，girlsFellowshipCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ Guild，guildCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ MenFellowship，menFellowshipCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ SingingBand，singingBandCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ SussanaWesley，sussanaWesleyCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ WomenFellowship，womenFellowshipCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ YouthChoir，youthChoirCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ YouthFellowship，youthFellowshipCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ Choreography，choreographyCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ Drama，dramaCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ ShiningArmour，shiningArmourCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ Ushers，ushersCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ EvangelismPrayerTower，evangelismPrayerTowerCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ Organizers，organisersCheckBox.Checked）; 
 cmd.Parameters.AddWithValue（  @ PraisesWorship，praisesWorshipCheckBox.Checked）; 
 cmd.ExecuteNonQuery（）; 
 con.Close（）; 
  if （photoPictureBox.Image！=  null ）
 {
 MemoryStream ms =  new  MemoryStream（）; 
 photoPictureBox.Image.Save（ms，photoPictureBox.Image.RawFormat）; 
  byte  [] a = ms.GetBuffer（）; 
 ms.Close（）; 
 cmd.Parameters.Clear（）; 
 cmd.Parameters.AddWithValue（  @ photo，a）; 
 cmd.CommandText =   INSERT INTO会员（照片）值（@photo）; 
 photoPictureBox.Image =  null ; 
 con.Open（）; 
 cmd.ExecuteNonQuery（）; 
} 
 
 MessageBox.Show（  RECORDS Successfully INSERTED ， 插入信息）; 

解决方案

您没有告诉您的SQL命令要插入图像的位置。



只需说 INSERT INTO会员（照片）VALUES（@photo）默认情况下，SQL会将其放入新行。



尝试将命令更改为是：

  INSERT INTO会员（照片）价值观（@photo）WHERE Id = @id  //更正
更新会员SET照片= @photo其中Id = @id //感谢Jas指出我的初始错误。

然后加上

 cmd.Parameters.AddWithValue（@ id，idTextBox.Text）; 

在你的行下面写着：

 cmd.Parameters.AddWithValue（@ photo，a）; 

你有两个单独的插入查询



首先查询注册（我希望）和第二个查询存储图像。这就是原因



当执行第一个查询时，将在表中创建一行（注册详细信息），并为第二个查询创建另一行（存储图像）。 br />


所以将它合并为一个查询或通过获取它的id来更新它

I have a table with 42 columns and one photo column.
But when I insert a photo, it gets inserted into the next row.

Any idea what is going wrong?

My table has a primary key with auto increment

This is my code:

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Brother\Documents\Visual Studio 2013\Projects\EMYS\EMYS\EMYS.mdf;Integrated Security=True");
           con.Open();
           SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[Member] ([Id], [FirstName], [LastName], [DateOfBirth], [Sex], [Hometown], [Region], [Single], [Married], [Student], [Employed], [Unemployed], [NameOfSpouse], [School], [Occupation], [Telephone], [Email], [HomeAddress], [HomeArea], [PostalAddress], [Baptism], [Confirmation], [ChurchStatus], [ClassLeader], [Brigade], [ChristLittleBand], [GirlsFellowship], [Guild], [MenFellowship], [SingingBand], [SussanaWesley], [WomenFellowship], [YouthChoir], [YouthFellowship], [Choreography], [Drama], [ShiningArmour], [Ushers], [EvangelismPrayerTower], [Organisers], [PraisesWorship]) VALUES (@Id, @FirstName, @LastName, @DateOfBirth, @Sex, @Hometown, @Region, @Single, @Married, @Student, @Employed, @Unemployed, @NameOfSpouse, @School, @Occupation, @Telephone, @Email, @HomeAddress, @HomeArea, @PostalAddress, @Baptism, @Confirmation, @ChurchStatus, @ClassLeader, @Brigade, @ChristLittleBand, @GirlsFellowship, @Guild, @MenFellowship, @SingingBand, @SussanaWesley, @WomenFellowship, @YouthChoir, @YouthFellowship, @Choreography, @Drama, @ShiningArmour, @Ushers, @EvangelismPrayerTower, @Organisers, @PraisesWorship)",con);
           cmd.Parameters.AddWithValue("@Id", idTextBox.Text);
           cmd.Parameters.AddWithValue("@FirstName", firstNameTextBox.Text);
           cmd.Parameters.AddWithValue("@LastName", lastNameTextBox.Text);
           cmd.Parameters.AddWithValue("@DateOfBirth", dateOfBirthDateTimePicker.Text);
           cmd.Parameters.AddWithValue("@Sex", sexComboBox.Text);
           cmd.Parameters.AddWithValue("@Hometown", hometownTextBox.Text);
           cmd.Parameters.AddWithValue("@Region", regionComboBox.Text);
           cmd.Parameters.AddWithValue("@Single", singleCheckBox.Checked);
           cmd.Parameters.AddWithValue("@Married", marriedCheckBox.Checked);
           cmd.Parameters.AddWithValue("@Student", studentCheckBox.Checked);
           cmd.Parameters.AddWithValue("@Employed", employedCheckBox.Checked);
           cmd.Parameters.AddWithValue("@Unemployed", unemployedCheckBox.Checked);
           cmd.Parameters.AddWithValue("@NameOfSpouse", nameOfSpouseTextBox.Text);
           cmd.Parameters.AddWithValue("@School", schoolTextBox.Text);
           cmd.Parameters.AddWithValue("@Occupation", occupationTextBox.Text);
           cmd.Parameters.AddWithValue("@Telephone", telephoneMaskedTextBox.Text);
           cmd.Parameters.AddWithValue("@Email", emailTextBox.Text);
           cmd.Parameters.AddWithValue("@HomeAddress", homeAddressTextBox.Text);
           cmd.Parameters.AddWithValue("@HomeArea", homeAreaTextBox.Text);
           cmd.Parameters.AddWithValue("@PostalAddress", postalAddressTextBox.Text);
           cmd.Parameters.AddWithValue("@Baptism", baptismCheckBox.Checked);
           cmd.Parameters.AddWithValue("@Confirmation", confirmationCheckBox.Checked);
           cmd.Parameters.AddWithValue("@ChurchStatus", churchStatusComboBox.Text);
           cmd.Parameters.AddWithValue("@ClassLeader", classLeaderComboBox.Text);
           cmd.Parameters.AddWithValue("@Brigade", brigadeCheckBox.Checked);
           cmd.Parameters.AddWithValue("@ChristLittleBand", christLittleBandCheckBox.Checked);
           cmd.Parameters.AddWithValue("@GirlsFellowship", girlsFellowshipCheckBox.Checked);
           cmd.Parameters.AddWithValue("@Guild", guildCheckBox.Checked);
           cmd.Parameters.AddWithValue("@MenFellowship", menFellowshipCheckBox.Checked);
           cmd.Parameters.AddWithValue("@SingingBand", singingBandCheckBox.Checked);
           cmd.Parameters.AddWithValue("@SussanaWesley", sussanaWesleyCheckBox.Checked);
           cmd.Parameters.AddWithValue("@WomenFellowship", womenFellowshipCheckBox.Checked);
           cmd.Parameters.AddWithValue("@YouthChoir", youthChoirCheckBox.Checked);
           cmd.Parameters.AddWithValue("@YouthFellowship", youthFellowshipCheckBox.Checked);
           cmd.Parameters.AddWithValue("@Choreography", choreographyCheckBox.Checked);
           cmd.Parameters.AddWithValue("@Drama", dramaCheckBox.Checked);
           cmd.Parameters.AddWithValue("@ShiningArmour", shiningArmourCheckBox.Checked);
           cmd.Parameters.AddWithValue("@Ushers", ushersCheckBox.Checked);
           cmd.Parameters.AddWithValue("@EvangelismPrayerTower", evangelismPrayerTowerCheckBox.Checked);
           cmd.Parameters.AddWithValue("@Organisers", organisersCheckBox.Checked);
           cmd.Parameters.AddWithValue("@PraisesWorship", praisesWorshipCheckBox.Checked);
           cmd.ExecuteNonQuery();
           con.Close();
           if (photoPictureBox.Image != null)
           {
               MemoryStream ms = new MemoryStream();
               photoPictureBox.Image.Save(ms, photoPictureBox.Image.RawFormat);
               byte[] a = ms.GetBuffer();
               ms.Close();
               cmd.Parameters.Clear();
               cmd.Parameters.AddWithValue("@photo", a);
               cmd.CommandText = "INSERT INTO Member (photo) values (@photo)";
               photoPictureBox.Image = null;
               con.Open();
               cmd.ExecuteNonQuery();
           }

           MessageBox.Show("RECORDS Successfully INSERTED","Insert Info");

解决方案

You aren't telling your SQL command where you want to insert the image.

By just saying INSERT INTO Member (photo) VALUES (@photo) SQL will by default put it in a new line.

Try changing the command to be:

INSERT INTO Member (photo) VALUES (@photo) WHERE Id = @id // Correction
UPDATE Member SET photo = @photo where Id=@id //Thanks to Jas for pointing out my initial error.

Then add

cmd.Parameters.AddWithValue("@id", idTextBox.Text);

beneath your line that reads:

cmd.Parameters.AddWithValue("@photo", a);

You have two separated Insert query

First query for registration(I hope) and second query for storing the image.That's the reason

When the first query get executed, a row(registration details) will be created in table and another row will be created for the second query(storing image).

So merge it as a single query or Update it by getting it's id

这篇关于如何将图像插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！