[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

insert value into two tables

Hello,

i have a problem with inserting value in two tables
The first table Hotel which is the main table
the second table RoomType

i wanna insert vaule in Room type table and insert the forign key in hotel tabel [[ RoomTypeID ]]
The problem is im creating my DataBAse using visual basic 2010 so i can use store procedure and SCOPE_IDENTITY

So what i ahve to do?????


some of my code


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.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 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;
            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=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],[RoomName],[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 + "','" + AddRoomName + "','" + AddRoomDescription + "','" + AddPersonPerRoom + "','" + AddPersonPrice + "','" + AddTerms + "','" + AddPolicy + "')";
           
            SqlDataReader reader = query.ExecuteReader();



            reader.Close();


            cnn.Close();

        }
    }
}
0
Alomiry
Asked:
Alomiry
  • 8
  • 7
5 Solutions
 
AlomiryAuthor Commented:
so i CAN NOT  use store procedure and SCOPE_IDENTITY
0
 
MlandaTCommented:
I dont see why you say you cannot use a stored procedure and/or SCOPE_IDENTITY(). You are using a SQL Server database (user instance of SQL Express). Since it is a SQL Server database, SCOPE_IDENTITY() is available to you, only that your table needs to have an identity column.
0
 
AlomiryAuthor Commented:
I have tried many times using SCOPE_IDENTITY() but it's dosent work.

i dont what i have to do?

Can u help me.. plz
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
MlandaTCommented:
Alternatively, you could try to get the identity value assigned to the inserted row by SELECTing the recently inserted row. This will work if there is a primary key value (or any other set of columns that can uniquely identify a row in that table for that matter) then you can use...

            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],[RoomName],[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 + "','" + AddRoomName + "','" + AddRoomDescription + "','" + AddPersonPerRoom + "','" + AddPersonPrice + "','" + AddTerms + "','" + AddPolicy + "'); SELECT myIDColumn FROM [Hotel] WHERE [Name] = '" + AddHotelName + "' AND [City] = '" + AddCity + "' AND [Street]'" + AddStreet + "'";

Note that I have just used the [Name],[City],[Street] columns in the SELECT...FROM...WHERE... just as an example. Use columns that will uniquely identify a row. I would still suggest however that SCOPE_IDENTITY() is your best option for this sort of thing.
0
 
MlandaTCommented:
To use SCOPE_IDENTIY()
cnn.Open();

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],[RoomName],[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 + "','" + AddRoomName + "','" + AddRoomDescription + "','" + AddPersonPerRoom + "','" + AddPersonPrice + "','" + AddTerms + "','" + AddPolicy + "'); SELECT SCOPE_IDENTITY();";
           
String hotelID = query.ExecuteScalar();

cnn.Close();

Open in new window


Make sure that you have an identity column in the Hotel table.
0
 
AlomiryAuthor Commented:
it's still some errors
Do u have some example ?
0
 
MlandaTCommented:
What errors? You have to provide detail so that I can understand the exact challenges you are facing.
0
 
AlomiryAuthor Commented:
I'm still new at ASP.net


 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],[RoomName],[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 + "','" + AddRoomName + "','" + AddRoomDescription + "','" + AddPersonPerRoom + "','" + AddPersonPrice + "','" + AddTerms + "','" + AddPolicy + "'); SELECT SCOPE_IDENTITY();";
           

String hotelid = query.ExecuteScalar(); ((I have error : cannot implicitly convert type ''object'' to 'string'))

           
            SqlDataReader reader = query.ExecuteReader();
0
 
MlandaTCommented:
Try this:
String hotelid = (string)query.ExecuteScalar();

Open in new window

0
 
AlomiryAuthor Commented:
Sorry but errors:

Unable to cast object of type 'System.Decimal' to type 'System.String'.


And my code is:

 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();";

            String hotelid = (string)query.ExecuteScalar();

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


Thx
0
 
MlandaTCommented:
Int32 hotelid = (Int32)cmd.ExecuteScalar();

Open in new window


The issue that we just need to deal with is about the data type being used there. I'm currently on my phone an dont have a compiler to test this code with.
0
 
MlandaTCommented:
Int32 hotelid = (Int32)cmd.ExecuteScalar();

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

Open in new window

0
 
AlomiryAuthor Commented:


 Int32 hotelid = (Int32)cmd.ExecuteScalar();

Do u mean cmd it's same query

and try to replace the:
 Int32 hotelid = (Int32)query.ExecuteScalar();

but it's error:Specified cast is not valid.

Thx
0
 
Alfred A.Commented:
Try this:

Int32 hotelid = (Int32)(decimal)cmd.ExecuteScalar();

Or,

Int32 hotelid = Convert.ToInt32(cmd.ExecuteScalar());
0
 
AlomiryAuthor Commented:
THx it's working
0
 
AlomiryAuthor Commented:
It's a great service
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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