Resolved Problem with query "Maximum of one result"

PDS8475

Active member
Joined
Jun 25, 2019
Messages
27
Programming Experience
Beginner
I have to access Tables
ItemsForSale
Engineering

I have a textbox
Details1_textBox

I am passing Details1_textBox.text to the parameter @s

The Details1_textBox.text value is a name

The ItemsForSale table doesn't contain a Name column
How ever the Engineering table does
Both tables contain a Serial column

The output of the query is displayed in the DataGridView called idataGridView

I am trying to sort the records in idataGridView to only show records based on the name that is entered into Details1_textBox

I am using the query

C#:
"Select * FROM ItemsForSale WHERE Serial = (SELECT Serial FROM Engineering WHERE Engineering.[Name] = @s)"
but this gives me a exception saying "At most only one record can be returned by this subquery"

How ever I need the query to be able to return multiple records

I know the C# code that I have wrote works, as if I change the query to something simple it works fine, hence why I have only put the query.
 
Last edited:

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
277
Location
UK
Programming Experience
10+

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
277
Location
UK
Programming Experience
10+
True, but you can never be too sure!

So I always like to confirm. ;)
 

PDS8475

Active member
Joined
Jun 25, 2019
Messages
27
Programming Experience
Beginner
I'm trying to learn C#
So this isn't actually going to be a database that will be used. But I have based the database and application on the place that I volunteer at.
This is the reports form of the application which contains a comboBox to select what to search by, a textbox to enter details, a button which contains the code to search/filter records and four DataGridViews to view records each corresponds to a table in the database. When the form loads all records show and when the user clicks search the records are filtered by a combonation of the combobox and textbox. Three of the DataGridViews are working fine because they have the column that is being searched, The forth however does not and that is the one that I'm having trouble with.
The full code for this part of the search button function Is below
C#:
               string iConString = System.IO.File.ReadAllText("FixIT.con");
                iConString = iConString.Replace(System.Environment.NewLine, string.Empty);

                System.Data.OleDb.OleDbConnection iconnn = new System.Data.OleDb.OleDbConnection();
                string iconnsString = "Provider = Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + iConString;
                try
                {

                    using (OleDbConnection iconnection = new OleDbConnection(iconnsString))
                    {

                        using (OleDbCommand icommand = new OleDbCommand("Select * FROM ItemsForSale WHERE Serial = (SELECT Serial FROM Engineering WHERE Engineering.[Name] = @s)", iconnection))
                        {

                            icommand.CommandType = CommandType.Text;
                            icommand.Parameters.AddWithValue(@s, Details1_textBox.Text);
                            using (OleDbDataAdapter iDA = new OleDbDataAdapter(icommand))
                            {

                                using (DataTable iDT = new DataTable())
                                {

                                    iDA.Fill(iDT);
                                    idataGridView.DataSource = iDT;
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    TextWriter tw = new StreamWriter("ex.txt");
                    tw.WriteLine(ex);
                    tw.Close();
                }
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
291
Location
Virginia Beach, VA
Programming Experience
10+
See not above about using a JOIN to get the data that you need. This isn't a C# question, but rather a SQL question. You also need to learn SQL to get the data you need.

If you were to this in a purely C# way, then you need to load all the rows the two different tables, and then manually perform the join in your code. Don't do this. Databases are built to do this kind of thing very efficiently.

As a quick aside, what's the logic behind your curious variable naming convention: iconn, iconnString, iconnection, etc.? Why the "i" prefix?
 

PDS8475

Active member
Joined
Jun 25, 2019
Messages
27
Programming Experience
Beginner
See not above about using a JOIN to get the data that you need. This isn't a C# question, but rather a SQL question. You also need to learn SQL to get the data you need.

If you were to this in a purely C# way, then you need to load all the rows the two different tables, and then manually perform the join in your code. Don't do this. Databases are built to do this kind of thing very efficiently.

As a quick aside, what's the logic behind your curious variable naming convention: iconn, iconnString, iconnection, etc.? Why the "i" prefix?
The "I" just donates the table that is being used in this case ItemsForSale , the prefix is "e" for the engineering table and so on
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
291
Location
Virginia Beach, VA
Programming Experience
10+
Odd, but okay. Two things to note:
1) The .NET Framework's recommended naming conventions is not to use Hungarian Notation -- or simply put, don't use prefixes.
2) If you follow the Single Responsibility Principle, you'll have small methods that will only be about 20-30 lines long. The chances of name collisions is such small functions go down dramatically that you don't really need a naming convention.
 

PDS8475

Active member
Joined
Jun 25, 2019
Messages
27
Programming Experience
Beginner
Thanks for the help guys. I have now done it with the Query below.
C#:
"Select * FROM ItemsForSale INNER JOIN Engineering ON Engineering.Serial = ItemsForSale.Serial WHERE Engineering.Name = @s"
 
Top Bottom