assign recordset to sql database using vb6

Question

HI,

I have created recordset in vb6 and stored values that i read from txt file. when iam trying to execute the sql query which will insert my recordset data into dataase table . I am getting error like
''' either EOF or BOF is true, or the current recoed has been deleted. requested operation requires current record. """

I am just attaching my code can anyone pls help where i am doing wrong. i believe there is mistake in prasing the individual name. But i dont know what it is.

Any help will be appreciated.

Public Function ProcessDNE(ByVal strFileName As String) As Boolean

Dim intFileNbr As Integer
Dim strCurrentLine As String
Dim strRoutingNbr As String
Dim strAcct As String
Dim strIndividualName As String
Dim strAmount As String
Dim curAmount As Currency
Dim strParseString As String
Dim strParseFirstNm As String
Dim strParseMidInit As String
Dim strParseLastNam As String
Dim lngMidInitPos As Long
Dim lngParsePos1 As Long
Dim lngParsePos2 As Long
Dim lngParsePos3 As Long
Dim lngParsePos4 As Long
Dim lngParsePos5 As Long
Dim lngParsePos6 As Long
Dim lngPos As Long

    frmDNELoad.lblStatus.Caption = "Reading File..."
    frmDNELoad.Refresh

    '' # Set up rcdDNE structure
    With rcdDNE.Fields
        .Append "RTN", adVarChar, 9
        .Append "AccountNbr", adVarChar, 17
        .Append "IndividualName", adVarChar, 22
        .Append "FirstName", adVarChar, 50
        .Append "MiddleName", adVarChar, 1
        .Append "LastName", adVarChar, 50
        .Append "Amount", adCurrency
    End With

    rcdDNE.Open
    intFileNbr = FreeFile(1)
    Open strFileName For Input As #intFileNbr Len = 95 '' # Open file for input.
    Do While Not EOF(intFileNbr)
       Line Input #intFileNbr, strCurrentLine
       If Mid(strCurrentLine, 1, 1) = 6 Then
         strRoutingNbr = Mid(strCurrentLine, 4, 8)
         strAcct = Trim(Mid(strCurrentLine, 13, 17))
         strIndividualName = Trim(Mid(strCurrentLine, 55, 22))
         strAmount = Trim(Mid(strCurrentLine, 30, 10))
         strAmount = Left(strAmount, Len(strAmount) - 1)
         curAmount = CCur(strAmount)

       '' # Add new record to temporary recordset
            With rcdDNE
                .AddNew
                .Fields![RTN] = strRoutingNbr
                .Fields![AccountNbr] = strAcct
                .Fields![IndividualName] = strIndividualName
                .Fields![Amount] = curAmount
                .Update
            End With
       End If
    Loop
    Close #intFileNbr

    frmDNELoad.lblStatus.Caption = "Formatting Names..."
    frmDNELoad.Refresh
    DoEvents

    '' # Parse the IndividualName field
    rcdDNE.MoveFirst
    Do Until rcdDNE.EOF
        lngMidInitPos = 0
        lngParsePos1 = 0
        lngParsePos2 = 0
        lngParsePos3 = 0
        lngParsePos4 = 0
        lngParsePos5 = 0
        lngParsePos6 = 0
        strParseString = ""
        strParseFirstNm = ""
        strParseMidInit = ""
        strParseLastNam = ""

        strParseString = Trim(rcdDNE.Fields![IndividualName])
        '' # Replace double spaces ("  ") with a single space (" ")
        lngPos = InStr(1, strParseString, "  ")
        Do While lngPos
            strParseString = Mid(strParseString, 1, lngPos - 1) & Mid(strParseString, lngPos + 1, Len(strParseString))
            lngPos = InStr(1, strParseString, "  ")
        Loop

        '' # Locate positions of remaining spaces
        lngParsePos1 = InStr(1, strParseString, " ")
        If lngParsePos1 = 0 Then
            lngParsePos2 = 0
        Else
            lngParsePos2 = InStr(lngParsePos1 + 1, strParseString, " ")
        End If

        If lngParsePos2 = 0 Then
            lngParsePos3 = 0
        Else
            lngParsePos3 = InStr(lngParsePos2 + 1, strParseString, " ")
        End If
        If lngParsePos3 = 0 Then
            lngParsePos4 = 0
        Else
            lngParsePos4 = InStr(lngParsePos3 + 1, strParseString, " ")
        End If
        If lngParsePos4 = 0 Then
            lngParsePos5 = 0
        Else
            lngParsePos5 = InStr(lngParsePos4 + 1, strParseString, " ")
        End If
        If lngParsePos5 = 0 Then
            lngParsePos6 = 0
        Else
            lngParsePos6 = InStr(lngParsePos5 + 1, strParseString, " ")
        End If

        '' # Determine if Middle initial is present
        If (lngParsePos3 - lngParsePos2) = 2 Then
            lngMidInitPos = lngParsePos2 + 1
            rcdDNE.Fields![MiddleName] = Mid(strParseString, lngMidInitPos, 1)
        ElseIf (lngParsePos4 - lngParsePos3) = 2 Then
            lngMidInitPos = lngParsePos3 + 1
            rcdDNE.Fields![MiddleName] = Mid(strParseString, lngMidInitPos, 1)
        ElseIf (lngParsePos5 - lngParsePos4) = 2 Then
            lngMidInitPos = lngParsePos4 + 1
            rcdDNE.Fields![MiddleName] = Mid(strParseString, lngMidInitPos, 1)
        ElseIf (lngParsePos6 - lngParsePos5) = 2 Then
            lngMidInitPos = lngParsePos5 + 1
            rcdDNE.Fields![MiddleName] = Mid(strParseString, lngMidInitPos, 1)
        ElseIf (lngParsePos2 - lngParsePos1) = 2 Then
            lngMidInitPos = lngParsePos1 + 1
            rcdDNE.Fields![MiddleName] = Mid(strParseString, lngMidInitPos, 1)
        End If

        '' # If there is a middle initial, everything to the left of it goes into the
        '' # first name field, and everything to the right of it goes into the last
        '' # name field. If there is no middle initial, everything after the first space
        '' # goes into the last name field.
        If lngMidInitPos <> 0 Then
            rcdDNE.Fields![FirstName] = Trim(Left(strParseString, lngMidInitPos - 1))
            rcdDNE.Fields![LastName] = Trim(Mid(strParseString, lngMidInitPos + 1, Len(strParseString)))
        Else
            rcdDNE.Fields![FirstName] = Trim(Left(strParseString, lngParsePos1))
            rcdDNE.Fields![LastName] = Trim(Mid(strParseString, lngParsePos1 + 1, Len(strParseString)))
        End If
        rcdDNE.Update
        rcdDNE.MoveNext
    Loop

    '' # Write records to Database

    frmDNELoad.lblStatus.Caption = "Loading data into database......"
    Call FindServerConnection_NoMsg

'' #    Do Until rcdDNE.EOF
'' #   rcdDNE.MoveFirst
'' #    cmdCommand.CommandText = "insert into t_DATA_DneFrc (RTN, AccountNbr, FirstName, MiddleName, LastName, Amount) values ('" & rcdDNE("RTN") & "', '" & rcdDNE("AccountNbr") & "', '" & rcdDNE("FirstName") & "', '" & rcdDNE("MiddleName") & "', '" & rcdDNE("LastName") & "', '" & rcdDNE("Amount") & "')"
'' #    cmdCommand.Execute ()
'' #    rcdDNE.MoveNext
'' #    Loop

    Dim lngRecCount As Long
    lngRecCount = 0

    Set rcdReclamation = New ADODB.Recordset
    With rcdReclamation
        .ActiveConnection = objConn
        .Source = "insert into t_DATA_DneFrc (RTN, AccountNbr, FirstName, MiddleName, LastName, Amount) values ('" & rcdDNE("RTN") & "', '" & rcdDNE("AccountNbr") & "', '" & rcdDNE("FirstName") & "', '" & rcdDNE("MiddleName") & "', '" & rcdDNE("LastName") & "', '" & rcdDNE("Amount") & "')"
        .CursorType = adOpenDynamic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open , , , , adCmdStoredProc
    End With

    rcdDNE.MoveFirst
    Do Until rcdDNE.EOF
        lngRecCount = lngRecCount + 1
        frmDNELoad.lblStatus.Caption = "Adding record " & lngRecCount & " of " & rcdDNE.RecordCount & " to database."
        frmDNELoad.Refresh

        DoEvents
        Call CommitNew
        rcdDNE.MoveNext
    Loop

    frmDNELoad.lblStatus.Caption = "DNE Processing Complete."
    frmDNELoad.Refresh

End Function

Answer 1

My guess is that the error occurs on 'rcdReclamation.Source = ...' because you're accessing items from the rcdDNE recordset in building the query. Just prior to your setup of rcdReclamation, you ran through a loop of rcdDNE until it was EOF. So when you're creating the query, rcdDNE is guaranteed to return EOF.

Edit: Possible solution It's been a bit since I used recordsets this way (I generally just use queries and execute them on the connection) so I might be off a bit. To actually save all the records to the database, you need to call UpdateBatch. When using AddNew to create records and Update to save changes, you need to call UpdateBatch at the end of all the adding/editing to commit the changes to the database. So I would replace everything from the "Loading data into database......" line to the end with a simple rcdDNE.UpdateBatch.

Answer 2

HI I AM TRYING TO RETRIEVE A VALUE FROM DATABASE(SQL2K SERVER) USING VB6.0 I HAVE TO USE THIS COMMAND OF SQL "SELECT MAX(COL-NAME) FROM TABLE-NAME" BUT HOW THIS IS USED IN VB6.0 FOR FETCHING THE MAX VALUE. PLEASE SEND THE............. THANKS