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 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/
