Hello all
I'm working on a 2 page form. I want to submit the data on the first page, collect the scope identity, and us it as the id when submitting the rest of the data on page 2. I'm using VB, I have all my vb in one class called "contact" but have given each aspx page its on function to call on submit:
The stuff below works independently of each other, so I'm not looking for syntax issues, but wondering what I need to do to pick up the scope_identity and set it as the id for the second sproc. I can get the sproc to fire alone if I enter in an id name, but just can't get it to submit via the webpage; i get :
Procedure or function 'addContact2' expects parameter '@id', which was not supplied.
Any help is appreciated, code is below. One thing I'm thinking is that I need to change the retval on the second page function to execute a dataset instead of scalar?
CONTACT CLASS
-FIRST PAGE FUNCTION-
Function Save() As Boolean
Dim retCode As Boolean = False
Try
Dim spname As String = "addContact"
Dim parm(62) As SqlParameter
Dim iSortOrder As Integer = 0
parm(1) = New SqlParameter("@Type", _contactType)
parm(2) = New SqlParameter("@LastName", _lastName)
parm(3) = New SqlParameter("@FirstName", _firstName)
Dim retVal As Integer
retVal = SqlLayer.ExecuteScalar(Connection.GetConnection, CommandType.StoredProcedure, spname, parm)
_contactId = retVal
retCode = True
Catch ex As Exception
Throw ex
End Try
Return retCode
End Function
-SECOND PAGE FUNCTION-
Function Update() As Boolean
Dim retCode As Boolean = False
Try
Dim spname As String = "addContact2"
Dim parm(24) As SqlParameter
Dim iSortOrder As Integer = 0
If Not _isEnglish = Nothing Then
parm(1) = New SqlParameter("@English", _isEnglish)
End If
If Not _englishProficiencyReading Is Nothing Then
parm(2) = New SqlParameter("@EnglishProficiencyReading", _englishProficiencyReading)
End If
If Not _englishProficiencySpeaking Is Nothing Then
parm(3) = New SqlParameter("@EnglishProficiencySpeaking", _englishProficiencySpeaking)
End If
If Not _englishProficiencyWriting Is Nothing Then
parm(4) = New SqlParameter("@EnglishProficiencyWriting", _englishProficiencyWriting)
End If
Dim retVal As Integer
retVal = SqlLayer.ExecuteScalar(Connection.GetConnection, CommandType.StoredProcedure, spname, parm)
_contactId = retVal
retCode = True
Catch ex As Exception
Throw ex
End Try
Return retCode
End Function
The 2 Sprocs being called look like this:
ALTER PROCEDURE [dbo].[addContact]
(@Type [varchar](50)= NULL,
@AppID [varchar](20)= NULL,
@CertainID [varchar] (20) = NULL,
@Salutation [varchar] (5) = NULL,
@FirstName [varchar](50),
@MiddleName [varchar](50)= NULL,
@LastName [varchar](75)
AS
BEGIN
INSERT INTO [Contact]
(
[Type],
[AppID],
[CertainID],
[Salutation],
[FirstName],
[MiddleName],
[LastName]
VALUES
(
@Type,
@AppID,
@CertainID,
@Salutation,
@FirstName,
@MiddleName,
@LastName
);
SELECT SCOPE_IDENTITY()
END
- and -
ALTER PROCEDURE [dbo].[addContact2]
(
@id varchar(20),
@English int = null,
@EnglishProficiencyReading varchar(50)= null,
@EnglishProficiencySpeaking varchar(50)= null,
@EnglishProficiencyWriting varchar(50)= null
)
AS
BEGIN
/* Update App with Page 2 info */
Insert into [Contact]
(
[ContactId],
[English],
[EnglishProficiencyReading],
[EnglishProficiencySpeaking],
[EnglishProficiencyWriting]
);
set @id = scope_identity()
END