0

I'm trying to make a VERY simple application to let customers book a trip. Basically, they enter Name, Address, Civic, Phone and then a number between 1-5 depending on which trip they want to book. So for example trip 1 equals a trip from Berlin to Paris, trip 2 from Stockholm to whatever and so on.

I need the last inserted ID into the Customer table to relate to the Booking.Customer, so that when the customer presses Book Trip, His information gets inserted into Customer table AND his ID + what trip gets inserted into my Booking Table

LAST_INSERTED_ID is for auto-increment, so I cant get it to work properly.

The Customer table has all the info about the customer, the Booking table consists of the rows Customer[references Customer.ID] and Trip[references a Trip.ID] in my Trip table.

I apologize if Im not making sense, just can't get it working and not sure how to continue.. Appreciate any and all help here!

 string sql = "Begin; INSERT INTO Customer(Name, PIN, Address, Phone) VALUES(@param2, @param3, @param4, @param5); INSERT INTO Booking(Customer, Trip) VALUES(LAST_INSERT_ID(), @param6);";


 SqlCommand command = new SqlCommand(sql, connection);
 command.Parameters.Add("@param2", SqlDbType.VarChar, 255).Value = txtBoxName.Text;
 command.Parameters.Add("@param3", SqlDbType.Char, 11).Value = textBoxPIN.Text;
 command.Parameters.Add("@param4", SqlDbType.VarChar, 255).Value = textBoxAddress.Text;
 command.Parameters.Add("@param5", SqlDbType.VarChar, 255).Value = textBoxPhone.Text;
 command.Parameters.Add("@param6", SqlDbType.Int, 1).Value = txtBoxTrip.Text;
 command.CommandType = CommandType.Text;
 command.ExecuteNonQuery();

 connection.Close();
captainsac
  • 2,484
  • 3
  • 27
  • 48
Datnametho
  • 3
  • 1
  • why is there `begin;` in your `sql` without any `end` -- doesnt looks like you are begining anything :) Why not simply split whole query to two commands within same transaction? (`command.Transaction = myTrans;`); – Jan 'splite' K. May 26 '15 at 11:09
  • 2
    possible duplicate of [Get the id of inserted row using C#](http://stackoverflow.com/questions/405910/get-the-id-of-inserted-row-using-c-sharp) – OldProgrammer May 26 '15 at 11:10
  • Its not a duplicate since I'm not using auto-increment in my booking table. – Datnametho May 26 '15 at 11:29

2 Answers2

1

First You have to make LAST_INSERTED_ID of Customer table as identity.

If already then you can go further

then your sql query should be

DECLARE @Last_Id int
INSERT INTO Customer(Name, PIN, Address, Phone) VALUES(@param2, @param3, @param4, @param5)
SET @Last_Id=SCOPE_IDENTITY()
INSERT INTO Booking(Customer, Trip) VALUES(@Last_Id, @param6)
Tufan Chand
  • 692
  • 4
  • 19
  • 36
  • Huge huge thank you, I got it working now. I still have another SQL-statement I need to fix, but I'm gonna try fixing that before I ask for more help! Thanks again! – Datnametho May 26 '15 at 18:04
1

It might be solved your problem.

But you always need to use stored procedure with transaction for better performance and security.

Martin Mathew
  • 36
  • 4