runtime error 3704 application defined or object defined error

Hi!

I have the following code that gets start date from a cell, end date from another cell and previous date from a thrid cell. Then it runs a stored proc off SQL Server with those 3 parameters and should spit the recordset outon another worksheet.

Sub Billing_Report()
 

  'Clear previous contents
Sheet1.Range("A9:AZ5000").Select
Selection.ClearContents


Sheet1.Range("C3").Value = "(for the period of " & Sheet2.Range("E10").Value & " to " & Sheet2.Range("E20").Value & ")"


   'release the selection from the previous step
Sheet1.Range("E5").Select


 'initialize variables with data in those cells
Start_Date = Sheet2.Range("E10").Value
End_Date = Sheet2.Range("E20").Value
Prev_Date = Sheet2.Range("E30").Value



    'checking if end_date happens earlier than start_date
If End_Date < Start_Date Then
        MsgBox "Start Date must be earlier than End Date. Check your selection and try again"
        Worksheets("Selection Data").Activate
        Exit Sub
End If


    'checking if prev_date happens after than start_date
If Prev_Date > Start_Date Then
        MsgBox "Previous Date must be earlier than Start Date. Check your selection and try again"
        Worksheets("Selection Data").Activate
        Exit Sub
End If


 'Declare the variables
Dim conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim strSQL As String, ConnStr As String


  'Set the connection string
ConnStr = "Provider=SQLOLEDB;Initial Catalog=master; Data Source=dr-ny-sql001; " & _
"INTEGRATED SECURITY=sspi;"
 
 
 'create  sql string for stored proc
strSQL = "exec usp_DR_Monthly_Billing_Report '" & Start_Date & "', '" & End_Date & "', '" & Prev_Date & "'"

'MsgBox strSQL
 
 
  'create and open the connection and recordset
Set conn = New ADODB.Connection
Set Rs = New ADODB.Recordset
conn.CommandTimeout = 0
conn.Open ConnStr
Rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly


  'Instantiate the Recordsetobject and execute the question.
Set Rs = conn.Execute(strSQL)


'If conn.State = adStateClosed Then
        'MsgBox "connection is closed"
'Else
        'MsgBox "connection is open"
'End If


'If Rs.State = 1 Then
        'MsgBox "Recordset is closed"
'Else
        'MsgBox "Recordset is open"

'If Rs.RecordCount > 0 Then MsgBox "The report has been populated with " & Sheets("Selection Data").ComboBox1.Value & " data from " & Sheets("Selection Data").Calendar1.Value & " to " & Sheets("Selection Data").Calendar2.Value Else If Rs.RecordCount <= 0 Then MsgBox "There is no data for " & Sheets("Selection Data").ComboBox1.Value & " for the period from " & Start_Date & " to " & End_Date

'If Not Rs.EOF Then
             'MsgBox "The report has been populated with data from " & Start_Date & " to " & End_Date
       ' Else
             'MsgBox "There is no data for the period from " & Start_Date & " to " & End_Date
'End If


  'Dump the records into the worksheet.
Sheet1.Range("A9").CopyFromRecordset Rs

 
   'Release objects from memory.
If Rs.State = 1 Then Rs.Close
    Set Rs = Nothing
    conn.Close
    Set conn = Nothing

 

' Exit the macro so that the error handler is not executed.
      Worksheets("Selection Data").Activate


'WrongDate:

     'MsgBox "End Date cannot happen before Start Date. Please check your selection and try again."



'Errhandler:

      'MsgBox "Error # " & Err & " : " & Error(Err) & ". Please make a note of this error message and contact Technical Support."

' Exit the macro so that the error handler is not executed.
      'Exit Sub
 
End Sub



I am getting runtime error 3704 application defined or object defined error
when hitting the following line:

  'Dump the records into the worksheet.
Sheet1.Range("A9").CopyFromRecordset Rs


Any help will be greatly appreciated.

Thanks!

JTStart Free Trial