473,574 Members | 2,659 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

call store procedure for inserting data in 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(assum e 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
Oct 6 '06 #1
11 4357
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.Connectio n
Set cn = New ADODB.Connectio n
cn.Open "Provider=SQLOL EDB;Data Source=<server> ;" & _
"Initial Catalog=Northwi nd;User ID=<user>;Passw ord=<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.

Oct 6 '06 #2

"c676228" <be****@communi ty.nospamwrote in message
news:E7******** *************** ***********@mic rosoft.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(assum e 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)

Oct 6 '06 #3
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.Connectio n
Set cn = New ADODB.Connectio n
cn.Open "Provider=SQLOL EDB;Data Source=<server> ;" & _
"Initial Catalog=Northwi nd;User ID=<user>;Passw ord=<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.

Oct 6 '06 #4
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...f inally 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.
Oct 6 '06 #5
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.

Oct 9 '06 #6
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****@communi ty.nospamwrote in message
news:E7******** *************** ***********@mic rosoft.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(assum e 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)

Oct 16 '06 #7
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****@communi ty.nospamwrote in message
news:E7******** *************** ***********@mic rosoft.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(assum e 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)


Oct 17 '06 #8

"c676228" <be****@communi ty.nospamwrote in message
news:C6******** *************** ***********@mic rosoft.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_UpdateMaster Child
@xml ntext
AS

SET NOCOUNT ON

DECLARE @hdoc int

EXEC sp_xml_prepared ocument @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_removedo cument @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_UpdateMaster Child @xml
Any questions?

--
Betty
"Anthony Jones" wrote:

"c676228" <be****@communi ty.nospamwrote in message
news:E7******** *************** ***********@mic rosoft.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(assum e 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)



Oct 17 '06 #9
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****@communi ty.nospamwrote in message
news:C6******** *************** ***********@mic rosoft.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_UpdateMaster Child
@xml ntext
AS

SET NOCOUNT ON

DECLARE @hdoc int

EXEC sp_xml_prepared ocument @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_removedo cument @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_UpdateMaster Child @xml
Any questions?

--
Betty
"Anthony Jones" wrote:
>
"c676228" <be****@communi ty.nospamwrote in message
news:E7******** *************** ***********@mic rosoft.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(assum e 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)
>
>
>
>


Oct 18 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2174
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 SQL commands executed per average of every batch; Table #1: always 1 Table #2: 5 Table #3: 5 Table #4: 3
2
1358
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 least 38 )which differ from person to person. 2.Each component is mapped to component table which contains the component Id. I may have 2 options...
2
4234
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 table the expected result value(s) are entered when executed against a sample database containing manually entered and verified data. My...
1
6126
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 procedure in SQL Server with a input parameter (to select the right hardware e.g. PC for a computer, CD for cdrom, etc) and the last code would I have...
3
1482
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 store procedure is returning a bunch of values to my end user. In a matter of time based, what happen from my client web page if my store...
1
9520
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 process the data. (I can't use triggers either) The command I need to execute on the as400 is CALL PGM(RNRB18) PARM(01 01)
6
2714
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 this: ALTER proc getProductCommScale @product As varchar(30), @TISCommRate As Decimal(5,2) OUTPUT, @BrokerCommRate As Decimal(5,2) OUTPUT, @Fee As...
9
4134
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. When calling the stored procedure from VB.NET, in the CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with...
4
1982
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)
0
8067
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8249
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7826
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8107
tracyyun
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5631
isladogs
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3748
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3756
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1352
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1071
bsmnconsultancy
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 can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.