Linq to Bindingsource

Issue: I want to build a dynamic treeview that 1) only loads the children when a parent is selected. 2) displays how many children in the node name. 3) works with filters already performed against the bindingsource.

The ideal way (in my mind at least) was to run sql against the bindingsource, that way the filters would be maintained. I do know a bit of linq and really hoped there was a linq to bindingsource; I didn't find anything showing this.
In the past, I have changed the commandtext of the tableadapter itself, but as I implemented it, I really didn't want to go that route because it meant maintaining multiple dynamic queries and filters that had to match back to the bindingsource filters.
So, after hours of work I gave up for the weekend. As often happens, I started from scratch this morning and had an epiphany: the tableadapter.fill is filling a data table, surely I can linq to that. Sure enough, a simple linq query worked! Now I can run a distinct linq query and enumerate the results to build my tree nodes.

My Settings (persisting data)

My Settings is a way to store data and settings used by your program for use on elsewhere (or at a later time. Say a user wants currency to show Euros instead of dollars, you can save this preference in My.Settings. Here's how:


Start by building a form with 2 checkboxes, a textbox, and a button.




Next go to Project->'yourAppName' Properties




Choose Settings on the left and create some properties to store your data:



Note that the default is String, but this can be changed to almost any data type. For collections of data you want to store (like history) choose StringCollection.





Once you choose Boolean as your type like the example above, False is the default value, you can change this and use it how you like.

One unintuitive (to me at least) items was the string collection. For this to be setup for easy use, you need to click the elipses (...) to the right and enter in a sample piece of data. If you want there to be standard entries, this is where you enter them:




Now you'll notice that some XML is stored in the value column, this is how My.Settings stores your data. Use the elipses to open the screen back up and delete what you just entered, we want it blank to start so you can see the results.

Double-click the button to get to the click event and enter the following:

My.Settings.CB1Value = Me.CheckBox1.Checked

My.Settings.CB2Value = Me.CheckBox2.Checked

My.Settings.TB1Value = Me.TextBox1.Text

My.Settings.Save()

This changes the values in the My.Settings to our checkbox and textbox values and then saves them, the values will stay until you close the program but will then clear back to the default unless you save them.

If you run the program, type some data in the textbox and then check one or both of the checkboxes and click your button. If you now go back to the Setting tab in your properties screen, you will see that nothing looks changed. This is because the data is saved in a fairly safe location that the average Joe wouldn't know how to get to. This is pretty secure, but I wouldn't go putting my SSN and credit card numbers in there without more security.

To view the data saved in the My.Settings, we need to bind that data to something, so add a listbox and another button to your form. On the click event of the new button add the following:

Me.ListBox1.DataSource = My.Settings.SettingsColl

Before we run this, we want to concatenate all of our options into one string that we can save to be used later. You can think of this in terms of a survey, we want to record all of the responses in one line per person. We will do that in a seperate routine that we will call from the first button click:

Sub storestring()

Dim myString As String

myString = Me.CheckBox1.Checked

myString &= "~" & Me.CheckBox2.Checked

myString &= "~" & Me.TextBox1.Text

My.Settings.SettingsColl.Add(myString)

My.Settings.Save()

End Sub

And the code to add to the first button click:

storestring()

Now run the project again, this time put something different in the textbox and select a different combination of checkboxes. Now click the second button.

As you can see, the data we put in is stored in a string collection in the listbox, each item seperated by a ~.

Doubleclicking on the listbox should bring up the event for SelectedIndexChanged. In this event place the following code:

Dim currentString As String
currentString = Me.ListBox1.SelectedItem.ToString()
Me.CheckBox1.Checked = Split(currentString, "~")(0)
Me.CheckBox2.Checked = Split(currentString, "~")(1)
Me.TextBox1.Text = Split(currentString, "~")(2)

What this does is parses the string, and sets the values of our controls to the saved values each time a user makes a selection in the listbox.

Thats it, there are endless possibilities with My.Settings and I'm sure you will find many uses for them. Feel free to post any questions or unique usages.

Here is the entire code:

Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
My.Settings.CB1Value = Me.CheckBox1.Checked
My.Settings.CB2Value = Me.CheckBox2.Checked
My.Settings.TB1Value = Me.TextBox1.Text
My.Settings.Save()
storestring()
End Sub
Sub storestring()
Dim myString As String
myString = Me.CheckBox1.Checked
myString &= "~" & Me.CheckBox2.Checked
myString &= "~" & Me.TextBox1.Text
My.Settings.SettingsColl.Add(myString)
My.Settings.Save()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.ListBox1.DataSource = My.Settings.SettingsColl
End Sub
Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim currentString As String
currentString = Me.ListBox1.SelectedItem.ToString()
Me.CheckBox1.Checked = Split(currentString, "~")(0)
Me.CheckBox2.Checked = Split(currentString, "~")(1)
Me.TextBox1.Text = Split(currentString, "~")(2)
End Sub
End Class

Pan Image with click and drag

Someone on MSDN asked a question about clicking and draging an image to pan around while zoomed. The control already did the zoom and consists of a picturebox within a panel with autoscroll turned on. This would let you use the scrollbars to navigate, but the poster wanted to pan by dragging so here it is. I will add to it as needed but this seemed to work pretty well.


Public Class Form1
Dim x1, y1 As Double
Dim scrollSensitivity As Double
Dim md As Boolean
Private Sub PictureBox1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles PictureBox1.MouseDown
md = True
x1 = Me.Panel1.HorizontalScroll.Value
y1 = Me.Panel1.VerticalScroll.Value
End Sub
Private Sub PictureBox1_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles PictureBox1.MouseMove
scrollSensitivity = 4
If md = True Then
Try
Me.Panel1.HorizontalScroll.Value = (e.X - x1) / scrollSensitivity
Me.Panel1.VerticalScroll.Value = (e.Y - y1) / scrollSensitivity
Catch ex As Exception
End Try
End If
End Sub
Private Sub PictureBox1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles PictureBox1.MouseUp
md = False
End Sub
End Class


VBA: Copy x sheet from each file in folder

Sub CopySheet()
Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Dim filePath As String
filePath = "C:\Documents and Settings\user\Desktop\Baskets\"
    Application.ScreenUpdating = False
    With Application.FileSearch
        .NewSearch
        .LookIn = filePath
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute() > 0 Then
            Set basebook = ThisWorkbook
            For i = 1 To .FoundFiles.Count
                Set mybook = Workbooks.Open(.FoundFiles(i))
                    mybook.Worksheets("What Sells With My Item").Copy after:= _
                    basebook.Sheets(1)
                    ActiveSheet.Name = Mid(mybook.Name, 1, Len(mybook.Name) - 4)
               mybook.Close
        
           Next i
       End If
     
    End With
    Application.ScreenUpdating = True
End Sub

VBA: Random Letter generator

Private Sub MakeRandom()
Dim theNum As Double
Dim theUpper As String
Dim theLower As String

' Upper Case
theUpper = Chr(64 + Rnd() * 10000000 Mod 26)
' Lower Case
theLower = Chr(96 + Rnd() * 10000000 Mod 26)

End Sub

VBA: Manipulate registry *MAKE A BACKUP FIRST*

*Not responsible for those who mess up and didn't backup.

        Dim pRegKey As RegistryKey = Registry.CurrentUser
        pRegKey = pRegKey.OpenSubKey("Software\\Microsoft\\Internet Explorer\Main", True)
        'Dim val As Object = pRegKey.GetValue("Display Inline Images")
        pRegKey.SetValue("Display Inline Images", "no")

VBA: Extract all data from a closed workbook

Dim ConString As String
Dim strSQL    As String
Dim DBPATH    As String
Dim recordset As New ADODB.recordset

DBPATH = ThisWorkbook.Path & "\Data.xls"

ConString = "Provider=Microsoft.jet.oledb.4.0;" & _
                   "Data Source=" & DBPATH & ";" & _
                   "extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [Data$]"

Set recordset = New ADODB.recordset

On Error GoTo cleanup:

Call recordset.Open(strSQL, ConString, adOpenForwardOnly, adLockReadOnly,
CommandTypeEnum.adCmdText)

Call Sheets("DataDump").Range("A2").CopyFromRecordset(recordset)

Set recordset = Nothing