"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" <bettys@community.nospam> wrote in message
news:E7C9085C-3D0A-4168-8C01-88206E7BA1F9@microsoft.com...
> 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

"Anthony Jones" wrote:

>
> "c676228" <bettys@community.nospam> wrote in message
> news:E7C9085C-3D0A-4168-8C01-88206E7BA1F9@microsoft.com...
> > 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,
> 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" <bettys@community.nospam> wrote in message
> > news:E7C9085C-3D0A-4168-8C01-88206E7BA1F9@microsoft.com...
> > > 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)
> >
> >
> >
> >

> --
> Betty
>
>
> "Anthony Jones" wrote:
>
> >
> > "c676228" <bettys@community.nospam> wrote in message
> > news:E7C9085C-3D0A-4168-8C01-88206E7BA1F9@microsoft.com...
> > > 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" wrote:

>
> "c676228" <bettys@community.nospam> wrote in message
> news:C6B882B2-CD34-4DE0-81C7-1520CDDD7693@microsoft.com...
> > 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" <bettys@community.nospam> wrote in message
> > > news:E7C9085C-3D0A-4168-8C01-88206E7BA1F9@microsoft.com...
> > > > 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)
> > >
> > >
> > >
> > >
>
>
>

> --
>
> I will try your sample
> Betty
>
>
> "Anthony Jones" wrote:
>
> >
> > "c676228" <bettys@community.nospam> wrote in message
> > news:C6B882B2-CD34-4DE0-81C7-1520CDDD7693@microsoft.com...
> > > 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" <bettys@community.nospam> wrote in message
> > > > news:E7C9085C-3D0A-4168-8C01-88206E7BA1F9@microsoft.com...
> > > > > 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" wrote:

>
> "c676228" <bettys@community.nospam> wrote in message
> news:6256F5F4-06C9-4500-9E9C-6CBA5A1CCE69@microsoft.com...
> > 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" <bettys@community.nospam> wrote in message
> > > news:C6B882B2-CD34-4DE0-81C7-1520CDDD7693@microsoft.com...
> > > > 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" <bettys@community.nospam> wrote in message
> > > > > news:E7C9085C-3D0A-4168-8C01-88206E7BA1F9@microsoft.com...
> > > > > > 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)
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>