Resolved Problem with query "Maximum of one result"

[PDS8475]

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

"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.

 

[NoUserHere]

Never leave out information. What database is this for?

Anyway, looks like you want to join join clause - C# Reference or C# LINQ Joins With SQL You can also look into UNION too if the details in both fields are the same.

 

[JohnH]

Never leave out information. What database is this for?

Thread is posted in MS Access forum

 

[NoUserHere]

True, but you can never be too sure!

So I always like to confirm.

 

[PDS8475]

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

               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]

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]

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]

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]

Thanks for the help guys. I have now done it with the Query below.

"Select * FROM ItemsForSale INNER JOIN Engineering ON Engineering.Serial = ItemsForSale.Serial WHERE Engineering.Name = @s"