Question about hiding duplicates
-
I have a database that i'm using for a shopping cart i'm hopefully successful in making. In any event here is what I want. Database = microsoft access database and contains cars, model,year,engine,price and so on The problem I have is this. lets say i have a car called buick and i have several differnet listing for the model lets say i have two entries for 593b. Well I don't want a primary key where i can't have two 593b's but when i display the model in my dropdown i don't want to have duplicates. basically if i select buick i only one to see the model names once like a b c and so on Any suggestion on this cause what i was hoping to do was the following select the different criteria from the dropdowns that are populated by using select commands and then at the end have a button to submit. and it query the database to where model = dropdownlist1's text and list2s text and so on. Am i going about this wrong i sure hope not. thanks for the help and i hope its clear what i'm asking. if not i'll try again. Win32newb "Programming is like sex, make one mistake and you have to support it for a long time"
-
I have a database that i'm using for a shopping cart i'm hopefully successful in making. In any event here is what I want. Database = microsoft access database and contains cars, model,year,engine,price and so on The problem I have is this. lets say i have a car called buick and i have several differnet listing for the model lets say i have two entries for 593b. Well I don't want a primary key where i can't have two 593b's but when i display the model in my dropdown i don't want to have duplicates. basically if i select buick i only one to see the model names once like a b c and so on Any suggestion on this cause what i was hoping to do was the following select the different criteria from the dropdowns that are populated by using select commands and then at the end have a button to submit. and it query the database to where model = dropdownlist1's text and list2s text and so on. Am i going about this wrong i sure hope not. thanks for the help and i hope its clear what i'm asking. if not i'll try again. Win32newb "Programming is like sex, make one mistake and you have to support it for a long time"
Your problem is not much of an ASP.NET problem but probably more of a DataBase design issue. Please explain a bit more what the application is supposed to do: 1) People buy cars? 2) How many tables in you DB do you have? Basically Models and Makes should be unique and should not be repeated in your DB. Explain to me also in which case you would have 2 entries for a model "593b" In general (and I don't know what you want to do exactly, but)the Model of a car should be referred to the Car as a ModelID..
-
Your problem is not much of an ASP.NET problem but probably more of a DataBase design issue. Please explain a bit more what the application is supposed to do: 1) People buy cars? 2) How many tables in you DB do you have? Basically Models and Makes should be unique and should not be repeated in your DB. Explain to me also in which case you would have 2 entries for a model "593b" In general (and I don't know what you want to do exactly, but)the Model of a car should be referred to the Car as a ModelID..
Well unfortunately for me I don't know a whole heck of a lot about access. I have just one table in the database right now for testing purposes. I have an aspx page that has a dropdown list for make so when i select say buick i then and only then want it to do a postback and populate the models dropdown that match buick. The problem is i have several rows in the database with the same model number so when i populate the list i get all those instances instead of just one instance. An example on the design of this would be great. The coding part I can probaby figure out but i think its like you said its an issue with how i have primary keys and secondary keys and so forth setup. Reason i say that is i know hardly anything about those. thanks for the help. Win32newb "Programming is like sex, make one mistake and you have to support it for a long time"
-
Well unfortunately for me I don't know a whole heck of a lot about access. I have just one table in the database right now for testing purposes. I have an aspx page that has a dropdown list for make so when i select say buick i then and only then want it to do a postback and populate the models dropdown that match buick. The problem is i have several rows in the database with the same model number so when i populate the list i get all those instances instead of just one instance. An example on the design of this would be great. The coding part I can probaby figure out but i think its like you said its an issue with how i have primary keys and secondary keys and so forth setup. Reason i say that is i know hardly anything about those. thanks for the help. Win32newb "Programming is like sex, make one mistake and you have to support it for a long time"
What I don't understand is How and Why you can the same model number more than once? Is this data that you are displaying only or is it data that was entered by users? Anyways, a good DB design would be to decompose your DB as much as possible. This is very important because it will allow you to easily modify everything in the future (expand, etc.). Again, presuming that you are displaying a catalog data (not data entered by use) here's an example (I will take only Make and Model for this example): You should have 2 tables tMake and tModel tMake should have 2 columns: MakeID, MakeName Make ID will be the primary key Make will be the name of the Make (Buick, etc.) tModel should have 3 columns: ModelID, ModelName and MakeID ModelID is the Primary Key ModelName is the Name of the Model (impala, suburban) MakeID id the Primary key of the tMake table (you should make a relation between this column and the MakeID column of your tMake table) Hope this helps, tell me what you think
-
What I don't understand is How and Why you can the same model number more than once? Is this data that you are displaying only or is it data that was entered by users? Anyways, a good DB design would be to decompose your DB as much as possible. This is very important because it will allow you to easily modify everything in the future (expand, etc.). Again, presuming that you are displaying a catalog data (not data entered by use) here's an example (I will take only Make and Model for this example): You should have 2 tables tMake and tModel tMake should have 2 columns: MakeID, MakeName Make ID will be the primary key Make will be the name of the Make (Buick, etc.) tModel should have 3 columns: ModelID, ModelName and MakeID ModelID is the Primary Key ModelName is the Name of the Model (impala, suburban) MakeID id the Primary key of the tMake table (you should make a relation between this column and the MakeID column of your tMake table) Hope this helps, tell me what you think
Ok yeah that works but here is something i don't understand. I use this table on a datagrid now. But the datagrid doesn't seem to like multi tables. I can't figure out to use the insert footer i made which is nothing more than a textbox to add a row to the database. a row consist of a make a model a year a engine a price and a shiping total. I run query builder and if i add all the tables and their columns i get an error saying that the insertcommands delete commands can not be added to multiple tables. So how is this done. i basically have something like this Make Model year Engine Price Shipping buick abcmodel 1991 V8 8.00 10.00 TEXTBOX TEXTBOX TEXTBOX TEXTBOX TEXTBOX TEXBOX inst so when i type in the textboxs the information i want to hit inst and it will make a new row that contains that information. Its kickin my butt over here any help would be great. Win32newb "Programming is like sex, make one mistake and you have to support it for a long time"
-
Ok yeah that works but here is something i don't understand. I use this table on a datagrid now. But the datagrid doesn't seem to like multi tables. I can't figure out to use the insert footer i made which is nothing more than a textbox to add a row to the database. a row consist of a make a model a year a engine a price and a shiping total. I run query builder and if i add all the tables and their columns i get an error saying that the insertcommands delete commands can not be added to multiple tables. So how is this done. i basically have something like this Make Model year Engine Price Shipping buick abcmodel 1991 V8 8.00 10.00 TEXTBOX TEXTBOX TEXTBOX TEXTBOX TEXTBOX TEXBOX inst so when i type in the textboxs the information i want to hit inst and it will make a new row that contains that information. Its kickin my butt over here any help would be great. Win32newb "Programming is like sex, make one mistake and you have to support it for a long time"
-
Please post your query. I guess you're using access: Open the quey, on the menu bar select View / SQL view, Copy and Paste it here. It's all based on how you're updating the tables.
Well I think my problem is more with my updatecommand insertcommand deletecommand because vs.net will not make them for me automatic when use multiple tables. I'm having to do them manually which i hardly know how to do. Here is what i have those far..
Make Table: Model Table: Engine Table:
MakeID ModelID EngineID
Make ModeName EngineName
MakeID Price
ShippingCost
ModelID*** they are related by makeId to modeltables makeid and modeltables modelid to enginetables ModelId****
each id is autonumbered MakeID ModelID and EngineID are primary keys.with that being said i now try to make my insert commands
this.oleDbInsertCommand1.CommandText = "INSERT INTO Make(Make) VALUES (?); SELECT SCOPE_IDENTITY() AS ModelID; INSERT INTO MODEL(ModelName,ModelID) VALUES(?,?);INSERT INTO Engine(EngineName, ModelID, Price, ShippingCost) VALUES (?, ?, ?, ?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Make", System.Data.OleDb.OleDbType.VarWChar, 0, "Make"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ModelName", System.Data.OleDb.OleDbType.VarWChar, 0, "ModelName"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("EngineName", System.Data.OleDb.OleDbType.VarWChar, 0, "EngineName"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Price", System.Data.OleDb.OleDbType.VarWChar, 0, "Price"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ShippingCost", System.Data.OleDb.OleDbType.VarWChar, 0, "ShippingCost"));Then my update command
this.oleDbUpdateCommand1.CommandText = @"UPDATE Make SET Make = ?;UPDATE MODEL SET ModelName = ?, ModelID = ?; UPDATE Engine SET EngineName= ?,
ModelID = ? WHERE (ModelID = ?) AND (Make = ? OR ? IS NULL AND Make IS NULL) AND (ModelName = ? OR ? IS NULL AND ModelName IS NULL) AND (EngineName = ? OR ? IS NULL AND EngineName IS NULL) AND (Price = ? OR ? IS NULL AND Price IS NULL) AND (ShippingCost = ? OR ? IS NULL AND ShippingCost IS NULL)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Make", System.Data.OleDb.OleDbType.VarWCh -
Well I think my problem is more with my updatecommand insertcommand deletecommand because vs.net will not make them for me automatic when use multiple tables. I'm having to do them manually which i hardly know how to do. Here is what i have those far..
Make Table: Model Table: Engine Table:
MakeID ModelID EngineID
Make ModeName EngineName
MakeID Price
ShippingCost
ModelID*** they are related by makeId to modeltables makeid and modeltables modelid to enginetables ModelId****
each id is autonumbered MakeID ModelID and EngineID are primary keys.with that being said i now try to make my insert commands
this.oleDbInsertCommand1.CommandText = "INSERT INTO Make(Make) VALUES (?); SELECT SCOPE_IDENTITY() AS ModelID; INSERT INTO MODEL(ModelName,ModelID) VALUES(?,?);INSERT INTO Engine(EngineName, ModelID, Price, ShippingCost) VALUES (?, ?, ?, ?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Make", System.Data.OleDb.OleDbType.VarWChar, 0, "Make"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ModelName", System.Data.OleDb.OleDbType.VarWChar, 0, "ModelName"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("EngineName", System.Data.OleDb.OleDbType.VarWChar, 0, "EngineName"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Price", System.Data.OleDb.OleDbType.VarWChar, 0, "Price"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ShippingCost", System.Data.OleDb.OleDbType.VarWChar, 0, "ShippingCost"));Then my update command
this.oleDbUpdateCommand1.CommandText = @"UPDATE Make SET Make = ?;UPDATE MODEL SET ModelName = ?, ModelID = ?; UPDATE Engine SET EngineName= ?,
ModelID = ? WHERE (ModelID = ?) AND (Make = ? OR ? IS NULL AND Make IS NULL) AND (ModelName = ? OR ? IS NULL AND ModelName IS NULL) AND (EngineName = ? OR ? IS NULL AND EngineName IS NULL) AND (Price = ? OR ? IS NULL AND Price IS NULL) AND (ShippingCost = ? OR ? IS NULL AND ShippingCost IS NULL)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Make", System.Data.OleDb.OleDbType.VarWChIn Your insert command change INSERT INTO MODEL(ModelName,ModelID) to INSERT INTO MODEL(ModelName,MakeID) as you can't insert a primary key value. When you update, you should update one table only (usually the primary table that holds all the info). If you have made the relationships between the tables correctly it will work. I don't know what you want to modify exactly, it seems you're modifying all the tables, even Make. Send me a screen capture of your user intereface flyboylb@yahoo.fr so I understand better what this app does. It seems that it can all be simplified easily.;)