我知道不能更新多个表,我也是用SQL语句更新的,看我的程序: Sub BindGridDetail() Dim strSearch, strCreator, whereSql As String strSearch = Me.txtInvConS.Text.Trim strCreator = Me.cbbInputBy.Text.Trim sqlStr = " SELECT A.WeekNo, A.Customer,(A.ReferenceNo) as BLNo, B.ContainerNo, B.CurrentStatus, B.StatusUpdate,B.ActualDestination,A.ETADestination, " & _ " A.ArrivalPort, A.ETAPort, B.YardIN, B.YardOut,B.LoadingNo, A.InvoiceNo, B.Re, A.Creator, B.BUCode,B.DiversionFlag " & _ " FROM dbo.GO_OManifestHeader A INNER JOIN dbo.GO_OManifestDetail1 B ON A.InvoiceNo = B.InvoiceNo" & _ " where (B.LoadingNo+A.InvoiceNo+B.ContainerNo+A.ReferenceNo) like '%" + strSearch + "%' and A.Creator like '%" + strCreator + "%'"
conn = New SqlConnection(gblSqlConn) conn.Open() sda = New SqlDataAdapter(sqlStr, conn) da = New DataSet sda.Fill(da) dvDetail = da.Tables(0).DefaultView GridDetail.DataSource = dvDetail myBindingContext = BindingContext(dvDetail) conn.Close() ShowCurrentRecordCount() End Sub Sub SaveUpdate() For i = 0 To Me.dvDetail.Count - 1 Dim strInvoiceNo, strLoadingNo, strCurrentStatus, strStatusUpdate, strActualDestination As String Dim strArrivalPort, strYardIn, strYardOut, strRe, strReferenceNo, strSQL As String strLoadingNo = IIf(IsDBNull(Me.GridDetail.Item(i, 12)), "null", Me.GridDetail.Item(i, 12)) strInvoiceNo = IIf(IsDBNull(Me.GridDetail.Item(i, 13)), "null", Me.GridDetail.Item(i, 13)) strReferenceNo = IIf(IsDBNull(Me.GridDetail.Item(i, 2)), "null", Me.GridDetail.Item(i, 2)) strCurrentStatus = IIf(IsDBNull(Me.GridDetail.Item(i, 4)), "null", Me.GridDetail.Item(i, 4)) strStatusUpdate = IIf(IsDBNull(Me.GridDetail.Item(i, 5)), "null", Me.GridDetail.Item(i, 5)) strActualDestination = IIf(IsDBNull(Me.GridDetail.Item(i, 6)), "", Me.GridDetail.Item(i, 6)) strArrivalPort = IIf(IsDBNull(Me.GridDetail.Item(i, 8)), "null", Me.GridDetail.Item(i, 8)) strYardIn = IIf(IsDBNull(Me.GridDetail.Item(i, 10)), "null", Me.GridDetail.Item(i, 10)) strYardOut = IIf(IsDBNull(Me.GridDetail.Item(i, 11)), "null", Me.GridDetail.Item(i, 11)) strRe = IIf(IsDBNull(Me.GridDetail.Item(i, 14)), "null", Me.GridDetail.Item(i, 14)) Dim objCmd As New DBCOM(Common.gblSqlConn) strSQL = " update GO_OManifestDetail1 set currentStatus ='" + strCurrentStatus + "',StatusUpdate='" + strStatusUpdate + "'," strSQL += " ActualDestination='" + strActualDestination + "',yardin='" + strYardIn + "',yardout='" + strYardOut + "',re='" + strRe + "'" strSQL += " where InvoiceNo='" + strInvoiceNo + "' and loadingNo='" + strLoadingNo + "'; " strSQL += " update GO_OManifestHeader set ArrivalPort='" + strArrivalPort + "' where referenceno='" + strReferenceNo + "'" objCmd.executeSQLCommand(strSQL) Next MessageBox.Show("已保存!", "保存提示:", MessageBoxButtons.OK) CurrentRowIndexStr = GridDetail.CurrentRowIndex IniDataSet() BindGridDetail() GridDetail.CurrentRowIndex = CurrentRowIndexStrexStr End Sub
我每次执行更新操作时都CALL SaveUpdate事件,但执行的速度很慢
一行一行地修改,最后按一次保存完成所有的更新在sql中似乎是不行的. 我认为你开始头两次的更新速度你是可以接受的,关键在于以后的速度会越来越慢. 我认为问题并不是你的sql语句的问题,问题是在于Dim objCmd As New DBCOM(Common.gblSqlConn)这句话,你每次更新时都执行此句,这句话的意思是新建一个objCmd,这是要占内存的,如此反复,objCmd会越来越多,那么分给执行程序的内存会越来越少,于是程序需要调用硬盘的虚拟内存,自然速度会慢了. 解决方法是:objCmd用完就dispose掉.
我的执行sql语句是从几个表中选出来的,目前我的做法是找出要update的列所在的table,直接在
table中执行update语句,可这样我每修改一行都要执行一次update事件,我是想修改很多行后一次
执行update事件!
Sub BindGridDetail()
Dim strSearch, strCreator, whereSql As String
strSearch = Me.txtInvConS.Text.Trim
strCreator = Me.cbbInputBy.Text.Trim sqlStr = " SELECT A.WeekNo, A.Customer,(A.ReferenceNo) as BLNo, B.ContainerNo, B.CurrentStatus, B.StatusUpdate,B.ActualDestination,A.ETADestination, " & _
" A.ArrivalPort, A.ETAPort, B.YardIN, B.YardOut,B.LoadingNo, A.InvoiceNo, B.Re, A.Creator, B.BUCode,B.DiversionFlag " & _
" FROM dbo.GO_OManifestHeader A INNER JOIN dbo.GO_OManifestDetail1 B ON A.InvoiceNo = B.InvoiceNo" & _
" where (B.LoadingNo+A.InvoiceNo+B.ContainerNo+A.ReferenceNo) like '%" + strSearch + "%' and A.Creator like '%" + strCreator + "%'"
conn = New SqlConnection(gblSqlConn)
conn.Open() sda = New SqlDataAdapter(sqlStr, conn)
da = New DataSet sda.Fill(da) dvDetail = da.Tables(0).DefaultView
GridDetail.DataSource = dvDetail myBindingContext = BindingContext(dvDetail)
conn.Close() ShowCurrentRecordCount() End Sub
Sub SaveUpdate()
For i = 0 To Me.dvDetail.Count - 1
Dim strInvoiceNo, strLoadingNo, strCurrentStatus, strStatusUpdate, strActualDestination As String
Dim strArrivalPort, strYardIn, strYardOut, strRe, strReferenceNo, strSQL As String strLoadingNo = IIf(IsDBNull(Me.GridDetail.Item(i, 12)), "null", Me.GridDetail.Item(i, 12))
strInvoiceNo = IIf(IsDBNull(Me.GridDetail.Item(i, 13)), "null", Me.GridDetail.Item(i, 13)) strReferenceNo = IIf(IsDBNull(Me.GridDetail.Item(i, 2)), "null", Me.GridDetail.Item(i, 2))
strCurrentStatus = IIf(IsDBNull(Me.GridDetail.Item(i, 4)), "null", Me.GridDetail.Item(i, 4))
strStatusUpdate = IIf(IsDBNull(Me.GridDetail.Item(i, 5)), "null", Me.GridDetail.Item(i, 5))
strActualDestination = IIf(IsDBNull(Me.GridDetail.Item(i, 6)), "", Me.GridDetail.Item(i, 6))
strArrivalPort = IIf(IsDBNull(Me.GridDetail.Item(i, 8)), "null", Me.GridDetail.Item(i, 8))
strYardIn = IIf(IsDBNull(Me.GridDetail.Item(i, 10)), "null", Me.GridDetail.Item(i, 10))
strYardOut = IIf(IsDBNull(Me.GridDetail.Item(i, 11)), "null", Me.GridDetail.Item(i, 11))
strRe = IIf(IsDBNull(Me.GridDetail.Item(i, 14)), "null", Me.GridDetail.Item(i, 14))
Dim objCmd As New DBCOM(Common.gblSqlConn) strSQL = " update GO_OManifestDetail1 set currentStatus ='" + strCurrentStatus + "',StatusUpdate='" + strStatusUpdate + "',"
strSQL += " ActualDestination='" + strActualDestination + "',yardin='" + strYardIn + "',yardout='" + strYardOut + "',re='" + strRe + "'"
strSQL += " where InvoiceNo='" + strInvoiceNo + "' and loadingNo='" + strLoadingNo + "'; "
strSQL += " update GO_OManifestHeader set ArrivalPort='" + strArrivalPort + "' where referenceno='" + strReferenceNo + "'" objCmd.executeSQLCommand(strSQL)
Next
MessageBox.Show("已保存!", "保存提示:", MessageBoxButtons.OK) CurrentRowIndexStr = GridDetail.CurrentRowIndex
IniDataSet()
BindGridDetail()
GridDetail.CurrentRowIndex = CurrentRowIndexStrexStr
End Sub
我每次执行更新操作时都CALL SaveUpdate事件,但执行的速度很慢
我认为你开始头两次的更新速度你是可以接受的,关键在于以后的速度会越来越慢.
我认为问题并不是你的sql语句的问题,问题是在于Dim objCmd As New DBCOM(Common.gblSqlConn)这句话,你每次更新时都执行此句,这句话的意思是新建一个objCmd,这是要占内存的,如此反复,objCmd会越来越多,那么分给执行程序的内存会越来越少,于是程序需要调用硬盘的虚拟内存,自然速度会慢了.
解决方法是:objCmd用完就dispose掉.