System.Data.SqlClient.SqlException: "The multi-part identifier "System.Data.DataRowView" could not be bound."

Question

I have an issue with my database. I have two tables Employee and Department.

Employee contains 7 columns:

Id (int), Name (nvarchar(max)), LastName(nvarcharmax), Age(int), Dep_nt(nvarchar(max)), Profession (nvarchar(max)), Salary (real).

Department contains two columns:

Id (int), DepartmentName(nvarchar(max)

I have a DpListBox (ListBox) which contains an information about departments, and Ep (Listview) which contains information about employees who work in those departments.

I need to populate Ep based on the selected value from DpListBox. The main problem is with this line of code:

SqlCommand command = new SqlCommand($@"SELECT * FROM Employee WHERE Dep_nt=" + DpListBox.SelectedValue.ToString(), connection);

I think DpListBox.SelectedValue = System.Data.DataRowView because my datable contains of two fields Id and DepartmentName and I only need here DepartmentName.

Here is my 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
    {
        string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Hospital;Integrated Security=True;Pooling=False";

        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);
            DpListBox.ItemsSource = dataTable.DefaultView;
            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;

            }
        }


        /// <summary>
        /// Выбран новый элемент ListBox для коллекции департаментов
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DpListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataAdapter adapter = new SqlDataAdapter();
            Console.WriteLine(DpListBox.SelectedValue.ToString());
            SqlCommand command = new SqlCommand($@"SELECT * FROM Employee WHERE Dep_nt=" + DpListBox.SelectedValue.ToString(), connection);
            adapter.SelectCommand = command;
            DataTable dataTable1 = new DataTable();
            adapter.Fill(dataTable1);
            Ep.ItemsSource = dataTable1.DefaultView;
        }
    }
}

I use WPF, here is XAML code:

<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="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"/>

        <TextBox x:Name="Department2" HorizontalAlignment="Left" Margin="10,12,0,0" VerticalAlignment="Top" Width="144" Height="22" Text="Департамент" IsReadOnly="True"/>
        <ListBox ItemsSource="{Binding Dep}" 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">
            <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>

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Dec 01 '18 at 07:30

er-sho · Accepted Answer · 2018-12-01T07:32:44.480

3

You need to do below to get column value from DataRowView.

DataRowView dataRowView = DpListBox.SelectedItem as DataRowView;

string value = "";

if (dataRowView != null) {
   value = dataRowView.Row["DepartmentName"] as string;
}

And then you have to pass value instead of DpListBox.SelectedValue.ToString() to your query like.

SqlCommand command = new SqlCommand(@"SELECT * FROM Employee WHERE Dep_nt='" + value + "'", connection);

Note: Always try to use prepared statements (parameterized query) this will prevent SQL injection.

So the parameterized query will be.

SqlCommand command = new SqlCommand(@"SELECT * FROM Employee WHERE Dep_nt=@Dep_nt", connection);
command.Parameters.AddWithValue("@Dep_nt", value);

edited Dec 01 '18 at 07:32

answered Dec 01 '18 at 06:34

er-sho

9,581
2
13
26

System.Data.SqlClient.SqlException: "Invalid column name" i think it searchs for a column name... which is DepartmentName and it is wrong – Fallingsappy Dec 01 '18 at 06:45
what is `Dep_nt` in your query? try to change it with appropriate column name , i think its `DepartmentName` in sql table – er-sho Dec 01 '18 at 06:47
Dep_nt it is a name in Employee query, and DepartmentName it is name in Department query, so i got two queris and when DepartmentName is chosen from the listbox i need to check which employes have same Dep_nt as DepartmentName – Fallingsappy Dec 01 '18 at 06:51
ok try to verify if `Dep_nt` column is exist in `Employee` table or not? because error specified that this coumn does not exist in your `Employee` table – er-sho Dec 01 '18 at 06:54
okay, look i have for example "Cardiology" DepartmentName in Department query, and when i choose Cardiology frome ListBox error appears saying: System.Data.SqlClient.SqlException: "Invalid column name 'Cardiology'." i think we almost there, but it some how thinks that i should have column named after departmentName, but instead i need to check if there is employees who have their Dep_nt as DepartmentName in Department query – Fallingsappy Dec 01 '18 at 07:07
add breakpoint on error line and copy here sql query. i want to see if query is right or not after debugged. – er-sho Dec 01 '18 at 07:11
mmm sorry but how to do it)) i know how to set up a stop point, but how to coppy sql query – Fallingsappy Dec 01 '18 at 07:18
SqlCommand command = new SqlCommand($@"SELECT * FROM Employee WHERE Dep_nt='Cardiology'", connection); this one is works perfectly, but as you see it will show only one Department always – Fallingsappy Dec 01 '18 at 07:21
youu try this query => `SqlCommand command = new SqlCommand($@"SELECT * FROM Employee WHERE Dep_nt='" + value + "'", connection);` upto I find some documentation to add breakpoint in vs – er-sho Dec 01 '18 at 07:23
this link gives you more details about breakpoint => https://learn.microsoft.com/en-us/visualstudio/debugger/getting-started-with-the-debugger?view=vs-2017#inspect-variables-with-data-tips – er-sho Dec 01 '18 at 07:26

System.Data.SqlClient.SqlException: "The multi-part identifier "System.Data.DataRowView" could not be bound."

1 Answers1