call store procedure for inserting data in asp

Discussion in 'ASP General' started by c676228, Oct 6, 2006.

  1. c676228

    c676228 Guest

    Hi everyone,
    I am just wodering in asp program, if there is anybody writing store
    procedure for inserting data into database since there are so many parameters
    need to be passed into store procedure(assume there are many columns in the
    table).
    I need to insert data into two separate tables, the relation between these
    two tables is 1 row of data in table1 could have multiple rows in table2
    related to table1, but if the data insertion into any one of the tables is
    failed, the transaction will roll back and no data will be remained in any of
    the tables.

    If this needs to be handled in asp program using transact sql, how do I
    implement it. If I need to it using store procedure, how do I pass the error
    or success signal to asp program from database.
    Thank you.

    --
    Betty
     
    c676228, Oct 6, 2006
    #1
    1. Advertising

  2. Hello Betty,

    I think there are two approach for this issue:

    1. Perform the transaction with ADO connection's transaction, for example:

    Dim cn As ADODB.Connection


    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
    "Initial Catalog=Northwind;User ID=<user>;Password=<password>"

    cn.BeginTrans

    'Put your insert records code here

    cn.CommitTrans

    cn.Close

    In this way, you can get the exception in ASP code if there is any error.

    2. Perform the transaction in a SQL store procedure, and return any error
    as OUTPUT parameter from the SP. Here are an article may be useful on this
    issue:

    Managing Transactions in SQL Server Stored Procedures
    http://www.4guysfromrolla.com/webtech/080305-1.shtml

    If you are working with SQL Server 2005, you even can use Try...Catch
    statement:

    Using TRY...CATCH in Transact-SQL
    http://msdn2.microsoft.com/en-us/library/ms179296.aspx
    http://www.codeproject.com/useritems/try_catch.asp

    Hope this help. If you want any further information, please feel free to
    let us know.

    Sincerely,

    Luke Zhang

    Microsoft Online Community Support
    ==================================================
    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
    ications.

    Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
    where an initial response from the community or a Microsoft Support
    Engineer within 1 business day is acceptable. Please note that each follow
    up response may take approximately 2 business days as the support
    professional working with you may need further investigation to reach the
    most efficient resolution. The offering is not appropriate for situations
    that require urgent, real-time or phone-based interactions or complex
    project analysis and dump analysis issues. Issues of this nature are best
    handled working with a dedicated Microsoft Support Engineer by contacting
    Microsoft Customer Support Services (CSS) at
    http://msdn.microsoft.com/subscriptions/support/default.aspx.
    ==================================================

    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Luke Zhang [MSFT], Oct 6, 2006
    #2
    1. Advertising

  3. "c676228" <> wrote in message
    news:...
    > Hi everyone,
    > I am just wodering in asp program, if there is anybody writing store
    > procedure for inserting data into database since there are so many

    parameters
    > need to be passed into store procedure(assume there are many columns in

    the
    > table).
    > I need to insert data into two separate tables, the relation between these
    > two tables is 1 row of data in table1 could have multiple rows in table2
    > related to table1, but if the data insertion into any one of the tables

    is
    > failed, the transaction will roll back and no data will be remained in any

    of
    > the tables.
    >
    > If this needs to be handled in asp program using transact sql, how do I
    > implement it. If I need to it using store procedure, how do I pass the

    error
    > or success signal to asp program from database.
    > Thank you.
    >
    > --
    > Betty


    In this scenario I pass XML into the stored procedure then use typical T-SQL
    to manage the transaction and OPENXML to access the input. This avoids
    creating long list of parameters and is a very flexible solution. (Eg. I
    can add a new field to the XML generated on the client and modify the SP but
    the ASP in between doesn't need to know anything about it)
     
    Anthony Jones, Oct 6, 2006
    #3
  4. c676228

    c676228 Guest

    Luke and Anthony,
    Thanks the information you provided. It's very helpful.
    I am wondering in asp.net we can use sqlTransaction object in Try ... Catch
    block easily.
    My concern is in classic asp, do we have similar way in doing that.
    --
    Betty


    "Luke Zhang [MSFT]" wrote:

    > Hello Betty,
    >
    > I think there are two approach for this issue:
    >
    > 1. Perform the transaction with ADO connection's transaction, for example:
    >
    > Dim cn As ADODB.Connection
    >
    >
    > Set cn = New ADODB.Connection
    > cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
    > "Initial Catalog=Northwind;User ID=<user>;Password=<password>"
    >
    > cn.BeginTrans
    >
    > 'Put your insert records code here
    >
    > cn.CommitTrans
    >
    > cn.Close
    >
    > In this way, you can get the exception in ASP code if there is any error.
    >
    > 2. Perform the transaction in a SQL store procedure, and return any error
    > as OUTPUT parameter from the SP. Here are an article may be useful on this
    > issue:
    >
    > Managing Transactions in SQL Server Stored Procedures
    > http://www.4guysfromrolla.com/webtech/080305-1.shtml
    >
    > If you are working with SQL Server 2005, you even can use Try...Catch
    > statement:
    >
    > Using TRY...CATCH in Transact-SQL
    > http://msdn2.microsoft.com/en-us/library/ms179296.aspx
    > http://www.codeproject.com/useritems/try_catch.asp
    >
    > Hope this help. If you want any further information, please feel free to
    > let us know.
    >
    > Sincerely,
    >
    > Luke Zhang
    >
    > Microsoft Online Community Support
    > ==================================================
    > Get notification to my posts through email? Please refer to
    > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
    > ications.
    >
    > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
    > where an initial response from the community or a Microsoft Support
    > Engineer within 1 business day is acceptable. Please note that each follow
    > up response may take approximately 2 business days as the support
    > professional working with you may need further investigation to reach the
    > most efficient resolution. The offering is not appropriate for situations
    > that require urgent, real-time or phone-based interactions or complex
    > project analysis and dump analysis issues. Issues of this nature are best
    > handled working with a dedicated Microsoft Support Engineer by contacting
    > Microsoft Customer Support Services (CSS) at
    > http://msdn.microsoft.com/subscriptions/support/default.aspx.
    > ==================================================
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
    >
    >
     
    c676228, Oct 6, 2006
    #4
  5. With VB Script, we don't have similar thing to try..catch. But we can check
    the Err object after some operation and use IF...ELSE to do similar thing
    like try...catch. for example:

    On Error Resume Next

    ' some operation here

    if Err.Number>0 then

    else

    endif

    Sincerely,

    Luke Zhang

    Microsoft Online Community Support
    ==================================================
    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
    ications.

    Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
    where an initial response from the community or a Microsoft Support
    Engineer within 1 business day is acceptable. Please note that each follow
    up response may take approximately 2 business days as the support
    professional working with you may need further investigation to reach the
    most efficient resolution. The offering is not appropriate for situations
    that require urgent, real-time or phone-based interactions or complex
    project analysis and dump analysis issues. Issues of this nature are best
    handled working with a dedicated Microsoft Support Engineer by contacting
    Microsoft Customer Support Services (CSS) at
    http://msdn.microsoft.com/subscriptions/support/default.aspx.
    ==================================================

    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Luke Zhang [MSFT], Oct 9, 2006
    #5
  6. c676228

    c676228 Guest

    Anthony,
    After I searched a little bit, it seems like it is a good solution, but I
    never did this before, can you provide some examples and resouces for this
    method?
    Thank you.
    --
    Betty


    "Anthony Jones" wrote:

    >
    > "c676228" <> wrote in message
    > news:...
    > > Hi everyone,
    > > I am just wodering in asp program, if there is anybody writing store
    > > procedure for inserting data into database since there are so many

    > parameters
    > > need to be passed into store procedure(assume there are many columns in

    > the
    > > table).
    > > I need to insert data into two separate tables, the relation between these
    > > two tables is 1 row of data in table1 could have multiple rows in table2
    > > related to table1, but if the data insertion into any one of the tables

    > is
    > > failed, the transaction will roll back and no data will be remained in any

    > of
    > > the tables.
    > >
    > > If this needs to be handled in asp program using transact sql, how do I
    > > implement it. If I need to it using store procedure, how do I pass the

    > error
    > > or success signal to asp program from database.
    > > Thank you.
    > >
    > > --
    > > Betty

    >
    > In this scenario I pass XML into the stored procedure then use typical T-SQL
    > to manage the transaction and OPENXML to access the input. This avoids
    > creating long list of parameters and is a very flexible solution. (Eg. I
    > can add a new field to the XML generated on the client and modify the SP but
    > the ASP in between doesn't need to know anything about it)
    >
    >
    >
    >
     
    c676228, Oct 16, 2006
    #6
  7. c676228

    Firas Guest

    If i were you, i would write this procedure my self, it will make me
    practice coding and in the future, when you require advance stuff you
    wont have to ask the coder or dont have to study the code to do some
    changes.

    Take my advice, try to do the best on your own, and ask for help if you
    get stuck, not ask for it to be done.


    Best Regards
    Firas S Assaad
    c676228 wrote:
    > Anthony,
    > After I searched a little bit, it seems like it is a good solution, but I
    > never did this before, can you provide some examples and resouces for this
    > method?
    > Thank you.
    > --
    > Betty
    >
    >
    > "Anthony Jones" wrote:
    >
    > >
    > > "c676228" <> wrote in message
    > > news:...
    > > > Hi everyone,
    > > > I am just wodering in asp program, if there is anybody writing store
    > > > procedure for inserting data into database since there are so many

    > > parameters
    > > > need to be passed into store procedure(assume there are many columns in

    > > the
    > > > table).
    > > > I need to insert data into two separate tables, the relation between these
    > > > two tables is 1 row of data in table1 could have multiple rows in table2
    > > > related to table1, but if the data insertion into any one of the tables

    > > is
    > > > failed, the transaction will roll back and no data will be remained in any

    > > of
    > > > the tables.
    > > >
    > > > If this needs to be handled in asp program using transact sql, how do I
    > > > implement it. If I need to it using store procedure, how do I pass the

    > > error
    > > > or success signal to asp program from database.
    > > > Thank you.
    > > >
    > > > --
    > > > Betty

    > >
    > > In this scenario I pass XML into the stored procedure then use typical T-SQL
    > > to manage the transaction and OPENXML to access the input. This avoids
    > > creating long list of parameters and is a very flexible solution. (Eg. I
    > > can add a new field to the XML generated on the client and modify the SP but
    > > the ASP in between doesn't need to know anything about it)
    > >
    > >
    > >
    > >
     
    Firas, Oct 17, 2006
    #7
  8. "c676228" <> wrote in message
    news:...
    > Anthony,
    > After I searched a little bit, it seems like it is a good solution, but I
    > never did this before, can you provide some examples and resouces for this
    > method?
    > Thank you.


    Create a test DB in sql server then using query analyser execute the
    following against it:-

    CREATE TABLE Master
    (Master_ID int not null CONSTRAINT PK_Master PRIMARY KEY,
    [Name] varchar(50) not null)

    CREATE TABLE Child
    (Master_ID int not null,
    Child_ID int not null,
    [Name] varchar(50) not null,
    SomeBoolean bit null,
    CONSTRAINT PK_CHILD PRIMARY KEY (Master_ID, Child_ID),
    CONSTRAINT FK_MASTER_CHILD FOREIGN KEY (Master_ID)
    REFERENCES Master (Master_ID)
    )
    GO

    CREATE PROCEDURE sp_UpdateMasterChild
    @xml ntext
    AS

    SET NOCOUNT ON

    DECLARE @hdoc int

    EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

    BEGIN TRANSACTION

    INSERT Master (Master_ID, [Name])
    SELECT [ID], [name]
    FROM OPENXML(@hdoc, '/root/master', 2)
    WITH ([ID] int '@ID', [name] varchar(50) 'name')

    INSERT Child (Master_ID, Child_ID, [Name], SomeBoolean)
    SELECT masterID, [ID], [name], someBoolean
    FROM OPENXML (@hdoc, '/root/master/child', 2)
    WITH ([masterID] int '../@ID', [ID] int '@ID',
    [name] varchar(50) 'name',
    someBoolean int '@someBoolean')

    COMMIT TRANSACTION

    EXEC sp_xml_removedocument @hdoc
    GO

    DECLARE @xml nvarchar(4000)
    SET @xml = N'<root>
    <master ID="1">
    <name>First Master</name>
    <child ID="1" someBoolean="-1">
    <name>First Child of First</name>
    </child>
    <child ID="2" someBoolean="0">
    <name>Second Child of First</name>
    </child>
    <child ID="3">
    <name>Third Child of First</name>
    </child>
    </master>
    <master ID="2">
    <name>Second Master</name>
    <child ID="1" someBoolean="-1">
    <name>First Child of Second</name>
    </child>
    <child ID="2">
    <name>Second Child of Second</name>
    </child>
    </master>


    </root>'

    EXEC sp_UpdateMasterChild @xml


    Any questions?


    > --
    > Betty
    >
    >
    > "Anthony Jones" wrote:
    >
    > >
    > > "c676228" <> wrote in message
    > > news:...
    > > > Hi everyone,
    > > > I am just wodering in asp program, if there is anybody writing store
    > > > procedure for inserting data into database since there are so many

    > > parameters
    > > > need to be passed into store procedure(assume there are many columns

    in
    > > the
    > > > table).
    > > > I need to insert data into two separate tables, the relation between

    these
    > > > two tables is 1 row of data in table1 could have multiple rows in

    table2
    > > > related to table1, but if the data insertion into any one of the

    tables
    > > is
    > > > failed, the transaction will roll back and no data will be remained in

    any
    > > of
    > > > the tables.
    > > >
    > > > If this needs to be handled in asp program using transact sql, how do

    I
    > > > implement it. If I need to it using store procedure, how do I pass the

    > > error
    > > > or success signal to asp program from database.
    > > > Thank you.
    > > >
    > > > --
    > > > Betty

    > >
    > > In this scenario I pass XML into the stored procedure then use typical

    T-SQL
    > > to manage the transaction and OPENXML to access the input. This avoids
    > > creating long list of parameters and is a very flexible solution. (Eg.

    I
    > > can add a new field to the XML generated on the client and modify the SP

    but
    > > the ASP in between doesn't need to know anything about it)
    > >
    > >
    > >
    > >
     
    Anthony Jones, Oct 17, 2006
    #8
  9. c676228

    c676228 Guest

    Thank you, Anthony, for your so detailed anwser. I also checked the sql
    server book on-line. I do have some questions:
    first of all, people use xml data format to pass to database to reduce the
    numer of store procedure call, but with openxml, it seems that the max length
    of the string is 4000, which basically means only smal xml file can be
    handled, did I understand correctly?
    Another thing is you used:
    INSERT Master (Master_ID, [Name])
    SELECT [ID], [name]
    FROM OPENXML(@hdoc, '/root/master', 2)
    WITH ([ID] int '@ID', [name] varchar(50) 'name')

    Is '@ID' is xpath representation of the attribute and name is the node,
    Is it Ok if I use the following:
    INSERT Master (Master_ID, [Name])
    SELECT [ID], [name]
    FROM OPENXML(@hdoc, '/root/master', 2)
    WITH ([Master_ID int, [name] varchar(50) )
    --

    I will try your sample
    Betty


    "Anthony Jones" wrote:

    >
    > "c676228" <> wrote in message
    > news:...
    > > Anthony,
    > > After I searched a little bit, it seems like it is a good solution, but I
    > > never did this before, can you provide some examples and resouces for this
    > > method?
    > > Thank you.

    >
    > Create a test DB in sql server then using query analyser execute the
    > following against it:-
    >
    > CREATE TABLE Master
    > (Master_ID int not null CONSTRAINT PK_Master PRIMARY KEY,
    > [Name] varchar(50) not null)
    >
    > CREATE TABLE Child
    > (Master_ID int not null,
    > Child_ID int not null,
    > [Name] varchar(50) not null,
    > SomeBoolean bit null,
    > CONSTRAINT PK_CHILD PRIMARY KEY (Master_ID, Child_ID),
    > CONSTRAINT FK_MASTER_CHILD FOREIGN KEY (Master_ID)
    > REFERENCES Master (Master_ID)
    > )
    > GO
    >
    > CREATE PROCEDURE sp_UpdateMasterChild
    > @xml ntext
    > AS
    >
    > SET NOCOUNT ON
    >
    > DECLARE @hdoc int
    >
    > EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
    >
    > BEGIN TRANSACTION
    >
    > INSERT Master (Master_ID, [Name])
    > SELECT [ID], [name]
    > FROM OPENXML(@hdoc, '/root/master', 2)
    > WITH ([ID] int '@ID', [name] varchar(50) 'name')
    >
    > INSERT Child (Master_ID, Child_ID, [Name], SomeBoolean)
    > SELECT masterID, [ID], [name], someBoolean
    > FROM OPENXML (@hdoc, '/root/master/child', 2)
    > WITH ([masterID] int '../@ID', [ID] int '@ID',
    > [name] varchar(50) 'name',
    > someBoolean int '@someBoolean')
    >
    > COMMIT TRANSACTION
    >
    > EXEC sp_xml_removedocument @hdoc
    > GO
    >
    > DECLARE @xml nvarchar(4000)
    > SET @xml = N'<root>
    > <master ID="1">
    > <name>First Master</name>
    > <child ID="1" someBoolean="-1">
    > <name>First Child of First</name>
    > </child>
    > <child ID="2" someBoolean="0">
    > <name>Second Child of First</name>
    > </child>
    > <child ID="3">
    > <name>Third Child of First</name>
    > </child>
    > </master>
    > <master ID="2">
    > <name>Second Master</name>
    > <child ID="1" someBoolean="-1">
    > <name>First Child of Second</name>
    > </child>
    > <child ID="2">
    > <name>Second Child of Second</name>
    > </child>
    > </master>
    >
    >
    > </root>'
    >
    > EXEC sp_UpdateMasterChild @xml
    >
    >
    > Any questions?
    >
    >
    > > --
    > > Betty
    > >
    > >
    > > "Anthony Jones" wrote:
    > >
    > > >
    > > > "c676228" <> wrote in message
    > > > news:...
    > > > > Hi everyone,
    > > > > I am just wodering in asp program, if there is anybody writing store
    > > > > procedure for inserting data into database since there are so many
    > > > parameters
    > > > > need to be passed into store procedure(assume there are many columns

    > in
    > > > the
    > > > > table).
    > > > > I need to insert data into two separate tables, the relation between

    > these
    > > > > two tables is 1 row of data in table1 could have multiple rows in

    > table2
    > > > > related to table1, but if the data insertion into any one of the

    > tables
    > > > is
    > > > > failed, the transaction will roll back and no data will be remained in

    > any
    > > > of
    > > > > the tables.
    > > > >
    > > > > If this needs to be handled in asp program using transact sql, how do

    > I
    > > > > implement it. If I need to it using store procedure, how do I pass the
    > > > error
    > > > > or success signal to asp program from database.
    > > > > Thank you.
    > > > >
    > > > > --
    > > > > Betty
    > > >
    > > > In this scenario I pass XML into the stored procedure then use typical

    > T-SQL
    > > > to manage the transaction and OPENXML to access the input. This avoids
    > > > creating long list of parameters and is a very flexible solution. (Eg.

    > I
    > > > can add a new field to the XML generated on the client and modify the SP

    > but
    > > > the ASP in between doesn't need to know anything about it)
    > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
    c676228, Oct 18, 2006
    #9
  10. "c676228" <> wrote in message
    news:...
    > Thank you, Anthony, for your so detailed anwser. I also checked the sql
    > server book on-line. I do have some questions:
    > first of all, people use xml data format to pass to database to reduce the
    > numer of store procedure call, but with openxml, it seems that the max

    length
    > of the string is 4000, which basically means only smal xml file can be
    > handled, did I understand correctly?


    No. I only used Nvarchar in the example calling code because in T-SQL you
    can't declare a variable as NText. However you can define an SP parameter
    as NText so in the real world you would pass a string from ASP to a
    parameter on an ADO command object that has the ado type adLongNVarChar. So
    there is no theoretical limit on the size of XML.

    > Another thing is you used:
    > INSERT Master (Master_ID, [Name])
    > SELECT [ID], [name]
    > FROM OPENXML(@hdoc, '/root/master', 2)
    > WITH ([ID] int '@ID', [name] varchar(50) 'name')
    >
    > Is '@ID' is xpath representation of the attribute and name is the node,
    > Is it Ok if I use the following:
    > INSERT Master (Master_ID, [Name])
    > SELECT [ID], [name]
    > FROM OPENXML(@hdoc, '/root/master', 2)
    > WITH ([Master_ID int, [name] varchar(50) )


    It is possible to skip the element path definitions in the With clause by
    using a field name that matches the node required from the XML and by
    varying the value of the third parameter of the OPENXML call. The books
    online docs describes this. However I would strongly recommend you stick
    with mode 2 and use explicit path definitions. Also note that in SQL server
    2000 the underlying XML library used is msxml 2.6 so the path language is in
    fact 'XSLPattern' which is a shame but in since these tend to be very simple
    paths not normally a problem.

    One other thing to be aware of is since OPENXML was a new feature in SQL
    2000 early implementations of it were a bit buggy. Ensure you have at least
    SP3 of SQL 2000 or are using 2005.

    > --
    >
    > I will try your sample
    > Betty
    >
    >
    > "Anthony Jones" wrote:
    >
    > >
    > > "c676228" <> wrote in message
    > > news:...
    > > > Anthony,
    > > > After I searched a little bit, it seems like it is a good solution,

    but I
    > > > never did this before, can you provide some examples and resouces for

    this
    > > > method?
    > > > Thank you.

    > >
    > > Create a test DB in sql server then using query analyser execute the
    > > following against it:-
    > >
    > > CREATE TABLE Master
    > > (Master_ID int not null CONSTRAINT PK_Master PRIMARY KEY,
    > > [Name] varchar(50) not null)
    > >
    > > CREATE TABLE Child
    > > (Master_ID int not null,
    > > Child_ID int not null,
    > > [Name] varchar(50) not null,
    > > SomeBoolean bit null,
    > > CONSTRAINT PK_CHILD PRIMARY KEY (Master_ID, Child_ID),
    > > CONSTRAINT FK_MASTER_CHILD FOREIGN KEY (Master_ID)
    > > REFERENCES Master (Master_ID)
    > > )
    > > GO
    > >
    > > CREATE PROCEDURE sp_UpdateMasterChild
    > > @xml ntext
    > > AS
    > >
    > > SET NOCOUNT ON
    > >
    > > DECLARE @hdoc int
    > >
    > > EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
    > >
    > > BEGIN TRANSACTION
    > >
    > > INSERT Master (Master_ID, [Name])
    > > SELECT [ID], [name]
    > > FROM OPENXML(@hdoc, '/root/master', 2)
    > > WITH ([ID] int '@ID', [name] varchar(50) 'name')
    > >
    > > INSERT Child (Master_ID, Child_ID, [Name], SomeBoolean)
    > > SELECT masterID, [ID], [name], someBoolean
    > > FROM OPENXML (@hdoc, '/root/master/child', 2)
    > > WITH ([masterID] int '../@ID', [ID] int '@ID',
    > > [name] varchar(50) 'name',
    > > someBoolean int '@someBoolean')
    > >
    > > COMMIT TRANSACTION
    > >
    > > EXEC sp_xml_removedocument @hdoc
    > > GO
    > >
    > > DECLARE @xml nvarchar(4000)
    > > SET @xml = N'<root>
    > > <master ID="1">
    > > <name>First Master</name>
    > > <child ID="1" someBoolean="-1">
    > > <name>First Child of First</name>
    > > </child>
    > > <child ID="2" someBoolean="0">
    > > <name>Second Child of First</name>
    > > </child>
    > > <child ID="3">
    > > <name>Third Child of First</name>
    > > </child>
    > > </master>
    > > <master ID="2">
    > > <name>Second Master</name>
    > > <child ID="1" someBoolean="-1">
    > > <name>First Child of Second</name>
    > > </child>
    > > <child ID="2">
    > > <name>Second Child of Second</name>
    > > </child>
    > > </master>
    > >
    > >
    > > </root>'
    > >
    > > EXEC sp_UpdateMasterChild @xml
    > >
    > >
    > > Any questions?
    > >
    > >
    > > > --
    > > > Betty
    > > >
    > > >
    > > > "Anthony Jones" wrote:
    > > >
    > > > >
    > > > > "c676228" <> wrote in message
    > > > > news:...
    > > > > > Hi everyone,
    > > > > > I am just wodering in asp program, if there is anybody writing

    store
    > > > > > procedure for inserting data into database since there are so many
    > > > > parameters
    > > > > > need to be passed into store procedure(assume there are many

    columns
    > > in
    > > > > the
    > > > > > table).
    > > > > > I need to insert data into two separate tables, the relation

    between
    > > these
    > > > > > two tables is 1 row of data in table1 could have multiple rows in

    > > table2
    > > > > > related to table1, but if the data insertion into any one of the

    > > tables
    > > > > is
    > > > > > failed, the transaction will roll back and no data will be

    remained in
    > > any
    > > > > of
    > > > > > the tables.
    > > > > >
    > > > > > If this needs to be handled in asp program using transact sql, how

    do
    > > I
    > > > > > implement it. If I need to it using store procedure, how do I pass

    the
    > > > > error
    > > > > > or success signal to asp program from database.
    > > > > > Thank you.
    > > > > >
    > > > > > --
    > > > > > Betty
    > > > >
    > > > > In this scenario I pass XML into the stored procedure then use

    typical
    > > T-SQL
    > > > > to manage the transaction and OPENXML to access the input. This

    avoids
    > > > > creating long list of parameters and is a very flexible solution.

    (Eg.
    > > I
    > > > > can add a new field to the XML generated on the client and modify

    the SP
    > > but
    > > > > the ASP in between doesn't need to know anything about it)
    > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
    Anthony Jones, Oct 19, 2006
    #10
  11. c676228

    c676228 Guest

    Anthony,
    Thank you for our expert opinion and detailed instructions. I am appreciated
    very much.
    --
    Betty


    "Anthony Jones" wrote:

    >
    > "c676228" <> wrote in message
    > news:...
    > > Thank you, Anthony, for your so detailed anwser. I also checked the sql
    > > server book on-line. I do have some questions:
    > > first of all, people use xml data format to pass to database to reduce the
    > > numer of store procedure call, but with openxml, it seems that the max

    > length
    > > of the string is 4000, which basically means only smal xml file can be
    > > handled, did I understand correctly?

    >
    > No. I only used Nvarchar in the example calling code because in T-SQL you
    > can't declare a variable as NText. However you can define an SP parameter
    > as NText so in the real world you would pass a string from ASP to a
    > parameter on an ADO command object that has the ado type adLongNVarChar. So
    > there is no theoretical limit on the size of XML.
    >
    > > Another thing is you used:
    > > INSERT Master (Master_ID, [Name])
    > > SELECT [ID], [name]
    > > FROM OPENXML(@hdoc, '/root/master', 2)
    > > WITH ([ID] int '@ID', [name] varchar(50) 'name')
    > >
    > > Is '@ID' is xpath representation of the attribute and name is the node,
    > > Is it Ok if I use the following:
    > > INSERT Master (Master_ID, [Name])
    > > SELECT [ID], [name]
    > > FROM OPENXML(@hdoc, '/root/master', 2)
    > > WITH ([Master_ID int, [name] varchar(50) )

    >
    > It is possible to skip the element path definitions in the With clause by
    > using a field name that matches the node required from the XML and by
    > varying the value of the third parameter of the OPENXML call. The books
    > online docs describes this. However I would strongly recommend you stick
    > with mode 2 and use explicit path definitions. Also note that in SQL server
    > 2000 the underlying XML library used is msxml 2.6 so the path language is in
    > fact 'XSLPattern' which is a shame but in since these tend to be very simple
    > paths not normally a problem.
    >
    > One other thing to be aware of is since OPENXML was a new feature in SQL
    > 2000 early implementations of it were a bit buggy. Ensure you have at least
    > SP3 of SQL 2000 or are using 2005.
    >
    > > --
    > >
    > > I will try your sample
    > > Betty
    > >
    > >
    > > "Anthony Jones" wrote:
    > >
    > > >
    > > > "c676228" <> wrote in message
    > > > news:...
    > > > > Anthony,
    > > > > After I searched a little bit, it seems like it is a good solution,

    > but I
    > > > > never did this before, can you provide some examples and resouces for

    > this
    > > > > method?
    > > > > Thank you.
    > > >
    > > > Create a test DB in sql server then using query analyser execute the
    > > > following against it:-
    > > >
    > > > CREATE TABLE Master
    > > > (Master_ID int not null CONSTRAINT PK_Master PRIMARY KEY,
    > > > [Name] varchar(50) not null)
    > > >
    > > > CREATE TABLE Child
    > > > (Master_ID int not null,
    > > > Child_ID int not null,
    > > > [Name] varchar(50) not null,
    > > > SomeBoolean bit null,
    > > > CONSTRAINT PK_CHILD PRIMARY KEY (Master_ID, Child_ID),
    > > > CONSTRAINT FK_MASTER_CHILD FOREIGN KEY (Master_ID)
    > > > REFERENCES Master (Master_ID)
    > > > )
    > > > GO
    > > >
    > > > CREATE PROCEDURE sp_UpdateMasterChild
    > > > @xml ntext
    > > > AS
    > > >
    > > > SET NOCOUNT ON
    > > >
    > > > DECLARE @hdoc int
    > > >
    > > > EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
    > > >
    > > > BEGIN TRANSACTION
    > > >
    > > > INSERT Master (Master_ID, [Name])
    > > > SELECT [ID], [name]
    > > > FROM OPENXML(@hdoc, '/root/master', 2)
    > > > WITH ([ID] int '@ID', [name] varchar(50) 'name')
    > > >
    > > > INSERT Child (Master_ID, Child_ID, [Name], SomeBoolean)
    > > > SELECT masterID, [ID], [name], someBoolean
    > > > FROM OPENXML (@hdoc, '/root/master/child', 2)
    > > > WITH ([masterID] int '../@ID', [ID] int '@ID',
    > > > [name] varchar(50) 'name',
    > > > someBoolean int '@someBoolean')
    > > >
    > > > COMMIT TRANSACTION
    > > >
    > > > EXEC sp_xml_removedocument @hdoc
    > > > GO
    > > >
    > > > DECLARE @xml nvarchar(4000)
    > > > SET @xml = N'<root>
    > > > <master ID="1">
    > > > <name>First Master</name>
    > > > <child ID="1" someBoolean="-1">
    > > > <name>First Child of First</name>
    > > > </child>
    > > > <child ID="2" someBoolean="0">
    > > > <name>Second Child of First</name>
    > > > </child>
    > > > <child ID="3">
    > > > <name>Third Child of First</name>
    > > > </child>
    > > > </master>
    > > > <master ID="2">
    > > > <name>Second Master</name>
    > > > <child ID="1" someBoolean="-1">
    > > > <name>First Child of Second</name>
    > > > </child>
    > > > <child ID="2">
    > > > <name>Second Child of Second</name>
    > > > </child>
    > > > </master>
    > > >
    > > >
    > > > </root>'
    > > >
    > > > EXEC sp_UpdateMasterChild @xml
    > > >
    > > >
    > > > Any questions?
    > > >
    > > >
    > > > > --
    > > > > Betty
    > > > >
    > > > >
    > > > > "Anthony Jones" wrote:
    > > > >
    > > > > >
    > > > > > "c676228" <> wrote in message
    > > > > > news:...
    > > > > > > Hi everyone,
    > > > > > > I am just wodering in asp program, if there is anybody writing

    > store
    > > > > > > procedure for inserting data into database since there are so many
    > > > > > parameters
    > > > > > > need to be passed into store procedure(assume there are many

    > columns
    > > > in
    > > > > > the
    > > > > > > table).
    > > > > > > I need to insert data into two separate tables, the relation

    > between
    > > > these
    > > > > > > two tables is 1 row of data in table1 could have multiple rows in
    > > > table2
    > > > > > > related to table1, but if the data insertion into any one of the
    > > > tables
    > > > > > is
    > > > > > > failed, the transaction will roll back and no data will be

    > remained in
    > > > any
    > > > > > of
    > > > > > > the tables.
    > > > > > >
    > > > > > > If this needs to be handled in asp program using transact sql, how

    > do
    > > > I
    > > > > > > implement it. If I need to it using store procedure, how do I pass

    > the
    > > > > > error
    > > > > > > or success signal to asp program from database.
    > > > > > > Thank you.
    > > > > > >
    > > > > > > --
    > > > > > > Betty
    > > > > >
    > > > > > In this scenario I pass XML into the stored procedure then use

    > typical
    > > > T-SQL
    > > > > > to manage the transaction and OPENXML to access the input. This

    > avoids
    > > > > > creating long list of parameters and is a very flexible solution.

    > (Eg.
    > > > I
    > > > > > can add a new field to the XML generated on the client and modify

    > the SP
    > > > but
    > > > > > the ASP in between doesn't need to know anything about it)
    > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
    c676228, Oct 19, 2006
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?c2VyZ2UgY2FsZGVyYXJh?=

    Help on Store procedure and ASP web page

    =?Utf-8?B?c2VyZ2UgY2FsZGVyYXJh?=, Nov 1, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    531
    =?Utf-8?B?c2VyZ2UgY2FsZGVyYXJh?=
    Nov 1, 2005
  2. =?Utf-8?B?YzY3NjIyOA==?=

    fetch data from output parameter of a store procedure

    =?Utf-8?B?YzY3NjIyOA==?=, Aug 29, 2006, in forum: ASP .Net
    Replies:
    6
    Views:
    2,346
    Steven Cheng[MSFT]
    Aug 31, 2006
  3. Bhavesh
    Replies:
    0
    Views:
    445
    Bhavesh
    Jul 16, 2007
  4. Bhavesh
    Replies:
    5
    Views:
    677
    Bhavesh
    Jul 18, 2007
  5. iHavAQuestion

    Inserting a cloum in a stored procedure

    iHavAQuestion, May 13, 2008, in forum: ASP .Net
    Replies:
    1
    Views:
    315
    Jeff Dillon
    May 13, 2008
Loading...

Share This Page