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));
}
}
}