No just the one.
What I am trying to do is based on the selected record (whichever
one is checked off from the datagrid), get that PK, pass it to the
SP.
In the SP, I want to create a new record with the same info from the
old record (checked off) and update the status fields.
Here's my SP:
CREATE procedure spLostCard
(
@CustomerUID int,
@Error_Code int OUTPUT
)
as
-- Declare variable for our return code
DECLARE
@SQLErrorCode int
begin tran LostCard
-- Step 1: Insert new record
Insert into Cardholders
Select Title, LastName, FirstName, MiddleInitial, Email, Phone
>From Cardholders
Where CustomerUID = @CustomerUID
-- Now check for any errors
Select @SQLErrorCode = @@Error
-- Get the PK for this new record
Select @CustomerUID = @@identity
-- If we have an error, set the variable and return it, and rollback
transaction
if @SQLErrorCode <> 0
begin
rollback tran LostCard
set @Error_Code = 1
return
end
-- Step 2: Insert the second record using the PK from the first
record inserted above
Insert into CallingCardInfo(CustomerUID, Requested)
Values(@CustomerUID, '1')
-- Now check for any errors
Select @SQLErrorCode = @@Error
-- If we have an error, set the variable and return it, and rollback
transaction
if @SQLErrorCode <> 0
begin
rollback tran LostCard
set @Error_Code = 2
return
end
-- Step 3: Update CallingCardInfo table (flag/deflag proper column)
Update CallingCardInfo
Set Lost = '1', CancelReq = '1'
Where @CustomerUID = CustomerUID
-- If we have it this far, then the transaction was successful
commit tran LostCard
set @Error_Code = 0
return
GO
I'm probably doing something wrong. Please advise. Thanks!
--- In [email protected], Peter Brunone
<[EMAIL PROTECTED]> wrote:
>
> Are there more input parameters than the one you're adding?
>
> On 11/18/05, sas0riza <[EMAIL PROTECTED]> wrote:
> >
> > Hi,
> >
> > I have the following:
> >
> > Sub Select_Click(sender As Object, e As EventArgs)
> >
> > Panel1.Visible = False
> >
> >
> > Dim myDataGridItem As DataGridItem
> >
> > Dim chkSelected As System.Web.UI.WebControls.CheckBox
> >
> > Dim strMCICardID As String
> >
> >
> > For Each myDataGridItem In searchresults.Items
> >
> > chkSelected = myDataGridItem.FindControl("chkSelection")
> >
> > If chkSelected.Checked Then
> >
> > strMCICardID = CType(myDataGridItem.FindControl
> > ("lblMCICardID"), Label).Text
> >
> >
> >
> > Panel2.Visible = True
> >
> >
> >
> > End Sub
> >
> >
> > Sub Submit_Click(sender As Object, e As EventArgs)
> > If Page.IsValid then
> >
> >
> > Dim objConn As SqlConnection
> > Dim objSQLCommand As SqlCommand
> > Dim objParam As SqlParameter
> > Dim boolInsertOK As Boolean = False
> > Dim intReturnError as Integer
> >
> > 'Set up our connection object and SQL command
> > object.
> > objConn = New SqlConnection("_")
> > objSQLCommand = New SqlCommand("spLostCard",
> > objConn)
> >
> > 'Set the stored procedure.
> > objSQLCommand.CommandType =
> > CommandType.StoredProcedure
> >
> >
> >
> > 'Add input parameters...
> > objParam = objSQLCommand.Parameters.Add
> > ("@CustomerUID", strMCICardID)
> >
> >
> > 'Add our ouput parameters...
> > objParam = objSQLCommand.Parameters.Add
> > ("@Error_Code", SqlDbType.Int)
> > objParam.Direction = ParameterDirection.Output
> >
> > 'Open the db connection & Execute Command.
> > objConn.Open()
> > objSQLCommand.ExecuteNonQuery()
> >
> > 'Get the return values.
> > If Not IsDBNull(objSQLCommand.Parameters
> > ("@Error_Code").Value) Then
> > intReturnError = (objSQLCommand.Parameters
> > ("@Error_Code").Value)
> > End If
> >
> > Response.Write ("<BR>return error code = " &
> > intReturnError)
> >
> > 'Check the error.
> > If intReturnError = 0 Then
> > 'No errors. Set our confirm msg.
> > boolInsertOK = True
> > Else
> > 'We had an error. Set our error msg.
> > boolInsertOK = False
> > End If
> >
> > 'Close the DB connection.
> > objConn.Close()
> >
> >
> > End If
> >
> >
> > End Sub
> >
> >
> >
> >
> > However, I am getting this error: Procedure 'spLostCard' expects
> > parameter '@CustomerUID', which was not supplied
> >
> > Thanks!
>
>
> [Non-text portions of this message have been removed]
>
------------------------ Yahoo! Groups Sponsor --------------------~-->
1.2 million kids a year are victims of human trafficking. Stop slavery.
http://us.click.yahoo.com/WpTY2A/izNLAA/yQLSAA/saFolB/TM
--------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
