高手帮忙,急 sql问题

懒牛科技 2008-06-08 09:13:40

我在看一个ASP.NET数码商店的网站时发现源代码有问题，下面这句代码会有异常：
data.RunProc("upOrdersAdd", prams); //这句话是把订单信息存入数据库Orders

upOrdersAdd是一个存储过程的名字，内容如下：
USE [D:\MIKECATCSHARP\CHAPTER10\DS\APP_DATA\DIGITALSHOP.MDF]
GO
/****** 对象: StoredProcedure [dbo].[upOrdersAdd] 脚本日期: 06/07/2008 22:58:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[upOrdersAdd]
(
@xml varchar(8000)
)
AS

DECLARE @idoc int -- xml doc
DECLARE @orderid int -- new order

-- parse xml doc
EXEC sp_xml_preparedocument @idoc output, @xml

SET NOCOUNT ON
DECLARE @CurrentError int

-- start transaction, updating three tables
BEGIN TRANSACTION

-- add new order to Orders table
INSERT INTO Orders
SELECT userid, orderdate, shipaddr1, shipaddr2, shipcity, shipstate,
shipzip, shipcountry, billaddr1, billaddr2, billcity, billstate, billzip,
billcountry, courier, totalprice, billtofirstname, billtolastname,
shiptofirstname, shiptolastname, creditcard, exprdate, cardtype, locale
FROM OpenXML(@idoc, '/Orders')
WITH Orders

-- check for error
SELECT @CurrentError = @@Error

IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END

-- get new order id
SELECT @orderid = @@IDENTITY

-- add line items to LineItem table
INSERT INTO LineItem
SELECT @orderid, linenum, itemid, quantity, unitprice
FROM OpenXML(@idoc, '/Orders/LineItem')
WITH LineItem

-- check for error
SELECT @CurrentError = @@Error

IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END

-- add status to OrderStatus table
INSERT INTO OrderStatus
SELECT @orderid, @orderid, getdate(), 'P'

-- check for error
SELECT @CurrentError = @@Error

IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END

-- update inventory
UPDATE Inventory
SET Inventory.qty = Inventory.qty - LineItem.quantity
FROM OpenXML(@idoc, '/Orders/LineItem')
WITH LineItem
WHERE Inventory.itemid=LineItem.itemid

-- check for error
select @CurrentError = @@Error

IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END

-- end of transaction
COMMIT TRANSACTION

SET NOCOUNT OFF

-- done with xml doc
EXEC sp_xml_removedocument @idoc

-- return the new order
RETURN @orderid

ERROR_HANDLER:
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN 0

prams数组只有一项为@xml,xml的内容是:
" <Orders userid='zhanghuan' orderdate='2008-6-7 22:45:34' shiptofirstname='张' shiptolastname='欢' shipaddr1='南京市江宁区' shipaddr2='' shipcity='南京' shipstate='New York' shipzip='211100' shipcountry='Canada' billtofirstname='张' billtolastname='欢' billaddr1='南京市江宁区' billaddr2='' billcity='南京' billstate='New York' billzip='211100' billcountry='Canada' creditcard='9999 9999 9999 9999' exprdate='01/2008' cardtype='交通银行太平洋卡' courier='UPS' totalprice='18.5' locale='US_en'> <LineItem itemid='EST-12' linenum='1' quantity='1' unitprice='18.5' /> </Orders>"

运行时出现了异常，我跟踪了一下，发现异常的内容如下：
ex.Message
"无法绑定由多个部分组成的标识符 \"LineItem.itemid\"。"

ex.ToString()
"System.Data.SqlClient.SqlException: 无法绑定由多个部分组成的标识符 \"LineItem.itemid\"。\r\n 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)\r\n 在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)\r\n 在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)\r\n 在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)\r\n 在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n 在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)\r\n 在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)\r
\n 在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)\r\n 在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n 在 digitalshop.Components.Database.RunProc(String procName, SqlParameter[] prams) 位置 d:\\Visual Studio 2005\\WebSites\\数码商店\\App_Code\\Components\\Database.cs:行号 41\r\n 在 digitalshop.Components.Order.Add(String xml) 位置 d:\\Visual Studio 2005\\WebSites\\数码商店\\App_Code\\Components\\Order.cs:行号 27"

代码比较多，请高手耐心看一下，如果问题解决，将非常感谢

...全文