ASP.NET 2 , SQL 2000, VB 2005 returned value

M

mrmgroups

Hi,

I am really new to this so please be gentle. I have a web page (aspx)
that is a simple form. The info is filled in and a stored procedure
updates the table. The table updates as expected no issues there.
What I am trying to do is get back the identity value(table field is
auto incrementing integer) for the record that has been updated. EG I
update the table it creates a new record with the id of 25 the 25 is
the value I am trying to get. Below is the test code I am using while
trying to sort this out (the actual page is the same just has more
parameters that are passed). Any help and pointers in the right
direction will be very much appreciated.

Thanks

Mark

------- Start of Code -----
Public Partial Class test
Inherits System.Web.UI.Page

Protected Sub submitButton_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles submitButton.Click
Dim TestDataSource As New SqlDataSource()
Dim cb1 As String

TestDataSource.ConnectionString =
ConfigurationManager.ConnectionStrings("WebEmployConnectionString1").ToString()
TestDataSource.InsertCommandType =
SqlDataSourceCommandType.StoredProcedure
TestDataSource.InsertCommand = "TestInsert"
TestDataSource.InsertParameters.Add("Name1",
Name1TextBox.Text)
Dim Birthdate As DateTime =
Convert.ToDateTime(Name2TextBox.Text)
TestDataSource.InsertParameters.Add("Name2",
TypeCode.DateTime, Birthdate)
TestDataSource.InsertParameters.Add("text1",
text1TextBox.Text)
TestDataSource.InsertParameters.Add("text2",
text2TextBox.Text)
If CheckBox1.Checked Then
cb1 = "checkedxxxx"
Else
cb1 = ""
End If
TestDataSource.InsertParameters.Add("text3", cb1)
TestDataSource.InsertParameters.Add("AppNo", 0)

Dim rowsAffected As Integer = 0

Try
rowsAffected = TestDataSource.Insert()
Catch ex As Exception
Server.Transfer("error.htm")
Finally
TestDataSource = Nothing
End Try

If rowsAffected < 1 Then
Server.Transfer("error2.htm")
Else
Server.Transfer("ok.htm")
End If

End Sub

End Class

----- End of Code -----
 
M

Mr. Arnold

mrmgroups said:
Hi,

I am really new to this so please be gentle. I have a web page (aspx)
that is a simple form. The info is filled in and a stored procedure
updates the table. The table updates as expected no issues there.
What I am trying to do is get back the identity value(table field is
auto incrementing integer) for the record that has been updated. EG I
update the table it creates a new record with the id of 25 the 25 is
the value I am trying to get. Below is the test code I am using while
trying to sort this out (the actual page is the same just has more
parameters that are passed). Any help and pointers in the right
direction will be very much appreciated.
Click to expand...

You use the @@Identity, you set an output variable to be returned out of the
SP with the @@Identity, which you can give the direction on the parameter as
outbound and using ADO.NET parameters supplied to a SQL Server SP, look it
up use Google.

http://www.fwzone.net/showDetail.asp?TypeId=2&NewsId=3932
 
M

mrmgroups

You use the @@Identity, you set an output variable to be returned out of the
SP with the @@Identity, which you can give the direction on the parameter as
outbound and using ADO.NET parameters supplied to a SQL Server SP, look it
up use Google.

http://www.fwzone.net/showDetail.asp?TypeId=2&NewsId=3932
Click to expand...

Thanks for that however the link is to do with using dreamweaver and I
am using Visual Basic within the Visual Studio IDE for this project.
I probably didnt make it clear but I dont have a problem getting the
id in the stored procedure (see below - this procedure is working and
if I execute it "manually" via the server explorer within the VS IDE
AppNo
is returning the value I expect. The problem I have is getting it from
the stored procedure back into the calling Visual basic code and being
able to use this value later in the program to provide feedback to the
user.

Regards

Mark


--------- Current Stored Procedure --------------------
ALTER PROCEDURE [dbo].[TestInsert]

@Name1 varchar(50),
@Name2 datetime,
@text1 varchar(50),
@text2 varchar(50),
@text3 varchar(50),
@AppNo int output
AS

INSERT test (Name1,Name2)
VALUES (@Name1,@Name2)

SELECT @AppNo = SCOPE_IDENTITY()
INSERT testsub (AppNo,text1)
VALUES (@AppNo,@text1)
INSERT testsub (AppNo,text1)
values (@AppNo,@text2)
INSERT TESTSUB (AppNo,text1)
VALUES (@AppNo,@text3)

RETURN @AppNo
-------------------- End Stored Procedure --------------------
 
R

rowe_newsgroups

Hi,

I am really new to this so please be gentle. I have a web page (aspx)
that is a simple form. The info is filled in and a stored procedure
updates the table. The table updates as expected no issues there.
What I am trying to do is get back the identity value(table field is
auto incrementing integer) for the record that has been updated. EG I
update the table it creates a new record with the id of 25 the 25 is
the value I am trying to get. Below is the test code I am using while
trying to sort this out (the actual page is the same just has more
parameters that are passed). Any help and pointers in the right
direction will be very much appreciated.

Thanks

Mark

------- Start of Code -----
Public Partial Class test
Inherits System.Web.UI.Page

Protected Sub submitButton_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles submitButton.Click
Dim TestDataSource As New SqlDataSource()
Dim cb1 As String

TestDataSource.ConnectionString =
ConfigurationManager.ConnectionStrings("WebEmployConnectionString1").ToString()
TestDataSource.InsertCommandType =
SqlDataSourceCommandType.StoredProcedure
TestDataSource.InsertCommand = "TestInsert"
TestDataSource.InsertParameters.Add("Name1",
Name1TextBox.Text)
Dim Birthdate As DateTime =
Convert.ToDateTime(Name2TextBox.Text)
TestDataSource.InsertParameters.Add("Name2",
TypeCode.DateTime, Birthdate)
TestDataSource.InsertParameters.Add("text1",
text1TextBox.Text)
TestDataSource.InsertParameters.Add("text2",
text2TextBox.Text)
If CheckBox1.Checked Then
cb1 = "checkedxxxx"
Else
cb1 = ""
End If
TestDataSource.InsertParameters.Add("text3", cb1)
TestDataSource.InsertParameters.Add("AppNo", 0)

Dim rowsAffected As Integer = 0

Try
rowsAffected = TestDataSource.Insert()
Catch ex As Exception
Server.Transfer("error.htm")
Finally
TestDataSource = Nothing
End Try

If rowsAffected < 1 Then
Server.Transfer("error2.htm")
Else
Server.Transfer("ok.htm")
End If

End Sub

End Class

----- End of Code -----
Click to expand...

Try this thread:

http://groups.google.com/group/micr...eturn+parameter&rnum=3&hl=en#a2bbf302317d43b8


Thanks,

Seth Rowe
 
M

Mr. Arnold

mrmgroups said:
Thanks for that however the link is to do with using dreamweaver and I
am using Visual Basic within the Visual Studio IDE for this project.
Click to expand...

It's not about DW. It's about what that SP is doing to capture the record ID
of the record that was inserted, which the @@Identity, a SQL Server
variable, will hold the information.

is returning the value I expect. The problem I have is getting it from
the stored procedure back into the calling Visual basic code and being
able to use this value later in the program to provide feedback to the
user.
Click to expand...

I saw the Stored Procedure . Your mission if you choose to accept the
mission is to use Google and find out how to get a *output* parameter from
an *output* variable in the Stored Procedure or when the RETURN statement is
used to return a value from the SP, using ADO.NET. There are plenty of
examples out there on Google on how to do it.
 
Top