 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / ADO.NET / January 2006Selecting, Inserting and Updating Relational Data (SQLServer/ADO.NET)
Dear Group, I have a heirarchical set of database tables, say - "order" and "order_type" and want to display a series of orders in a grid control, and in place of the order_type foreign key identifier, I would like a dropdown combo box (lookup from the "order_type" table) to change the type of the order. I also need an update command button, a delete row button and also an insert new row button. I'm sure this is a very common design pattern, although I can't find any good tutorials anywhere. Can someone point me in the right direction, as I want to get the design pattern right from the very beginning. Also a couple of specific questions: 1) If I use a typed dataset with a SQL query to return the rows, and databind this to the grid control - how can I override/load the order_type column to load a combobox lookup dropdown? 2) If I use a typed dataset with a SQL query and the relations set, bound to the grid control - how can I change the WHERE clause dynamically at runtime? Since I'm building the dataset at designtime, I can't see how I can restrict the amount of data queried in memory (example: WHERE customer_name = "bob"). I can do all of the above in ADODc, and with careful use of recordsets; but I'm sure ADO.NET would be faster for this job! I'm using ADO.NET 2.0, VB2005 (Professional Edtn) and SQL Server Express. Many thanks for your help! Mike Have you tried the Add New Item and DataForm wizard? So you have a project in vb.net then click File then Add New Item then pick data form wizard. it will do most of the work for you? Its very easy to connect a datagrid once you have the logical data sources identified. You can draw the grid and did you want the drop down list inside grid elements? Or a separate drop down list? > Dear Group, > [quoted text clipped - 28 lines] > > Mike > Have you tried the Add New Item and DataForm wizard? > > So you have a project in vb.net then click File then Add New Item then > pick > data form wizard. it will do most of the work for you? Not yet (didn't realise it was an option) - I will try this.. > Its very easy to connect a datagrid once you have the logical data sources > identified. You can draw the grid and did you want the drop down list > inside grid elements? Or a separate drop down list? But I thought that a dataset (a typed one anyway) contains multiple tables as seperate objects and their rows as seperate objects - I want a literal SET or view which comprises of a whole series of tables (there are about 13 in all). I'm fairly sure that I can manage to put together something that works using a read only view, but I want to be able to "add row" and "update row" - which of course needs to handle referential integrity and work with the database contraints. I want the drop down list inside the grid elements - such that one of the columns always displays a drop down list. Thanks for your help :) Cheers! Mike Right, the DataSet is the whole DataBase, everything you need, tables and all, just that its in memory. If you add drop down items on columns, Ive heard its tricky to make it work or may not be intuitive A typed dataset allows you to reference tables and fields directly, by name, without having to reference the underlying collection. Typed datasets are a custom DataSet that derive from the System.Data.DataSet class. Imports System.Data 'statements Public Class dsUtility Inherits DataSet 'statements End Class But now youre defining your tables in Access? If you used the Wizard to make the database viewer, it should import all tables you select? Im still learning this also, it seems like database theory is just a very old concept thats been implemented so many ways we have legacy framework things to deal with, if it were all new? it wouldnt be this way, Im betcha. Im thinking about trying to learn OLD database concepts in order to make better sense of it all. hth > > Have you tried the Add New Item and DataForm wizard? > > [quoted text clipped - 24 lines] > > Mike > Right, the DataSet is the whole DataBase, everything you need, tables and > all, just that its in memory. I have a couple of issues with that. Issue #1 is that I could load the whole database into memory, but that would have huge overheads, would it not? Also, if I can't do that - then I need to create a whole load of datasets to describe the data and relationship (customer and customer_type, order and order_type for example), so I have loads of datasets knocking around. > If you add drop down items on columns, Ive heard its tricky to make it > work > or may not be intuitive Should be intuitive enough. My dropdown will say, "open" or "closed". I reckon I should be able to work out how to make a dataset using a TableAdapter to fill the information set, but I'm not sure about how this will insert new records - it all doesn't make much sense to me and I can't find a clear example anywhere. My datagrid isn't going to want to show ALL the columns of ALL of the tables in the dataset, since that would be messy. Also when I add a new row I will need to populate all the columns of all the tables in the dataset so that the data isn't messy with nulls everywhere. > A typed dataset allows you to reference tables and fields directly, by > name, [quoted text clipped - 10 lines] > > But now youre defining your tables in Access? If you used the Wizard to Nope, SQL Server Express (2005) > make the database viewer, it should import all tables you select? Im > still I'm only importing a small subset of tables each time, yes - just about got the hang of it. > learning this also, it seems like database theory is just a very old > concept > thats been implemented so many ways we have legacy framework things to > deal > with, if it were all new? it wouldnt be this way, Im betcha. > Im thinking about trying to learn OLD database concepts in order to make > better sense of it all. No problem in ADODc - use a recordset and loop through it. The advantage as I see things is not having to use an @@IDENTITY function and repeated insert/update statements to update a series of related tables. I'm assuming the dataset will handle that for me. I hope. Mike Mike, Maybe does this sample help you with your question. See that it is a sample and far from a complete application. http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723 I hope this hels, Cor > Mike, > [quoted text clipped - 4 lines] > > I hope this hels, Thanks for that, the site actually looks very useful for a few other things I'm stuck on too - cheers. Mike Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.