none
VB6 Datagrid的問題 RRS feed

  • 一般討論

  • 以下一小段程式碼
    我從資料庫把資料一筆筆讀出來
    先判斷CCY是否一樣 AMOUNT再分正負加總
    再分正負做一個小計

    但是 我跑出來的結果 在DataGrid畫面上
    看都是正常的
    在資料庫裡看 順序都是亂的
    順序應該是 同CCY的在一起 下一筆是小計
    錯誤的順序是 資料亂掉 小計數字卻是對的
    而且 神奇的是 跑了N次 會有幾次正常?!?!

    Private Sub Command4_Click()

        Dim criteria As String
        Dim criteria1 As String
        Dim marked1 As Boolean
        Dim marked2 As Boolean
        Dim marked3 As Boolean
        Dim Ccy1, Ccy2 As String
        Dim Prod1, Prod2 As String
        Dim Total, Total2

        marked1 = False '皆有
        marked2 = False 'OD有


        Ccy1 = ""
        Prod1 = ""
        Total = 0
        Total2 = 0

        'Bank

        rsmdb.Close
        cnmdb.Close

        SQLSRV1 = "Provider=SQLOLEDB.1;" _
            & "Password=7777777;Persist Security Info=True;User ID=sa;" _
            & "Initial Catalog=CPA;Data Source=TEST\TESTSERVER"
        DBname = "Bank"

        Set cnmdb = New ADODB.Connection
        cnmdb.ConnectionString = SQLSRV1
        cnmdb.CursorLocation = adUseClient
        cnmdb.Open

        Set rsmdb = New ADODB.Recordset
        query1 = "SELECT * FROM " & DBname
        rsmdb.Open query1, cnmdb, adOpenStatic, adLockOptimistic
        Set DataGrid1.DataSource = rsmdb


        'OD
        SQLSRV2 = "Provider=SQLOLEDB.1;" _
            & "Password=7777777;Persist Security Info=True;User ID=sa;" _
            & "Initial Catalog=CPA;Data Source=TEST\TESTSERVER"

        DBname2 = "OD"

        Set cnmdb2 = New ADODB.Connection
        cnmdb2.ConnectionString = SQLSRV2
        cnmdb2.CursorLocation = adUseClient
        cnmdb2.Open

        Set rsmdb2 = New ADODB.Recordset
        query2 = "DELETE " & DBname2
        rsmdb2.Open query2, cnmdb2, adOpenStatic, adLockOptimistic
        Set DataGrid2.DataSource = rsmdb2

        Set rsmdb2 = New ADODB.Recordset
        query = "SELECT * FROM " & DBname2
        rsmdb2.Open query, cnmdb2, adOpenStatic, adLockOptimistic
        Set DataGrid2.DataSource = rsmdb2

        If Len(Text1.Text) = 0 Or IsNumeric(Text1.Text) = False Then
            MsgBox "Wrong Input!!"
            Unload Me
            Derivative1.Show
        Else
            criteria = "RCIF " & "=" & Text1.Text
            rsmdb.Find criteria, , adSearchForward
            If (rsmdb.EOF = True) Then
                rsmdb.Find criteria, , adSearchBackward
                If (rsmdb.BOF = True) Then
                    MsgBox "Sorry!Can not find RCIF !!", 48, "Attention:"
                    Unload Me
                    Derivative1.Show
                End If
            Else
                marked1 = True
            End If
        End If

        rsmdb.Close
        cnmdb.Close

        Dim i, j

        If marked1 = True Then

            SQLSRV1 = "Provider=SQLOLEDB.1;" _
                & "Password=7777777;Persist Security Info=True;User ID=sa;"
    _
                & "Initial Catalog=CPA;Data Source=TEST\TESTSERVER"

            DBname = "Bank"

            Set cnmdb = New ADODB.Connection
            cnmdb.ConnectionString = SQLSRV1
            cnmdb.CursorLocation = adUseClient
            cnmdb.Open

            Set rsmdb = New ADODB.Recordset
            query1 = "SELECT * FROM " & DBname & " where rcif " & "=" & " " & Text1.Text & " and Date " & "=" & " " & Combo1.Text & " AND PROD <> " & "'OD'" & " ORDER BY 6" & "," & "8"
            rsmdb.Open query1, cnmdb, adOpenStatic, adLockOptimistic

            For i = 1 To rsmdb.RecordCount
                    If Ccy1 = rsmdb.Fields(7).Value Then
                        If rsmdb.Fields(6).Value > 0 Then
                            Total = Total + rsmdb.Fields(6).Value
                            rsmdb.Fields(15).Value = Total
                        Else
                            Total2 = Total2 + rsmdb.Fields(6).Value
                            rsmdb.Fields(15).Value = Total2
                        End If

                        rsmdb2.AddNew
                        rsmdb2.Fields(0).Value = "OD"
                        rsmdb2.Fields(1).Value = rsmdb.Fields(0).Value
                        rsmdb2.Fields(2).Value = rsmdb.Fields(1).Value
                        rsmdb2.Fields(3).Value = rsmdb.Fields(2).Value
                        rsmdb2.Fields(4).Value = rsmdb.Fields(3).Value
                        rsmdb2.Fields(5).Value = rsmdb.Fields(4).Value
                        rsmdb2.Fields(6).Value = rsmdb.Fields(5).Value
                        rsmdb2.Fields(7).Value = rsmdb.Fields(6).Value
                        rsmdb2.Fields(8).Value = rsmdb.Fields(7).Value
                        rsmdb2.Fields(9).Value = rsmdb.Fields(8).Value
                        rsmdb2.Fields(10).Value = rsmdb.Fields(9).Value
                        rsmdb2.Fields(11).Value = rsmdb.Fields(10).Value
                        rsmdb2.Fields(12).Value = rsmdb.Fields(11).Value
                        rsmdb2.Update

                        If i = rsmdb.RecordCount Then

                            rsmdb.Fields(15).Value = Total
                            If Total <> 0 Then

                                rsmdb2.AddNew
                                rsmdb2.Fields(0).Value = "小計"
                                rsmdb2.Fields(7).Value = Total
                                rsmdb2.Fields(8).Value = Ccy1
                                rsmdb2.Update

                            End If

                            rsmdb.Fields(15).Value = Total2
                            If Total2 <> 0 Then

                                rsmdb2.AddNew
                                rsmdb2.Fields(0).Value = "小計"
                                rsmdb2.Fields(7).Value = Total2
                                rsmdb2.Fields(8).Value = Ccy1
                                rsmdb2.Update

                            End If

                        End If
                    Else

                        Ccy2 = Ccy1
                        Ccy1 = rsmdb.Fields(7).Value
                        If i <> 1 Then

                            rsmdb.Fields(15).Value = Total
                            If Total <> 0 Then

                                rsmdb2.AddNew
                                rsmdb2.Fields(0).Value = "小計"
                                rsmdb2.Fields(7).Value = Total
                                rsmdb2.Fields(8).Value = Ccy2
                                rsmdb2.Update

                            End If
                            Total = 0

                            rsmdb.Fields(15).Value = Total2
                            If Total2 <> 0 Then

                                rsmdb2.AddNew
                                rsmdb2.Fields(0).Value = "小計"
                                rsmdb2.Fields(7).Value = Total2
                                rsmdb2.Fields(8).Value = Ccy2
                                rsmdb2.Update

                            End If
                            Total2 = 0
                        End If

                        If rsmdb.Fields(6).Value > 0 Then
                            Total = Total + rsmdb.Fields(6).Value
                            rsmdb.Fields(15).Value = Total
                        Else
                            Total2 = Total2 + rsmdb.Fields(6).Value
                            rsmdb.Fields(15).Value = Total2
                        End If

                        rsmdb2.AddNew
                        rsmdb2.Fields(0).Value = "OD"
                        rsmdb2.Fields(1).Value = rsmdb.Fields(0).Value
                        rsmdb2.Fields(2).Value = rsmdb.Fields(1).Value
                        rsmdb2.Fields(3).Value = rsmdb.Fields(2).Value
                        rsmdb2.Fields(4).Value = rsmdb.Fields(3).Value
                        rsmdb2.Fields(5).Value = rsmdb.Fields(4).Value
                        rsmdb2.Fields(6).Value = rsmdb.Fields(5).Value
                        rsmdb2.Fields(7).Value = rsmdb.Fields(6).Value
                        rsmdb2.Fields(8).Value = rsmdb.Fields(7).Value
                        rsmdb2.Fields(9).Value = rsmdb.Fields(8).Value
                        rsmdb2.Fields(10).Value = rsmdb.Fields(9).Value
                        rsmdb2.Fields(11).Value = rsmdb.Fields(10).Value
                        rsmdb2.Fields(12).Value = rsmdb.Fields(11).Value
                        rsmdb2.Update

                        If i = rsmdb.RecordCount Then
                            rsmdb.Fields(15).Value = Total
                            If Total <> 0 Then
                                rsmdb2.AddNew
                                rsmdb2.Fields(0).Value = "小計"
                                rsmdb2.Fields(7).Value = Total
                                rsmdb2.Fields(8).Value = Ccy1
                                rsmdb2.Update
                            End If
                            rsmdb.Fields(15).Value = Total2
                            If Total2 <> 0 Then
                                rsmdb2.AddNew
                                rsmdb2.Fields(0).Value = "小計"
                                rsmdb2.Fields(7).Value = Total2
                                rsmdb2.Fields(8).Value = Ccy1
                                rsmdb2.Update
                            End If
                        End If
                    End If
                marked2 = True
                rsmdb.Fields(15).Value = 0
                rsmdb.MoveNext
            Next i

        End If

        MsgBox "完成"

    End Sub

    Private Sub Form_Load()

        'Bank
        SQLSRV1 = "Provider=SQLOLEDB.1;" _
            & "Password=7777777;Persist Security Info=True;User ID=sa;" _
            & "Initial Catalog=CPA;Data Source=TEST\TESTSERVER"

        DBname = "Bank"

        Set cnmdb = New ADODB.Connection
        cnmdb.ConnectionString = SQLSRV1
        cnmdb.CursorLocation = adUseClient
        cnmdb.Open

        Set rsmdb = New ADODB.Recordset
        query = "SELECT * FROM " & DBname
        rsmdb.Open query, cnmdb, adOpenStatic, adLockOptimistic

        Set DataGrid1.DataSource = rsmdb

    End Sub

    2007年4月16日 上午 03:21