none
Visual Studio 2010 Excel Project retrieve data from SQL Server 2008 R2 Stored Procedure RRS feed

  • Question

  • I have created a stored procedure using SQL Server 2008 R2 that has dynamic parameters. I am trying to import the results to an excel spreadsheet. I know that this can be done straight from SQL Server Management Studio or straight from excel without using any VBA. However I am trying to create an end-user application that will do this all in excel with the click of a button and selection of two parameters.

    I am a newbie to the Visual Studio environment and am running version 2010 Premium. I am a little familiar with establishing the connection to the server, but really just the basics as described in the "Walkthrough: Simple Databinding in a Document Level Project" This was a good start but I am having trouble because of the dynamic parameters. 

    THANKS!!!


    Saturday, April 6, 2013 4:27 AM

All replies

  • Hi Aguevaraf,

    Thank you for posting in the MSDN Forum.

    Could you please elaborate

    I am having trouble because of the dynamic parameters.

    You can post a code snippet on which you stuck so as that we can work it out together.

    Best regards, 


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, April 9, 2013 1:20 PM
    Moderator
  • I have been working on this a little bit and have made quite some headway. However I am getting the following run-time error.

    Run-time error '-2147217900 (80040e14)'


    Here is what I have so far in VBA

    _________________________________________________________________________________

    Public PeriodEndDate As String
    Public LastYearClosed As String



    Public Sub SQLQuery()

    ' Create a connection object.
    Dim cnDatabase As ADODB.Connection
    Set cnDatabase = New ADODB.Connection

    ' Provide the connection string.
    Dim strConn As String
    Dim SQLString As String

    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB.1;"

    'Connect to the Pubs database on the local server.
    strConn = strConn & "DATA SOURCE=mycomputer;INITIAL CATALOG=mydatabase;"

    'Use an integrated login.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"

    'Now open the connection.
    cnDatabase.Open strConn
                        

    ' Create a recordset object.
    Dim rsRecords As ADODB.Recordset
    Set rsRecords = New ADODB.Recordset
    PeriodEndDate1 = Cells(2, 9)
    PeriodEndDate2 = Cells(3, 9)
    LastYearClosed = Cells(2, 10)

    ' Create sqlstring.
    SQLString = SQLString & "select sOrigTransSourceIDf,dtmPostTo,sDescription,scodeidf_1 as Fund, sCodeIDf_0 as GL , SUM(curAmount)as 'Amount'" & _
    "from tblDLTrans" & _
    "where sCodeIDf_0 = '2101' and dtmPostTo between" & "'" & PeriodEndDate1 & "'" & "," & "'" & PeriodEndDate2 & "'" & _
    "group by sOrigTransSourceIDf,dtmPostTo,sDescription,scodeidf_1, sCodeIDf_0" & _
    "order by sOrigTransSourceIDf"

       
        
     
      Debug.Print SQLString
      
      
       With rsRecords
        ' Assign the Connection object.
        .ActiveConnection = cnDatabase
        ' Extract the required records.
        
         .Open SQLString

        Sheets("Object").Range("A:H").EntireColumn.Clear
        Cells(1, 1) = "Transaction Source"
        Cells(1, 2) = "Effective Date"
        Cells(1, 3) = "Description"
        Cells(1, 4) = "Fund"
        Cells(1, 5) = "GL"
        Cells(1, 6) = "Amount"
            
        'Copy the records into cell A2 on byobject.
        Sheets("Object").Range("A2").CopyFromRecordset rsRecords
        Columns("E:H").Select
        Selection.NumberFormat = "#,##0.00_);(#,##0.00)"
        Columns("C:D").Select
        Selection.NumberFormat = "0.00%"
       
        
        ' Tidy up
        .Close
    End With
    cnDatabase.Close
    Set rsRecords = Nothing
    Set cnDatabase = Nothing
    End Sub


    Wednesday, April 24, 2013 7:24 PM