?
Solved

return id after adding record store procedure

Posted on 2005-04-04
4
Medium Priority
?
228 Views
Last Modified: 2010-04-07
Hi,

How can we query the autonumber after inserting?

id called stsn

Thanks.


create proc [dbo].[SP_shift_break_add]
@break1 datetime,
@break2 datetime,
@lunch datetime
as
insert into shiftbreak_master (lunch,break1,break2) values (@lunch,@break1,@break2)
select @@identity as 'stsn'


GO
0
Comment
Question by:lynnton
  • 3
4 Comments
 
LVL 23

Expert Comment

by:b1xml2
ID: 13699466
'cmd is the SqlCommand object
Dim id As Int = CInt(cmd.ExecuteScalar())
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13699469
typo
===
 'cmd is the SqlCommand object
Dim id As Integer = CInt(cmd.ExecuteScalar())
0
 
LVL 1

Author Comment

by:lynnton
ID: 13699563
b1xml2,

What could be happening when I try to add; it always returns 0. How can we troubleshoot?

Thanks.


                                    cmd.CommandType = CommandType.StoredProcedure
                                    cmd.CommandText = "SP_shift_break_add"
                                    cmd.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, Nothing))
                                    cmd.Parameters.Add("@lunch",SqlDbType.datetime).Value = DirectCast(Me.FindControl(String.Format("txtlb{0}",count)),textbox).text
                                    cmd.Parameters.Add("@break1",SqlDbType.datetime).Value =  DirectCast(Me.FindControl(String.Format("txtfb{0}",count)),textbox).text
                                    cmd.Parameters.Add("@break2",SqlDbType.datetime).Value =  DirectCast(Me.FindControl(String.Format("txtsb{0}",count)),textbox).text
                                    cmd.executenonquery()
                                    returnvalue = cmd.parameters("@RETURN_VALUE").value
                                    cmd.dispose
                                    cmd.parameters.clear

                                label1.text=returnvalue

0
 
LVL 23

Accepted Solution

by:
b1xml2 earned 2000 total points
ID: 13699718
the return_value is not being used in your stored proc...

change it like so and then you shd be able to use the return param...

create proc [dbo].[SP_shift_break_add]
@break1 datetime,
@break2 datetime,
@lunch datetime
as
insert into shiftbreak_master (lunch,break1,break2) values (@lunch,@break1,@break2)
RETURN @@identity
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month9 days, 22 hours left to enroll

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question