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. 


    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
  • 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

        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
        Selection.NumberFormat = "#,##0.00_);(#,##0.00)"
        Selection.NumberFormat = "0.00%"
        ' Tidy up
    End With
    Set rsRecords = Nothing
    Set cnDatabase = Nothing
    End Sub

    Wednesday, April 24, 2013 7:24 PM