We help IT Professionals succeed at work.
Get Started

i got double value in database

470 Views
Last Modified: 2012-05-11
Hello,
Please can u help me to review my code becuse when i insert data from my form, i got a different row for my image at HOTELS TABLE and double value in my ROOM_TYPE TABLE

I mean when i click the button to submit the form,the value it's saving in the database.
But the Image row in the HOTELS Table saving in different row, i mean the value of the HOTEL in one row and The image in another one even if the are in same TABLE (Hotels TABLE)

my code behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;

namespace BacholerProject
{
    public partial class AddHotel : System.Web.UI.Page
    {

        string AddType;
        string AddRates;
        string AddHotelName;
        string AddCity;
        string AddStreet;
        string AddZipCode;
        string AddCountry;
        string AddBuildingNo;

        string AddContactName;
        string AddPhone;
        string AddFax;
        string AddWebsite;
        string AddEmail;

        string AddHotelDescription;
        string AddCheckIn;
        string AddCheckOut;

        string AddDistanceCenter;
        string AddDistanceTrain;
        string AddDistanceBeach;

        string AddFacilities;
        string AddService;

        string AddRoomName;
        string AddRoomName2;
        string AddRoomName3;
        string AddRoomDescription;
        string AddRoomDescription2;
        string AddRoomDescription3;

        /// int AddPersonPerRoom;
        ///int AddPersonPrice;

        string AddTerms;
        string AddPolicy;


        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void Addyourhotel_Click(object sender, EventArgs e)
        {
            AddType = id_type.SelectedItem.Value;
            AddRates = id_rate.SelectedItem.Value;
            AddHotelName = id_HotelName.Text;
            AddCity = id_city.Text;
            AddStreet = id_street.Text;
            AddZipCode = id_zipCode.Text;
            AddCountry = id_country_select.SelectedItem.Value;
            AddBuildingNo = id_buildingNo.Text;

            AddContactName = id_name.Text;
            AddPhone = id_phone.Text;
            AddFax = id_fax.Text;
            AddWebsite = id_web.Text;
            AddEmail = id_email.Text;

            AddHotelDescription = id_hotelDescription.Text;
            AddCheckIn = id_checkIn.Text;
            AddCheckOut = id_checkOut.Text;

            AddDistanceCenter = id_distance_center.Text;
            AddDistanceTrain = id_distance_train.Text;
            AddDistanceBeach = id_distance_beach.Text;

            AddFacilities = id_facilities.Text;
            AddService = id_service.Text;

            AddRoomName = id_room_name.Text;

            AddRoomName2 = id_room_name2.Text;

            AddRoomName3 = id_room_name3.Text;


            AddRoomDescription = id_room_description.Text;

            AddRoomDescription2 = id_room_description2.Text;

            AddRoomDescription3 = id_room_description3.Text;


            ///AddPersonPerRoom = Convert.ToInt32(id_room_person.Text);
            ///AddPersonPrice = Convert.ToInt32(id_room_price.Text);

            AddTerms = id_terms.Text;
            AddPolicy = id_policy.Text;


            SqlConnection cnn = new SqlConnection();
            cnn.ConnectionString =
            @"Data Source=localhost\SQLEXPRESS;
                   AttachDbFilename=C:\Users\Anas\Documents\Visual Studio 2010\Projects\BacholerProject\BacholerProject\App_Data\Booking.mdf;
                   Integrated Security=True;
                   User Instance=True";

            cnn.Open();


            SqlCommand query = cnn.CreateCommand();

            query.CommandText = "INSERT INTO [Hotels] ([Type],[Rates],[Name],[City],[Street],[ZipCode],[Country],[BulidingNumber],[ContactFullName],[PhoneNumber],[FaxNumber],[Website],[Email],[Hotel_description],[Check_in],[Check_out],[Distance_center],[Distance_train],[Distance_beach],[FacilitiesDescription],[ServiceDescription],[Terms_condition],[Policy]) VALUES ('" + AddType + "','" + AddRates + "','" + AddHotelName + "','" + AddCity + "','" + AddStreet + "','" + AddZipCode + "','" + AddCountry + "','" + AddBuildingNo + "','" + AddContactName + "','" + AddPhone + "','" + AddFax + "','" + AddWebsite + "','" + AddEmail + "','" + AddHotelDescription + "','" + AddCheckIn + "','" + AddCheckOut + "','" + AddDistanceCenter + "','" + AddDistanceTrain + "','" + AddDistanceBeach + "','" + AddFacilities + "','" + AddService + "','" + AddTerms + "','" + AddPolicy + "') SELECT SCOPE_IDENTITY();";

            decimal hotelid = (decimal)query.ExecuteScalar();
            //var hotelid = query.ExecuteScalar();


            query.CommandText = "INSERT INTO [Room_type] (Hotels_id, Name, Description)VALUES('" + hotelid.ToString() + "', '" + AddRoomName + "', '" + AddRoomDescription + "')";

            query.CommandText += "INSERT INTO [Room_type] ([Hotels_id],[Name],[Description]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName2 + "','" + AddRoomDescription2 + "')";

            query.CommandText += "INSERT INTO [Room_type] ([Hotels_id],[Name],[Description]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName3 + "','" + AddRoomDescription3 + "')";

            query.ExecuteNonQuery();

            if (Page.IsValid) //save the image
            {
                Stream imgStream = UploadFile.PostedFile.InputStream;
                int imgLen = UploadFile.PostedFile.ContentLength;
                string img_contenttype = UploadFile.PostedFile.ContentType;
                string img_name = txtImgName.Value;
                byte[] img_data = new byte[imgLen];
                int n = imgStream.Read(img_data, 0, imgLen);

                String idis = Request.QueryString["id"];
                int business_id = System.Convert.ToInt32(idis);

                int RowsAffected = SaveToDB(img_name, img_data, img_contenttype);
                if (RowsAffected > 0)
                {
                    TextBox TextBox2 = new TextBox();
                    TextBox2.Text = "The Image was Saved";
                    Response.Write("<BR>The Image was saved");
                }
                else
                {
                    Response.Write("<BR>An error occurred uploading the image");
                }
            }

        }




        private int SaveToDB(string img_name, byte[] img_data, string img_contenttype)
        {
            //use the web.config to store the connection string


            SqlConnection cnn = new SqlConnection();
            cnn.ConnectionString =
              @"Data Source=localhost\SQLEXPRESS;
                   AttachDbFilename=C:\Users\Anas\Documents\Visual Studio 2010\Projects\BacholerProject\BacholerProject\App_Data\Booking.mdf;
                   Integrated Security=True;
                   User Instance=True";
         
                cnn.Open();
         
            SqlCommand query = cnn.CreateCommand();

            query.CommandText = "INSERT INTO Hotels (Image_name,Image_data, Image_contenttype) VALUES ( @img_name, @img_data,@img_contenttype)";
           
           

            SqlParameter param0 = new SqlParameter("@img_name", System.Data.SqlDbType.VarChar, 50);
            param0.Value = img_name;
            query.Parameters.Add(param0);

            SqlParameter param1 = new SqlParameter("@img_data", System.Data.SqlDbType.Image, img_data.Length);
            param1.Value = img_data;
            query.Parameters.Add(param1);



            SqlParameter param2 = new SqlParameter("@img_contenttype", System.Data.SqlDbType.VarChar, 50);
            param2.Value = img_contenttype;
            query.Parameters.Add(param2);


            Label1.Text = "<b>Server Version:</b> " + cnn.ServerVersion;

            int numRowsAffected = query.ExecuteNonQuery();
            cnn.Close();

            return numRowsAffected;
             
        }





    }

}
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 14 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE