- 帖子
- 231
- 主題
- 55
- 精華
- 0
- 積分
- 293
- 點名
- 0
- 作業系統
- winxp
- 軟體版本
- office2003
- 閱讀權限
- 20
- 性別
- 男
- 來自
- KEELUNG
- 註冊時間
- 2010-7-24
- 最後登錄
- 2018-8-28
|
[發問] 引用ADO語法要清空某一筆資料內的某一欄位值失敗
各位大大好：
小弟又遇到一個難題
想請各位大大協助。
小弟想引用ADO語法
來將資料庫指定合乎SQL語法某些資料
的某一欄位內資料並將其清空
可是會出現如下問題
ERROR NO:-2147217887
ERR DESCRIPTION:
多重步驟操作發生錯誤。請檢查每一個狀態值。- Private Sub clearNwCmd6_Click()
-
- Call comBindCmd56
- mLoadFile.mConstrCls = mconStr
-
- With mLoadFile
- Set .mShtCls = upDataSht1
- .pDataSource = mDataBasePath
- If .DataSourceExisted(dataPath) = True Then 'dataPath 為 combobox 指定資料庫名稱
- 'Call .LoadDataSource(dataPath, mData1) ', multiPageNo)
- Call .clearDataNWSource(dataPath, getAppTxt5.Text)
- Else
- MsgBox "指定報單號碼資料不存在"
- End If
- End With
-
- End Sub
- Sub comBindCmd56()
- With upDataSht1
- getAppTxt5.Value = .Range("b2").Value
- End With
- dataPath = dataBaseComb2.Value '先由comBoBox1取出資料庫名稱
- Select Case dataPath
-
- Case Is = "TRANCBS"
- mDataBasePath = "D:\trancbs\database\cbsData.mdb"
- mconStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
- mLoadFile.mSqlCls = "SELECT 項次_INV主檔.MSGCODE,項次_INV主檔.SERSNO,項次_INV主檔.項次號碼,項次_INV主檔.數量,項次_INV主檔.UNITAMT,項次_INV主檔.報單淨重 FROM 項次_INV主檔 WHERE 項次_INV主檔.MSGCODE AND SERSNO= '" & getAppTxt5.Text & "' ORDER BY 項次_INV主檔.項次號碼 ASC" '測試
-
- End Select
- End Sub
- Public Function clearDataNWSource(ByVal dataPath As String, ByVal mAppNo As String)
-
- Application.ScreenUpdating = False
-
- On Error GoTo ErrHandle
- Set mCon = New ADODB.Connection
- Select Case dataPath
- Case Is = "MYSQL"
- With mCon
- .Open mconStr
- End With
- Case Else
- With mCon
- .Open mconStr & mDataSource
- End With
- End Select
- Set mRst = New ADODB.Recordset
- With mRst
- .ActiveConnection = mCon
- .CursorLocation = adUseClient
- .CursorType = adOpenDynamic
- .LockType = adLockPessimistic
- .Source = mSql
- .Open
- End With
-
- If Not mRst.EOF Then
- mData1 = mRst.GetRows
- End If
- 's = mRst.Fields.Count
- With mSht
- mRow = .Range("c" & .Rows.Count).End(xlUp).Row
- End With
-
- For m = 2 To mRow
- cStr1 = mSht.Cells(m, 1)
- cStr2 = mSht.Cells(m, 2)
- cStr3 = mSht.Cells(m, 3)
- cStr4 = mSht.Cells(m, 4)
- cStr5 = mSht.Cells(m, 5)
-
- mRst.MoveFirst
- Do Until mRst.EOF = True
- mStr1 = mRst.Fields("MSGCODE")
- mStr2 = mRst.Fields("SERSNO")
- mStr3 = mRst.Fields("項次號碼")
- mStr4 = mRst.Fields("數量")
- mStr5 = mRst.Fields("UNITAMT")
- If cStr1 = mRst.Fields("MSGCODE") And cStr2 = mRst.Fields("SERSNO") And cStr3 = mRst.Fields("項次號碼") And cStr4 = mRst.Fields("數量") And cStr5 = mRst.Fields("UNITAMT") Then
- mRst.Fields("報單淨重").Value = """" '出現錯誤 執行至此即跳至ErrHandle
- mRst.Update
- Exit Do
- End If
- mRst.MoveNext
- Loop
- Next
-
- mRst.Close
- mCon.Close
-
- Set mRst = Nothing
- Set mCon = Nothing
- MsgBox "已完成新增指定簽審文號" & vbCrLf & "報單號碼：" & mAppNo & "至資料庫內"
- Exit Function
- ErrHandle:
- LastErrNumber = Err.Number
- MsgBox Err.Number
- Debug.Print Err.Number
-
- LastErrDescription = Err.Description
- MsgBox Err.Description
- Debug.Print Err.Description
- If LastErrNumber = 13 Then '如果遇到陣列 ERR 時，直接改由 MDATA1 陣列取出資料內容
- mErr mData1, dataPath
- End If
-
- Err.Clear
- End Function
|
|
發表於 2015-3-26 16:09
| 
