高手帮忙,急 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"


代码比较多,请高手耐心看一下,如果问题解决,将非常感谢
...全文
130 点赞 收藏 3
写回复
3 条回复
yinqi025 2008年07月03日
没有SQL2005.....
回复 点赞
huangqing_80 2008年06月09日
建议转移到ASP.NET区
回复 点赞
pgy8288 2008年06月09日
检查一下xml这块,是不是有的地方没传入ItemID或是没设置值
回复 点赞
发动态
发帖子
疑难问题
创建于2007-09-28

9306

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
社区公告
暂无公告