call store procedure for inserting data in asp

[c676228]

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.

 

[Luke Zhang [MSFT]]

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.

 

[Anthony Jones]

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.

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]

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.

 

[Luke Zhang [MSFT]]

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.

 

[c676228]

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.

 

[Firas]

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

 

[Anthony Jones]

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?

 

[c676228]

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,
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?

 

[Anthony Jones]

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


:


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.

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]

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

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


:


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)