locked
GetLastInsertId request RRS feed

  • Question

  • User-946286577 posted

    I have one form with which I want to send different parts of information to three tables into the database . The data for the first table populates it OK , I am trying to insert the first  part of the data into the Name table where name_id is the PK, then extract the name_id and insert that as a FK into Meeting table and Event table with the rest of the data using db.GetLastInsertId. The relevant lines of code are below, could someone explain what I have missed or got it wrong. Thanks

                   var sql = "INSERT INTO person.Name(full_name,email,phone_number,address,signed_in) VALUES (@0,@1,@2,@3,@4)";
                   db.Execute(sql, full_name, email, phone_number, address, DateTime.Now.ToLocalTime());
                
                   var col = db.GetLastInsertId();
     
     
                   var query = "insert into Meeting(name_id,name_of_company,purpose,signed_in)values(@0,@1,@2,@3)";
     
                   db.Execute(query, col, name_of_company, purpose, DateTime.Now.ToLocalTime());
     
            
     
                   var query1 = "insert into Event(name_id,name_of_company,name_of_event,signed_in)values(@0,@1,@2,@3)";
     
                   db.Execute(query1, col, name_of_company, name_of_event, DateTime.Now.ToLocalTime());
    Friday, June 8, 2018 1:40 PM

All replies

  • User753101303 posted

    Hi,

    Looks weird it fails with "ordinal position 6" while you have only 4 parameters ? Is col null ? Which db are you using? My first move would be likely to do a SQL trace on the server side to see what is sent exactly to my db engine.

    Friday, June 8, 2018 2:14 PM
  • User-946286577 posted

    please review the question again, made some mistakes

    Friday, June 8, 2018 2:19 PM
  • User753101303 posted

    Noooooo !!! Seems you removed the error message ? If it was something else could you please post it.

    IMHO trying to find out which error you have by reading your code is a VERY BAD approach. The number of columns seems to fit the number of parameters in your SQL statement and in your C# calls.

    It then left us with tons of assumptions about what could be wrong regarding the value of the parameters you are using, what your code does in GetLastInsertId or Execute, about what you have done maybe before to initialize your db connections and tons of other things (I saw once someone who had an error because the db server was requiring an enccrypted connection, how one could GUESS that ???)

    So in short you should ALWAYS start from the error message (or from a description of the unexpected behavior you see) which allows THEN ONLY to look at the relevant place in your code.

    Friday, June 8, 2018 4:58 PM
  • User1120430333 posted

    Are all the tables you are trying to insert a record using the Identity column as the primary key column? If so, why are you not using the T-SQL Identity Scope to get the primary-key of the record inserted?

    What does the db.GetLastInsertId have to do in getting the ID of a record that was inserted into a table?  This seems kind of questionable. 

    Saturday, June 9, 2018 8:35 AM
  • User-946286577 posted
    Thank you for your response, I have a form that part of it is inserted into a name table while another part is inserted into an Event table etc.
    The name table has an ID in other tables as a foreign key ,so I don't know if using getlastinsertedId is possible.
    I'm a newbie though.
    Thanks
    Saturday, June 9, 2018 11:06 AM
  • User475983607 posted

    Thank you for your response, I have a form that part of it is inserted into a name table while another part is inserted into an Event table etc.
    The name table has an ID in other tables as a foreign key ,so I don't know if using getlastinsertedId is possible.
    I'm a newbie though.
    Thanks

    You don't have to submit three separate scripts.  You can combine all the parameterized script and send the single script to the DB.    Rather than writing a C# method to get the last ID you can write that bit of logic in the script which is a lot easier.

    It looks you are using MySQL.  I would create a script using the MySQL admin or whatever client you have.  Copy the script to the C# source then parameterize the string.  Of course, you'll need to consult the MySQL documentation for the proper syntax depending on your design.

    https://dev.mysql.com/doc/refman/8.0/en/getting-unique-id.html

    Saturday, June 9, 2018 11:30 AM
  • User1120430333 posted

    Thank you for your response, I have a form that part of it is inserted into a name table while another part is inserted into an Event table etc.
    The name table has an ID in other tables as a foreign key ,so I don't know if using getlastinsertedId is possible.
    I'm a newbie though.
    Thanks

    I see someone has posted with the impression that you are using MySQL. If this correct, I can't help you as I am familiar with MS SQL Server and Oracle, which both have Express versions that are free to download and use too.

    Saturday, June 9, 2018 4:48 PM