如何在另一个表中插入主键(SQL c#)

本文关键字：SQL 插入 另一个 | 更新日期: 2023-09-27 17:50:22

我将数据插入到一个名为Booking的表中，然后我想使用分配给该记录的主键，并将其作为外键插入到另一个名为Reservation的表中。我想把主键赋值给这一行:

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

但是我不知道在等号后面放什么:/

我该怎么做呢?我真的被它难住了:/任何帮助都会很感激。我意识到我可能解释得不够好，但我就是很困惑!：／下面是代码:

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

这是我遵循你的建议后的代码Hassaam:

//初始化类中的局部变量为bookingno;这个get @@identity方法只需要插入订票表数据

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

//现在休息，你已经实现如下。我不知道为什么你使用所有oledbtype字符(例如，如果你有id int比你必须使用oledbtype。Int32否则会产生异常希望对你有帮助

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

谢谢Hassaan的帮助。我已经实现了你所说的代码，但我得到一个错误说"没有一个或多个给定参数的值"。我不确定此刻是否这是代码的错误或数据库方面。你觉得我的代码怎么样?(这也是所有的代码)

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

如何在另一个表中插入主键(SQL c#)

你需要做我过去在SqlServer中做的一件事，运行另一个像下面这样的查询，你必须使用你的oledb命令，我习惯了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());
    }
}

您已获得的值将其用作另一个命令

的参数

注意:我已经给你的想法从我的场景，实现它在你的方式。Getcommand是我调用的一个方法，你可以简单地创建像

这样的命令
SqlCommand cmd = new SqlCommand("Select @@identity",conn)

谢谢

相关文章：