Help With SQL

[Stonewall382]

Hello, I'm building a program for an accountant at a factory that produces rollformed metal products.  The program will be used to track production reports.  I've decided to use an SQL database to handle everything.  The database contains the following tables:
Coils - Types of coils, primary key is 'name', and foreign key is 'material type'
Customers
Employees
Entries - Only key you need to know is that the foreign key is 'product'
Job Types
Machines
Materials - Primary key is 'name'
Products - Primary key is 'name', foreign key is 'coil'

So the hierarchy we need to talk about is:
Entry - Product - Coil - Material
Every entry has a product, every product uses one coil, and every coil is made of one material (steel, aluminum, copper, etc.)
As a sidenote:  Entries contain references to customers, employees, job types, machines, and products, directly, and coil and material indirectly (as shown above).

The problem I'm having concerns building a flexible query.  The user needs to be able to search the Entries table on one or more of the above tables, INCLUDING coil and material (which are not directly in the entry).  So, in the 'WHERE' part of my statement, I need to be able to check if an entry's product's coil matches the search criteria, and, when the user wants to search on material, if the entry's product's coil's material matches the search criteria.  Does anyone know how to do this?

For what it's worth, I'm using Visual Studio 2008, language is Visual Basic.  Thanks for your time.


Edit:  Here's my query statement, which works fine in Visual Studio's Query Builder:

SELECT     Entries.Date, Entries.[M/O Number], Entries.[P/O Number], Entries.[W/O Number], Entries.Product, Entries.Customer, Coil.Name AS Coil, 
                      Material.Name AS Material, Entries.[Time (minutes)], Entries.[Job Type], Entries.Employee, Entries.Machine, Entries.[Piece Length], 
                      Entries.[Number of Pieces]
FROM         Coil INNER JOIN
                      Product ON Coil.Name = Product.Coil INNER JOIN
                      Entries ON Product.Name = Entries.Product INNER JOIN
                      Material ON Coil.[Material Type] = Material.Name
WHERE     (@ProductName = '' OR
                      @ProductName = Entries.Product) AND (@CustomerName = '' OR
                      @CustomerName = Entries.Customer) AND (@MachineName = '' OR
                      @MachineName = Entries.Machine) AND (@CoilName = '' OR
                      @CoilName = Coil.Name) AND (@MaterialName = '' OR
                      @MaterialName = Material.Name) AND (@JobType = '' OR
                      @JobType = Entries.[Job Type])

The problem is, I get 'No mapping exists from type dbType AnsiString to a known SQL Server Compact data type'.  Any thoughts?

 

[Janus]

So, it sounds like a basic data type mismatch between Visual Basic and SQL.

 

[Stonewall382]

Yeah, I wasn't using Visual Studio's query builder correctly.  I was trying to make my own parameters, and they were being declared as ansiStrings, rather than nvarchars.  Thanks for your time, Janus.

If anyone can help, I now have another problem.  In the 'SELECT' statement, I select values from other tables, including coil name, material name, and now that I used an associative entity, several employee's names (each row in the entries table links to a row in an 'operators' table, which contains that particular job's employees) as well.  However, in my DataGridView, the columns I selected from the other tables don't show up.  In the query builder, when I 'execute query', they show up just fine, but when I actually run the program, those columns don't show.  Does anyone have any ideas?  And again, thanks for your time.

 

[Janus]

Hmm, sounds odd. I've used JOINs in MySQL and SQLite (usually "LEFT JOIN") a fair bit and never had much trouble with them. Are you being sure to specify the table name for each column you're specifying (table_name.column_name)?