Hello i have a problem with exception System.ArgumentException Column <Id> does not belong to table. I have two Tables. First one called Employee:
Id (int), Name (nvarchar(max)), LastName(nvarcharmax), Age(int), Dep_nt(nvarchar(max)), Profession (nvarchar(max)), Salary (real).
Second one called Department:
Id (int), DepartmentName(nvarchar(max)
I have an event for each one witch is bound to two buttons clicks. Deletion from Employee works perfectly fine, but deletion from Department doesn't work at all and gives me an error i mentioned earlier. Here is the code for both of them:
private void ButtonDelDep_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataRowView dataRowView = DpListBox.SelectedItem as DataRowView;
cmd = new SqlCommand("DELETE FROM Department WHERE ID=@ID", connection);
connection.Open();
cmd.Parameters.AddWithValue("@ID", dataRowView.Row["Id"] as Nullable<int>);
cmd.ExecuteNonQuery();
adapter.UpdateCommand = cmd;
DataTable dataTable1 = new DataTable();
adapter.Fill(dataTable1);
DpListBox.ItemsSource = dataTable1.DefaultView;
connection.Close();
}
}
private void ButtonDeleteEmp_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataRowView dataRowView = Ep.SelectedItem as DataRowView;
cmd = new SqlCommand("DELETE FROM Employee WHERE ID=@ID", connection);
connection.Open();
cmd.Parameters.AddWithValue("@ID", dataRowView.Row["Id"] as Nullable<int>);
cmd.ExecuteNonQuery();
adapter.UpdateCommand = cmd;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
Ep.ItemsSource = dataTable.DefaultView;
connection.Close();
}
}
Do you have any idea why is that so? Thanks.
Full code behind:
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
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;
namespace BigCompanyinc
{
/// <summary>
/// Логика взаимодействия для MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
Logic L = new Logic();
string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Hospital;Integrated Security=True;Pooling=False";
SqlCommand cmd;
SqlDataAdapter adapter = new SqlDataAdapter();
public MainWindow()
{
InitializeComponent();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("SELECT DepartmentName FROM Department ", connection);
adapter.SelectCommand = command;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
List<string> DepartmentNames = new List<string>();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
String DepartmentName = Convert.ToString(dataTable.Rows[i]["DepartmentName"]);
DepartmentNames.Add(DepartmentName);
}
Department4.ItemsSource = DepartmentNames;
DepartmentF.ItemsSource = DepartmentNames;
DpListBox.ItemsSource = dataTable.DefaultView;
L.InitReadOnly(true, Name4, LastName4, Age4, Department4, Profession4, Salary4);
L.InitReadOnly(true, NameF, LastNameF, AgeF, DepartmentF, ProfessionF, SalaryF);
MessageBox.Show("Выберите департамент, чтобы начать работу.");
}
/// <summary>
/// Добавить новый департамент
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button2_Click(object sender, RoutedEventArgs e)
{
var sql = String.Format("INSERT INTO Department (DepartmentName) " + "VALUES (N'{0}')",
Name7.Text);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
command = new SqlCommand(@"UPDATE Deparment SET DepartmentName = @DepartmentName WHERE ID =@ID", connection);
command.Parameters.Add("@DepartmentName", SqlDbType.NVarChar, -1, "DepartmentName");
SqlParameter param = command.Parameters.Add("@ID", SqlDbType.Int, 0, "ID");
param.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand = command;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
DpListBox.ItemsSource = dataTable.DefaultView;
connection.Close();
}
}
/// <summary>
/// Выбран новый элемент ListBox для коллекции департаментов
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DpListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
L.InitReadOnly(false, Name4, LastName4, Age4, Department4, Profession4, Salary4);
L.InitReadOnly(false, NameF, LastNameF, AgeF, DepartmentF, ProfessionF, SalaryF);
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
DataRowView dataRowView = DpListBox.SelectedItem as DataRowView;
//Console.WriteLine(dataRowView.Row["Id"]);
string value = "";
if (dataRowView != null)
{
value = dataRowView.Row["DepartmentName"] as string;
}
SqlCommand command = new SqlCommand($@"SELECT * FROM Employee WHERE Dep_nt='" + value + "'", connection);
adapter.SelectCommand = command;
DataTable dataTable1 = new DataTable();
adapter.Fill(dataTable1);
Ep.ItemsSource = dataTable1.DefaultView;
connection.Close();
}
private void Button1_Click(object sender, RoutedEventArgs e)
{
var sql = String.Format("INSERT INTO Employee (Name, LastName, Age, Dep_nt, Profession, Salary) " + "VALUES (N'{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", Name4.Text, LastName4.Text, Age4.Text, Department4.Text, Profession4.Text, Salary4.Text);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
SqlConnection connection1 = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
DataRowView dataRowView = DpListBox.SelectedItem as DataRowView;
string value = "";
if (dataRowView != null)
{
value = dataRowView.Row["DepartmentName"] as string;
}
SqlCommand command1 = new SqlCommand($@"SELECT * FROM Employee WHERE Dep_nt='" + value + "'", connection1);
adapter.SelectCommand = command1;
DataTable dataTable1 = new DataTable();
adapter.Fill(dataTable1);
Ep.ItemsSource = dataTable1.DefaultView;
connection.Close();
}
}
private void button0_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataRowView dataRowView = Ep.SelectedItem as DataRowView;
cmd = new SqlCommand("UPDATE Employee SET Name = @Name, LastName = @LastName, Age = @Age, Dep_nt = @Dep_nt, Profession = @Profession, Salary = @Salary WHERE ID = @ID", connection);
connection.Open();
cmd.Parameters.AddWithValue("@ID", dataRowView.Row["Id"] as Nullable<int>);
cmd.Parameters.AddWithValue("@Name", NameF.Text);
cmd.Parameters.AddWithValue("@LastName", LastNameF.Text);
cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(AgeF.Text));
cmd.Parameters.AddWithValue("@Dep_nt", DepartmentF.Text);
cmd.Parameters.AddWithValue("@Profession", ProfessionF.Text);
cmd.Parameters.AddWithValue("@Salary", Convert.ToDouble(SalaryF.Text));
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
adapter.UpdateCommand = cmd;
adapter.Fill(dt);
Ep.ItemsSource = dt.DefaultView;
connection.Close();
}
}
private void Ep_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
L.InitReadOnly(false, NameF, LastNameF, AgeF, DepartmentF, ProfessionF, SalaryF);
DataRowView dataRowView = Ep.SelectedItem as DataRowView;
if (dataRowView != null)
{
NameF.Text = dataRowView.Row["Name"] as string;
LastNameF.Text = dataRowView["LastName"] as string;
AgeF.Text = Convert.ToString(dataRowView["Age"]);
DepartmentF.Text = dataRowView["Dep_nt"] as string;
ProfessionF.Text = dataRowView["Profession"] as string;
SalaryF.Text = Convert.ToString(dataRowView["Salary"]);
}
else
{
NameF.Text = "";
LastNameF.Text = "";
AgeF.Text = "";
DepartmentF.Text = "";
ProfessionF.Text = "";
SalaryF.Text = "";
}
}
private void ButtonDelDep_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataRowView dataRowView = DpListBox.SelectedItem as DataRowView;
cmd = new SqlCommand("DELETE FROM Department WHERE ID=@ID", connection);
connection.Open();
cmd.Parameters.AddWithValue("@ID", dataRowView.Row["Id"] as Nullable<int>);
cmd.ExecuteNonQuery();
adapter.UpdateCommand = cmd;
DataTable dataTable1 = new DataTable();
adapter.Fill(dataTable1);
DpListBox.ItemsSource = dataTable1.DefaultView;
connection.Close();
}
}
private void ButtonDeleteEmp_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataRowView dataRowView = Ep.SelectedItem as DataRowView;
cmd = new SqlCommand("DELETE FROM Employee WHERE ID=@ID", connection);
connection.Open();
cmd.Parameters.AddWithValue("@ID", dataRowView.Row["Id"] as Nullable<int>);
cmd.ExecuteNonQuery();
adapter.UpdateCommand = cmd;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
Ep.ItemsSource = dataTable.DefaultView;
connection.Close();
}
}
}
}
XAML:
<Window x:Name="Staff" x:Class="BigCompanyinc.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:local="clr-namespace:BigCompanyinc"
mc:Ignorable="d"
Title="Staff" Height="513.5" Width="991.833" ResizeMode="NoResize"
Icon="icon1.ico">
<Grid Height="504" VerticalAlignment="Top" Margin="10,0,-23,-19">
<Grid.RowDefinitions>
</Grid.RowDefinitions>
<TextBox x:Name="NameF" HorizontalAlignment="Left" Margin="164,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="LastNameF" HorizontalAlignment="Left" Margin="294,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="AgeF" HorizontalAlignment="Left" Margin="424,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<ComboBox x:Name="DepartmentF" HorizontalAlignment="Left" Margin="554,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="ProfessionF" HorizontalAlignment="Left" Margin="684,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="SalaryF" HorizontalAlignment="Left" Margin="814,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<Button x:Name="button0" HorizontalAlignment="Left" Margin="164,448,0,0" VerticalAlignment="Top" Width="120" Height="22" Background="LightBlue"
Content="Изменить данные" Click="button0_Click"/>
<TextBox x:Name="Name1" HorizontalAlignment="Left" Margin="164,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Имя" IsReadOnly="True"/>
<TextBox x:Name="LastName1" HorizontalAlignment="Left" Margin="294,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Фамилия" IsReadOnly="True"/>
<TextBox x:Name="Age1" HorizontalAlignment="Left" Margin="424,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Возраст" IsReadOnly="True"/>
<TextBox x:Name="Department1" HorizontalAlignment="Left" Margin="554,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Департамент" IsReadOnly="True"/>
<TextBox x:Name="Profession1" HorizontalAlignment="Left" Margin="684,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Профессия" IsReadOnly="True"/>
<TextBox x:Name="Salary1" HorizontalAlignment="Left" Margin="814,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Заработная плата" IsReadOnly="True"/>
<TextBox x:Name="Header1" HorizontalAlignment="Left" Margin="164,39,0,0" VerticalAlignment="Top" Width="250" Height="22" Text="Добавить сотрудника" IsReadOnly="True"/>
<TextBox x:Name="Name4" HorizontalAlignment="Left" Margin="164,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="LastName4" HorizontalAlignment="Left" Margin="294,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="Age4" HorizontalAlignment="Left" Margin="424,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<ComboBox x:Name="Department4" HorizontalAlignment="Left" Margin="554,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="Profession4" HorizontalAlignment="Left" Margin="684,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="Salary4" HorizontalAlignment="Left" Margin="814,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="Name5" HorizontalAlignment="Left" Margin="164,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Имя" IsReadOnly="True"/>
<TextBox x:Name="LastName5" HorizontalAlignment="Left" Margin="294,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Фамилия" IsReadOnly="True"/>
<TextBox x:Name="Age5" HorizontalAlignment="Left" Margin="424,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Возраст" IsReadOnly="True"/>
<TextBox x:Name="Department5" HorizontalAlignment="Left" Margin="554,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Департамент" IsReadOnly="True"/>
<TextBox x:Name="Profession5" HorizontalAlignment="Left" Margin="684,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Профессия" IsReadOnly="True"/>
<TextBox x:Name="Salary5" HorizontalAlignment="Left" Margin="814,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Заработная плата" IsReadOnly="True"/>
<Button x:Name="button1" HorizontalAlignment="Left" Margin="164,129,0,0" VerticalAlignment="Top" Width="120" Height="22" Background="LightBlue"
Content="Добавить" Click="Button1_Click"/>
<Button x:Name="buttonDeleteEmp" HorizontalAlignment="Left" Margin="294,129,0,0" VerticalAlignment="Top" Width="120" Height="22" Background="LightBlue"
Content="Удалить" Click="ButtonDeleteEmp_Click"/>
<TextBox x:Name="Header2" HorizontalAlignment="Left" Margin="10,39,0,0" VerticalAlignment="Top" Width="144" Height="22" Text="Добавить департамент" IsReadOnly="True"/>
<TextBox x:Name="Name6" HorizontalAlignment="Left" Margin="10,69,0,0" VerticalAlignment="Top" Width="144" Height="22" Text="Название" IsReadOnly="True"/>
<TextBox x:Name="Name7" HorizontalAlignment="Left" Margin="10,99,0,0" VerticalAlignment="Top" Width="144" Height="22" IsReadOnly="False"/>
<Button x:Name="button2" HorizontalAlignment="Left" Margin="10,129,0,0" VerticalAlignment="Top" Width="70" Height="22" Background="LightBlue"
Content="Добавить" Click="Button2_Click"/>
<Button x:Name="buttonDelDep" HorizontalAlignment="Left" Margin="84,129,0,0" VerticalAlignment="Top" Width="70" Height="22" Background="LightBlue"
Content="Удалить" Click="ButtonDelDep_Click"/>
<TextBox x:Name="Department2" HorizontalAlignment="Left" Margin="10,12,0,0" VerticalAlignment="Top" Width="144" Height="22" Text="Департамент" IsReadOnly="True"/>
<ListBox SelectedItem="DepartmentName" x:Name="DpListBox" HorizontalAlignment="Left" Margin="10,165,0,138" Width="144" SelectionChanged="DpListBox_SelectionChanged">
<ListBox.ItemTemplate>
<DataTemplate>
<TextBlock Text="{Binding DepartmentName}"/>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
<ListView x:Name="Ep" HorizontalAlignment="Left" Height="201" Margin="164,165,0,0" VerticalAlignment="Top" Width="791" SelectionChanged="Ep_SelectionChanged">
<ListView.View>
<GridView>
<GridViewColumn Width="130" Header="Имя" DisplayMemberBinding="{Binding Name}"/>
<GridViewColumn Width="130" Header="Фамилия" DisplayMemberBinding="{Binding LastName}"/>
<GridViewColumn Width="130" Header="Возраст" DisplayMemberBinding="{Binding Age}"/>
<GridViewColumn Width="130" Header="Департамент" DisplayMemberBinding="{Binding Dep_nt}"/>
<GridViewColumn Width="130" Header="Профессия" DisplayMemberBinding="{Binding Profession}"/>
<GridViewColumn Width="130" Header="Заработная плата" DisplayMemberBinding="{Binding Salary}"/>
</GridView>
</ListView.View>
</ListView>
</Grid>
</Window>
