Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > call store procedure for inserting data in asp

Reply
Thread Tools

call store procedure for inserting data in asp

 
 
c676228
Guest
Posts: n/a
 
      10-06-2006
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
 
Reply With Quote
 
 
 
 
Luke Zhang [MSFT]
Guest
Posts: n/a
 
      10-06-2006
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.



 
Reply With Quote
 
 
 
 
Anthony Jones
Guest
Posts: n/a
 
      10-06-2006

"c676228" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)



 
Reply With Quote
 
c676228
Guest
Posts: n/a
 
      10-06-2006
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.
>
>
>
>

 
Reply With Quote
 
Luke Zhang [MSFT]
Guest
Posts: n/a
 
      10-09-2006
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.



 
Reply With Quote
 
c676228
Guest
Posts: n/a
 
      10-16-2006
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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)
>
>
>
>

 
Reply With Quote
 
Firas
Guest
Posts: n/a
 
      10-17-2006
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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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)
> >
> >
> >
> >


 
Reply With Quote
 
Anthony Jones
Guest
Posts: n/a
 
      10-17-2006

"c676228" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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)
> >
> >
> >
> >



 
Reply With Quote
 
c676228
Guest
Posts: n/a
 
      10-18-2006
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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)
> > >
> > >
> > >
> > >

>
>
>

 
Reply With Quote
 
Anthony Jones
Guest
Posts: n/a
 
      10-19-2006

"c676228" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > 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)
> > > >
> > > >
> > > >
> > > >

> >
> >
> >



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting a cloum in a stored procedure iHavAQuestion ASP .Net 1 05-13-2008 08:54 PM
Fetching & Inserting Data into a column of TEXT Data type in SQL server 2000 Using ASP.NET Bhavesh ASP .Net 5 07-18-2007 07:39 AM
Inserting & Fetching Data into a column of TEXT Data type in SQL server 2000 Using ASP.NET Bhavesh ASP .Net 0 07-16-2007 11:15 AM
fetch data from output parameter of a store procedure =?Utf-8?B?YzY3NjIyOA==?= ASP .Net 6 08-31-2006 01:21 AM
Help on Store procedure and ASP web page =?Utf-8?B?c2VyZ2UgY2FsZGVyYXJh?= ASP .Net 3 11-01-2005 03:07 PM



Advertisments