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 11 4342
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/subscripti...ult.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/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
"c676228" <be****@community.nospamwrote in message
news:E7**********************************@microsof t.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)
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/subscripti...ult.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/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
c676228 wrote:
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.
I use try...catch...finally all the time in classic ASP. But that's because
I use JScript.
--
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
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/subscripti...ult.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/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
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" <be****@community.nospamwrote in message
news:E7**********************************@microsof t.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)
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" <be****@community.nospamwrote in message
news:E7**********************************@microsof t.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)
"c676228" <be****@community.nospamwrote in message
news:C6**********************************@microsof t.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" <be****@community.nospamwrote in message
news:E7**********************************@microsof t.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)
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" <be****@community.nospamwrote in message
news:C6**********************************@microsof t.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" <be****@community.nospamwrote in message
news:E7**********************************@microsof t.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)
>
>
>
>
"c676228" <be****@community.nospamwrote in message
news:62**********************************@microsof t.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" <be****@community.nospamwrote in message
news:C6**********************************@microsof t.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" <be****@community.nospamwrote in message
news:E7**********************************@microsof t.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,
Thank you for our expert opinion and detailed instructions. I am appreciated
very much.
--
Betty
"Anthony Jones" wrote:
>
"c676228" <be****@community.nospamwrote in message
news:62**********************************@microsof t.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" <be****@community.nospamwrote in message
news:C6**********************************@microsof t.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" <be****@community.nospamwrote in message
news:E7**********************************@microsof t.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)
>
>
>
>
>
>
>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Magnus Österberg |
last post by:
My application fetches a batch of data through a web service and writes 1000
entities per batch to a SQL Server 2000 database. There are 4 tables in
every batch. There are the following number of...
|
by: saurabh |
last post by:
Hi All,
I want to store the processed salary information in the data
base. My requirement is as follows:
1.For each location for an employee , I have to pay/deduct different
components (at...
|
by: serge |
last post by:
My project is to automate testing of Stored Procedures
of type SELECT (at least for now).
I want to create a table where each stored procedure's
input parameter values are entered and in another...
|
by: Hugo Lefevre |
last post by:
Dear,
I have a problem : I have a database which contains my data of hardware. The
Id is a varchar and I want at my filling form that my user know which is the
last one. So I made a store...
|
by: serge calderara |
last post by:
Dear all,
One simple question relative to store procedure withinh ASP code on a web
page.
Let say that I am calling a store procedure to execute from a server button
click on my web page. That...
| |
by: Scott Johnson |
last post by:
Hi
Does anyone know how to execute a program on an AS400 from Visual Basic? I
have no problem inserting data into the tables (files) on the as400, but I
need to execute a program on there to...
|
by: c676228 |
last post by:
Hi everyone,
I wrote a store procedure that fetch one row data in the database based on
the parameter value I entered. After I created the store procedure,
the store procedure code looks like...
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL2000 database.
I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1
parameter (varchar(10)) and returns the identity column value from that
table....
|
by: maurixgr |
last post by:
Hi:
I´m calling a store procedure inside another store procedure and I have two problems
create sp1(in variable int)
begin
call sp2(variable)
call sp3(variable)
call sp4(variable)
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |