• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

insert multiple value into two tables

Hello,

I ’m working in from for hotel booking, inside the form there is a Input for room type , when I try to insert one type of room in my database and get the id from Hotel TABLE by using SCOP IDENTITY it’s working perfect BUT MY QUESTION IS?
How can insert many type of room in ( 5 Input )ROOM TYPE TALBEL and get the value of HOTEL ID ?
HOTEL TALBE:
ID
NAME
ROOMTYPE ID


ROOMTYPE
ID
HOTELID
ROOM DESCRIPTION

My codeBehind:  

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;

namespace BookingWebApplication
{
   
    public partial class Addyourhotel : 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 AddRoomDescription;

        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;

            AddRoomDescription = id_room_description.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\BookingWebApplication\BookingWebApplication\App_Data\Booking.mdf;
                   Integrated Security=True;
                   User Instance=True";

            cnn.Open();



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

            SqlCommand query = cnn.CreateCommand();

            query.CommandText = "INSERT INTO [Hotel] ([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],[Room_description],[PersonPerRoom],[PresonPrice],[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 + "','" + AddRoomDescription + "','" + AddPersonPerRoom + "','" + AddPersonPrice + "','" + AddTerms + "','" + AddPolicy + "'); SELECT SCOPE_IDENTITY();";

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


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

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



            reader.Close();


            cnn.Close();

        }
    }
}
0
Alomiry
Asked:
Alomiry
  • 3
2 Solutions
 
dj_alikCommented:
You have some bug in
query.CommandText = "INSERT INTO [Room_type] ([Hotels_id],[Name]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName + "')";
query.CommandText = "INSERT INTO [Room_type] ([Hotels_id],[Name]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName2 + "')";

replace to:

query.CommandText = "INSERT INTO [Room_type] ([Hotels_id],[Name]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName + "');";
query.CommandText += "INSERT INTO [Room_type] ([Hotels_id],[Name]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName2 + "')";
0
 
dj_alikCommented:
and query.ExecuteNonQuery();
0
 
AlomiryAuthor Commented:
It is a best service in the world.
0
 
dj_alikCommented:


replace
SqlDataReader reader = query.ExecuteReader();
reader.Close();

to
query.ExecuteNonQuery();

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now