• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

SQL Stored proc running twice

Hi Experts,

I am building an applicationt that I need to insert data and then return the ID of the inserted record.

an examples of my code looks similar to this..

dim param as new sqlparameter("@risk", sqldbtype)
(there a number of parameters)

sqlconn.connection= connstr


riskID = cint(sqlcmd.executescalar)


It is inserting the data, and returning the ID value...

however, its inserting it twice and returning the ID once... any suggestions?
1 Solution
Jitendra PatilSr.Software EngineerCommented:
the problem is with your below code


riskID = cint(sqlcmd.executescalar)

you are running the same command query two differnt ways i.e. executenonquery and executescalar

Remove the sqlcmd.executenonquery statement.

hope this helps.
SimonPrice33Author Commented:
i will try this later this afternoon and get back to you thank you.

I thought it would be something like this, but was under the assumption the execuscalar would only return the @@identity data.


Guy Hengel [angelIII / a3]Billing EngineerCommented:
both commands will actually RUN the command.
the difference is that the executenonquery will discard any results.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Jacques Bourgeois (James Burger)PresidentCommented:
If the command is a stored procedure that finish with SELECT @@IDENTITY, simply remove the ExecuteNonQuery. ExecuteScalar alone will do the insert and retrieve @@IDENTITY.

If the command is a SQL string, then create a second command object with "SELECT @@IDENTITY" and run in separately after the ExecuteNonQuery.
SimonPrice33Author Commented:
Although each of you gave the answer that solved my issue, this was the first to give me the answer.

many thanks to you all
Jitendra PatilSr.Software EngineerCommented:
Thanks SimonPrice33
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now