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());
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.
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.
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.
If you find the post has answered your issue, then please mark post as 'answered'.
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
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.
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.
If you find the post has answered your issue, then please mark post as 'answered'.
None
0 Points
3 Posts
GetLastInsertId request
Jun 08, 2018 01:40 PM|tochi|LINK
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
All-Star
42410 Points
14539 Posts
Re: GetLastInsertId request
Jun 08, 2018 02:14 PM|PatriceSc|LINK
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.
None
0 Points
3 Posts
Re: GetLastInsertId request
Jun 08, 2018 02:19 PM|tochi|LINK
please review the question again, made some mistakes
All-Star
42410 Points
14539 Posts
Re: GetLastInsertId request
Jun 08, 2018 04:58 PM|PatriceSc|LINK
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.
Contributor
2471 Points
2292 Posts
Re: GetLastInsertId request
Jun 09, 2018 08:35 AM|DA924|LINK
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.
None
0 Points
3 Posts
Re: GetLastInsertId request
Jun 09, 2018 11:06 AM|tochi|LINK
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
All-Star
35391 Points
14068 Posts
Re: GetLastInsertId request
Jun 09, 2018 11:30 AM|mgebhard|LINK
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
Contributor
2471 Points
2292 Posts
Re: GetLastInsertId request
Jun 09, 2018 04:48 PM|DA924|LINK
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.