用于在asp中插入数据的调用存储过程 [英] call store procedure for inserting data in asp

问题描述

大家好，

我只是在编写asp程序，如果有人写商店

程序将数据插入数据库，因为有很多参数

需要传递到存储过程（假设

表中有很多列）。

我需要将数据插入两个单独的表中，这两个表之间的关系是两个表中的1行数据，表1中的数据行可能有多行与表1相关，但是如果数据插入到任何一个表中是
失败，交易将回滚，表格中没有任何数据会保留在

中。


如果这样需要使用transact sql在asp程序中处理，我如何实现它。
实现它。如果我需要使用存储过程，如何将错误

或成功信号从数据库传递给asp程序。

谢谢。

-

贝蒂

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

推荐答案

Hello Betty，


我认为这个问题有两种方法：


1.使用ADO连接的交易执行交易，例如：


Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Open" Provider = SQLOLEDB; Data Source =< server>;" &安培; _

"初始目录= Northwind;用户ID =< user> ;;密码=<密码>"


cn.BeginTrans

''将您的插入记录代码放在这里


cn.CommitTrans


cn.Close


通过这种方式，如果出现任何错误，您可以在ASP代码中获取异常。


2.在SQL存储过程中执行事务，并从SP返回任何错误

作为OUTPUT参数。这篇文章可能对此问题非常有用

问题：


管理SQL Server存储过程中的事务
http://www.4guysfromrolla.com/webtech/080305-1.shtml


如果您正在使用SQL Server 2005，您甚至可以使用Try ... Catch

语句：


使用尝试...在Transact-SQL中捕获
http ：//msdn2.microsoft.com/en-us/library/ms179296.aspx
http://www.codeproject.com/useritems/try_catch.asp


希望这个帮助。如果您想了解更多信息，请随时告诉我们。


此致，


卢克Zhang


Microsoft在线社区支持

========================= =========================

通过电子邮件收到我的帖子通知？请参阅
http://msdn.microsoft .com / subscripti ... ult.aspx＃notif

ications。


注意：MSDN托管新闻组支持服务是针对非紧急问题

如果社区或微软支持人员在1个工作日内做出初步回复是可以接受的。请注意，每个跟随

的响应可能需要大约2个工作日作为支持

专业人士与您合作可能需要进一步调查才能达到

最有效的分辨率。该产品不适用于需要紧急，实时或基于电话的交互或复杂的b $ b项目分析和转储分析问题的情况。这种性质的问题最好通过联系

Microsoft客户支持服务（CSS）处理
href =http://msdn.microsoft.com/subscriptions/support/default.aspx\"target =_ blank> http://msdn.microsoft.com/subscripti...t/default.aspx 。

======================================== ==========


此帖子按原样提供。没有保证，也没有授予任何权利。

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

新闻：E7 ************************** ******** @ microsof t.com ...

"c676228" <be****@community.nospamwrote in message
news:E7**********************************@microsof t.com...

大家好，

我只是在使用asp程序，如果有人写商店

程序将数据插入数据库，因为有这么多

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

表格。

我需要将数据插入两个单独的表中，这两个表之间的关系两个表是table1中的1行数据可能有table2中的多行

与table1相关，但如果数据插入任何一个表

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

表格。


如果这样需要使用transact sql在asp程序中处理，我如何实现它。
实现它。如果我需要使用商店程序，我如何传递

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

或成功信号从数据库到asp程序。

谢谢。


-

Betty

or success signal to asp program from database.
Thank you.

--
Betty

在这种情况下，我将XML传递给存储过程，然后使用典型的T-SQL

来管理事务，使用OPENXML来访问输入。这样可以避免创建长参数列表，这是一个非常灵活的解决方案。 （例如，我
可以在客户端生成的XML中添加一个新字段并修改SP但是

之间的ASP不需要知道任何关于它）

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和Anthony，

感谢您提供的信息。这非常有帮助。

我想知道在asp.net中我们可以在Try ... Catch

块中轻松使用sqlTransaction对象。

我关注的是经典的asp，我们有类似的方式吗。

-

贝蒂

" Luke Zhang [ MSFT] QUOT;写道：

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，


我认为这个问题有两种方法：

1.使用ADO连接执行交易，例如：


Dim cn As ADODB.Connection


设置cn =新ADODB.Connection

cn.Open" Provider = SQLOLEDB; Data Source =< server>;" &安培; _

"初始目录= Northwind;用户ID =< user> ;;密码=<密码>"


cn.BeginTrans

''将您的插入记录代码放在这里


cn.CommitTrans


cn.Close


通过这种方式，如果出现任何错误，您可以在ASP代码中获取异常。


2.在SQL存储过程中执行事务，并从SP返回任何错误

作为OUTPUT参数。这篇文章可能对此问题非常有用

问题：


管理SQL Server存储过程中的事务
http://www.4guysfromrolla.com/webtech/080305-1.shtml


如果您正在使用SQL Server 2005，您甚至可以使用Try ... Catch

语句：


使用尝试...在Transact-SQL中捕获
http ：//msdn2.microsoft.com/en-us/library/ms179296.aspx
http://www.codeproject.com/useritems/try_catch.asp


希望这个帮助。如果您想了解更多信息，请随时告诉我们。


此致，


卢克Zhang


Microsoft在线社区支持

========================= =========================

通过电子邮件收到我的帖子通知？请参阅
http://msdn.microsoft .com / subscripti ... ult.aspx＃notif

ications。


注意：MSDN托管新闻组支持服务是针对非紧急问题

如果社区或微软支持人员在1个工作日内做出初步回复是可以接受的。请注意，每个跟随

的响应可能需要大约2个工作日作为支持

专业人士与您合作可能需要进一步调查才能达到

最有效的分辨率。该产品不适用于需要紧急，实时或基于电话的交互或复杂的b $ b项目分析和转储分析问题的情况。这种性质的问题最好通过联系

Microsoft客户支持服务（CSS）处理
href =http://msdn.microsoft.com/subscriptions/support/default.aspx\"target =_ blank> http://msdn.microsoft.com/subscripti...t/default.aspx 。

======================================== ==========


此帖子按原样提供。没有保证，也没有授予任何权利。

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.

这篇关于用于在asp中插入数据的调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！