Solved

Excel 3021 VBA error on blank/null field when running query to access SQL 2005 database

Posted on 2010-11-09
4
838 Views
Last Modified: 2012-06-27
Hi
I am running a query from Excel 2007 VBA to extract particular fields from my SQL database.  Soome of the fields are NULL or blank.  When the code hits these fields I get a 3021 error.  Please see code below:

Sub getDetail()

Dim rs As ADODB.Recordset
Dim con As New ADODB.Connection
Dim LastCell As Integer
Dim osheet As Worksheet

On Error GoTo errHandler

    Set con = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
    iRow = 1
   
    'Select worksheet
    Sheets("ACCT").Select
   
    'specify server
    dataServer = "Database"
   
    'open conncetion
    con.Open "Driver={SQL Server};Server=" & dataServer & ";Database=TEST;Trusted_Connection=yes;"
   
   'Select worksheet
    Sheets("ACCT").Select

     'get last cell with Data
    LastCell = ActiveSheet.UsedRange.Rows.Count
     
   'start processing loan numbers
    For counter = 2 To LastCell
 
            'Get Number
        Set curCell = Worksheets("TD").Cells(counter, 1)
        LNumber = curCell.Value

                 With rs
                     .ActiveConnection = con
                 
                    .Open "SELECT SRNumber,LNumber,CreationDate, " & _
                           "ERSCompleteDate,SRStatus,PrimaryCategory, " & _
                           "CompletionType,RType,Workgroup, " & _
                           "ResolutionSummary " & _
                           "FROM " & _
                           "VW_ECRData " & _
                           "WHERE LNumber = '" & LNumber & "' "
                            iRow = iRow + 1
                            iCol = 2
                            'copy records
                            If Not rs.EOF Then
                               
                                   'Do Until rs.EOF
                                    For i = 0 To rs.Fields.Count - 1
                                        'If IsNull(rs("ERSCompleteDate")) Then
                                        Sheets("TD").Cells(iRow, iCol).Value = rs.Fields.Item(i).Value
                                        'End If
                                        iCol = iCol + 1
                                        'iRow = iRow + 1
                                        rs.MoveNext
                                    Next i
                                   'Loop
                            Else
                               Sheets("TD").Cells(iRow, 2).Value = "CLOSED"
                               appErr = True
                            End If
                 
                  'rs.MoveNext
                'cleanup rs
                .Close
                End With
          Else
         
      End If
    Next

    'cleanup
    con.Close
    Set rs = Nothing
    Set con = Nothing
   
errHandler:

If Err.Number = 3021 Then
    rs.MoveFirst
    Resume Next
ElseIf Err.Number = -2147217871 Then
    MsgBox "Sorry, server is busy, please try again later!", vbInformation, "SERVER BUSY"
    Exit Sub
End If
End Sub

As you can see, I am currently using an Error Handler which will skip the field, but I am not sure is this is the best way to do this and wondering what issues it will cause.

Also, In some instances I'm getting the 3021 error even when the field contains data.  
0
Comment
Question by:DMUM
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 125 total points
ID: 34099787
I have had this issue also and used a function to trap nulls

you will note I changed the Open function to build the Sql string separately as I find it much easier to debug when I can see exactly what sqlquery has been built by my code as I frequently get syntax wrong and can rebuild or make test queries on the fly.

I also have a function that converts Null to zero for numeric data; though the BlankNull generally does the same thing as the string "" is converted to zero when assigned to a numeric variable.

'start processing loan numbers
    For counter = 2 To LastCell
 
            'Get Number
        Set curCell = Worksheets("TD").Cells(counter, 1)
        LNumber = curCell.Value

                 With rs
                     .ActiveConnection = con
                    
                    sqlQ = "SELECT SRNumber,LNumber,CreationDate, " & _
                           "ERSCompleteDate,SRStatus,PrimaryCategory, " & _
                           "CompletionType,RType,Workgroup, " & _
                           "ResolutionSummary " & _
                           "FROM " & _
                           "VW_ECRData " & _
                           "WHERE LNumber = '" & LNumber & "' "
                    .Open sqlQ
                            iRow = iRow + 1
                            iCol = 2
                            'copy records
                            If Not rs.EOF Then
                               
                                   'Do Until rs.EOF
                                    For i = 0 To rs.Fields.Count - 1
                                        'If IsNull(rs("ERSCompleteDate")) Then

                                        Sheets("TD").Cells(iRow, iCol).Value = BlankNull(rs.Fields.Item(i).Value)  '<<<<<< call the trap function
                                        'End If
                                        iCol = iCol + 1
                                        'iRow = iRow + 1
                                        rs.MoveNext
                                    Next i
                                   'Loop
                            Else
                               Sheets("TD").Cells(iRow, 2).Value = "CLOSED"
                               appErr = True
                            End If
                 
                  'rs.MoveNext
                'cleanup rs
                .Close
                End With
          Else
         
      End If
    Next

'function to turn db nulls or empty into blank strings
Function BlankNull(dbdata As Variant) As Variant
    If IsNull(dbdata) Then
        BlankNull = ""
     ElseIf IsEmpty(dbdata) Then
        BlankNull = ""
     Else
        BlankNull = dbdata
    End If
End Function

Open in new window

0
 

Author Comment

by:DMUM
ID: 34398510
Hi, I do want to close this and accept the last solution provided.  I apologize for the delay, but I was pulled onto another project and was not able to get back to this before going on vacation and since my return have just been able to get back to this, but not the task itself.  Thank you
0
 

Author Closing Comment

by:DMUM
ID: 34398512
Thank you
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now