SQL Server express 2005 Question about tables

[port27374]

hi folks,
im having a problem with some table issues, hoping someone could shed some light...

what im trying to do... i am trying to create a small web form where a customer to the website can select the make and model of their car (from drop down boxes which have an sql datasource) and type in some details in a text box and then submit this form to my email address...

problem--- i have just started learning visual studio express 2005 web developers edition... i am not completely new to asp nor SQL... what im doing is (trying) is to allow the customer to select a MAKE of the car (which i have successfully done from an SQL datasource)...the problem im having is populating the second drop down box(Model), depending on the selction in the MAKE box...

so i created a database called cars.mdf
then i made two tables one called MAKE and one called Model. the Make table populates the make drop down and the model table is supposed to populate the model drop down filtering the results as needed.

could someone tell me what SQL code i would need to do this.. i have made my Model table with an ID for primary key, a Make column and Model Column.
What im trying to do is get the second dropdownbox to display the model if the Make value is the same as the make value in the Make table...

So far ive tried something like...

SELECT Model FROM Model
WHERE (Make.Model=Make.Make)

and i get an error something like :

"SQL Execution error
The multi-part identifier Make.Model could not be bound
The multi-part identifier Make.Make could not be bound

.....

any ideas how i can resolve this issue so that the Model Box value coressponds to the Make box value...??
many thanks for your time

 

[Olaf Doschke]

Hi,

In your select you reference Make.Make, while you don't select from the Make table, so only the Model table can be referenced. And you reference a Model.Make field, which does not exist.

table structure

Make-Table
Make_ID
Make

Model-Table
Model_ID
Model
Make_ID

Here's pseudocode:

Select Model.Model From Model
Where Model.Make_ID = [Dropdownbox.Make_ID]

I'm not much into Visual Studio, you couold do such things as dynamically filling the second dropdownbox via Ajax. Dropdownbox.Make_ID must be substituted by the choosen Make of the first Dropdownbox. You could display it just then, when the user has choosen the make.

Bye, Olaf.