How to use LAST_INSERT_ID() in c# windows form?

Question

In database I have three tables-

patient(patientID,fName,lName) 
illness(diseaseID,diseaseName)
patientDisease(patientID, diseaseID, dateChecked)

patientID and diseaseID are index. So on in c# I have three textboxes fNameTxt and lNameTXT, diseaseTxt.I want to store the name in patient table and disease name in illness table. Besides, I have to record patientID and diseaseID in patientDisease table as well. For patient table, I used following code. I knew, I can use

SET @variable = LAST_INSERT_ID()

to get the id, but realised c#(visual studio) doesnt recognize it. Basically, I couldnt make the overall statement. Could anybody help me to get through this condition please.

string connStr = @"server=localhost; DATABASE=mario;User ID=root;Password=;";
MySqlConnection conn1 = new MySqlConnection();
conn1.ConnectionString = connStr;

MySqlCommand cmd = conn1.CreateCommand();

cmd.CommandText = "INSERT INTO patient(patientID,fName, lName)" 
    + "Values("NULL",'" + fNameTxt.Text + "','" + lNameTxt.Text + "');";
conn1.Open();
cmd.ExecuteNonQuery();

I searched some other questions here, but they are almost about suggesting the use of LAST_INSERT_ID() but not how to use it.

Are you sure you want to add your `patientID` column `"NULL"` as a _string_ or you try to add just `null`? Also please use [_parameterized queries_](http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html). This kind of string concatenations are open for [_SQL Injection_](http://en.wikipedia.org/wiki/SQL_injection) attacks. — Soner Gönül, Dec 04 '13 at 07:36
@SonerGönül I mean to add just null, not "NULL". Thanks for the security advice. I just typed demo question based on my program problem. Have you got any idea to solve it ?? — Shasi, Dec 04 '13 at 07:38
Can't you simply get the the value by executing a Query like "SELECT LASR_INSERT_ID()" just after the ExecuteNonQuery()? — Peaceman71, Dec 04 '13 at 07:45
@Peaceman71, how practical it would be to make select query for this, then insert query for illness table, again select query for illness table and at last insert query for patientDisease. Are you suggesting this ? — Shasi, Dec 04 '13 at 07:57
Do you enter a new disease every time ? or do you have a collection of disease out of which you choose one to assign???Cause i think it will change a lot . — Suraj Singh, Dec 04 '13 at 08:19
@user2439565 Okay please try the below answer if it works for you, it will be better if you use `Stored Procedures`. — Suraj Singh, Dec 04 '13 at 09:03
I am not sure how you can avoid querying for the ID. I think you have to Query for it sometime, unless you are using some sort of data Library that will be automatically updated on add, i.e. Entity Framework. — Peaceman71, Dec 04 '13 at 11:25
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) — Jim Fell, May 31 '16 at 17:07

score 0 · Answer 1 · answered Dec 04 '13 at 07:54

0

You can use this to get the last inserted id:

"SELECT * FROMtable(column) WHERE id = last_insert_id();

And use this if you want to insert a last id:

"INSERT INTO table(column) VALUES (LAST_INSERT_ID())";

Hope this might be useful.

answered Dec 04 '13 at 07:54

vahnevileyes

415
3
6

Suraj Singh · Accepted Answer · 2013-12-04T09:02:22.420

It will be much better if you use stored procedures

     INSERT INTO patient (patientID,patientID,lName)
            VALUES("NULL",'" + fNameTxt.Text + "','" + lNameTxt.Text + "');
   SET @last_id_in_patient  = LAST_INSERT_ID();        
     INSERT INTO patientDisease (patientID,diseaseID,dateChecked)
            VALUES( @last_id_in_patient ,NULL,'text');  # use ID in second table";

Now You can update your PatientDisease table for particular PatientId.

How to use LAST_INSERT_ID() in c# windows form?

2 Answers2