Here's my 2nd insert:
--
************************************************************
-- *** Step 2 - Insert the second record using the PK
from ***
-- *** the first record inserted above
***
--
************************************************************
Insert into CallingCardInfo(CustomerUID, PK1, CardUID,
CardNum, CardPIN)
Values (@CustomerUID, @PK1, @CardUID, @CardNum,
@CardPIN)
-- Now check for any errors.
select @SQLErrorCode = @@Error
-- If we have an error, set the variable and return it
and rollback the transaction.
if @SQLErrorCode <> 0
begin
rollback tran InsertAllRecords
set @Error_Code = 2
return
end
-- If we made it this far, then the transaction was
successful.
commit tran InsertAllRecords
set @Error_Code = 0
return
---------------------------------
And I get this error:
Server: Msg 207, Level 16, State 1, Procedure
sp_InsertAllRecords, Line 40
Invalid column name 'PK1'.
It doesn't recognize 'PK1' as a column in my table.
Thanks!!
--- Mark E <[EMAIL PROTECTED]> wrote:
> Oh, I see the problem.
>
> In the second insert, change @AccountUID to @PK1.
> That should solve the problem.
>
> Mark
>
> Anna Leon <[EMAIL PROTECTED]> wrote:
> Thanks for taking your time to help me. I believe I
> removed all references to that parameter. However,
> in
> the SP, in the second step, the primary key and
> foreign keys are referenced there. And since
> @AccountUID is referenced there, SQL Analyzer gives
> the error about not @AccountUID not being declared.
>
>
> --- Mark E <[EMAIL PROTECTED]> wrote:
>
> > Anna,
> >
> > Remove all references to this parameter, as you do
> > not need it. As a matter of fact, you do not know
> > the value because it the PK of the record that
> > hasen't even been inserted.
> >
> > Did you use the 1 stored procedure that I posted?
> I
> > removed that parameter and recommend using it.
> >
> > If you like, zip up everything and email it to me
> > off-list and I will look at it tonight.
> >
> > Mark
> >
> > Anna Leon <[EMAIL PROTECTED]> wrote:
> > It still gives me an error about the "@AccountUID"
> > parameter. I believe I need to declare it below,
> as
> > one of the input parameters since in the SP, it is
> > declared in there. BTW, I had to declare
> @AccountUID
> > in the SP because it gave me an error there too.
> >
> > If I were to declare the input parameter,
> > @AccountUID,
> > what should I put in the bracket below? Because my
> > other parameters are being passed by whatever is
> in
> > their corresponding textboxes...
> >
> > objParam
> > =objSQLCommand.Parameters.Add("@AccountUID",[WHAT
> TO
> > PUT HERE?])
> >
> >
> > Thanks!!!
> >
> > --- Mark E <[EMAIL PROTECTED]> wrote:
> >
> > > Unless I missed something, It looks fine to me.
> > >
> > > Mark
> > >
> > > Anna Leon <[EMAIL PROTECTED]> wrote:
> > > BTW, I need to alter this sub routine too?
> > >
> > > Sub Submit2_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(ConfigurationSettings.AppSettings("ConnectionString"))
> > > objSQLCommand = New
> > > SqlCommand("sp_InsertAllRecords", objConn)
> > > 'Set the stored procedure.
> > > objSQLCommand.CommandType =
> > > CommandType.StoredProcedure
> > > 'Add input parameters...
> > > objParam =
> > > objSQLCommand.Parameters.Add("@AccountNumber",
> > > TextBox1.Text)
> > > objParam =
> > >
> >
> objSQLCommand.Parameters.Add("@BillingAddressName",
> > > TextBox_new.Text)
> > > objParam =
> > > objSQLCommand.Parameters.Add("@BillingAddress",
> > > TextBox2.Text)
> > > objParam =
> > >
> >
> objSQLCommand.Parameters.Add("@BillingAddressCity",
> > > TextBox3.Text)
> > > objParam =
> > >
> >
> objSQLCommand.Parameters.Add("@BillingAddressState",
> > > DropDownList1.SelectedItem.Text)
> > > objParam =
> > >
> objSQLCommand.Parameters.Add("@BillingAddressZip",
> > > TextBox4.Text)
> > > objParam =
> > > objSQLCommand.Parameters.Add("@CardNum",
> > > TextBox5.Text)
> > > objParam =
> > > objSQLCommand.Parameters.Add("@CardPIN",
> > > TextBox6.Text)
> > > '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
> > > '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
> > >
> > > --- Mark E <[EMAIL PROTECTED]> wrote:
> > >
> > > > Anna,
> > > >
> > > > Below is 1 SP that does both inserts. You
> will
> > > need
> > > > to remove the first input parameter from your
> > web
> > > > page, as it's no longer needed.
> > > >
> > > > I prefer one since I like the element of total
> > > > control over all related inserts. And by
> doing
> > it
> > > > this way, you can see all your code in 1 SP
> and
> > > wrap
> > > > it within a trans tag. I don't know if you
> are
> > > > familiar with SQL Server transactions, but if
> > you
> > > > use the trans tag, you have the ability to
> > > > "rollback" transactions (inserts, updates &
> > > deletes)
> > > > if something goes wrong anywhere in your SP.
> > > >
> > > > Anyhow, look it over and let me know if you
> have
> > > any
> > > > questions.
> > > >
> > > > Mark
> > > >
> > > > --drop procedure sp_InsertAllRecords
> > > > CREATE procedure sp_InsertAllRecords
> > > >
> > > > @AccountNumber varchar(50),
> > > > @BillingAddressName varchar(100),
> > > > @BillingAddress varchar(100),
> > > > @BillingAddressCity varchar(50),
> > > > @BillingAddressState char(2),
> > > > @BillingAddressZip varchar(20),
> > > > @CardUID int,
> > > > @CardNum varchar(50),
> > > > @CardPIN varchar(50),
> > > > @Error_Code int output
> > > > as
> > > > declare
> > > > @SQLErrorCode int,
> > > > @WhateverID int,
> > > > @PK1 int
> > > >
> > > > begin tran InsertAllRecords
> > > > --
> > > >
> > >
> >
>
************************************************************
> > > > -- *** Step 1 - Insert the first record ***
> > > > --
> > > >
> > >
> >
>
************************************************************
> > > > Insert CallingCardAccount
> > > > (AccountNumber,BillingAddressName,
> > BillingAddress,
> > > > BillingAddressCity, BillingAddressState,
> > > > BillingAddressZip)
> > > > Values (@AccountNumber, @BillingAddressName,
> > > > @BillingAddress, @BillingAddressCity,
> > > > @BillingAddressState, @BillingAddressZip)
> > > > -- Now check for any errors.
> > > > select @SQLErrorCode = @@Error
> > > > -- Get the PK for this new record.
> > > > select @PK1 = @@identity
> > > > -- If we have an error, set the variable and
> > > return
> > > > it and rollback the transaction.
> > > > if @SQLErrorCode <> 0
> > > > begin
> > > > rollback tran InsertAllRecords
> > > > 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,
> > > AccountUID,
> > > > CardUID, CardNum, CardPIN)
> > > > Values (@CustomerUID, @AccountUID, @CardUID,
> > > > @CardNum, @CardPIN)
> > > > -- Now check for any errors.
> > > > select @SQLErrorCode = @@Error
> > > > -- If we have an error, set the variable and
> > > return
> > > > it and rollback the transaction.
> > > > if @SQLErrorCode <> 0
> > > > begin
> > > > rollback tran InsertAllRecords
> > > > set @Error_Code = 2
> > > > return
> > > > end
> > > > -- If we made it this far, then the
> transaction
> > > was
> > > > successful.
> > > > commit tran InsertAllRecords
> > > > set @Error_Code = 0
> > > > return
> > > >
> > > > Anna Leon <[EMAIL PROTECTED]> wrote:
> > > > Yes I am calling multiple SPs. I am passing
> > > > "@AccountUID" to one of them. Correct me if
> I'm
> > > > wrong.
> > > >
> > > > From webpage, call sp_InsertMain
> > > > From sp_InsertMain, call sp_InsertAccount2 and
> > > > sp_InsertCallingCardInfo
> > > >
> > > >
> > > > SP1
> > > > -----------------------------
> > > > CREATE PROCEDURE sp_InsertMain
> > > > @AccountUID int,
> > > > @AccountNumber varchar(50),
> > > > @BillingAddressName varchar(100),
> > > > @BillingAddress varchar(100),
> > > > @BillingAddressCity varchar(50),
> > > > @BillingAddressState char(2),
> > > > @BillingAddressZip varchar(20),
> > > >
> > > > @CardUID int,
> > > > @CardNum varchar(50),
> > > > @CardPIN varchar(50),
> > > >
> > > > @Error_Code int
> > > > AS
> > > > Declare @WhateverID int
> > > >
> > > > EXEC @WhateverID = sp_InsertAccount2
> > @AccountUID,
> > > > @AccountNumber,
> > > > @BillingAddressName,
> > > > @BillingAddress,
> > > > @BillingAddressCity,
> > > > @BillingAddressState,
> > > > @BillingAddressZip
> > > >
> > > >
> > > > Exec InsertCallingCardInfo @AccountUID,
> > > @WhateverID,
> > > > @CardUID,
> > > > @CardNum,
> > > > @CardPIN
> > > > ----------------------
> > > >
> > > > SP2
> > > > ------------------------
> > > > CREATE PROCEDURE sp_InsertAccount2
> > > > @AccountUID int,
> > > > @AccountNumber varchar(50),
> > > > @BillingAddressName varchar(100),
> > > > @BillingAddress varchar(100),
> > > > @BillingAddressCity varchar(50),
> > > > @BillingAddressState char(2),
> > > > @BillingAddressZip varchar(20)
> > > >
> > > >
> > > > AS
> > > > Begin
> > > > Set NoCount on
> > > > Insert CallingCardAccount(AccountNumber,
> > > > BillingAddressName, BillingAddress,
> > > > BillingAddressCity, BillingAddressState,
> > > > BillingAddressZip)
> > > > Values
> > > > (@AccountNumber, @BillingAddressName,
> > > > @BillingAddress, @BillingAddressCity,
> > > > @BillingAddressState, @BillingAddressZip)
> > > >
> > > > RETURN @@IDENTITY
> > > >
> > > > END
> > > > -----------------------------
> > > >
> > > > SP3
> > > > -------------------------
> > > > CREATE PROCEDURE InsertCallingCardInfo
> > > > @CustomerUID int,
> > > > @AccountUID int,
> > > > @CardUID int,
> > > > @CardNum varchar(50),
> > > > @CardPIN varchar(50)
> > > > As
> > > > Insert into CallingCardInfo
> > > > (CustomerUID, AccountUID, CardUID,
> > CardNum,
> > > > CardPIN)
> > > > Values
> > > > (@CustomerUID, @AccountUID, @CardUID,
> > > > @CardNum,
> > > > @CardPIN)
> > > > ----------------------------------
> > > >
> > > >
> > > > Sub Submit2_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(ConfigurationSettings.AppSettings("ConnectionString"))
> > > > objSQLCommand = New
> > > > SqlCommand("sp_InsertMain", objConn)
> > > > 'Set the stored procedure.
> > > > objSQLCommand.CommandType =
> > > > CommandType.StoredProcedure
> > > > 'Add input parameters...
> > > > objParam =
> > > > objSQLCommand.Parameters.Add("@AccountNumber",
> > > > TextBox1.Text)
> > > > objParam =
> > > >
> > >
> >
> objSQLCommand.Parameters.Add("@BillingAddressName",
> > > > TextBox_new.Text)
> > > > objParam =
> > > >
> objSQLCommand.Parameters.Add("@BillingAddress",
> > > > TextBox2.Text)
> > > > objParam =
> > > >
> > >
> >
> objSQLCommand.Parameters.Add("@BillingAddressCity",
> > > > TextBox3.Text)
> > > > objParam =
> > > >
> > >
> >
> objSQLCommand.Parameters.Add("@BillingAddressState",
> > > > DropDownList1.SelectedItem.Text)
> > > > objParam =
> > > >
> > objSQLCommand.Parameters.Add("@BillingAddressZip",
> > > > TextBox4.Text)
> > > > objParam =
> > > > objSQLCommand.Parameters.Add("@CardNum",
> > > > TextBox5.Text)
> > > > objParam =
> > > > objSQLCommand.Parameters.Add("@CardPIN",
> > > > TextBox6.Text)
> > > > '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
> > > > '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()
> > > >
> > > > Panel1.Visible = False
> > > > Panel3.Visible = False
> > > > Panel4.Visible = True
> > > > Label3.Visible = True
> > > > Label3.Text = "<b>" & "The
> following
> > > > data
> > > > has been added." & "<p>" & "<table border=0
> > > > cellpadding=2 cellspacing=5><tr><td>" &
> "Account
> > > > #::</td>" & "<td>" & TextBox1.Text & "</td>" &
> > > > "<tr><td>Billing Address Name:</td>" & "<td>"
> &
> > > > TextBox_new.Text & "</td>" &
> > > "<tr><td>Address:</td>"
> > > > &
> > > > "<td>" & TextBox2.text & "</td><tr><td>" &
> > > > "City:</td><td>" & TextBox3.Text & "</td>" &
> > > > "<tr><td>State:" & "<td>" &
> > > > DropDownList1.SelectedItem.Text & "</td>" &
> > > > "<tr><td>Zip:</td>" & "<td>" & TextBox4.Text &
> > > > "</td>"
> > > > & "</table></b>"
> > > >
> > > > End If
> > > >
> > > > End Sub
> > > >
> > > >
> > > > Thanks!!!
> > > >
> > > > --- Mark E <[EMAIL PROTECTED]> wrote:
> > > >
> > > > > Anna,
> > > > >
> > > > > If I understand correctly, you are calling
> > > > multiple
> > > > > SP's. So it looks as though you need to
> pass
> > > your
> > > > > "@AccountUID" to one of them.
> > > > >
> > > > > Post a high level flow of how you are
> calling
> > > your
> > > > > SP's. Like this:
> > > > >
> > > > > From web page, call sp_InsertMainRecord
> > > > > Within sp_InsertMainRecord, call
> > > > > sp_InsertSecondRecord
> > > > > Within sp_InsertMainRecord, call
> > > > > sp_InsertThirdRecord
> > > > >
> > > > > Mark
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Anna Leon <[EMAIL PROTECTED]> wrote:
> > > > > Do I have to add as an input parameter, the
> > > > primary
> > > > > key in my code? i.e.
> > > > >
> > > > > objParam =
> > > > > objSQLCommand.Parameters.Add("@AccountUID",
> > > > > ????)
> > > > >
> > > > > What do I put where the question marks are?
> > Like
> > > > the
> > > > > other input parameters, I have:
> > > > >
> > > > > objParam =
> > > > >
> objSQLCommand.Parameters.Add("@AccountNumber",
> > > > > TextBox1.Text)
> > > > >
> > > > >
> > > > > And right now I'm getting an error:
> > > > >
> > > > > =====================================
> > > > > Procedure 'sp_InsertMain' expects parameter
> > > > > '@AccountUID', which was not supplied.
> > > > > Description: An unhandled exception occurred
> > > > during
> > > > > the execution of the current web request.
> > Please
> > > > > review the stack trace for more information
> > > about
> > > > > the
> > > > > error and where it originated in the code.
> > > > >
> > > > > Exception Details:
> > > > > System.Data.SqlClient.SqlException:
> > > > > Procedure 'sp_InsertMain' expects parameter
> > > > > '@AccountUID', which was not supplied.
> > > > >
> > > > > Source Error:
> > > > >
> > > > >
> > > > > Line 302: 'Open the db
> > connection
> > > &
> > > > > Execute Command.
> > > > > Line 303: objConn.Open()
> > > > > Line 304:
> > > > > objSQLCommand.ExecuteNonQuery()
> > > > > Line 305: 'Get the return
> > values.
> > > > > Line 306: 'If Not
> > > > >
> > > >
> > >
> >
>
IsDBNull(objSQLCommand.Parameters("@Error_Code").Value)
> > > > > Then
> > > > >
> > > > >
> > > > > Source File: I:\10-16-04\addaccount2.aspx
> > > Line:
> > > > > 304
> > > > >
> > > > >
> > > > > Stack Trace:
> > > > >
> > > > >
> > > > > [SqlException: Procedure 'sp_InsertMain'
> > expects
> > > > > parameter '@AccountUID', which was not
> > > supplied.]
> > > > >
> > > > >
> > > >
> > >
> >
>
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> > > > > cmdBehavior, RunBehavior runBehavior,
> Boolean
> > > > > returnStream) +635
> > > > >
> > > > >
> > >
> System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> > > > > +198
> > > > > ASP.addaccount2_aspx.Submit2_Click(Object
> > > > sender,
> > > > > EventArgs e) in
> > I:\10-16-04\addaccount2.aspx:304
> > > > >
> > > > >
> > >
> System.Web.UI.WebControls.Button.OnClick(EventArgs
> > > > > e) +83
> > > > >
> > > > >
> > > >
> > >
> >
>
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
> > > > > eventArgument) +57
> > > > >
> > > > >
> > > >
> > >
> >
>
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
> > > > > sourceControl, String eventArgument) +18
> > > > >
> > > > >
> > > >
> > >
> >
>
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection
> > > > > postData) +33
> > > > > System.Web.UI.Page.ProcessRequestMain()
> > +1263
> > > > >
> > > > >
> > > > >
> =============================================
> > > > > @AccountUID is declared in my main SP.
> > > > >
> > > > > Thanks!!
> > > > >
> > > > >
> > > > > --- Mark E <[EMAIL PROTECTED]> wrote:
> > > > >
> > > > > > Add this line after the other variable
> > > > > declarations
> > > > > > within the sub:
> > > > > >
> > > > > > Dim intReturnError as Integer
> > > > > >
> > > > > > Mark
> > > > > >
> > > > > > sas0riza <[EMAIL PROTECTED]> wrote:
> > > > > >
> > > > > > I'm getting an error:
> > > > > >
> > > > > > Description: An error occurred during the
> > > > > > compilation of a resource
> > > > > > required to service this request. Please
> > > review
> > > > > the
> > > > > > following
> > > > > > specific error details and modify your
> > source
> > > > code
> > > > > > appropriately.
> > > > > >
> > > > > > Compiler Error Message: BC30451: Name
> > > > > > 'intReturnError' is not
> > > > > > declared.
> > > > > >
> > > > > > Source Error:
> > > > > >
> > > > > >
> > > > > >
> > > > > > Line 304: 'Get the return
> > > values.
> > > > > > Line 305: If Not
> > > > > > IsDBNull(objSQLCommand.Parameters
> > > > > > ("@Error_Code").Value) Then
> > > > > > Line 306:
> intReturnError
> > =
> > > > > >
> > > (objSQLCommand.Parameters("@Error_Code").Value)
> > > > > > Line 307: End If
> > > > > > Line 308: 'Check the error.
> > > > > >
> > > > > >
> > > > > > Source File: I:\10-16-04\addaccount2.aspx
>
> > > > Line:
> > > > > > 306
> > > > > >
> > > > > >
> > > > > >
> > > > > > Show Detailed Compiler Output:
> > > > > >
> > > > > >
> > > > > > I:\10-16-04>
> > > > > >
> "c:\winnt\microsoft.net\framework\v1.0.3705
> > > > > > \vbc.exe" /t:library /utf8output
> > > > > > /R:"c:\winnt\assembly\gac\system.data
> > > > > > \1.0.3300.0__b77a5c561934e089
> > > > > > \system.data.dll"
> > > > > >
> > > > >
> > > >
> > >
> >
> /R:"c:\winnt\assembly\gac\system.web.services\1.0.33
> > > > > >
> > > 00.0__b03f5f7f11d50a3a\system.web.services.dll"
> > > > > > /R:"c:\winnt\assembly\
> > > > > >
> > > > >
> > > >
> > >
> >
>
gac\system.drawing\1.0.3300.0__b03f5f7f11d50a3a\system.drawing.dll"
> > > > > > /R
> > > > > >
> > > > >
> > > >
> > >
> >
>
:"c:\winnt\assembly\gac\system.enterpriseservices\1.0.3300.0__b03f5f7f
> > > > > > 11d50a3a\system.enterpriseservices.dll"
> > > > > > /R:"c:\winnt\assembly\gac\syst
> > > > > >
> > > > >
> > > >
> > >
> >
> em.web\1.0.3300.0__b03f5f7f11d50a3a\system.web.dll"
> > > > > > /R:"c:\winnt\assem
> > > > > >
> > > bly\gac\system.xml\1.0.3300.0__b77a5c561934e089
> > > > > > \system.xml.dll"
> > > > > >
> > > > >
> > > >
> > >
> >
>
/R:"c:\winnt\assembly\gac\system\1.0.3300.0__b77a5c56
> > > > > > 1934e089\system.dll"
> > > > > >
> > > /out:"C:\WINNT\Microsoft.NET\Framework\v1.0.3705
> > > > > > \Temporary ASP.NET
> > > > > > Files\root\b3a0069f\92c21b1b\wnih9oqm.dll"
> > > > > > /D:DEBUG=1 /debug+ /win32re
> > > > > >
> > > > >
> > > >
> > >
> >
>
source:"C:\WINNT\Microsoft.NET\Framework\v1.0.3705\Temporary
> > > > > > ASP.NET
> > > > > > Files\root\b3a0069f\92c21b1b\wnih9oqm.res"
>
> > > > > > "C:\WINNT\Microsoft.NET\Fr
> > > > > > amework\v1.0.3705\Temporary ASP.NET
> > > > > >
> Files\root\b3a0069f\92c21b1b\wnih9oqm.0.vb"
> > > > > >
> > > > > >
> > > > > > Microsoft (R) Visual Basic .NET Compiler
> > > version
> > > > > > 7.00.9466
> > > > > > for Microsoft (R) .NET Framework version
> > > > > > 1.00.3705.288
> > > > > > Copyright (C) Microsoft Corporation
> > 1987-2001.
> > > > All
> > > > > > rights reserved.
> > > > > >
> > > > > > I:\10-16-04\addaccount2.aspx(306) : error
> > > > BC30451:
> > > > >
> > > > > > Name 'intReturnError' is not declared.
> > > > > >
> > > > > > intReturnError =
> > > > > > (objSQLCommand.Parameters
> > > > > > ("@Error_Code").Value)
> > > > > >
> > > > > > ~~~~~~~~~~~~~~
>
> >
> > >
> > > >
> > > > >
> > > > > >
> > > > > > I:\10-16-04\addaccount2.aspx(309) : error
> > > > BC30451:
> > > > >
> > > > > > Name 'intReturnError' is not declared.
> > > > > >
> > > > > > If intReturnError = 0 Then
> > > > > > ~~~~~~~~~~~~~~
> > > > > >
> > > > > >
> > > > > > ??
> > > > > >
> > > > > >
> > > > > >
> > > > > > --- In
> > [EMAIL PROTECTED],
> > > > Mark
> > > > > E
> > > > > >
> > > > > > <[EMAIL PROTECTED]> wrote:
> > > > > > > @Some_Param would represent a name of an
> > > input
> > > > > > parameter (value)
> > > > > > being passed into your proc.
> > > > > > >
> > > > > > > SomeValue would be the name of a
> variable
> > > that
> > > > > > holds your value for
> > > > > > @Some_Param. Or you could use
> control.value
> > > or
> > > > > > control.text.
> > > > > > >
> > > > > > > Mark
> > > > > > >
> > > > > > > Anna Leon <[EMAIL PROTECTED]> wrote:
> > > > > > > 'Add input parameters...
> > > > > > > objParam =
> > > > > > >
> > objSQLCommand.Parameters.Add("@Some_Param",
> > > > > > SomeValue)
> > > > > > > 'Add our ouput parameters...
> > > > > > > objParam =
> > > > > > >
> > objSQLCommand.Parameters.Add("@Error_Code",
> > > > > > > SqlDbType.Int)
> > > > > > >
> > > > > > >
> > > > > > > @Some_Param is what I'm inserting into
> the
> > > > > tables?
> > > > > > > SomeValue is my textbox ids??
> > > > > > >
> > > > > > >
> > > > > > > --- Mark E <[EMAIL PROTECTED]> wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > Here is a sample of what you can do.
> > Just
> > > > > note
> > > > > > that
> > > > > > > > it was typed directly in to the email
> > and
> > > > > needs
> > > > > > > > testing.
> > > > > > > >
> > > > > > > > My suggestion is to call your 2nd or
> 3rd
> > > > proc
> > > > > > from
> > > > > > > > the first one or make it one proc. I
> > > prefer
> > > > 1
> > > > > > proc,
> > > > > > > > unless I see some value in seperating
> > > them,
> > > > > like
> > > > > > > > reusing them.
> > > > > > > >
> > > > > > > > Anyhow, here is a button:
> > > > > > > >
> > > > > > > > <asp:Button ID="btnSubmit"
> Text="Become
> > a
> > > > > > Member"
> > > > > > > > CssClass="LargeButton"
> > > > > > Runat="server"></asp:Button>
> > > > > > > >
> > > > > > > > Don't forget your imports in your
> page:
> > > > > > > >
> > > > > > > > Imports System.Data
> > > > > > > > Imports System.Data.SqlClient
> > > > > > > >
> > > > > > > > And here is some code. Just replace
> the
> > > > > values
> > > > > > for
> > > > > > > > the proc name & params to whatever you
> > > need:
> > > > > > > >
> > > > > > > > Private Sub btnSubmit_Click(ByVal
> sender
> > > As
> > > > > > > > System.Object, ByVal e As
> > > System.EventArgs)
> > > > > > Handles
> > > > > > > > btnSubmit.Click
> > > > > > > > Dim objConn As SqlConnection
> > > > > > > > Dim objSQLCommand As SqlCommand
> > > > > > > > Dim objParam As
> SqlParameter
> > > > > > > > Dim boolInsertOK As
> Boolean
> > =
> > > > > False
> > > > > > > > 'Set up our connection
> > object
> > > > and
> > > > > > SQL
> > > > > > > > command object.
> > > > > > > > objConn = New
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
> > > > > > > > objSQLCommand = New
> > > > > > > > SqlCommand("Name_Of_Your_PROC",
> objConn)
> > > > > > > > 'Set the stored procedure.
> > > > > > > > objSQLCommand.CommandType
> =
> > > > > > > > CommandType.StoredProcedure
> > > > > > > > 'Add input parameters...
> > > > > > > > objParam =
> > > > > > > >
> > > objSQLCommand.Parameters.Add("@Some_Param",
> > > > > > > > SomeValue)
> > > > > > > > '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
> > > > > > > > '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()
> > > > > > > > '***** Do something here
> > based
> > > > on
> > > > > > > > success or failure of the insert
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > And watch for wrapping.
> > > > > > > >
> > > > > > > > Mark
> > > > > > > >
> > > > > > > > sas0riza <[EMAIL PROTECTED]> wrote:
> > > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I have 3 stored procedures for
> inserting
> > > > data
> > > > > > into
> > > > > > > > multiple tables.
> > > > > > > > Mainly 2 tables get the inserted data.
> > The
> > > > 2nd
> > > > > > table
> > > > > > > > requires 2
> > > > > > > > foreign keys added. One foreign key is
> > > from
> > > > > the
> > > > > > 1st
> > > > > > > > table and the 2nd
> > > > > > > > foreign key is from another table
> (which
> > > I'm
> > > > > not
> > > > > > > > doing anything to
> > > > > > > > other than retrieving the primary key)
> > to
> > > be
> > > > > > > > inserted into the 2nd
> > > > > > > > table.
> > > > > > > >
> > > > > > > > How do I call the SP(s) in my code?
> > Note,
> > > I
> > > > > > would
> > > > > > > > like to call the SP
> > > > > > > > (s) in my button_click sub routine.
> > > > > > > >
> > > > > > > > Thank you.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Yahoo! Groups SponsorADVERTISEMENT
> > > > > > > >
> > > > > > > >
> > > > > > > > ---------------------------------
> > > > > > > > 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
> > > > the
> > > > > > > > Yahoo! Terms of Service.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > [Non-text portions of this message
> have
> > > been
> > > > > > > > removed]
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > _______________________________
> > > > > > > Do you Yahoo!?
> > > > > > > Declare Yourself - Register online to
> vote
> > > > > today!
> > > > > > > http://vote.yahoo.com
> > > > > > >
> > > > > > > Yahoo! Groups SponsorADVERTISEMENT
> > > > > > >
> > > > > > >
> > > > > > > ---------------------------------
> > > > > > > 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
> > > the
> > > > > > Yahoo! Terms of
> > > > > > Service.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > [Non-text portions of this message have
> > been
> > > > > > removed]
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > Yahoo! Groups SponsorADVERTISEMENT
> > > > > >
> > > > > >
> > > > > > ---------------------------------
> > > > > > 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
> > the
> > > > > > Yahoo! Terms of Service.
> > > > > >
> > > > > >
> > > > > >
> > > > > > [Non-text portions of this message have
> been
> > > > > > removed]
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > __________________________________
> > > > > Do you Yahoo!?
> > > > > Take Yahoo! Mail with you! Get it on your
> > mobile
> > > > > phone.
> > > > > http://mobile.yahoo.com/maildemo
> > > > >
> > > > > Yahoo! Groups SponsorADVERTISEMENT
> > > > >
> > > > >
> > > > > ---------------------------------
> > > > > 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
> the
> > > > > Yahoo! Terms of Service.
> > > > >
> > > > >
> > > > >
> > > > > [Non-text portions of this message have been
> > > > > removed]
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > __________________________________________________
> > > > Do You Yahoo!?
> > > > Tired of spam? Yahoo! Mail has the best spam
> > > > protection around
> > > > http://mail.yahoo.com
> > > >
> > > > Yahoo! Groups SponsorADVERTISEMENT
> > > >
> > > >
> > > > ---------------------------------
> > > > 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 the
> > > > Yahoo! Terms of Service.
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been
> > > > removed]
> > > >
> > > >
> > >
> > >
> > >
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > Yahoo! Mail Address AutoComplete - You start. We
> > > finish.
> > > http://promotions.yahoo.com/new_mail
> > >
> > > Yahoo! Groups SponsorADVERTISEMENT
> > >
> > >
> > > ---------------------------------
> > > 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 the
> > > Yahoo! Terms of Service.
> > >
> > >
> > >
> > > [Non-text portions of this message have been
> > > removed]
> > >
> > >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam
> > protection around
> > http://mail.yahoo.com
> >
> > Yahoo! Groups SponsorADVERTISEMENT
> >
> >
> > ---------------------------------
> > 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 the
> > Yahoo! Terms of Service.
> >
> >
> >
> > [Non-text portions of this message have been
> > removed]
> >
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com
> ----------
>
> Sub Submit2_Click(sender As Object, e As EventArgs)
>
> If Page.IsValid then
>
> 'INSERT Account Info and get PKID
> 'AddAccount(TextBox1.Text,
> TextBox_new.Text, TextBox2.Text, TextBox3.Text,
> DropDownList1.SelectedItem.Text, TextBox4.Text)
>
>
> 'Search and retrieve PKID from Customer
> Table
> 'Dim PKID as String
> 'PKID = GetCustomerUID(TextBox7.Text)
>
>
> 'INSERT Card#/PIN info plus foreign keys
> from "CallingCardAccount"
> ' and "Customer" tables.
>
>
>
> '===================================================
> 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(ConfigurationSettings.AppSettings("ConnectionString"))
> objSQLCommand = New
> SqlCommand("sp_InsertAllRecords", objConn)
> 'Set the stored procedure.
> objSQLCommand.CommandType =
> CommandType.StoredProcedure
> 'Add input parameters...
> objParam =
> objSQLCommand.Parameters.Add("@AccountNumber",
> TextBox1.Text)
> objParam =
> objSQLCommand.Parameters.Add("@BillingAddressName",
> TextBox_new.Text)
> objParam =
> objSQLCommand.Parameters.Add("@BillingAddress",
> TextBox2.Text)
> objParam =
> objSQLCommand.Parameters.Add("@BillingAddressCity",
> TextBox3.Text)
> objParam =
> objSQLCommand.Parameters.Add("@BillingAddressState",
> DropDownList1.SelectedItem.Text)
> objParam =
> objSQLCommand.Parameters.Add("@BillingAddressZip",
> TextBox4.Text)
> objParam =
> objSQLCommand.Parameters.Add("@CardNum",
> TextBox5.Text)
> objParam =
> objSQLCommand.Parameters.Add("@CardPIN",
> TextBox6.Text)
> '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
> '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()
>
>
'========================================================
>
>
>
>
>
>
>
> Panel1.Visible = False
> Panel3.Visible = False
> Panel4.Visible = True
> Label3.Visible = True
> Label3.Text = "<b>" & "The following
> data has been added." & "<p>" & "<table border=0
> cellpadding=2 cellspacing=5><tr><td>" & "Account
> #::</td>" & "<td>" & TextBox1.Text & "</td>" &
> "<tr><td>Billing Address Name:</td>" & "<td>" &
> TextBox_new.Text & "</td>" & "<tr><td>Address:</td>"
> & "<td>" & TextBox2.text & "</td><tr><td>" &
> "City:</td><td>" & TextBox3.Text & "</td>" &
> "<tr><td>State:" & "<td>" &
> DropDownList1.SelectedItem.Text & "</td>" &
> "<tr><td>Zip:</td>" & "<td>" & TextBox4.Text &
> "</td>" & "</table></b>"
>
> End If
>
> End Sub
>
> ----------
>
> CREATE procedure sp_InsertAllRecords
> @AccountNumber varchar(50),
> @BillingAddressName varchar(100),
> @BillingAddress varchar(100),
> @BillingAddressCity varchar(50),
> @BillingAddressState char(2),
> @BillingAddressZip varchar(20),
> @CustomerUID int,
> @CardUID int,
> @CardNum varchar(50),
> @CardPIN varchar(50),
> @Error_Code int output
> as
> declare
> @SQLErrorCode int,
> @WhateverID int,
> @PK1 int
>
> begin tran InsertAllRecords
> --
>
************************************************************
> -- *** Step 1 - Insert the first record ***
> --
>
************************************************************
> Insert CallingCardAccount
> (AccountNumber,BillingAddressName, BillingAddress,
> BillingAddressCity, BillingAddressState,
> BillingAddressZip)
> Values (@AccountNumber, @BillingAddressName,
> @BillingAddress, @BillingAddressCity,
> @BillingAddressState, @BillingAddressZip)
> -- Now check for any errors.
> select @SQLErrorCode = @@Error
> -- Get the PK for this new record.
> select @PK1 = @@identity
> -- If we have an error, set the variable and return
> it and rollback the transaction.
> if @SQLErrorCode <> 0
> begin
> rollback tran InsertAllRecords
> 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, AccountUID,
> CardUID, CardNum, CardPIN)
> Values (@CustomerUID, @AccountUID, @CardUID,
> @CardNum, @CardPIN)
> -- Now check for any errors.
> select @SQLErrorCode = @@Error
> -- If we have an error, set the variable and return
> it and rollback the transaction.
> if @SQLErrorCode <> 0
> begin
> rollback tran InsertAllRecords
> set @Error_Code = 2
> return
> end
> -- If we made it this far, then the transaction was
> successful.
> commit tran InsertAllRecords
> set @Error_Code = 0
> return
>
>
> [Non-text portions of this message have been
> removed]
>
>
> Yahoo! Groups SponsorADVERTISEMENT
>
>
> ---------------------------------
> 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 the
> Yahoo! Terms of Service.
>
>
>
> [Non-text portions of this message have been
> removed]
>
>
__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail
------------------------ Yahoo! Groups Sponsor --------------------~-->
Make a clean sweep of pop-up ads. Yahoo! Companion Toolbar.
Now with Pop-Up Blocker. Get it for free!
http://us.click.yahoo.com/L5YrjA/eSIIAA/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/
