Last post Jun 09, 2018 04:48 PM by DA924
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
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());
Jun 08, 2018 02:14 PM|PatriceSc|LINK
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.
Jun 08, 2018 02:19 PM|tochi|LINK
please review the question again, made some mistakes
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.
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.
Jun 09, 2018 11:06 AM|tochi|LINK
Jun 09, 2018 11:30 AM|mgebhard|LINK
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.
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.
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.