Retrieving data from a Stored Procedure (with parameters) in Microsoft Excel 2007 / 2010

I have a method here which allows you to add Parameters to the Stored Procedure from Excel.

There are quite a few steps, but I’ve broken it down quite alot.

The process is actually really easy once you know how... as with anything.

1. Open Excel
2. Select the Data Tab (If you’re using Excel 2007 onwards!)
3. Select the “From Other Sources” drop down box
4. Select “From SQL Server”
5. It will open up the following box
   
   
   
6. Enter “.\SQLExpress” (That’s Dot Backslash SQLExpress and it’s without the quotes) in the “Server name:” box
7. Depending on your Server Configuration, either leave the Log on credentials as “Use Windows Authentication”, or select the “Use the following User Name and Password” radio button and enter your SQL Server credentials
8. Hit “Next >”
9. You should then see the following screen:
   
   
   
10. In the “Select the database that contains the data you want:” Dropdown, select the database you wish to use.
11. In the list at the bottom, select a table, it doesn’t matter which you choose.
12. Press “Next >”
13. You should then be presented with the following screen:
    
    
    
14. In the “Filename” Textbox, set the name you would like to save the connection as, remembering to leave the “.odc” on the end.
15. If you want the Password to be stored with this connection, then check the “Save password in file” Checkbox, say “Yes” to the warning box.
16. In the “Description” enter something meaningful for this connection
17. In the “Friendly Name:” Textbox, set the name you would like to appear in the Recent Connections dialog. There’s no need for a “.odc” extension here.
18. Hit “Finish”
19. You should then be shown the following screen:
    
    
    
20. Click “Properties...”
21. The “Connection Properties” window will be shown:
    
    
    
22. Click the “Definition” tab, which will show:
    
    
    
23. If required click the “Save password” Checkbox, and press “Yes” to the warning box
24. Change the “Command Type” Dropdown to be “SQL”
25. Set the “Command text to “exec ” + the name of your Stored Procedure. If you stored procedure requires parameters then simply tack them on the end seperated by commas.
    
    e.g. exec GetUsersByAreaandName Eastern, Smith
    
    If you wish to pass in a Null, then simply type “NULL” as a parameter.
    
    e.g. exec GetUsersByAreaandName Eastern, NULL
    
26. Press “OK”, and Press “Yes” to the warning box
27. You should be shown the Import Data screen again, Press “OK”

If everything went according to plan, you should have all your data on screen!

Just started looking at Windows Workflow Foundation (WF) 4

I’ve just started looking at the Windows Workflow Foundation 4. Now in it’s fourth Generation, the Windows Workflow Foundation, or WF for short, is a completely different way to go about Programming.

WF allows a programmer to create applications using a graphical approach, such as Flowcharts, Sequence Diagrams and State Machines.

Complex programs can be created direct on a xaml designer workspace with very little in the way of lines of code.

I’ve been following a great series of screencasts by DevelopMentor instructor, freelance developer and MVP Maurice de Beijer.

In these screencasts, Maurice takes you through from beginning your first “Hello World” (or “Hello workflow” maybe?), all the way through to creating custom activity and error handling.

The Screencast series can be found here;

http://msdn.microsoft.com/en-us/netframework/dd733248

One thing which is missing from the series are the code samples however, so I’ve uploaded the code for first five Screencasts to my website in case anyone needs them. They’re available here…

http://www.pjgcreations.co.uk/public/WFScreenCasts/

Note however that, as a I am a VB.net programmer, these Code Samples are all in VB.net, however Maurice has chosen to use C#.

This isn’t a major problem as there’s not a massive amount of actual coding involved, and most of the code is very similar anyhow.

But, if you do need the C# version, I highly recommend you use the following free website to convert VB.net code to C#;

http://www.developerfusion.com/tools/convert/csharp-to-vb/

.EndEdit causes row movement of a sorted BindingSource

When applying a sort to a Database BindingSource, executing the BindingSource.EndEdit method causes the currently selected row to change.

This has the effect of reloading any bound controls when tee edited row is saved, which of course can have unwanted effects if you need to operating on data related to the currently selected row after the EndEdit has been performed.

This issue can also be observed to a degree by selecting a row in a bound DataGrid, then choose the sorted column. It can be seen that the physical location of the selected row doesn’t change, however the previously selected row has now moved.

There are a number of ways to deal with the EndEdit issue, one of which  I found here…

http://social.msdn.microsoft.com/Forums/en/winformsdatacontrols/thread/0878567a-ccb1-441b-a51d-3e014372e61b

Where we check the ListChanged event and the e.ListChangeType;

Private Sub bsBindingSource_ListChanged(ByVal sender As Object, ByVal e _

    As System.ComponentModel.ListChangedEventArgs) Handles _

    bsBindingSource.ListChanged

    If e.ListChangedType = _

        System.ComponentModel.ListChangedType.ItemMoved Then

        bsBindingSource.Position = e.NewIndex

    End If

End Sub

Another method is to store the current state of the form, i.e. editing, adding, duplicating, normal etc in an enum. then use the following code;

Private Sub bsBindingSource_ListChanged(ByVal sender As Object, ByVal e _

    As System.ComponentModel.ListChangedEventArgs) Handles _

    bsBindingSource.ListChanged

    If sttCurrentFormState = Normal Then

        bsBindingSource.Position = e.NewIndex

    End If

End Sub