CODEWITHSUNDEEP
c#sqlforeign-keysprimary-keyinsert-into
Lloyd
Lloyd

Reputation: 403

How to use primary key in another table after inserting (SQL C#)

I'm inserting data into a table called Booking and then I want to use that primary key assigned to that record and insert it in another table called Reservation as a foreign key. I basically want to assign that primary key to this line:

myCommand.Parameters.Add("@BookingNo", OleDbType.Char).Value =  ;

But I don't know what to put after the equals sign :/

Any ideas how I would do this? I'm really stuck with it :/ any help would be appreciated. And I realise I probably haven't explained it well enough but I'm just that confused with it! :/ Here's the code:

        OleDbConnection connect = new OleDbConnection();
        connect.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
                                   F:\Hotel_Reservation_System\Hotel_Reservation_System\bin\Debug\TeamProjectTeam5.accdb; Persist Security Info=False");
        OleDbCommand myCmd = new OleDbCommand();
        OleDbCommand myCommand = new OleDbCommand();
        myCmd.Connection = connect;
        myCommand.Connection = connect;

        DateTime ArrivalDate = dateTimePicker1.Value.Date;
        DateTime DepartureDate = dateTimePicker2.Value.Date;

        myCmd.CommandText = "INSERT INTO Booking(Payment_ID, Guest_No, [Arrival_Date], [Departure_Date], [Booking_Received])" + "VALUES(@PaymentID, @GuestNo, @ArrivalDate, @DepartureDate, @BookingReceived)";

        myCmd.Parameters.Add("@PaymentID", OleDbType.Char).Value = textBox1.Text;
        myCmd.Parameters.Add("@GuestNo", OleDbType.Char).Value = textBox3.Text;
        myCmd.Parameters.Add("@ArrivalDate", OleDbType.DBDate).Value = ArrivalDate;
        myCmd.Parameters.Add("@DepartureDate", OleDbType.DBDate).Value = DepartureDate;
        myCmd.Parameters.Add("@BookingReceived", OleDbType.DBDate).Value = DateTime.Today;

        myCommand.CommandText = "INSERT INTO Reservation(Booking_No, Room_No, Hotel_No)" + "VALUES(@BookingNo, @RoomNo, @HotelNo)";

        myCommand.Parameters.Add("@BookingNo", OleDbType.Char).Value = ;
        myCommand.Parameters.Add("@RoomNo", OleDbType.Char).Value = comboBox4.SelectedItem;
        myCommand.Parameters.Add("@HotelNo", OleDbType.Char).Value = comboBox1.SelectedItem;

        connect.Open();
        int rowsChanged = myCmd.ExecuteNonQuery();
        int rowsChanged2 = myCommand.ExecuteNonQuery();

        connect.Close();

This is the code I have after following your advice Hassaam:

//initialze the local variable in your class int bookingno; this get @@identity method to be implement just blow your booking table insertion data 

OleDbCommand myCommand = new OleDbCommand("Select @@identity",connect);
try {
connection.Open();
object value = myCommand.ExecuteScalar();
bookingNo = Convert.ToInt32(value);//it obtained
}

//now rest as below you have implemented. and i am not sure why you are using all oledbtype char (for eg if u have id int than you must use oleDbtype.Int32 else it will generate exception hope it will help you

        myCommand.CommandText = "INSERT INTO Reservation(Booking_No, Room_No, Hotel_No)" + "VALUES(@BookingNo, @RoomNo, @HotelNo)";
        try
        {               
            myCommand.Parameters.Add("@BookingNo", OleDbType.Char).Value = bookingNo;
            myCommand.Parameters.Add("@RoomNo", OleDbType.Char).Value = comboBox4.SelectedItem;
            myCommand.Parameters.Add("@HotelNo", OleDbType.Char).Value = comboBox1.SelectedItem;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString());
        }

        if (rowsChanged == 1 && rowsChanged2 ==1)
        {
            MessageBox.Show("Booking Made");
        }
        else
        {
            MessageBox.Show("Error: Booking Not Made");
        }

Thanks Hassaan for your help. I've implemented the code as you've said but I'm getting an error saying "No value for one or more given parameters". I'm not sure at the moment whether it's the code's fault or the database side.. How does my code look to you? (It's all the code too)

OleDbConnection connect = new OleDbConnection();
        connect.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
                                   F:\Hotel_Reservation_System\Hotel_Reservation_System\bin\Debug\TeamProjectTeam5.accdb; Persist Security Info=False");
        OleDbCommand myCmd = new OleDbCommand();

        myCmd.Connection = connect;


        DateTime ArrivalDate = dateTimePicker1.Value.Date;
        DateTime DepartureDate = dateTimePicker2.Value.Date;

        myCmd.CommandText = "INSERT INTO Booking(Payment_ID, Guest_No, [Arrival_Date], [Departure_Date], [Booking_Received])" + "VALUES(@PaymentID, @GuestNo, @ArrivalDate, @DepartureDate, @BookingReceived)";

        myCmd.Parameters.Add("@PaymentID", OleDbType.Char).Value = textBox1.Text;
        myCmd.Parameters.Add("@GuestNo", OleDbType.Char).Value = textBox3.Text;
        myCmd.Parameters.Add("@ArrivalDate", OleDbType.DBDate).Value = ArrivalDate;
        myCmd.Parameters.Add("@DepartureDate", OleDbType.DBDate).Value = DepartureDate;
        myCmd.Parameters.Add("@BookingReceived", OleDbType.DBDate).Value = DateTime.Today;

        OleDbCommand myCommand = new OleDbCommand("Select @@identity");
        myCommand.Connection = connect;

        connect.Open();
        object value = myCommand.ExecuteScalar();
        int bookingNo = Convert.ToInt32(value);

        myCommand.CommandText = "INSERT INTO Reservation(Booking_No, Room_No, Hotel_No)" + "VALUES(@BookingNo, @RoomNo, @HotelNo)";
        try
        {
            myCommand.Parameters.Add("@BookingNo", OleDbType.Char).Value = bookingNo;
            myCommand.Parameters.Add("@RoomNo", OleDbType.Char).Value = comboBox4.SelectedItem;
            myCommand.Parameters.Add("@HotelNo", OleDbType.Char).Value = comboBox1.SelectedItem;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString());
        }

        int rowsChanged = myCmd.ExecuteNonQuery();
        int rowsChanged2 = myCommand.ExecuteNonQuery();

        connect.Close();

        if (rowsChanged == 1 && rowsChanged2 == 1)
        {
            MessageBox.Show("Booking Made");
        }
        else
        {
            MessageBox.Show("Error: Booking Not Made");
        }

Upvotes: 0

Views: 1621

Answers (1)

Hassaan
Hassaan

Reputation: 3991

you need to do 1 thing which i used to do in SqlServer, run another query like the below one , you must use your oledb command, i am used to of sqlserver 

using (SqlCommand cmd1 = GetCommand(connection, transaction, 
                                                  "select @@IDENTITY", null))
{
    try
    {
        object value = cmd1.ExecuteScalar();
        studentId = Convert.ToInt32(value);
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        MessageBox.Show(ex.Message.ToString());
    }
}

the value which you have obtained used it as parameter in another command

Note: i have given you idea from my scenario, implement it in your way. Getcommand is a method i am calling you can simply create command like 

SqlCommand cmd = new SqlCommand("Select @@identity",conn)

Thanks

Upvotes: 2

Related Questions