Joining multiple tables in MS Access

The syntax is tricky and I do it every once in a while and look it up every time (because the SQL errors in MS Access are far from being user-friendly). This site here has the general idea. But the details are below:

SELECT a.AField, b.BField, c.CField
FROM TableA AS a INNER JOIN (TableB AS b INNER JOIN TableC AS c ON b.BKey = c.BForeignKey)
ON a.AKey = b.BForeignKey

And, by the way:

TableB AS b INNER JOIN TableC AS c ON b.BKey = c.BForeignKey AND b.BField2 = 'someValue' AND c.CField2 = 'someValue'

join condition is not supported in Access, you have to put the join by some value in the table condition into the WHERE clause:

TableB AS b INNER JOIN TableC AS c ON b.BKey = c.BForeignKey 
...
WHERE b.BField2 = 'someValue' AND c.CField2 = 'someValue'

Implementing your own keyboard on Windows Phone using MVVM pattern

In my previous article on MVVM pattern in Windows phone, I explained the entire model. Today, I will explain the usage of same pattern on Windows Phone in simpler terms. I will show how to implement a bunch of buttons (which can be repurposed for keyboard) on Windows Phone using MVVM. Let's start with a view:

    <Grid x:Name="LayoutRoot" Background="Transparent">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="Auto"/>
        </Grid.RowDefinitions>
        
        <TextBlock Text="{Binding MyText}" />

        <ItemsControl ItemsSource="{Binding MyButtons}" HorizontalAlignment="Center" Padding="0" Grid.Row="1"
                              Style="{StaticResource HorizontalStackPanel}" />

    </Grid>

Quite a standard phone page, the first row contains the property we will be changing through the buttons that we create. The second row contains the ItemsControl that will display the collection of newly-created buttons.

What is non-standard about it is the style definiton for ItemsControl (HorizontalStackPanel). The reason for it is that by default, ItemsControl lays out its children in vertical fashion. But if you add the following code into App.xaml file in <Application.Resources> block, your items will be laid out horizontally:

        <Style x:Key="HorizontalStackPanel" TargetType="ItemsControl">
            <Setter Property="ItemsPanel">
                <Setter.Value>
                    <ItemsPanelTemplate>
                        <StackPanel Orientation="Horizontal" />
                    </ItemsPanelTemplate>
                </Setter.Value>
            </Setter>
        </Style>

Now let's get to the code-behind page, pretty easy too:

public MainPage()
{
    InitializeComponent();
    this.DataContext = new ButtonsViewModel();
}

And now let's get to the ButtonsViewModel. First, implementing the MyText property in accordance with MVVM:

        private string _myText;
        public string MyText
        {
            get { return _myText; }
            set
            {
                if (_myText == value)
                    return;
                _myText = value;
                this.RaisePropertyChanged("MyText");
            }
        }

Then, implementing the plumbing for INotifyPropertyChanged interface:

//INotifyPropertyChanged implementation
        public event PropertyChangedEventHandler PropertyChanged;
        public void RaisePropertyChanged(string propertyName)
        {
            PropertyChangedEventHandler handler = this.PropertyChanged;
            if (handler != null)
            {
                handler(this, new PropertyChangedEventArgs(propertyName));
            }
        }

And here we implement the MyButtons property by creating a list of Buttons and adding Buttons one-by-one:

//Buttons property
        public List<Button> MyButtons
        {
            get
            {
                List<Button> buttons = new List<Button>();
                buttons.Add(MakeAButton("q");
                buttons.Add(MakeAButton("w");
                buttons.Add(MakeAButton("e");
                buttons.Add(MakeAButton("r");
                buttons.Add(MakeAButton("s");
                return buttons;
            }
        }
//Button plumbing
        private Button MakeAButton(string letter)
        {
            Button b = new Button
            {
                CommandParameter = letter,
                Content =  new TextBlock { Text = letter, FontSize = 24, FontFamily = new System.Windows.Media.FontFamily("Segoe UI Mono") },
                Height=80
            };
            b.Click += b_Click; 
            return b;
        }
//The magic
        void b_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            string a = (sender as Button).CommandParameter.ToString();
            SomeText += a;
        }

This example is not a classic MVVM implementation, it uses the Button Click event to alter the value of the MyText property. The classic implementation would be to use ICommand, but I chose not to do it here because this is a quick and easy example. Hopefully it can provide the feel for MVVM for those who are trying to understand the pattern.

A word of caution: if you need to fit a lot of buttons on the same row, you will be better off adjusting Margin property on each button.

Dealing with "Nested types are not supported" message while working on WPF styles.

I am working on implementing an interface with many buttons on Windows Phone. First thing I learned (the hard way) is that the StackPanel does not support adding controls to it dynamically. Oh well, ItemsControl to the rescue. The tricky part about the ItemsControl is that it lays out its children vertically by default. I did some customizations to help that:

<ItemsControl ItemsSource="{Binding MyProperty}" HorizontalAlignment="Center" Padding="0">  
  <ItemsControl.ItemsPanel>
    <ItemsPanelTemplate>
      <StackPanel Orientation="Horizontal" />
    </ItemsPanelTemplate>
  </ItemsControl.ItemsPanel>
</ItemsControl>

All nice and pretty but my interface involves multiple rows of items. Naturally, I can use styles to help me with that! So I open up App.xaml and I start typing:

<Style x:Key="HorizontalStackPanel" TargetType="ItemsControl">
  <Setter Property="Padding" Value="0" />
    <Setter Property="ItemsPanel" Value=...

And this is where I got lost a bit, because the style does not accept nested definitions. Luckily, MSDN has a great example here. This is what I ended up writing:

<Style x:Key="HorizontalStackPanel" TargetType="ItemsControl">
  <Setter Property="ItemsPanel">
    <Setter.Value>
      <ItemsPanelTemplate>
        <StackPanel Orientation="Horizontal"
                    VerticalAlignment="Center"
                    HorizontalAlignment="Center" />
      </ItemsPanelTemplate>
    </Setter.Value>
  </Setter>
</Style>

And everything is working just fine. So this is how anyone can deal with the "Nested types are not supported" message while setting their styles in a somewhat complex way.