问题描述
存储过程如下:CREATEPROCEDUREUpdate_FinishedProductPlan@FinishedProductPlanDatechar(10),@TradeModechar(50),@Articlechar(30),@Colourchar(20),@ColourCodechar(40),@PlannedHidesint,@SRLNochar(10),@Priorityint,@Destinationchar(50),@Remarkschar(100),@FinishedProductPlanDate_Oldchar(10),@TradeMode_Oldchar(50),@Article_Oldchar(30),@Colour_Oldchar(20),@ColourCode_Oldchar(40),@PlannedHides_Oldint,@SRLNo_Oldchar(10),@Priority_Oldint,@Destination_Oldchar(50),@Remarks_Oldchar(100)ASbegintransactionupdateFinishedProductPlansetFinishedProductPlanDate=@FinishedProductPlanDate,TradeMode=@TradeMode,Article=@Article,Colour=@Colour,ColourCode=@ColourCode,PlannedHides=@PlannedHides,SRLNo=@SRLNo,Priority=@Priority,Destination=@Destination,Remarks=@RemarkswhereFinishedProductPlanDate=@FinishedProductPlanDate_OldandTradeMode=@TradeMode_Old--andArticle=@Article_OldandColour=@Colour_OldandColourCode=@ColourCode_OldandPlannedHides=@PlannedHides_OldandSRLNo=@SRLNo_OldandPriority=@Priority_OldandDestination=@Destination_OldandRemarks=@Remarks_Oldif@@error<>0beginrollbacktransactionraiserror('error',16,1)returnendcommittransactionGO
更新逻辑如下:PrivateFunctionCreatUpdateCommand()AsOleDbCommandDimcmdAsNewOleDbCommand("update_FinishedProductPlan",cn)cmd.CommandType=CommandType.StoredProcedureDimpcAsOleDbParameterCollection=cmd.Parameterspc.Add("FinishedProductPlanDate",OleDbType.Char,10,"FinishedProductPlanDate")pc.Add("TradeMode",OleDbType.Char,50,"TradeMode")pc.Add("Article",OleDbType.Char,30,"Article")pc.Add("Colour",OleDbType.Char,20,"Colour")pc.Add("ColourCode",OleDbType.Char,4,"ColourCode")pc.Add("PlannedHides",OleDbType.Integer,4,"PlannedHides")pc.Add("SRLNo",OleDbType.Char,10,"SRLNo")pc.Add("Priority",OleDbType.Integer,4,"Priority")pc.Add("Destination",OleDbType.Char,50,"Destination")pc.Add("Remarks",OleDbType.Char,100,"Remarks")DimparamAsOleDbParameterparam=pc.Add("FinishedProductPlanDate_Old",OleDbType.Char,10,"FinishedProductPlanDate")param.SourceVersion=DataRowVersion.Originalpc.Add("TradeMode_Old",OleDbType.Char,50,"TradeMode")param.SourceVersion=DataRowVersion.Originalpc.Add("Article_Old",OleDbType.Char,30,"Article")param.SourceVersion=DataRowVersion.Originalpc.Add("Colour_Old",OleDbType.Char,20,"Colour")param.SourceVersion=DataRowVersion.Originalpc.Add("ColourCode_Old",OleDbType.Char,4,"ColourCode")param.SourceVersion=DataRowVersion.Originalpc.Add("PlannedHides_Old",OleDbType.Integer,4,"PlannedHides")param.SourceVersion=DataRowVersion.Originalpc.Add("SRLNo_Old",OleDbType.Char,10,"SRLNo")param.SourceVersion=DataRowVersion.Originalpc.Add("Priority_Old",OleDbType.Integer,4,"Priority")param.SourceVersion=DataRowVersion.Originalpc.Add("Destination_Old",OleDbType.Char,50,"Destination")param.SourceVersion=DataRowVersion.Originalpc.Add("Remarks_Old",OleDbType.Char,100,"Remarks")param.SourceVersion=DataRowVersion.OriginalReturncmdEndFunction
但是发觉无法更新我的目的是要更新前比对下数据库的数据是否更改如果更改抛出异常但是现在就算不改变也无法更新高手帮我看下
解决方案
本帖最后由 lzmtw 于 2008-05-28 17:32:16 编辑
解决方案二：
改为：更新逻辑如下:PrivateFunctionCreatUpdateCommand()AsOleDbCommandDimcmdAsNewOleDbCommand("update_FinishedProductPlan",cn)cmd.CommandType=CommandType.StoredProcedureDimpcAsOleDbParameterCollection=cmd.Parameterspc.Add("FinishedProductPlanDate",OleDbType.Char,10,"FinishedProductPlanDate")pc.Add("TradeMode",OleDbType.Char,50,"TradeMode")pc.Add("Article",OleDbType.Char,30,"Article")pc.Add("Colour",OleDbType.Char,20,"Colour")pc.Add("ColourCode",OleDbType.Char,4,"ColourCode")pc.Add("PlannedHides",OleDbType.Integer,4,"PlannedHides")pc.Add("SRLNo",OleDbType.Char,10,"SRLNo")pc.Add("Priority",OleDbType.Integer,4,"Priority")pc.Add("Destination",OleDbType.Char,50,"Destination")pc.Add("Remarks",OleDbType.Char,100,"Remarks")DimparamAsOleDbParameterparam=pc.Add("FinishedProductPlanDate_Old",OleDbType.Char,10,"FinishedProductPlanDate_Old")pc.Add("TradeMode_Old",OleDbType.Char,50,"TradeMode_Old")pc.Add("Article_Old",OleDbType.Char,30,"Article_Old")pc.Add("Colour_Old",OleDbType.Char,20,"Colour_Old")pc.Add("ColourCode_Old",OleDbType.Char,4,"ColourCode_Old")pc.Add("PlannedHides_Old",OleDbType.Integer,4,"PlannedHides_Old")pc.Add("SRLNo_Old",OleDbType.Char,10,"SRLNo_Old")pc.Add("Priority_Old",OleDbType.Integer,4,"Priority_Old")pc.Add("Destination_Old",OleDbType.Char,50,"Destination_Old")pc.Add("Remarks_Old",OleDbType.Char,100,"Remarks_Old")ReturncmdEndFunction
解决方案三：
你的存储过程无论更不更改都不会抛出异常,最多就是更新0行(更新失败)或1行(更新成功),除非参数错误.如果要知道更改否,要检查更新影响了多少行.
解决方案四：
那这样我所有的old的参数都要自己取?就是我在第一次取出数据的同时就要把old的数据取出来手动的留下我第一取数据的副本是吗?但是我在csdn里看到通过DataRowVersion.Original的参数可以自动取出副本
解决方案五：
呵呵。可以看参考我的问题：http://topic.csdn.net/u/20080408/17/31afeefc-3575-4c46-8eba-04501fede12c.htmlhttp://topic.csdn.net/u/20080311/17/de3bc5bc-56cc-4c6c-bf9e-7af8be0ccce9.html
解决方案六：
引用3楼passacc67的回复:
那这样我所有的old的参数都要自己取?就是我在第一次取出数据的同时就要把old的数据取出来手动的留下我第一取数据的副本是吗?但是我在csdn里看到通过DataRowVersion.Original的参数可以自动取出副本
这个是如果你使用DataGridView这样的数据绑定控件时，它可以帮你做这件事情
解决方案七：
pc.Add("FinishedProductPlanDate",OleDbType.Char,10,"FinishedProductPlanDate")能对得上@FinishedProductPlanDate吗?是否应该是pc.Add("@FinishedProductPlanDate",OleDbType.Char,10,"FinishedProductPlanDate")
解决方案八：
是啊,应该这样PrivateFunctionCreatUpdateCommand()AsOleDbCommandDimcmdAsNewOleDbCommand("update_FinishedProductPlan",cn)cmd.CommandType=CommandType.StoredProcedureDimpcAsOleDbParameterCollection=cmd.ParametersDimparamAsOleDbParameterparam=pc.Add("@FinishedProductPlanDate",OleDbType.Char,10)param.value="你想要更新的新值"param=pc.Add("@TradeMode",OleDbType.Char,50param.value="同上,下面一样,不写了"param=pc.Add("@Article",OleDbType.Char,30)param=pc.Add("@Colour",OleDbType.Char,20param=pc.Add("@ColourCode",OleDbType.Char,4)param=pc.Add("@PlannedHides",OleDbType.Integer,4)param=pc.Add("@SRLNo",OleDbType.Char,10)param=pc.Add("@Priority",OleDbType.Integer,4)param=pc.Add("@Destination",OleDbType.Char,50)param=pc.Add("@Remarks",OleDbType.Char,100)'param=pc.Add("@FinishedProductPlanDate_Old",OleDbType.Char,10)param.value="你想要对比的旧值"param=pc.Add("@TradeMode_Old",OleDbType.Char,50)param.value="同上,下面一样,不写了"param=pc.Add("@Article_Old",OleDbType.Char,30)param=pc.Add("@Colour_Old",OleDbType.Char,20)param=pc.Add("@ColourCode_Old",OleDbType.Char,4)param=pc.Add("@PlannedHides_Old",OleDbType.Integer,4)param=pc.Add("@SRLNo_Old",OleDbType.Char,10)param=pc.Add("@Priority_Old",OleDbType.Integer,4)param=pc.Add("@Destination_Old",OleDbType.Char,50)param=pc.Add("@Remarks_Old",OleDbType.Char,100)ReturncmdEndFunction
解决方案九：
应该如下：引用6楼lzmtw的回复:
pc.Add("FinishedProductPlanDate",OleDbType.Char,10,"FinishedProductPlanDate")能对得上@FinishedProductPlanDate吗?是否应该是pc.Add("@FinishedProductPlanDate",OleDbType.Char,10,"FinishedProductPlanDate")
解决方案十：
mark