By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

Selecting, Inserting and Updating Relational Data (SQLServer/ADO.NET)

P: n/a
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
Jan 4 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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?
"Mike Wilson" <none> wrote in message
news:eY**************@TK2MSFTNGP10.phx.gbl...
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

Jan 4 '06 #2

P: n/a
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?...e-22eaaebb2723

I hope this hels,

Cor
Jan 4 '06 #3

P: n/a
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:u8*************@TK2MSFTNGP15.phx.gbl...
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?...e-22eaaebb2723

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
Jan 4 '06 #4

P: n/a
"Brad Rogers" <br*************@yahoo.com> wrote in message
news:48Iuf.3078$Uf7.162@trnddc01...
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
Jan 4 '06 #5

P: n/a
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
"Mike Wilson" <none> wrote in message
news:O1**************@tk2msftngp13.phx.gbl...
"Brad Rogers" <br*************@yahoo.com> wrote in message
news:48Iuf.3078$Uf7.162@trnddc01...
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

Jan 4 '06 #6

P: n/a
"Brad Rogers" <br*************@yahoo.com> wrote in message
news:_sTuf.1032$Gu6.149@trnddc06...
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,
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
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
Jan 4 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.