473,288 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Selecting, 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
Jan 4 '06 #1
6 3112
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
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
"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
"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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Marko Poutiainen | last post by:
Situation: We had to make our SQLServer 2000 database multi-lingual. That is, certain things (such as product names) in the database should be shown in the language the user is using (Finnish,...
0
by: foobar | last post by:
I'm exporting data from sqlserver into a text file. I don't want to use a linked server or DTS. I have no problem getting the data into the correct dataset (the dataset linked to the text...
0
by: Stylus Studio | last post by:
DataDirect XQuery(TM) is the First Embeddable Component for XQuery That is Modeled after the XQuery API for Java(TM) (XQJ) BEDFORD, Mass.--Sept. 20, 2005--DataDirect Technologies...
1
by: Srinadh | last post by:
Hi all, We have files with about 20 to 30 fields per row. We are trying to update such files with about 60 rows as contiguous data in a CLOB field. It passes through. But when we try...
6
by: aaj | last post by:
Hi all I use a data adapter to read numerous tables in to a dataset. The dataset holds tables which in turn holds full details of the records i.e. keys, extra colums etc.. In some cases I...
1
by: yuchang | last post by:
Hi, Using the FormView control is very efficient. But I want to do some action,like showing a success message or redirect to another page, after inserting, updating and deleting. How do I get...
2
by: toddw607 | last post by:
Hi Everyone! I have a ASP.NET webform that brings data in from SQL Server 2000 and displays it on a page. I want to update the webpage while in IE using the following code: <%@ Import...
15
by: slinky | last post by:
Thanks in advance fo rany help... I have an XML data file (well- formed) that I need to place into my website's app_data folder. I would like to have an .aspx form on my site that simply has two...
2
by: sdanda | last post by:
Hi , Do you have any idea how to improve my java class performance while selecting and inserting data into DB using JDBC Connectivity ......... This has to work for more than 8,00,000...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.