Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-11-09
4
Medium Priority
?
852 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

721 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