I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @.@.IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck:
Public Sub InsertOrder()
Conn.Open()
cmd = New SqlCommand("Add_NewOrder", Conn)
cmd.CommandType = CommandType.StoredProcedure
' pass customer info to stored proc
cmd.Parameters.Add("@.FirstName", txtFName.Text)
cmd.Parameters.Add("@.LastName", txtLName.Text)
cmd.Parameters.Add("@.AddressLine1", txtStreet.Text)
cmd.Parameters.Add("@.CityID", dropdown_city.SelectedValue)
cmd.Parameters.Add("@.Zip", intZip.Text)
cmd.Parameters.Add("@.EmailPrefix", txtEmailPre.Text)
cmd.Parameters.Add("@.EmailSuffix", txtEmailSuf.Text)
cmd.Parameters.Add("@.PhoneAreaCode", txtPhoneArea.Text)
cmd.Parameters.Add("@.PhonePrefix", txtPhonePre.Text)
cmd.Parameters.Add("@.PhoneSuffix", txtPhoneSuf.Text)
' pass order info to stored proc
cmd.Parameters.Add("@.NumberOfPeopleID", dropdown_people.SelectedValue)
cmd.Parameters.Add("@.BeanOptionID", dropdown_beans.SelectedValue)
cmd.Parameters.Add("@.TortillaOptionID", dropdown_tortilla.SelectedValue)
'Session.Add("FirstName", txtFName.Text)
cmd.ExecuteNonQuery()
cmd = New SqlCommand("Add_EntreeItems", Conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@.CateringOrderID", get identity from previous stored proc) <--------
Dim li As ListItem
Dim p As SqlParameter = cmd.Parameters.Add("@.EntreeID", Data.SqlDbType.VarChar)
For Each li In chbxl_entrees.Items
If li.Selected Then
p.Value = li.Value
cmd.ExecuteNonQuery()
End If
Next
Conn.Close()
I want to somehow grab the @.CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
Sorry that I don't know the exact syntax off hand, but if your stored procedure is returning the @.@.Identity as it's return, then you need to add the return parameter to the first command.
It'd be something like (sorry again, this is from memory):
cmd.Parameters.Add(New SqlParameter("@.CateringOrderID",RETURN_VALUE)) or something similiar, then at later add
dim ret as long = cmd.Parameters("@.CateringOrderID").value
Or something very similiar.
If your stored procedure is using an output parameter, you need to do the same thing, but instead of calling it RETURN_VALUE, it's like OUTPUT_VALUE or something. There is a more descriptive way of adding parameters that you can tell it if it's return value type parameter, an input type, or an output type. Once you find that, that's 90% of your solution.
|||thanks for your input, still working on it.|||right now I'm trying to use this to obtain the returned @.@.IDENTITY from my first stored proc:
Dim NewCateringOrderIDAsInteger =CType(cmd.ExecuteScalar(),Integer)
|||Yes, that will work if the identity is the first and only result the stored procedure generates. To turn off empty resultsets (From inserts, updates, etc that go on in the stored procedure), place a SET NOCOUNT ON at the beginning of the stored procedure, and place a SET NOCOUNT OFF right before your SELECT SCOPE_IDENTITY(). Then optionally add SET NOCOUNT ON again if there are any other database modification statements after that, and finally SET NOCOUNT OFF at the very end.|||Here we go, thanks for the start
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
|||You can do something like this:SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand("sproc_name",myConnection);
// Attach the parameters to the myCommand object
Set the parameter direction of the commad object to returnValue
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
int identity = (int) myCommand.Parameters["MyReturnValue"].value;
// now that you have identity you can send into next stored procedure
Also remember that you can also send the values from one stored procedure to another stored procedure using T-SQL queries this way you dont need to return the identity to the presentation layer.
CREATE PROCEDURE #test_proc
AS
INSERT INTO #test VALUES(123)
RETURN SCOPE_IDENTITY()|||Here is another way which simply calls one stored procedure from another stored procedure:
CREATE TABLE #Names (k1 int identity(1,1) , name varchar(20) )
CREATE TABLE #Phone (k2 int identity(1,1), phone varchar(20), k1 int )
CREATE PROCEDURE #insert_name
@.Name varchar(20)
AS
DECLARE @.returnValue int
INSERT INTO #Names VALUES(@.Name)
SET @.returnValue = SCOPE_IDENTITY()
EXEC #insert_phone @.nameID = @.returnValue
EXEC #insert_name @.Name = 'AzamSharp'
SELECT * FROM #Names
SELECT * FROM #phone|||thanks but in this case, I'm not able to send the IDENTITY to another stored procedure because I need to run through another stored proc to insert checkboxlist items and must do this through another call where I loop through each item in the checkbox. I suppose it would be much better to do what you say and just loop through the checkboxlist items first then pass one string to the same stored procedure...actually I'll try that way instead this time since I do know how to pass the identity or other parameters to other stored procs from within the same stored proc|||
Create proc ( int @.id Output) AS
Select * from table ;
Select @.id=@.@.identity;
===============================
cmd.Parameters.Add(@.id,SqlDbType.Int32);
cmd.Parameters["@.id"].Direction=ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return (int)cmd.Parameters["@.id"].value;
============================
i am not fimiliar with english, may be missing spell but here is a idea which i can work through out.
