How can I get an identity value in a controller

Question

0.00/5 (No votes)

See more:

Hi everyone,

Currently, I am using MVC on creating a project. Now I want to insert an Identity ID value into an INSERT statement.

Example:

string payment = @"INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount)
                           VALUES('{0}','{1}',{2})";
          int pay = DBUtl.ExecSQL(payment, "Cash", currency,total);

So the above codes is a SQL statement that I used for INSERT values into the database which is the payment table and is successfully been run and inserted into the table.

Now, I trying to get the ID that has been inserted into the Payment Table for the next INSERT Statement for Order table. As there is a relationship between these 2 tables which is the payment_id is a foreign key in my Order Table.

So, I plan to retrieve the ID and then put it into my INSERT Statement for my Order table.

Please Help

Thank you

What I have tried:

So far, this is what I tried on getting the ID:

1) Include a SELECT statement while inserting:

int ord = DBUtl.ExecSQL(order, item.Cart_Service, item.Additional_Notes, item.Quantity, ("SELECT TOP 1 Payment_id FROM Payment ORDER BY Payment_id DESC"));

2) Assign a variable to get the value:

string pid = "SELECT TOP 1 Payment_id FROM Payment ORDER BY Payment_id DESC";
                int paymentid = DBUtl.ExecSQL(pid);

And all of these are not working.

Posted 22-Jan-21 19:30pm

Yong Kevin

Updated 22-Jan-21 19:51pm

v2

Add a Solution

Comments

Richard Deeming 25-Jan-21 4:25am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

You need to update your DBUtil class to accept properly parameterized queries. Until you do, you are forcing yourself to write vulnerable code.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2021-01-22T19:47:00

Solution 1

Create a stored proc that runs a transaction to insert all the values you wanted and returns them. You need this to be atomic

This is not an MVC question, because it has nothing to do with the fact you happen to be using MVC

Posted 22-Jan-21 19:47pm

Christian Graus

Comments

Yong Kevin 23-Jan-21 1:55am

Hi Christian,

Thank you for your reply

I am a little bit confused about what do you mean by creating a stored proc. As for now, I only wanted to retrieve the payment_id that has been inserted into the payment table and use the Payment_id for inserting into Order table

Christian Graus 23-Jan-21 1:57am

so you want to make two steps. So do them in a stored proc, in a transaction, and return the result. Then it all worked, or none of it did

Yong Kevin 23-Jan-21 2:26am

Hi Christian,

Thank you for your reply,

Two steps, do you mean on doing something like this:
string payment = @"INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount)
VALUES('{0}','{1}',{2})";
int pay = DBUtl.ExecSQL(payment, "Cash", currency,total);
if (pay == 1)
{
string pid = "SELECT TOP 1 Payment_id FROM Payment ORDER BY Payment_id DESC";
int paymentid = DBUtl.ExecSQL(pid);
if (cart.Count() != 0)
{
string order = @"INSERT INTO [Order](Order_Name,Order_Description,Order_Quantity,Payment_Id)
VALUES('{0}','{1}',{2},{3})";

Thank you

Christian Graus 23-Jan-21 2:41am

God no. I mean what I said. Call a STORED PROCEDURE that manages the process