troubleshooting Question

i got double value in database

Avatar of Alomiry
Alomiry asked on
DatabasesASPMySQL ServerASP.NETSQL
14 Comments1 Solution474 ViewsLast Modified:
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;
             
        }





    }

}
ASKER CERTIFIED SOLUTION
srikanthreddyn143

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros