Click here to Skip to main content
15,886,137 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

C#
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");
Posted
Updated 30-Mar-14 22:44pm
v2

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);
 
Share this answer
 
v3
Comments
Tom Marvolo Riddle 31-Mar-14 4:56am    
(My vote of 3)Your query is totally wrong.Insert statement have no where clause
Pheonyx 31-Mar-14 5:02am    
Oh yeah, my bad. It should be an "Update"
Tom Marvolo Riddle 31-Mar-14 5:07am    
:thumbsup:
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
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900