Want to add IDs from two different tables into one associative table using C# and Windows Forms

Question

I want to use Windows Forms and C# to implement a Database application which consists of the following tables:

Student table:

CREATE TABLE [dbo].[Student] (
    [Id]   INT           NOT NULL,
    [Name] NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Class table:

CREATE TABLE [dbo].[Class] (
    [Id]      INT           NOT NULL,
    [Teacher] NVARCHAR (50) NOT NULL,
    [Grade]   INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

StudentClassCombo:

CREATE TABLE [dbo].[StudentClassCombo] (
    [ClassID]   INT NOT NULL,
    [StudentID] INT NOT NULL,
    CONSTRAINT [ClassFK] FOREIGN KEY ([ClassID]) REFERENCES [dbo].[Class] ([Id]),
    CONSTRAINT [StudentFK] FOREIGN KEY ([StudentID]) REFERENCES [dbo].[Student] ([Id])
);

I have created a Windows form through which I want to assign students to classes:

As you can see in the form above, I can successfully populate my combo-box and list view with the expected values. However, I am struggling to implement the functionality for the "Add" button which must assign the checked values in the list view (student names) to the item in the combo-box (Class) and add the mappings to the table StudentClassCombo.

My code for the button-click event is as follows:

private void student2class_Click(object sender, EventArgs e)
        {
            

            string insertCommand = "INSERT INTO dbo.StudentClassCombo (ClassID, StudentID) "
    + "(SELECT Id FROM dbo.Class "
    + "WHERE Grade = @Grade)"
    + "(SELECT Id FROM dbo.Student "
    + "WHERE Name = @StudentName)";

            using (connection = new SqlConnection(connectionString))
            using (SqlCommand Insertcmd = new SqlCommand(insertCommand, connection))
            {
                Insertcmd.Parameters.Add("@Grade", SqlDbType.Int);
                Insertcmd.Parameters.Add("@StudentName", SqlDbType.NVarChar, 50);

                connection.Open();

                foreach (ListViewItem eachItem in StudentsList.CheckedItems)
                {
                    //string Selected = eachItem.SubItems[0].Text; //directly access "eachItem"

                    Insertcmd.Parameters["@Grade"].Value = int.Parse(ClassNames.Text);
                    Insertcmd.Parameters["@StudentName"].Value = eachItem.SubItems[0].Text;

                    Insertcmd.ExecuteNonQuery();
                }

                connection.Close();
            }
            
        }
    }

But when I run the above code, I run into the following exception:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

There are two SELECT statements in my query. I do not understand why the program is not recognizing both of them?

@DaleK Thank you for the suggestion. I have updated my question. — a_sid, May 19 '21 at 21:38

score 1 · Accepted Answer · answered May 19 '21 at 19:06

1

I looked at the insert statement quickly and appear to incorrect.

Change from:

string insertCommand = "INSERT INTO dbo.StudentClassCombo (ClassID, StudentID) "
    + "(SELECT Id FROM dbo.Class "
    + "WHERE Grade = @Grade)"
    + "(SELECT Id FROM dbo.Student "
    + "WHERE Name = @StudentName)";

To:

string insertCommand = @"INSERT INTO dbo.StudentClassCombo (ClassID, StudentID) VALUES ((SELECT Id FROM dbo.Class WHERE Grade = @Grade), (SELECT Id FROM dbo.Student WHERE Name = @StudentName))";

answered May 19 '21 at 19:06

MestreDosMagros

1,000
5
19

For some reason, with your query my SQL table is not getting populated with the expected values. – a_sid May 19 '21 at 20:02
The tables are getting updated but in my `bin/Debug` folder. [This SO answer](https://stackoverflow.com/questions/31604951/sql-command-insert-is-working-but-the-data-not-appear-in-table) was helpful. – a_sid May 19 '21 at 21:33

score 1 · Answer 2 · answered May 19 '21 at 19:08

1

You are missing a comma after the first Select statement

answered May 19 '21 at 19:08

Robert Oats

23
4

score 1 · Answer 3 · answered May 19 '21 at 19:33

The other two answers resolve the error you're experiencing but in terms of how we would typically structure an app, you'd download both the Id and the Grade (and the Id and StudentName) and you'd store both locally in some associated way. When the user choose "John" you will already know which ID it relates to and you'd just insert that ie

INSERT INTO dbo.StudentClassCombo (ClassID, StudentID) VALUES ( @GradeId, @StudentId )

You wouldn't have this "look up the ID based on the name the user picked in the ui" process, especially as it's prone to fail if there are two entries with the same name

Most windows list type controls that can be bound to a list of items have a facility for declaring which property holds the text to be displayed and which holds a value to use as the ID. For example a listbox set up like:

lb.DisplayMember = "StudentName";
lb.ValueMember = "Id";
lb.DataSource = someStudentDataTable;

will associate with a bunch of student rows in a datatable, show the name but whenever the listbox is asked for the SelectedValue the ID of the selected student is returned

Serge · Answer 4 · 2021-05-19T20:36:45.690

1

Try this query string:

 string insertCommand = "INSERT INTO dbo.StudentClassCombo (ClassID, StudentID) "
+ "SELECT Class.ID as ClassId, Student.Id as StudentId from dbo.Class,dbo.Student"+ 
" where Class.Grade=@Grade and Student.Name = @StudentName";

and fix the parameters

 Insertcmd.Parameters.Add("@Grade", SqlDbType.Int).Value=0;
Insertcmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value=string.Empty;

edited May 19 '21 at 20:36

answered May 19 '21 at 19:43

Serge

40,935
4
18
45

Your query throws an exception when I click on the Add button: `Additional information: Incorrect syntax near '='. "` – a_sid May 19 '21 at 20:02
@a_sid It was a typo. I replaced == with = Class.Grade=@Grade – Serge May 19 '21 at 20:13
Your new code is not throwing an exception anymore but the data is not being stored in the table for some reason. Do you have any idea about this? – a_sid May 19 '21 at 20:18
Did you check ClassNames.Text) and eachItem.SubItems[0].Text using debugger? – Serge May 19 '21 at 20:32
Yes I did. The proper values are being recorded but the information is not getting stored in the database. – a_sid May 19 '21 at 20:35
The tables are getting updated but in my `bin/Debug` folder. [This SO answer](https://stackoverflow.com/questions/31604951/sql-command-insert-is-working-but-the-data-not-appear-in-table) was helpful. – a_sid May 19 '21 at 21:33

Want to add IDs from two different tables into one associative table using C# and Windows Forms

4 Answers4