How can I get this ComboBox to bind, populate the dropdown and write back selected value on update?

47 views Asked by At

I've tried everything to get a comboBox to retrieve a value from my database upon load and the update it. If there's a value in the column, comboBox should be filled with the selection. If it's null then pick from a list (Ey/Manual or Accepted/Rejected) and update the value when update command is called. Could someone point me in the right direction? No matter how much I try, the comboBox stays empty after the load and if I manage to write to the db it's this format instead of the value: "System.Windows.Controls.ComboBoxItem: Accepted"

Current behavior: the comboBox doesn't return and set the value after the load. Desired outcome: return value from db and set the comboBox to it. Also if I select a different value (Accepted/Rejected) and update, it will write the comboBox value to the db correctly without the entire string suffix "System.Windows.Controls.ComboBoxItem:"

XAML:

<Controls:MetroWindow 
        x:Class="BillingStatus.MainWindow"
        x:Name="BillingStatus"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:Controls="clr-namespace:MahApps.Metro.Controls;assembly=MahApps.Metro"
        xmlns:materialDesign="http://materialdesigninxaml.net/winfx/xaml/themes"     
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:BillingStatus"
        mc:Ignorable="d" 
        xmlns:sys="clr-namespace:System;assembly=mscorlib"
        xmlns:ViewModel="clr-namespace:BillingStatus.ViewModel">

    <Controls:MetroWindow.DataContext>
        <ViewModel:BillingStatusViewModel/>
    </Controls:MetroWindow.DataContext>


    <Grid>
        <Grid>
            <Grid.ColumnDefinitions>
                <ColumnDefinition Width="0.4*"/>
                <ColumnDefinition Width="3*"/>
                <ColumnDefinition Width="0.4*"/>
            </Grid.ColumnDefinitions>
        </Grid>

        <ScrollViewer VerticalScrollBarVisibility="Auto">
            <TabControl TabStripPlacement="Left" DockPanel.Dock="Top" Margin="20,211,0,0" ItemsSource="{Binding BillingStatusItems}" SelectedItem="{Binding SelectedBillingStatusItem}">
                <TabControl.ItemTemplate>
                    <DataTemplate>
                        <TextBlock Text="{Binding Location}" />
                    </DataTemplate>
                </TabControl.ItemTemplate>
                <TabControl.ContentTemplate>
                    <DataTemplate>
                        <Grid>
                            <Grid.ColumnDefinitions>
                                <ColumnDefinition Width="Auto" />
                                <ColumnDefinition Width="Auto" />
                                <ColumnDefinition Width="Auto" />
                                <ColumnDefinition Width="Auto" />
                                <ColumnDefinition Width="*" />
                            </Grid.ColumnDefinitions>

                            <Grid.RowDefinitions>
                                <RowDefinition Height="Auto" />
                                <RowDefinition Height="Auto" />
                                <RowDefinition Height="Auto" />
                                <RowDefinition Height="Auto" />
                                <RowDefinition Height="Auto" />
                                <RowDefinition Height="Auto" />
                            </Grid.RowDefinitions>

                            <Label Content="Reviewed By:" Grid.Row="0" Grid.Column="6" Margin="25" />
                            <TextBox Text="{Binding ReviewedBy, Mode=TwoWay}" Margin="25" Grid.Row="0" Grid.Column="7" Grid.ColumnSpan="3"/>

                            <Label Content="Upload Type:" Grid.Row="1" Grid.Column="0" Margin="25" />
                            <ComboBox ItemsSource="{Binding UploadTypes}" SelectedItem="{Binding SelectedUploadType, Mode=TwoWay}" Margin="25" Grid.Row="1" Grid.Column="1" Grid.ColumnSpan="2">
                            </ComboBox>

                        </Grid>
                    </DataTemplate>
                </TabControl.ContentTemplate>
            </TabControl>
        </ScrollViewer>
    </Grid>
</Controls:MetroWindow>

Code Behind:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;
using MahApps.Metro.Controls;
using BillingStatus.ViewModel;

namespace BillingStatus
{
    public partial class MainWindow : MetroWindow
    {
        public MainWindow()
        {
            InitializeComponent();
            DataContext = new BillingStatusViewModel();

        }

        private void btnUpdate_Click(object sender, RoutedEventArgs e)
        {
            if (DataContext is BillingStatusViewModel viewModel)
            {
                // Get the selected item from the view model
                BillingStatusItem selectedItem = viewModel.SelectedBillingStatusItem;

                // Call the UpdateRecordInDatabase method with the selected item
                UpdateRecordInDatabase(selectedItem);

                viewModel.LoadData();
            }
        }



        private void UpdateRecordInDatabase(BillingStatusItem item)
        {
             
            // Check if Location is provided and not null or empty
            if (string.IsNullOrEmpty(item.Location))
            {
                Messagebox.Show();
                return;
            }

            string connectionString = "data Source=; initial catalog=; user id=; password=";
            string updateQuery = "UPDATE ReviewedBy = @reviewedBy, UploadType = @uploadType WHERE Location = @location";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(updateQuery, connection);

                cmd.Parameters.AddWithValue("@location", item.Location);
                cmd.Parameters.AddWithValue("@reviewedBy", (object)item.ReviewedBy ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@uploadType", (object)item.SelectedUploadType ?? DBNull.Value);             

                // Set parameter types explicitly
                cmd.Parameters["@location"].SqlDbType = SqlDbType.NVarChar;
                cmd.Parameters["@reviewedBy"].SqlDbType = SqlDbType.VarChar;
                cmd.Parameters["@uploadType"].SqlDbType = SqlDbType.VarChar;

                try
                {
                    connection.Open();
                    int rowsAffected = cmd.ExecuteNonQuery();
                    if (rowsAffected > 0)
                    {
                        MessageBox.Show();
                    }
                    else
                    {
                        MessageBox.Show();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

    }
}

ViewModel:

using System;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Windows;

namespace BillingStatus.ViewModel
{
    public class BillingStatusViewModel : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

        private ObservableCollection<string> _locations;
        public ObservableCollection<string> Locations
        {
            get { return _locations; }
            set
            {
                _locations = value;
                OnPropertyChanged(nameof(Locations));
            }
        }

        private ObservableCollection<BillingStatusItem> _billingStatusItems;
        public ObservableCollection<BillingStatusItem> BillingStatusItems
        {
            get { return _billingStatusItems; }
            set
            {
                _billingStatusItems = value;
                OnPropertyChanged(nameof(BillingStatusItems));
            }
        }

        private BillingStatusItem _selectedBillingStatusItem;
        public BillingStatusItem SelectedBillingStatusItem
        {
            get { return _selectedBillingStatusItem; }
            set
            {
                _selectedBillingStatusItem = value;
                OnPropertyChanged(nameof(SelectedBillingStatusItem));
            }
        }


        private ObservableCollection<string> _uploadTypes;
        public ObservableCollection<string> UploadTypes
        {
            get { return _uploadTypes; }
            set
            {
                _uploadTypes = value;
                OnPropertyChanged(nameof(UploadTypes));
            }
        }
        
        public BillingStatusViewModel()
        {
            LoadData();
        }

        protected virtual void OnPropertyChanged(string propertyName)
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
        }


        public void LoadData()
        {
            string connectionString = "data Source=; initial catalog=; user id=; password=";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("SELECT Location, LocationStatus, ReviewedBy, " +
                                                 "UploadType, " +
                                                 "FROM dbo.BillingStatus WHERE LocationStatus = 'Open'", connection);

                try
                {
                    connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();

                    // Initialize collections
                    Locations = new ObservableCollection<string>();
                    BillingStatusItems = new ObservableCollection<BillingStatusItem>();
                    UploadTypes = new ObservableCollection<string>();

();

                    while (reader.Read())
                    {
                        string location = reader.GetString(0);
                        Locations.Add(location);
                        string locationStatus = reader.GetString(1);
                        string uploadType = reader.IsDBNull(7) ? null : reader.GetString(7);

                        // Add distinct upload types
                        if (!string.IsNullOrEmpty(uploadType) && !UploadTypes.Contains(uploadType))
                            UploadTypes.Add(uploadType);
                        string reviewedBy = reader.IsDBNull(6) ? null : reader.GetString(6);

                        BillingStatusItem newItem = new BillingStatusItem
                        {
                            Location = location,
                            LocationStatus = locationStatus,
                            ReviewedBy = reviewedBy,
                            SelectedUploadType = uploadType,
                        };

                        BillingStatusItems.Add(newItem);
                    }

                    reader.Close(); // Close the reader when done
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }



    public class BillingStatusItem : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

        private string _location;
        public string Location
        {
            get { return _location; }
            set
            {
                _location = value;
                OnPropertyChanged(nameof(Location));
            }
        }

        private string _locationStatus;
        public string LocationStatus
        {
            get { return _locationStatus; }
            set
            {
                _locationStatus = value;
                OnPropertyChanged(nameof(LocationStatus));
            }
        }

        private string _reviewedBy;
        public string ReviewedBy
        {
            get { return _reviewedBy; }
            set
            {
                _reviewedBy = value;
                OnPropertyChanged(nameof(ReviewedBy));
            }
        }

        private string _selectedUploadType;
        public string SelectedUploadType
        {
            get { return _selectedUploadType; }
            set
            {
                _selectedUploadType = value;
                OnPropertyChanged(nameof(SelectedUploadType));
            }
        }

        protected virtual void OnPropertyChanged(string propertyName)
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}
0

There are 0 answers