|
A contrived example that demonstrates what I'm trying to accomplish:
Four tables, like so:
main_table:
persons_name
car
color
car_lookup:
car_id
car_description
color_lookup:
color_id
color_description
car_color:
car_id
color_id
The idea is that for each type of car in the car_lookup table, there are only a few values in the color_lookup table that are valid. The car_color table has the all of the color_id values that are valid for each car_id (that is, if I select all of the rows for a particluar car_id, I'll retrieve all of the valid color_ids for that car).
Question 1: ok design? Is there a better way to approach this?
Question 2: I'm using a DataGridView to present the main_table to the end-user for editing. Works fine when I'm just presenting the table with the car and color fields just showing ID numbers. How can I present the data so that the DataGridView presents the car_description instead of the car_id that is in the car field (and this one field is not changeable) and for the color field present a drop down of just the valid colors for the given car_id? This would mean that the drop down is different for each row. If car #1 could be only green or blue, whereas car #2 could be blue or red, then the drop down would reflect that.
The database is on a SQL server, the DataGridView is connected to a BindingSource, which is connected to a SqlDataAdapter.
Unsure if this belongs in the .NET section or here. Apologies if this is the wrong place. I'm writing in C#, but any general DataGridView info would be appreciated.
|
|
|
|
|
The database design is fine, as the car and color has many-to-many relationship.
Regarding the database binding, you can have queries in your stored procedure to return multi-recordsets and then you can store them in multi datatables in your dataset with relationship between the datatables...
Then bind the cars and based on the car the colors to the dropdown list by iterating through the rows in the GridView...
|
|
|
|
|
Your table design seems fine
I never use a SQLDataAdapter as a binding source as the adapter is always in the DAL and never comes out, only a datatable comes out of the DAL.
I would create a stored proc to service the car grid (CarGetForGrid) that joined the 4 tables and displayed the descriptors and the carid only. the result set would be retrieved via the DAL and used to populate the DGV. I then hide col0 from the user in the DGV
I have a major issue with allowing the user to do inline editing of the DGV. This is a personal design issue, I almost never allow inline editing. I force the user to double click the required record and display a dialog nicely layed out for CRUD. This does require rather a lot of dialogs but I have much greater control with a dialog. A list control is dor display purposes only, a dialog is for data entry.
This design eliminates all the crap around combos in grids.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the advice. I think I'll do what you suggest -- force the user to double-click the row to edit, then present a form showing just that record. Since the editing is minimal, I was hoping to get away with just a table, but this is probably better design.
|
|
|
|
|
If you don't use a SQLDataAdapter as your binding source, what do you recommend? There appear to be a few different ways to bind the the DataGridView to the database -- I think I just used a method I found in Microsoft's docs. (While I'm a resonably experienced programmer, I'm pretty new to .NET.)
|
|
|
|
|
Art Frank wrote: There appear to be a few different ways
This my friend is an understatement, I usually use 2 data sources, a datatable/view or a List<myclass> either bound to a bindingsource and the bindingsource as the datasource to the table. I then hide any columns in the DGV I don't like, I have a base rule that the recordID is ALWAYS in column0 and this is almost always hidden.
On the dblclick, grab the selected row.cell[0].value and pass it to the dialogs constructor. If you are using a List<myclass> you can pass the instance of MyClass.
I have a Utils class that does all the binding and formatting for me so I dont even think about it. It has some requirements like controls are named and prefixed correctly but I have been doing that for so long it is automatic.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all
Can you help me to protect sql server 2005 database from unauthorized access.I am using C#.net2005. Please help me to solve this problem
Thanks in advance
regards
shine.paravur@gmail.com
|
|
|
|
|
Create user logins and assign them appropriate roles.
|
|
|
|
|
shine_paravur wrote: Can you help me to protect sql server 2005 database from unauthorized access.I am using C#.net2005.
1. Use Windows Authentication mode, insted of SQL Server Authentication Mode ( As Windows Authentication Mode used ActiveDirecotry to Authenticate user)
2. As you are asking about C# too,
Please Encrypt your connection String
|
|
|
|
|
There are few options here for you:
Using Windows authentication to access the database.
Using SQL authentication to access the database.
For Windows Authentication, you need to use Active Directory User to map to your database.
For SQL Authentication, you need to create a SQL User and map the appropriate Roles and map with your database.
In your C# application, encrypt your connection string and use the same.
Many a times in shared deployment environment, they allow on SQL Authentication only, in such a case you will have to use SQL Authentication.
|
|
|
|
|
|
How would anyone that doesn't know anything about your website or database be able to tell you? Surely its a matter of common sense.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Balaji_rcs wrote: The Checklist items should cover both front end and back end. What are the steps to follow to test the back end environment and also the website
I guess you are using some tool or SQL Server itself to migrate the DB. So I am sure there should not be any messup.
If you are asking about checklist to be cover for both Front End and BackEnd,
I will Say, you need to run ALL THE TEST CASES to check the functionlaity is working fine or not
|
|
|
|
|
If you are migrating the database from SQL Server 2000 to 2005, there should ot be any issue as such as SQL Server 2005 is backward compatible and all the statements, functions and other database objects and syntax of SQL Server 2000 works fine in SQL Server 2005.
In case of conflict, during migration it will show the alerts.
However for double checking, you can run through the test cases that are meant for sections, that have database transactions.
|
|
|
|
|
Hi,
Recently, my team migrated the websites from sql server 2000 to sql server 2005. My leader asked to prepare the checklist to do testing the sites in both front end and back end. Can anyone please share the checklist items to do the testing.
The Checklist items should cover both front end and back end. What are the steps to follow to test the back end environment and also the website.
|
|
|
|
|
Is this a specific question or simply a request for free consultancy for your company?
Anyways, if it is the former then please post your schema and database diagrams so that we may have something to start from for our answer.
If it is the latter, then please send the 25k US retainer to Code Project for answers...
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Hey, I guess you repeated your question.
|
|
|
|
|
Hi all,
I need to know whether SQL provides custom constraints??? i.e if i want to enforce a constraint on a Parent table given that its key is been referenced as a foriegn key in a child table, that it should have atleast one child???
Is it possible?????
When you fail to plan, you are planning to fail.
|
|
|
|
|
You can wrte triggers for the same. However it is not a good approach.
Better check in your stored procedure with the condition...
|
|
|
|
|
Hi,
I am new to SSIS. I have two different servers, one is AS400 and other in SQL server. There is one table ABC on AS400 and two table ABC and XYZ in SQL server. I need to take the join of the tables ABC (of AS400) and XYZ of SQL server and need to push the matching records into ABC table of SQL server.
Please advise me how can I achieve this in SSIS.
Thanks in Advance.
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
|
Hi Abhijit,
Thanks for quick reply. But on clicking the link "This may help you", it redirects me to some where else and I do not have any registration there.
It would be great if you can post the solution here.
Thanks
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
I really surprised to see the same thing happening with me. It was fine, when I was post the answer.
|
|
|
|
|
Hi Abhijit/CodeManiac,
Expert exchange displays the answers at the bottom when your referrer is Google. Try searching for the question title "Linking, Connecting MS SQL 2000 to AS400 Database" using Google, go to the first result, and scroll down to the bottom of page to view the replies.
Hope that helps.
Regards,
Syed Mehroz Alam
My Blog | My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
One option could be, create multiple Data Source (one for AS400 and another for SQL Server) in your DataFlow task and then use Union All Data Flow transformation Item and set the output to one Data Destination that is your SQL Server.
|
|
|
|