SQL Stored proc running twice

SimonPrice33
SimonPrice33 used Ask the Experts™
on
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
sqlconn.open

sqlcmd.executenonquery

riskID = cint(sqlcmd.executescalar)

sqlconn.close

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

however, its inserting it twice and returning the ID once... any suggestions?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sr.Software Engineer
Top Expert 2013
Commented:
the problem is with your below code

sqlcmd.executenonquery

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.

Author

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.

thanks

Simon
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
both commands will actually RUN the command.
the difference is that the executenonquery will discard any results.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2015

Commented:
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.

Author

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 Engineer
Top Expert 2013

Commented:
Thanks SimonPrice33

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial