Go Premium for a chance to win a PS4. Enter to Win

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
?
859 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 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

963 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