Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I need to
change some data in the first table and let VB updates the same data in all
other four tables in the right places.
I know it would be possible by using the ForeignKeyConstraint object. I have
created the tables using the DataSet Visual Tool and I know it doesn't
create any ForeignKeyConstraint obj. I have found many codes examples on it,
but I don't know how to merge the code they in an existing dataset.
Hope somebody help.
Thanks in advance,
Max.
27  3732 
What kind of database are you using? VB2003 or VB2005?
..Net 1.1 or .Net 2.0?
How are you filling your tables?
Robin S.
-------------------------------------
"max" <ma*@max.maxwrote in message
news:GY*****************@tornado.fastwebnet.it...
>
Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I
need to change some data in the first table and let VB updates the
same data in all other four tables in the right places.
I know it would be possible by using the ForeignKeyConstraint object.
I have created the tables using the DataSet Visual Tool and I know it
doesn't create any ForeignKeyConstraint obj. I have found many codes
examples on it, but I don't know how to merge the code they in an
existing dataset.
Hope somebody help.
Thanks in advance,
Max.
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a dataset
using the Data Source Configuration Wizard. Then I created a form, dragged
the table from the Dataset which creates a bindingnavigator obj on top of
form; a second form load this form by a button and I correctly see the data.
There are only three columns in the tables: an incremental ID (I gave it
primary key), a number, and a name. Then I need to change, insert and delete
records in the first table and replicate this change in all the other four
tables, that are identical in structure and data. I can change and update
data in the first table (because I see that changes was really on it), but I
cannot replicate in the others. I have already tried to make relations using
Data Designer, but it doesn't work; I have read in some newsgroup that
ForeignKeyConstraint cannot be created by this way.
I'd like to understand where and how insert vb code to create relationship
between columns in the first table to all the others. I can't realize in
which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:0e******************************@comcast.com. ..
>
What kind of database are you using? VB2003 or VB2005?
.Net 1.1 or .Net 2.0?
How are you filling your tables?
Robin S.
-------------------------------------
"max" <ma*@max.maxwrote in message
news:GY*****************@tornado.fastwebnet.it...
>>
Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I need
to change some data in the first table and let VB updates the same data
in all other four tables in the right places.
I know it would be possible by using the ForeignKeyConstraint object. I
have created the tables using the DataSet Visual Tool and I know it
doesn't create any ForeignKeyConstraint obj. I have found many codes
examples on it, but I don't know how to merge the code they in an
existing dataset.
Hope somebody help.
Thanks in advance,
Max.
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?
I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)
Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.
This uses the Customers and Orders tables from the Northwind
database.
'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.
Robin S.
----------------
"max" <ma*@max.maxwrote in message
news:19*****************@tornado.fastwebnet.it...
>
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a
dataset using the Data Source Configuration Wizard. Then I created a
form, dragged the table from the Dataset which creates a
bindingnavigator obj on top of form; a second form load this form by a
button and I correctly see the data. There are only three columns in
the tables: an incremental ID (I gave it primary key), a number, and a
name. Then I need to change, insert and delete records in the first
table and replicate this change in all the other four tables, that are
identical in structure and data. I can change and update data in the
first table (because I see that changes was really on it), but I
cannot replicate in the others. I have already tried to make relations
using Data Designer, but it doesn't work; I have read in some
newsgroup that ForeignKeyConstraint cannot be created by this way.
I'd like to understand where and how insert vb code to create
relationship between columns in the first table to all the others. I
can't realize in which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:0e******************************@comcast.com. ..
>>
What kind of database are you using? VB2003 or VB2005?
.Net 1.1 or .Net 2.0?
How are you filling your tables?
Robin S.
-------------------------------------
"max" <ma*@max.maxwrote in message
news:GY*****************@tornado.fastwebnet.it. ..
>>>
Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I
need to change some data in the first table and let VB updates the
same data in all other four tables in the right places.
I know it would be possible by using the ForeignKeyConstraint
object. I have created the tables using the DataSet Visual Tool and
I know it doesn't create any ForeignKeyConstraint obj. I have found
many codes examples on it, but I don't know how to merge the code
they in an existing dataset.
Hope somebody help.
Thanks in advance,
Max.
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have set in
Dataset Designer.
Trying to use your code, I'm in trouble about connection string, this is
mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by SqlDataAdapter.
I have this code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSe t.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPres t1)
This cames automatically from dragging the tblPrest1 from dataset to the
form, in a datagrid format. I put your code under last line. And it doesn't
work.
Because I have other four tables identical to this, I get also five forms
identical, changing only the names of form (modPrest2...) and names of
tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all tables in
datagrid format.
Then I make a change in the tblPrest1, and save the change by clicking in
the floppy disk icon. When I reload the table, changes are effectively in
use.
It seems this procedure already fill the table in dataset... or not?
I can't understand how automatically changing the other four tables without
calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:p6******************************@comcast.com. ..
>
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?
I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)
Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.
This uses the Customers and Orders tables from the Northwind
database.
'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.
Robin S.
----------------
"max" <ma*@max.maxwrote in message
news:19*****************@tornado.fastwebnet.it...
>>
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a dataset
using the Data Source Configuration Wizard. Then I created a form,
dragged the table from the Dataset which creates a bindingnavigator obj
on top of form; a second form load this form by a button and I correctly
see the data. There are only three columns in the tables: an incremental
ID (I gave it primary key), a number, and a name. Then I need to change,
insert and delete records in the first table and replicate this change in
all the other four tables, that are identical in structure and data. I
can change and update data in the first table (because I see that changes
was really on it), but I cannot replicate in the others. I have already
tried to make relations using Data Designer, but it doesn't work; I have
read in some newsgroup that ForeignKeyConstraint cannot be created by
this way.
I'd like to understand where and how insert vb code to create
relationship between columns in the first table to all the others. I
can't realize in which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:0e******************************@comcast.com ...
>>>
What kind of database are you using? VB2003 or VB2005?
.Net 1.1 or .Net 2.0?
How are you filling your tables?
Robin S.
-------------------------------------
"max" <ma*@max.maxwrote in message
news:GY*****************@tornado.fastwebnet.it.. .
Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I need
to change some data in the first table and let VB updates the same data
in all other four tables in the right places.
I know it would be possible by using the ForeignKeyConstraint object. I
have created the tables using the DataSet Visual Tool and I know it
doesn't create any ForeignKeyConstraint obj. I have found many codes
examples on it, but I don't know how to merge the code they in an
existing dataset.
Hope somebody help.
Thanks in advance,
Max.
Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.
I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?
ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.
>'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.
And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.
Robin S.
-----------------------
"max" <ma*@max.maxwrote in message
news:9d***************@tornado.fastwebnet.it...
>
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have set
in Dataset Designer.
Trying to use your code, I'm in trouble about connection string, this
is mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by SqlDataAdapter.
I have this code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSe t.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPres t1)
This cames automatically from dragging the tblPrest1 from dataset to
the form, in a datagrid format. I put your code under last line. And
it doesn't work.
Because I have other four tables identical to this, I get also five
forms identical, changing only the names of form (modPrest2...) and
names of tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all
tables in datagrid format.
Then I make a change in the tblPrest1, and save the change by clicking
in the floppy disk icon. When I reload the table, changes are
effectively in use.
It seems this procedure already fill the table in dataset... or not?
I can't understand how automatically changing the other four tables
without calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:p6******************************@comcast.com. ..
>>
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?
I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)
Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.
This uses the Customers and Orders tables from the Northwind
database.
'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.
Robin S.
----------------
"max" <ma*@max.maxwrote in message
news:19*****************@tornado.fastwebnet.it. ..
>>>
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a
dataset using the Data Source Configuration Wizard. Then I created a
form, dragged the table from the Dataset which creates a
bindingnavigator obj on top of form; a second form load this form by
a button and I correctly see the data. There are only three columns
in the tables: an incremental ID (I gave it primary key), a number,
and a name. Then I need to change, insert and delete records in the
first table and replicate this change in all the other four tables,
that are identical in structure and data. I can change and update
data in the first table (because I see that changes was really on
it), but I cannot replicate in the others. I have already tried to
make relations using Data Designer, but it doesn't work; I have read
in some newsgroup that ForeignKeyConstraint cannot be created by
this way.
I'd like to understand where and how insert vb code to create
relationship between columns in the first table to all the others. I
can't realize in which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:0e******************************@comcast.co m...
What kind of database are you using? VB2003 or VB2005?
.Net 1.1 or .Net 2.0?
How are you filling your tables?
Robin S.
-------------------------------------
"max" <ma*@max.maxwrote in message
news:GY*****************@tornado.fastwebnet.it. ..
>
Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I
need to change some data in the first table and let VB updates the
same data in all other four tables in the right places.
I know it would be possible by using the ForeignKeyConstraint
object. I have created the tables using the DataSet Visual Tool
and I know it doesn't create any ForeignKeyConstraint obj. I have
found many codes examples on it, but I don't know how to merge the
code they in an existing dataset.
>
Hope somebody help.
Thanks in advance,
Max.
>
>
Ok. I loaded in this form the secon table, too, and I fill it (I hope). Then
nothing, the second table remians unchanged. I'm really getting crazy.
This is the code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSe t.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet. tblPrest2)
'TODO: questa riga di codice carica i dati nella tabella
'GestPrestDataSet.tblPrest1'. È possibile spostarla o rimuoverla se
necessario.
Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet. tblPrest1)
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_tbl1_tbl2", _
GestPrestDataSet.Tables("tblPrest1").Columns("Nome Prest"), _
GestPrestDataSet.Tables("tblPrest2").Columns("Nome Prest"))
End Sub
End Class
Thanks Robin
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:AL******************************@comcast.com. ..
Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.
I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?
ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.
>>'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.
And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.
Robin S.
-----------------------
"max" <ma*@max.maxwrote in message
news:9d***************@tornado.fastwebnet.it...
>>
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have set in
Dataset Designer.
Trying to use your code, I'm in trouble about connection string, this is
mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by SqlDataAdapter.
I have this code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDataS et.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPre st1)
This cames automatically from dragging the tblPrest1 from dataset to the
form, in a datagrid format. I put your code under last line. And it
doesn't work.
Because I have other four tables identical to this, I get also five forms
identical, changing only the names of form (modPrest2...) and names of
tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all tables
in datagrid format.
Then I make a change in the tblPrest1, and save the change by clicking in
the floppy disk icon. When I reload the table, changes are effectively in
use.
It seems this procedure already fill the table in dataset... or not?
I can't understand how automatically changing the other four tables
without calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:p6******************************@comcast.com ...
>>>
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?
I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)
Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.
This uses the Customers and Orders tables from the Northwind
database.
'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.
Robin S.
----------------
"max" <ma*@max.maxwrote in message
news:19*****************@tornado.fastwebnet.it.. .
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a
dataset using the Data Source Configuration Wizard. Then I created a
form, dragged the table from the Dataset which creates a
bindingnavigator obj on top of form; a second form load this form by a
button and I correctly see the data. There are only three columns in
the tables: an incremental ID (I gave it primary key), a number, and a
name. Then I need to change, insert and delete records in the first
table and replicate this change in all the other four tables, that are
identical in structure and data. I can change and update data in the
first table (because I see that changes was really on it), but I cannot
replicate in the others. I have already tried to make relations using
Data Designer, but it doesn't work; I have read in some newsgroup that
ForeignKeyConstraint cannot be created by this way.
I'd like to understand where and how insert vb code to create
relationship between columns in the first table to all the others. I
can't realize in which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:0e******************************@comcast.c om...
>
What kind of database are you using? VB2003 or VB2005?
.Net 1.1 or .Net 2.0?
>
How are you filling your tables?
>
Robin S.
-------------------------------------
>
"max" <ma*@max.maxwrote in message
news:GY*****************@tornado.fastwebnet.it ...
>>
>Hello,
>I am a newbye, and I'm trying to write a simple application.
>I have five tables with three columns; all tables are identical; I
>need to change some data in the first table and let VB updates the
>same data in all other four tables in the right places.
>I know it would be possible by using the ForeignKeyConstraint object.
>I have created the tables using the DataSet Visual Tool and I know it
>doesn't create any ForeignKeyConstraint obj. I have found many codes
>examples on it, but I don't know how to merge the code they in an
>existing dataset.
>>
>Hope somebody help.
>Thanks in advance,
>Max.
>>
>>
>
>
>
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:AL******************************@comcast.com. ..
Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.
I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?
ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.
>>'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.
And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.
Robin S.
-----------------------
"max" <ma*@max.maxwrote in message
news:9d***************@tornado.fastwebnet.it...
>>
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have set in
Dataset Designer.
Trying to use your code, I'm in trouble about connection string, this is
mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by SqlDataAdapter.
I have this code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDataS et.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPre st1)
This cames automatically from dragging the tblPrest1 from dataset to the
form, in a datagrid format. I put your code under last line. And it
doesn't work.
Because I have other four tables identical to this, I get also five forms
identical, changing only the names of form (modPrest2...) and names of
tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all tables
in datagrid format.
Then I make a change in the tblPrest1, and save the change by clicking in
the floppy disk icon. When I reload the table, changes are effectively in
use.
It seems this procedure already fill the table in dataset... or not?
I can't understand how automatically changing the other four tables
without calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:p6******************************@comcast.com ...
>>>
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?
I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)
Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.
This uses the Customers and Orders tables from the Northwind
database.
'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.
Robin S.
----------------
"max" <ma*@max.maxwrote in message
news:19*****************@tornado.fastwebnet.it.. .
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a
dataset using the Data Source Configuration Wizard. Then I created a
form, dragged the table from the Dataset which creates a
bindingnavigator obj on top of form; a second form load this form by a
button and I correctly see the data. There are only three columns in
the tables: an incremental ID (I gave it primary key), a number, and a
name. Then I need to change, insert and delete records in the first
table and replicate this change in all the other four tables, that are
identical in structure and data. I can change and update data in the
first table (because I see that changes was really on it), but I cannot
replicate in the others. I have already tried to make relations using
Data Designer, but it doesn't work; I have read in some newsgroup that
ForeignKeyConstraint cannot be created by this way.
I'd like to understand where and how insert vb code to create
relationship between columns in the first table to all the others. I
can't realize in which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:0e******************************@comcast.c om...
>
What kind of database are you using? VB2003 or VB2005?
.Net 1.1 or .Net 2.0?
>
How are you filling your tables?
>
Robin S.
-------------------------------------
>
"max" <ma*@max.maxwrote in message
news:GY*****************@tornado.fastwebnet.it ...
>>
>Hello,
>I am a newbye, and I'm trying to write a simple application.
>I have five tables with three columns; all tables are identical; I
>need to change some data in the first table and let VB updates the
>same data in all other four tables in the right places.
>I know it would be possible by using the ForeignKeyConstraint object.
>I have created the tables using the DataSet Visual Tool and I know it
>doesn't create any ForeignKeyConstraint obj. I have found many codes
>examples on it, but I don't know how to merge the code they in an
>existing dataset.
>>
>Hope somebody help.
>Thanks in advance,
>Max.
>>
>>
>
>
>
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:AL******************************@comcast.com. ..
Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.
I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?
ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.
>>'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.
And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.
Robin S.
-----------------------
"max" <ma*@max.maxwrote in message
news:9d***************@tornado.fastwebnet.it...
>>
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have set in
Dataset Designer.
Trying to use your code, I'm in trouble about connection string, this is
mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by SqlDataAdapter.
I have this code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDataS et.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPre st1)
This cames automatically from dragging the tblPrest1 from dataset to the
form, in a datagrid format. I put your code under last line. And it
doesn't work.
Because I have other four tables identical to this, I get also five forms
identical, changing only the names of form (modPrest2...) and names of
tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all tables
in datagrid format.
Then I make a change in the tblPrest1, and save the change by clicking in
the floppy disk icon. When I reload the table, changes are effectively in
use.
It seems this procedure already fill the table in dataset... or not?
I can't understand how automatically changing the other four tables
without calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:p6******************************@comcast.com ...
>>>
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?
I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)
Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.
This uses the Customers and Orders tables from the Northwind
database.
'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.
Robin S.
----------------
"max" <ma*@max.maxwrote in message
news:19*****************@tornado.fastwebnet.it.. .
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a
dataset using the Data Source Configuration Wizard. Then I created a
form, dragged the table from the Dataset which creates a
bindingnavigator obj on top of form; a second form load this form by a
button and I correctly see the data. There are only three columns in
the tables: an incremental ID (I gave it primary key), a number, and a
name. Then I need to change, insert and delete records in the first
table and replicate this change in all the other four tables, that are
identical in structure and data. I can change and update data in the
first table (because I see that changes was really on it), but I cannot
replicate in the others. I have already tried to make relations using
Data Designer, but it doesn't work; I have read in some newsgroup that
ForeignKeyConstraint cannot be created by this way.
I'd like to understand where and how insert vb code to create
relationship between columns in the first table to all the others. I
can't realize in which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:0e******************************@comcast.c om...
>
What kind of database are you using? VB2003 or VB2005?
.Net 1.1 or .Net 2.0?
>
How are you filling your tables?
>
Robin S.
-------------------------------------
>
"max" <ma*@max.maxwrote in message
news:GY*****************@tornado.fastwebnet.it ...
>>
>Hello,
>I am a newbye, and I'm trying to write a simple application.
>I have five tables with three columns; all tables are identical; I
>need to change some data in the first table and let VB updates the
>same data in all other four tables in the right places.
>I know it would be possible by using the ForeignKeyConstraint object.
>I have created the tables using the DataSet Visual Tool and I know it
>doesn't create any ForeignKeyConstraint obj. I have found many codes
>examples on it, but I don't know how to merge the code they in an
>existing dataset.
>>
>Hope somebody help.
>Thanks in advance,
>Max.
>>
>>
>
>
>
Sorry for the unwanted replication of messages.
I forgot to tell you to add the constraint to the table definition.
Oops. Here's some code, with the line(s) I missed (look for the
asterisks).
Here's a working example. I set up a dataset for Northwind
with Customers and Orders and deleted the constraint that was
generated so I could create one in code. Customers corresponds
to your table1; Orders corresponds to your table2.
I didn't try modifying any of my data because I don't want to muck
up my data, but it should work. (famous last words)
----------
Dim ds As NorthwindDataSet = New NorthwindDataSet()
Dim custAdapter As CustomersTableAdapter = New CustomersTableAdapter()
custAdapter.Fill(ds.Customers)
Dim orderAdapter As OrdersTableAdapter = New OrdersTableAdapter()
orderAdapter.Fill(ds.Orders)
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
'***THIS IS THE STEP I FORGOT TO INCLUDE -- Oops!
ds.Tables("Orders").Constraints.Add(fk)
ds.EnforceConstraints = True
'Note: You can use the data relation created by the fk
' constraint to read through your data:
Dim rowCustomer As NorthwindDataSet.CustomersRow
Dim rowOrder As NorthwindDataSet.OrdersRow
Dim rel As DataRelation = ds.Relations("FK_Customers_Orders")
For Each rowCustomer In ds.Customers
Console.WriteLine("Orders for {0}", rowCustomer.CompanyName)
For Each rowOrder In rowCustomer.GetChildRows(rel)
Console.WriteLine(" {0} - {1:d}", rowOrder.OrderID,
rowOrder.OrderDate)
Next rowOrder
Next rowCustomer
And here's something else I figured out: You *can* add the constraint
to your DataSet in the DataSet Designer.
To add a DataRelation, right-click on any item in the DataSet designer
(like in your table definition) and select Add and then Relation.
You can select the parent and child tables using the drop-down lists
at the top of the dialog box, and then the datacolumns just below
that.
The lower half of that dialog box lets you control whether the action
will create a DataRelation, a ForeignKeyConstraint, or both. The
default is just DataRelation, but you should choose ForeignKey or
Both. You can also specify values for the UpdateRule, DeleteRule,
and AcceptRejectChangesRule on the FK constraint. It defaults to
Cascade, Cascade, and None, which is fine.
If you have all of your tables in one DataSet that you created
through the DAtaSet designer, you can try adding the constraints
there. If you ever re-create the DataSet, you have to remember
to add the constraints, too, though.
Hope this helps.
Robin S.
-----------------------------------------------------------------
"Max" <no****@devnull.spamcop.netwrote in message
news:45**********************@news.tiscali.it...
Ok. I loaded in this form the secon table, too, and I fill it (I
hope). Then
nothing, the second table remians unchanged. I'm really getting crazy.
This is the code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSe t.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object,
ByVal e
As System.EventArgs) Handles MyBase.Load
Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet. tblPrest2)
'TODO: questa riga di codice carica i dati nella tabella
'GestPrestDataSet.tblPrest1'. È possibile spostarla o rimuoverla se
necessario.
Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet. tblPrest1)
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_tbl1_tbl2", _
GestPrestDataSet.Tables("tblPrest1").Columns("Nome Prest"),
_
GestPrestDataSet.Tables("tblPrest2").Columns("Nome Prest"))
End Sub
End Class
Thanks Robin
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:AL******************************@comcast.com. ..
>Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.
I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?
ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.
>>>'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.
And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.
Robin S.
-----------------------
"max" <ma*@max.maxwrote in message
news:9d***************@tornado.fastwebnet.it...
>>>
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have set
in Dataset Designer.
Trying to use your code, I'm in trouble about connection string,
this is mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by SqlDataAdapter.
I have this code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestData Set.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPr est1)
This cames automatically from dragging the tblPrest1 from dataset to
the form, in a datagrid format. I put your code under last line. And
it doesn't work.
Because I have other four tables identical to this, I get also five
forms identical, changing only the names of form (modPrest2...) and
names of tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all
tables in datagrid format.
Then I make a change in the tblPrest1, and save the change by
clicking in the floppy disk icon. When I reload the table, changes
are effectively in use.
It seems this procedure already fill the table in dataset... or not?
I can't understand how automatically changing the other four tables
without calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:p6******************************@comcast.co m...
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?
I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)
Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.
This uses the Customers and Orders tables from the Northwind
database.
'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.
Robin S.
----------------
"max" <ma*@max.maxwrote in message
news:19*****************@tornado.fastwebnet.it. ..
>
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a
dataset using the Data Source Configuration Wizard. Then I created
a form, dragged the table from the Dataset which creates a
bindingnavigator obj on top of form; a second form load this form
by a button and I correctly see the data. There are only three
columns in the tables: an incremental ID (I gave it primary key),
a number, and a name. Then I need to change, insert and delete
records in the first table and replicate this change in all the
other four tables, that are identical in structure and data. I can
change and update data in the first table (because I see that
changes was really on it), but I cannot replicate in the others. I
have already tried to make relations using Data Designer, but it
doesn't work; I have read in some newsgroup that
ForeignKeyConstraint cannot be created by this way.
I'd like to understand where and how insert vb code to create
relationship between columns in the first table to all the others.
I can't realize in which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
>
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:0e******************************@comcast. com...
>>
>What kind of database are you using? VB2003 or VB2005?
>.Net 1.1 or .Net 2.0?
>>
>How are you filling your tables?
>>
>Robin S.
>-------------------------------------
>>
>"max" <ma*@max.maxwrote in message
>news:GY*****************@tornado.fastwebnet.i t...
>>>
>>Hello,
>>I am a newbye, and I'm trying to write a simple application.
>>I have five tables with three columns; all tables are identical;
>>I need to change some data in the first table and let VB updates
>>the same data in all other four tables in the right places.
>>I know it would be possible by using the ForeignKeyConstraint
>>object. I have created the tables using the DataSet Visual Tool
>>and I know it doesn't create any ForeignKeyConstraint obj. I
>>have found many codes examples on it, but I don't know how to
>>merge the code they in an existing dataset.
>>>
>>Hope somebody help.
>>Thanks in advance,
>>Max.
>>>
>>>
>>
>>
>>
>
>
>
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:AL******************************@comcast.com. ..
>Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.
I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?
ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.
>>>'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.
And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.
Robin S.
-----------------------
"max" <ma*@max.maxwrote in message
news:9d***************@tornado.fastwebnet.it...
>>>
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have set
in Dataset Designer.
Trying to use your code, I'm in trouble about connection string,
this is mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by SqlDataAdapter.
I have this code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestData Set.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPr est1)
This cames automatically from dragging the tblPrest1 from dataset to
the form, in a datagrid format. I put your code under last line. And
it doesn't work.
Because I have other four tables identical to this, I get also five
forms identical, changing only the names of form (modPrest2...) and
names of tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all
tables in datagrid format.
Then I make a change in the tblPrest1, and save the change by
clicking in the floppy disk icon. When I reload the table, changes
are effectively in use.
It seems this procedure already fill the table in dataset... or not?
I can't understand how automatically changing the other four tables
without calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:p6******************************@comcast.co m...
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?
I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)
Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.
This uses the Customers and Orders tables from the Northwind
database.
'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.
Robin S.
----------------
"max" <ma*@max.maxwrote in message
news:19*****************@tornado.fastwebnet.it. ..
>
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a
dataset using the Data Source Configuration Wizard. Then I created
a form, dragged the table from the Dataset which creates a
bindingnavigator obj on top of form; a second form load this form
by a button and I correctly see the data. There are only three
columns in the tables: an incremental ID (I gave it primary key),
a number, and a name. Then I need to change, insert and delete
records in the first table and replicate this change in all the
other four tables, that are identical in structure and data. I can
change and update data in the first table (because I see that
changes was really on it), but I cannot replicate in the others. I
have already tried to make relations using Data Designer, but it
doesn't work; I have read in some newsgroup that
ForeignKeyConstraint cannot be created by this way.
I'd like to understand where and how insert vb code to create
relationship between columns in the first table to all the others.
I can't realize in which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
>
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:0e******************************@comcast. com...
>>
>What kind of database are you using? VB2003 or VB2005?
>.Net 1.1 or .Net 2.0?
>>
>How are you filling your tables?
>>
>Robin S.
>-------------------------------------
>>
>"max" <ma*@max.maxwrote in message
>news:GY*****************@tornado.fastwebnet.i t...
>>>
>>Hello,
>>I am a newbye, and I'm trying to write a simple application.
>>I have five tables with three columns; all tables are identical;
>>I need to change some data in the first table and let VB updates
>>the same data in all other four tables in the right places.
>>I know it would be possible by using the ForeignKeyConstraint
>>object. I have created the tables using the DataSet Visual Tool
>>and I know it doesn't create any ForeignKeyConstraint obj. I
>>have found many codes examples on it, but I don't know how to
>>merge the code they in an existing dataset.
>>>
>>Hope somebody help.
>>Thanks in advance,
>>Max.
>>>
>>>
>>
>>
>>
>
>
>
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:AL******************************@comcast.com. ..
>Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.
I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?
ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.
>>>'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.
And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.
Robin S.
-----------------------
"max" <ma*@max.maxwrote in message
news:9d***************@tornado.fastwebnet.it...
>>>
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have set
in Dataset Designer.
Trying to use your code, I'm in trouble about connection string,
this is mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by SqlDataAdapter.
I have this code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestData Set.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPr est1)
This cames automatically from dragging the tblPrest1 from dataset to
the form, in a datagrid format. I put your code under last line. And
it doesn't work.
Because I have other four tables identical to this, I get also five
forms identical, changing only the names of form (modPrest2...) and
names of tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all
tables in datagrid format.
Then I make a change in the tblPrest1, and save the change by
clicking in the floppy disk icon. When I reload the table, changes
are effectively in use.
It seems this procedure already fill the table in dataset... or not?
I can't understand how automatically changing the other four tables
without calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:p6******************************@comcast.co m...
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?
I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)
Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.
This uses the Customers and Orders tables from the Northwind
database.
'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.
Robin S.
----------------
"max" <ma*@max.maxwrote in message
news:19*****************@tornado.fastwebnet.it. ..
>
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a
dataset using the Data Source Configuration Wizard. Then I created
a form, dragged the table from the Dataset which creates a
bindingnavigator obj on top of form; a second form load this form
by a button and I correctly see the data. There are only three
columns in the tables: an incremental ID (I gave it primary key),
a number, and a name. Then I need to change, insert and delete
records in the first table and replicate this change in all the
other four tables, that are identical in structure and data. I can
change and update data in the first table (because I see that
changes was really on it), but I cannot replicate in the others. I
have already tried to make relations using Data Designer, but it
doesn't work; I have read in some newsgroup that
ForeignKeyConstraint cannot be created by this way.
I'd like to understand where and how insert vb code to create
relationship between columns in the first table to all the others.
I can't realize in which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
>
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:0e******************************@comcast. com...
>>
>What kind of database are you using? VB2003 or VB2005?
>.Net 1.1 or .Net 2.0?
>>
>How are you filling your tables?
>>
>Robin S.
>-------------------------------------
>>
>"max" <ma*@max.maxwrote in message
>news:GY*****************@tornado.fastwebnet.i t...
>>>
>>Hello,
>>I am a newbye, and I'm trying to write a simple application.
>>I have five tables with three columns; all tables are identical;
>>I need to change some data in the first table and let VB updates
>>the same data in all other four tables in the right places.
>>I know it would be possible by using the ForeignKeyConstraint
>>object. I have created the tables using the DataSet Visual Tool
>>and I know it doesn't create any ForeignKeyConstraint obj. I
>>have found many codes examples on it, but I don't know how to
>>merge the code they in an existing dataset.
>>>
>>Hope somebody help.
>>Thanks in advance,
>>Max.
>>>
>>>
>>
>>
>>
>
>
>
I'm still trying Robin, but seems I cannot fill the second tabel after
implementing your code in my form_load. I see it after the console.writeline
routine. Data are present only in 'tblPrest1' tabel, while, moving mouse
around the console window, I see 'nothing' when I go to examinate tblPrest2'
table. In effect, the program skips the section regarding 'each
rowTblPrest2...', because the value is 'nothing'. But I can't understand
why.
Anyway, I used the DataSet designer as the very first time to create
relationships, but it didn't work; I didn't get error messages, just no
change in 'tblPrest2'. Later, looking in newsgroup, I found somebody talking
about it's not possible to create it in this ** friendly ** way, but only by
writing code...
Thanks. I'll let you know.
PS: Are you looking at the newsgroup also in these 'holidays days'?
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:9L******************************@comcast.com. ..
>I forgot to tell you to add the constraint to the table definition.
Oops. Here's some code, with the line(s) I missed (look for the
asterisks).
Here's a working example. I set up a dataset for Northwind
with Customers and Orders and deleted the constraint that was
generated so I could create one in code. Customers corresponds
to your table1; Orders corresponds to your table2.
I didn't try modifying any of my data because I don't want to muck
up my data, but it should work. (famous last words)
----------
Dim ds As NorthwindDataSet = New NorthwindDataSet()
Dim custAdapter As CustomersTableAdapter = New CustomersTableAdapter()
custAdapter.Fill(ds.Customers)
Dim orderAdapter As OrdersTableAdapter = New OrdersTableAdapter()
orderAdapter.Fill(ds.Orders)
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
'***THIS IS THE STEP I FORGOT TO INCLUDE -- Oops!
ds.Tables("Orders").Constraints.Add(fk)
ds.EnforceConstraints = True
'Note: You can use the data relation created by the fk
' constraint to read through your data:
Dim rowCustomer As NorthwindDataSet.CustomersRow
Dim rowOrder As NorthwindDataSet.OrdersRow
Dim rel As DataRelation = ds.Relations("FK_Customers_Orders")
For Each rowCustomer In ds.Customers
Console.WriteLine("Orders for {0}", rowCustomer.CompanyName)
For Each rowOrder In rowCustomer.GetChildRows(rel)
Console.WriteLine(" {0} - {1:d}", rowOrder.OrderID,
rowOrder.OrderDate)
Next rowOrder
Next rowCustomer
And here's something else I figured out: You *can* add the constraint
to your DataSet in the DataSet Designer.
To add a DataRelation, right-click on any item in the DataSet designer
(like in your table definition) and select Add and then Relation.
You can select the parent and child tables using the drop-down lists
at the top of the dialog box, and then the datacolumns just below
that.
The lower half of that dialog box lets you control whether the action
will create a DataRelation, a ForeignKeyConstraint, or both. The
default is just DataRelation, but you should choose ForeignKey or
Both. You can also specify values for the UpdateRule, DeleteRule,
and AcceptRejectChangesRule on the FK constraint. It defaults to
Cascade, Cascade, and None, which is fine.
If you have all of your tables in one DataSet that you created
through the DAtaSet designer, you can try adding the constraints
there. If you ever re-create the DataSet, you have to remember
to add the constraints, too, though.
Hope this helps.
Robin S.
-----------------------------------------------------------------
"Max" <no****@devnull.spamcop.netwrote in message
news:45**********************@news.tiscali.it...
>Ok. I loaded in this form the secon table, too, and I fill it (I hope).
Then
nothing, the second table remians unchanged. I'm really getting crazy.
This is the code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSe t.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet. tblPrest2)
'TODO: questa riga di codice carica i dati nella tabella
'GestPrestDataSet.tblPrest1'. È possibile spostarla o rimuoverla se
necessario.
Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet. tblPrest1)
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_tbl1_tbl2", _
GestPrestDataSet.Tables("tblPrest1").Columns("Nome Prest"), _
GestPrestDataSet.Tables("tblPrest2").Columns("Nome Prest"))
End Sub
End Class
Thanks Robin
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:AL******************************@comcast.com ...
>>Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.
I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?
ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.
And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.
Robin S.
-----------------------
"max" <ma*@max.maxwrote in message
news:9d***************@tornado.fastwebnet.it.. .
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have set in
Dataset Designer.
Trying to use your code, I'm in trouble about connection string, this
is mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by SqlDataAdapter.
I have this code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDat aSet.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblP rest1)
This cames automatically from dragging the tblPrest1 from dataset to
the form, in a datagrid format. I put your code under last line. And it
doesn't work.
Because I have other four tables identical to this, I get also five
forms identical, changing only the names of form (modPrest2...) and
names of tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all tables
in datagrid format.
Then I make a change in the tblPrest1, and save the change by clicking
in the floppy disk icon. When I reload the table, changes are
effectively in use.
It seems this procedure already fill the table in dataset... or not?
I can't understand how automatically changing the other four tables
without calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:p6******************************@comcast.c om...
>
>
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?
>
I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)
>
Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.
>
This uses the Customers and Orders tables from the Northwind
database.
>
'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
>
'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
>
Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.
>
Robin S.
I don't understand why you can't fill both tables, either. Are you
filling both tables *before* adding the constraint?
One thing is, it's a *constraint*, so every entry in the child table
*must* have a corresponding entry in the parent table. IS that true?
Yes, I'll be around during the holidays.
Robin S.
---------------------------------
"Max" <no****@devnull.spamcop.netwrote in message
news:45**********************@news.tiscali.it...
I'm still trying Robin, but seems I cannot fill the second tabel after
implementing your code in my form_load. I see it after the
console.writeline routine. Data are present only in 'tblPrest1' tabel,
while, moving mouse around the console window, I see 'nothing' when I
go to examinate tblPrest2' table. In effect, the program skips the
section regarding 'each rowTblPrest2...', because the value is
'nothing'. But I can't understand why.
Anyway, I used the DataSet designer as the very first time to create
relationships, but it didn't work; I didn't get error messages, just
no change in 'tblPrest2'. Later, looking in newsgroup, I found
somebody talking about it's not possible to create it in this **
friendly ** way, but only by writing code...
Thanks. I'll let you know.
PS: Are you looking at the newsgroup also in these 'holidays days'?
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:9L******************************@comcast.com. ..
>>I forgot to tell you to add the constraint to the table definition.
Oops. Here's some code, with the line(s) I missed (look for the
asterisks).
Here's a working example. I set up a dataset for Northwind
with Customers and Orders and deleted the constraint that was
generated so I could create one in code. Customers corresponds
to your table1; Orders corresponds to your table2.
I didn't try modifying any of my data because I don't want to muck
up my data, but it should work. (famous last words)
----------
Dim ds As NorthwindDataSet = New NorthwindDataSet()
Dim custAdapter As CustomersTableAdapter = New
CustomersTableAdapter()
custAdapter.Fill(ds.Customers)
Dim orderAdapter As OrdersTableAdapter = New OrdersTableAdapter()
orderAdapter.Fill(ds.Orders)
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
'***THIS IS THE STEP I FORGOT TO INCLUDE -- Oops!
ds.Tables("Orders").Constraints.Add(fk)
ds.EnforceConstraints = True
'Note: You can use the data relation created by the fk
' constraint to read through your data:
Dim rowCustomer As NorthwindDataSet.CustomersRow
Dim rowOrder As NorthwindDataSet.OrdersRow
Dim rel As DataRelation = ds.Relations("FK_Customers_Orders")
For Each rowCustomer In ds.Customers
Console.WriteLine("Orders for {0}", rowCustomer.CompanyName)
For Each rowOrder In rowCustomer.GetChildRows(rel)
Console.WriteLine(" {0} - {1:d}", rowOrder.OrderID,
rowOrder.OrderDate)
Next rowOrder
Next rowCustomer
And here's something else I figured out: You *can* add the constraint
to your DataSet in the DataSet Designer.
To add a DataRelation, right-click on any item in the DataSet
designer
(like in your table definition) and select Add and then Relation.
You can select the parent and child tables using the drop-down lists
at the top of the dialog box, and then the datacolumns just below
that.
The lower half of that dialog box lets you control whether the action
will create a DataRelation, a ForeignKeyConstraint, or both. The
default is just DataRelation, but you should choose ForeignKey or
Both. You can also specify values for the UpdateRule, DeleteRule,
and AcceptRejectChangesRule on the FK constraint. It defaults to
Cascade, Cascade, and None, which is fine.
If you have all of your tables in one DataSet that you created
through the DAtaSet designer, you can try adding the constraints
there. If you ever re-create the DataSet, you have to remember
to add the constraints, too, though.
Hope this helps.
Robin S.
-----------------------------------------------------------------
"Max" <no****@devnull.spamcop.netwrote in message
news:45**********************@news.tiscali.it.. .
>>Ok. I loaded in this form the secon table, too, and I fill it (I
hope). Then
nothing, the second table remians unchanged. I'm really getting
crazy.
This is the code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender
As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestData Set.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object,
ByVal e
As System.EventArgs) Handles MyBase.Load
Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet. tblPrest2)
'TODO: questa riga di codice carica i dati nella tabella
'GestPrestDataSet.tblPrest1'. È possibile spostarla o rimuoverla se
necessario.
Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet. tblPrest1)
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_tbl1_tbl2", _
GestPrestDataSet.Tables("tblPrest1").Columns("No mePrest"), _
GestPrestDataSet.Tables("tblPrest2").Columns("No mePrest"))
End Sub
End Class
Thanks Robin
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:AL******************************@comcast.co m...
Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code
defining
the Foreign Key Restraint. I included the other just to show
position.
I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?
ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.
>'now add the foreign key constraint
>Dim fk as ForeignKeyConstraint
>fk = New ForeignKeyConstraint("FK_Customers_Orders", _
> ds.Tables("Customers").Columns("CustomerID"), _
> ds.Tables("Orders").Columns("CustomerID"))
If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.
And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.
Robin S.
-----------------------
"max" <ma*@max.maxwrote in message
news:9d***************@tornado.fastwebnet.it. ..
>
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have
set in Dataset Designer.
Trying to use your code, I'm in trouble about connection string,
this is mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by
SqlDataAdapter.
>
I have this code:
>
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender
As System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDa taSet.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tbl Prest1)
>
This cames automatically from dragging the tblPrest1 from dataset
to the form, in a datagrid format. I put your code under last
line. And it doesn't work.
Because I have other four tables identical to this, I get also
five forms identical, changing only the names of form
(modPrest2...) and names of tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all
tables in datagrid format.
Then I make a change in the tblPrest1, and save the change by
clicking in the floppy disk icon. When I reload the table, changes
are effectively in use.
It seems this procedure already fill the table in dataset... or
not?
I can't understand how automatically changing the other four
tables without calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
>
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:p6******************************@comcast. com...
>>
>>
>Do you have no relationships defined between the tables
>in Access? Do they not show up in the Dataset Designer?
>>
>I'm not going to swear this will work. But you could
>try adding your own foreign key constraints. I'm also not
>sure where you would do this, but I would guess *after* the
>dataset is filled, and *before* you do the binding. (I'm
>assuming you're doing the binding in code, but maybe not?)
>>
>Here's an example on setting up a foreign key constraint.
>This is from David Sceppa's book ADO.Net 2.0 The Core
>Reference.
>>
>This uses the Customers and Orders tables from the Northwind
>database.
>>
>'how to define a foreign key constraint
>'first, fill your dataset with the two tables involved
>'this is mine; this is loading from SQLServer, but
>' load it however you're loading it from Access
>Dim ss As String = "SELECT * FROM Customers; SELECT * FROM
>Orders"
>Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
>da.TableMappings.Add("Table","Customers")
>da.TableMappings.Add("Table1","Orders")
>Dim ds as New DataSet()
>da.Fill(ds)
>>
>'now add the foreign key constraint
>Dim fk as ForeignKeyConstraint
>fk = New ForeignKeyConstraint("FK_Customers_Orders", _
> ds.Tables("Customers").Columns("CustomerID"), _
> ds.Tables("Orders").Columns("CustomerID"))
>>
>Maybe if you play around with the code in this example,
>you can figure out some way to get it to work. If you want
>more help, you're going to need to post some code.
>>
>Robin S.
The two tables are identical, 73 record, with 3 columns each, prepared in
Access. As I post before, the tables are filled by default by DataSet
designer, before the constraint by this code:
Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet. tblPrest1)
Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet. tblPrest2)
I'll back to program on tuesday, but I'll take a look at the ng. I'll let
you know.
Thanks and Merry Christmas.
Max
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:jY******************************@comcast.com. ..
>I don't understand why you can't fill both tables, either. Are you
filling both tables *before* adding the constraint?
One thing is, it's a *constraint*, so every entry in the child table
*must* have a corresponding entry in the parent table. IS that true?
Yes, I'll be around during the holidays.
Robin S.
---------------------------------
"Max" <no****@devnull.spamcop.netwrote in message
news:45**********************@news.tiscali.it...
>I'm still trying Robin, but seems I cannot fill the second tabel after
implementing your code in my form_load. I see it after the
console.writeline routine. Data are present only in 'tblPrest1' tabel,
while, moving mouse around the console window, I see 'nothing' when I go
to examinate tblPrest2' table. In effect, the program skips the section
regarding 'each rowTblPrest2...', because the value is 'nothing'. But I
can't understand why.
Anyway, I used the DataSet designer as the very first time to create
relationships, but it didn't work; I didn't get error messages, just no
change in 'tblPrest2'. Later, looking in newsgroup, I found somebody
talking about it's not possible to create it in this ** friendly ** way,
but only by writing code...
Thanks. I'll let you know.
PS: Are you looking at the newsgroup also in these 'holidays days'?
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:9L******************************@comcast.com ...
>>>I forgot to tell you to add the constraint to the table definition.
Oops. Here's some code, with the line(s) I missed (look for the
asterisks).
Here's a working example. I set up a dataset for Northwind
with Customers and Orders and deleted the constraint that was
generated so I could create one in code. Customers corresponds
to your table1; Orders corresponds to your table2.
I didn't try modifying any of my data because I don't want to muck
up my data, but it should work. (famous last words)
----------
Dim ds As NorthwindDataSet = New NorthwindDataSet()
Dim custAdapter As CustomersTableAdapter = New CustomersTableAdapter()
custAdapter.Fill(ds.Customers)
Dim orderAdapter As OrdersTableAdapter = New OrdersTableAdapter()
orderAdapter.Fill(ds.Orders)
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
'***THIS IS THE STEP I FORGOT TO INCLUDE -- Oops!
ds.Tables("Orders").Constraints.Add(fk)
ds.EnforceConstraints = True
'Note: You can use the data relation created by the fk
' constraint to read through your data:
Dim rowCustomer As NorthwindDataSet.CustomersRow
Dim rowOrder As NorthwindDataSet.OrdersRow
Dim rel As DataRelation = ds.Relations("FK_Customers_Orders")
For Each rowCustomer In ds.Customers
Console.WriteLine("Orders for {0}", rowCustomer.CompanyName)
For Each rowOrder In rowCustomer.GetChildRows(rel)
Console.WriteLine(" {0} - {1:d}", rowOrder.OrderID,
rowOrder.OrderDate)
Next rowOrder
Next rowCustomer
And here's something else I figured out: You *can* add the constraint
to your DataSet in the DataSet Designer.
To add a DataRelation, right-click on any item in the DataSet designer
(like in your table definition) and select Add and then Relation.
You can select the parent and child tables using the drop-down lists
at the top of the dialog box, and then the datacolumns just below
that.
The lower half of that dialog box lets you control whether the action
will create a DataRelation, a ForeignKeyConstraint, or both. The
default is just DataRelation, but you should choose ForeignKey or
Both. You can also specify values for the UpdateRule, DeleteRule,
and AcceptRejectChangesRule on the FK constraint. It defaults to
Cascade, Cascade, and None, which is fine.
If you have all of your tables in one DataSet that you created
through the DAtaSet designer, you can try adding the constraints
there. If you ever re-create the DataSet, you have to remember
to add the constraints, too, though.
Hope this helps.
Robin S.
-----------------------------------------------------------------
"Max" <no****@devnull.spamcop.netwrote in message
news:45**********************@news.tiscali.it. ..
Ok. I loaded in this form the secon table, too, and I fill it (I hope).
Then
nothing, the second table remians unchanged. I'm really getting crazy.
This is the code:
Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDat aSet.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal
e
As System.EventArgs) Handles MyBase.Load
Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet. tblPrest2)
'TODO: questa riga di codice carica i dati nella tabella
'GestPrestDataSet.tblPrest1'. È possibile spostarla o rimuoverla se
necessario.
Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet. tblPrest1)
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_tbl1_tbl2", _
GestPrestDataSet.Tables("tblPrest1").Columns("N omePrest"), _
GestPrestDataSet.Tables("tblPrest2").Columns("N omePrest"))
End Sub
End Class
Thanks Robin
"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
news:AL******************************@comcast.c om...
Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.
>
I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?
>
ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.
>
>>'now add the foreign key constraint
>>Dim fk as ForeignKeyConstraint
>>fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>> ds.Tables("Customers").Columns("CustomerID"), _
>> ds.Tables("Orders").Columns("CustomerID"))
>
If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.
>
And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.
>
Robin S.
-----------------------
>
"max" <ma*@max.maxwrote in message
news:9d***************@tornado.fastwebnet.it.. .
>>
>I'm sorry, but I really can't solve the problem.
>I don't have any relationships defined in Access, neither I have set
>in Dataset Designer.
>Trying to use your code, I'm in trouble about connection string, this
>is mine (I put the code in a Form_Load environment):
>Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
>I get an error in "Provider", it's not recognized by SqlDataAdapter.
>>
>I have this code:
>>
>Public Class Form_modPrest1
>Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>System.Object, ByVal e As System.EventArgs) Handles
>TblPrest1BindingNavigatorSaveItem.Click
>Me.Validate()
>Me.TblPrest1BindingSource.EndEdit()
>Me.TblPrest1TableAdapter.Update(Me.GestPrestD ataSet.tblPrest1)
>End Sub
>Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal
>e As System.EventArgs) Handles MyBase.Load
>Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tb lPrest1)
>>
>This cames automatically from dragging the tblPrest1 from dataset to
>the form, in a datagrid format. I put your code under last line. And
>it doesn't work.
>Because I have other four tables identical to this, I get also five
>forms identical, changing only the names of form (modPrest2...) and
>names of tables (tblPrest2...)
>Then I load these forms by a menu, and I see the contents of all
>tables in datagrid format.
>Then I make a change in the tblPrest1, and save the change by
>clicking in the floppy disk icon. When I reload the table, changes
>are effectively in use.
>It seems this procedure already fill the table in dataset... or not?
>I can't understand how automatically changing the other four tables
>without calling each one by one.
>I'm sorry for this but I'm really at the beginning about database
>programming.
>Please help and thanks for your patience.
>>
>"RobinS" <Ro****@NoSpam.yah.noneha scritto nel messaggio
>news:p6******************************@comcast .com...
>>>
>>>
>>Do you have no relationships defined between the tables
>>in Access? Do they not show up in the Dataset Designer?
>>>
>>I'm not going to swear this will work. But you could
>>try adding your own foreign key constraints. I'm also not
>>sure where you would do this, but I would guess *after* the
>>dataset is filled, and *before* you do the binding. (I'm
>>assuming you're doing the binding in code, but maybe not?)
>>>
>>Here's an example on setting up a foreign key constraint.
>>This is from David Sceppa's book ADO.Net 2.0 The Core
>>Reference.
>>>
>>This uses the Customers and Orders tables from the Northwind
>>database.
>>>
>>'how to define a foreign key constraint
>>'first, fill your dataset with the two tables involved
>>'this is mine; this is loading from SQLServer, but
>>' load it however you're loading it from Access
>>Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
>>Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
>>da.TableMappings.Add("Table","Customers")
>>da.TableMappings.Add("Table1","Orders")
>>Dim ds as New DataSet()
>>da.Fill(ds)
>>>
>>'now add the foreign key constraint
>>Dim fk as ForeignKeyConstraint
>>fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>> ds.Tables("Customers").Columns("CustomerID"), _
>> ds.Tables("Orders").Columns("CustomerID"))
>>>
>>Maybe if you play around with the code in this example,
>>you can figure out some way to get it to work. If you want
>>more help, you're going to need to post some code.
>>>
>>Robin S.
max wrote:
Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I need to
change some data in the first table and let VB updates the same data in all
other four tables in the right places.
I know it would be possible by using the ForeignKeyConstraint object. I have
created the tables using the DataSet Visual Tool and I know it doesn't
create any ForeignKeyConstraint obj. I have found many codes examples on it,
but I don't know how to merge the code they in an existing dataset.
<snip>
I really didn't get what you're trying. And it seems you're mistaking
what a foreign key really is.
>From what I got you want five tables identical to each other that get
updated when one of then is updated. This design seems very odd to me
(why one would want five identical tables in the same database?), but I
assume you have your reasons. It's this, or I completely misunderstood
the layout of the tables -- for instance, they may be related somehow,
in a way you didn't describe (or I failled to understand).
Anyway, a foreign key constraint won't give exectly what you want.
A foreign key (or FK) is a column in a table that holds *primary keys*
of another table. This way you become assured that the foreign element
really exists in the other table (also called foreign, or parent
table).
Consider for example a table B, with a column IDB (it's primary key, or
PK) and other columns with data. I may have a table A, that references
rows in table B. The columns in A that references the rows in B will be
foreign keys in A to B.
For instance, table A could have the columns IDA ( it's PK), IDB (an FK
to table B, whose value would be, as you probalbly guessed, taken from
column B.IDB), and other columns, that we won't care here.
In this layout, B would be "a" parent table for A. Each row in A would
have to have a valid value in the IDB column, that is, the value in
A.IDB would have to be either NULL or an existing value in B.IDB.
More over, if a row in B was to be deleted and there existed rows in A
that referenced that row, an error would occur (well, not necessarily.
Read on). In the same token, if a row in B had the value of IDB
updated, an error would occur if there existed references to that row
in A.
The name of this game is "referencial integrity".
Notice that you may configure your FK constraint to delete the items
from table A that referenced items in table B when those are deleted.
Also, you may configure it to automagically update the value in A.IDB
whenever the corresponding B.IDB becomes updated.
This may seem exactly what you want, but, as I said before, an FK can
only reference the *primary key* of the foreign table, and this doesn't
seem to be the case of your tables.
Even if you managed to define FKs from your table to other tables in
your setup, you'd still have a problem. Because you're defining the FK
constraint in code (not in the database), you'd have to load the data
from *all* the referenced tables into the application before the
constraint could be applied. This may be feasible, but if you have
large tables it may become prohibitive, performance-wise.
It seems a more detailed description of what you really are up to is
needed here...
HTH.
Regards,
Branco.
Thanks Branco,
probably I'm talking about FK without knowing exactly what a FK is. But, if
it is the case, it's a misunderstanding coming from a lot of confusion
generated by reading too many manuals, newsgroups, online help and much
more.
I wish to explain why I need five identicals tables. I want write an
application based on medical issues; in a single medical session, a single
patient could have many different medical visits (I extimated a max number
of five), because in the medical office there are many different doctors
making different visits. So, for example, Mr. Smith, in the same day, could
have three different visits (cardiologist, dentist and dermatologist). In a
form, I have a box for the patients (coming from a 'patient' table), the
date, and the five possible visits that the patient could have in that day.
In these five tables (called tblPrest1... tblPrest5), there are three
columns: a progressive ID of the medical visit, the kind of medical visit,
and the price related. If the boss wants to change the price of most of the
medical visits, for example in the new year, I want that the operator
changes it just in a table and replicate the change in all other four
tables, without going to change the price one table at a time.
The five tables was already created in Access and filled with the actual
medical visits and related price. I'm making an application in vb2005 that
connects to the .mdb file (and it already works), and permits at the
operators the updating, deleting and importing new records as needed.
At this point, after reading your answer, I really don't know if using FK is
what I really need. Effectively, using relations, I noted the pk issues you
described and, because the operators may need to change either the price and
the type of medical visit when needed, I can realize that it's probably not
possible using relations.
The question is: which different methods can I use to achieve this?
I'm sorry with RobinS if I omitted this long explanation.
Thanks,
Max
"Branco Medeiros" <br*************@gmail.comha scritto nel messaggio
news:11**********************@n51g2000cwc.googlegr oups.com...
max wrote:
>Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I need
to
change some data in the first table and let VB updates the same data in
all
other four tables in the right places.
I know it would be possible by using the ForeignKeyConstraint object. I
have
created the tables using the DataSet Visual Tool and I know it doesn't
create any ForeignKeyConstraint obj. I have found many codes examples on
it,
but I don't know how to merge the code they in an existing dataset.
<snip>
I really didn't get what you're trying. And it seems you're mistaking
what a foreign key really is.
>>From what I got you want five tables identical to each other that get
updated when one of then is updated. This design seems very odd to me
(why one would want five identical tables in the same database?), but I
assume you have your reasons. It's this, or I completely misunderstood
the layout of the tables -- for instance, they may be related somehow,
in a way you didn't describe (or I failled to understand).
Anyway, a foreign key constraint won't give exectly what you want.
A foreign key (or FK) is a column in a table that holds *primary keys*
of another table. This way you become assured that the foreign element
really exists in the other table (also called foreign, or parent
table).
Consider for example a table B, with a column IDB (it's primary key, or
PK) and other columns with data. I may have a table A, that references
rows in table B. The columns in A that references the rows in B will be
foreign keys in A to B.
For instance, table A could have the columns IDA ( it's PK), IDB (an FK
to table B, whose value would be, as you probalbly guessed, taken from
column B.IDB), and other columns, that we won't care here.
In this layout, B would be "a" parent table for A. Each row in A would
have to have a valid value in the IDB column, that is, the value in
A.IDB would have to be either NULL or an existing value in B.IDB.
More over, if a row in B was to be deleted and there existed rows in A
that referenced that row, an error would occur (well, not necessarily.
Read on). In the same token, if a row in B had the value of IDB
updated, an error would occur if there existed references to that row
in A.
The name of this game is "referencial integrity".
Notice that you may configure your FK constraint to delete the items
from table A that referenced items in table B when those are deleted.
Also, you may configure it to automagically update the value in A.IDB
whenever the corresponding B.IDB becomes updated.
This may seem exactly what you want, but, as I said before, an FK can
only reference the *primary key* of the foreign table, and this doesn't
seem to be the case of your tables.
Even if you managed to define FKs from your table to other tables in
your setup, you'd still have a problem. Because you're defining the FK
constraint in code (not in the database), you'd have to load the data
from *all* the referenced tables into the application before the
constraint could be applied. This may be feasible, but if you have
large tables it may become prohibitive, performance-wise.
It seems a more detailed description of what you really are up to is
needed here...
HTH.
Regards,
Branco.
Max wrote:
<back posted/>
It seems to me that there are many approaches to your problem. The
separate tables, although looking practical, would have some serious
issues. One of them is the inhability to automatically update all the
other tables in response to changes in one of the them. Other issue is
the possibility of a patient having more than five visists in a given
day (who knows, sh*te happens...). Even if there are less than five
visits, you'd have the issue of the ID not being synchronized between
tables. A maintenance hell, IMHO.
I didn't understand very well the issue of the visit pricing, but it
seems you may keep everything in a single Visit table and decouple the
items that would be common to all the visits of a given patient in a
single day and put then in a separate table. Something in the likes of:
Visit table:
VisitID (PK)
PatientID (FK to the patients table)
VisitKind (probably an FK to a simple visit description table)
VisitInfoID (an FK to a VisitInfo table)
... other fields unique to a visit, such as visit time, doctor name,
duration, etc
VisitInfo table:
VisitInfoID (PK)
VisitDate
VisitPrice
... other fields common to all visits of a patient in a given day
HTH.
Regards,
Branco.
Thanks Branco,
probably I'm talking about FK without knowing exactly what a FK is. But, if
it is the case, it's a misunderstanding coming from a lot of confusion
generated by reading too many manuals, newsgroups, online help and much
more.
I wish to explain why I need five identicals tables. I want write an
application based on medical issues; in a single medical session, a single
patient could have many different medical visits (I extimated a max number
of five), because in the medical office there are many different doctors
making different visits. So, for example, Mr. Smith, in the same day, could
have three different visits (cardiologist, dentist and dermatologist). In a
form, I have a box for the patients (coming from a 'patient' table), the
date, and the five possible visits that the patient could have in that day.
In these five tables (called tblPrest1... tblPrest5), there are three
columns: a progressive ID of the medical visit, the kind of medical visit,
and the price related. If the boss wants to change the price of most of the
medical visits, for example in the new year, I want that the operator
changes it just in a table and replicate the change in all other four
tables, without going to change the price one table at a time.
The five tables was already created in Access and filled with the actual
medical visits and related price. I'm making an application in vb2005 that
connects to the .mdb file (and it already works), and permits at the
operators the updating, deleting and importing new records as needed.
At this point, after reading your answer, I really don't know if using FK is
what I really need. Effectively, using relations, I noted the pk issues you
described and, because the operators may need to change either the price and
the type of medical visit when needed, I can realize that it's probably not
possible using relations.
The question is: which different methods can I use to achieve this?
I'm sorry with RobinS if I omitted this long explanation.
Thanks,
Max
<snip>
For what it's worth, I agree with Branco Medeiros.
Robin S.
--------------------------------
"Branco Medeiros" <br*************@gmail.comwrote in message
news:11*********************@a3g2000cwd.googlegrou ps.com...
Max wrote:
<back posted/>
It seems to me that there are many approaches to your problem. The
separate tables, although looking practical, would have some serious
issues. One of them is the inhability to automatically update all the
other tables in response to changes in one of the them. Other issue is
the possibility of a patient having more than five visists in a given
day (who knows, sh*te happens...). Even if there are less than five
visits, you'd have the issue of the ID not being synchronized between
tables. A maintenance hell, IMHO.
I didn't understand very well the issue of the visit pricing, but it
seems you may keep everything in a single Visit table and decouple the
items that would be common to all the visits of a given patient in a
single day and put then in a separate table. Something in the likes
of:
Visit table:
VisitID (PK)
PatientID (FK to the patients table)
VisitKind (probably an FK to a simple visit description table)
VisitInfoID (an FK to a VisitInfo table)
... other fields unique to a visit, such as visit time, doctor name,
duration, etc
VisitInfo table:
VisitInfoID (PK)
VisitDate
VisitPrice
... other fields common to all visits of a patient in a given day
HTH.
Regards,
Branco.
>Thanks Branco,
probably I'm talking about FK without knowing exactly what a FK is.
But, if
it is the case, it's a misunderstanding coming from a lot of
confusion
generated by reading too many manuals, newsgroups, online help and
much
more.
I wish to explain why I need five identicals tables. I want write an
application based on medical issues; in a single medical session, a
single
patient could have many different medical visits (I extimated a max
number
of five), because in the medical office there are many different
doctors
making different visits. So, for example, Mr. Smith, in the same day,
could
have three different visits (cardiologist, dentist and
dermatologist). In a
form, I have a box for the patients (coming from a 'patient' table),
the
date, and the five possible visits that the patient could have in
that day.
In these five tables (called tblPrest1... tblPrest5), there are three
columns: a progressive ID of the medical visit, the kind of medical
visit,
and the price related. If the boss wants to change the price of most
of the
medical visits, for example in the new year, I want that the operator
changes it just in a table and replicate the change in all other four
tables, without going to change the price one table at a time.
The five tables was already created in Access and filled with the
actual
medical visits and related price. I'm making an application in vb2005
that
connects to the .mdb file (and it already works), and permits at the
operators the updating, deleting and importing new records as needed.
At this point, after reading your answer, I really don't know if
using FK is
what I really need. Effectively, using relations, I noted the pk
issues you
described and, because the operators may need to change either the
price and
the type of medical visit when needed, I can realize that it's
probably not
possible using relations.
The question is: which different methods can I use to achieve this?
I'm sorry with RobinS if I omitted this long explanation.
Thanks,
Max
<snip>
Branco Medeiros wrote:
<back posted/>
Well, the max number of visits (5) in a single day has decided by the boss
of the office, so it would be ok to me, too. So, it's not a problem of mine.
:) I don't understand why, if I have less than five visits, I don't have ID
synchronized between tables. The five tables are identicals from the
beginning, and the ID is always the same in the other tables, so every
changes in a record in one table should replicate to the others...
About the visit pricing: there are more than 70 different type of medical
visits, and each one has a price. So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.
If the boss decides to change the prices of, for i.e., 45 type of medical
visits, the changes must reflect to the other four tables.
More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits, the
other combo box will be blank);
Doctor's name.
That's it.
I just need to change the Price column in one table, and make automatically
the changes (update, delete or insert) in the other four tables.
It sounds simple...
Thanks,
Max
It seems to me that there are many approaches to your problem. The
separate tables, although looking practical, would have some serious
issues. One of them is the inhability to automatically update all the
other tables in response to changes in one of the them. Other issue is
the possibility of a patient having more than five visists in a given
day (who knows, sh*te happens...). Even if there are less than five
visits, you'd have the issue of the ID not being synchronized between
tables. A maintenance hell, IMHO.
I didn't understand very well the issue of the visit pricing, but it
seems you may keep everything in a single Visit table and decouple the
items that would be common to all the visits of a given patient in a
single day and put then in a separate table. Something in the likes of:
Visit table:
VisitID (PK)
PatientID (FK to the patients table)
VisitKind (probably an FK to a simple visit description table)
VisitInfoID (an FK to a VisitInfo table)
... other fields unique to a visit, such as visit time, doctor name,
duration, etc
VisitInfo table:
VisitInfoID (PK)
VisitDate
VisitPrice
... other fields common to all visits of a patient in a given day
HTH.
Regards,
Branco.
>Thanks Branco,
probably I'm talking about FK without knowing exactly what a FK is. But,
if
it is the case, it's a misunderstanding coming from a lot of confusion
generated by reading too many manuals, newsgroups, online help and much
more.
I wish to explain why I need five identicals tables. I want write an
application based on medical issues; in a single medical session, a
single
patient could have many different medical visits (I extimated a max
number
of five), because in the medical office there are many different doctors
making different visits. So, for example, Mr. Smith, in the same day,
could
have three different visits (cardiologist, dentist and dermatologist). In
a
form, I have a box for the patients (coming from a 'patient' table), the
date, and the five possible visits that the patient could have in that
day.
In these five tables (called tblPrest1... tblPrest5), there are three
columns: a progressive ID of the medical visit, the kind of medical
visit,
and the price related. If the boss wants to change the price of most of
the
medical visits, for example in the new year, I want that the operator
changes it just in a table and replicate the change in all other four
tables, without going to change the price one table at a time.
The five tables was already created in Access and filled with the actual
medical visits and related price. I'm making an application in vb2005
that
connects to the .mdb file (and it already works), and permits at the
operators the updating, deleting and importing new records as needed.
At this point, after reading your answer, I really don't know if using FK
is
what I really need. Effectively, using relations, I noted the pk issues
you
described and, because the operators may need to change either the price
and
the type of medical visit when needed, I can realize that it's probably
not
possible using relations.
The question is: which different methods can I use to achieve this?
I'm sorry with RobinS if I omitted this long explanation.
Thanks,
Max
<snip>
Forgot to describe how I'm planning to make the changes in the five tables.
I just call the form from a menu 'Tools' (something like that:
Menu---'Tools'---'Change Visit table'), with the datagrid dragged on it,
binding to the first table. I have already made this, and this works fine
for the first table.
Max wrote:
>
Branco Medeiros wrote:
<back posted/>
Well, the max number of visits (5) in a single day has decided by the boss
of the office, so it would be ok to me, too. So, it's not a problem of
mine. :) I don't understand why, if I have less than five visits, I don't
have ID synchronized between tables. The five tables are identicals from
the beginning, and the ID is always the same in the other tables, so every
changes in a record in one table should replicate to the others...
About the visit pricing: there are more than 70 different type of medical
visits, and each one has a price. So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.
If the boss decides to change the prices of, for i.e., 45 type of medical
visits, the changes must reflect to the other four tables.
More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits, the
other combo box will be blank);
Doctor's name.
That's it.
I just need to change the Price column in one table, and make
automatically the changes (update, delete or insert) in the other four
tables.
It sounds simple...
Thanks,
Max
>It seems to me that there are many approaches to your problem. The
separate tables, although looking practical, would have some serious
issues. One of them is the inhability to automatically update all the
other tables in response to changes in one of the them. Other issue is
the possibility of a patient having more than five visists in a given
day (who knows, sh*te happens...). Even if there are less than five
visits, you'd have the issue of the ID not being synchronized between
tables. A maintenance hell, IMHO.
I didn't understand very well the issue of the visit pricing, but it
seems you may keep everything in a single Visit table and decouple the
items that would be common to all the visits of a given patient in a
single day and put then in a separate table. Something in the likes of:
Visit table:
VisitID (PK)
PatientID (FK to the patients table)
VisitKind (probably an FK to a simple visit description table)
VisitInfoID (an FK to a VisitInfo table)
... other fields unique to a visit, such as visit time, doctor name,
duration, etc
VisitInfo table:
VisitInfoID (PK)
VisitDate
VisitPrice
... other fields common to all visits of a patient in a given day
HTH.
Regards,
Branco.
>>Thanks Branco,
probably I'm talking about FK without knowing exactly what a FK is. But,
if
it is the case, it's a misunderstanding coming from a lot of confusion
generated by reading too many manuals, newsgroups, online help and much
more.
I wish to explain why I need five identicals tables. I want write an
application based on medical issues; in a single medical session, a
single
patient could have many different medical visits (I extimated a max
number
of five), because in the medical office there are many different doctors
making different visits. So, for example, Mr. Smith, in the same day,
could
have three different visits (cardiologist, dentist and dermatologist).
In a
form, I have a box for the patients (coming from a 'patient' table), the
date, and the five possible visits that the patient could have in that
day.
In these five tables (called tblPrest1... tblPrest5), there are three
columns: a progressive ID of the medical visit, the kind of medical
visit,
and the price related. If the boss wants to change the price of most of
the
medical visits, for example in the new year, I want that the operator
changes it just in a table and replicate the change in all other four
tables, without going to change the price one table at a time.
The five tables was already created in Access and filled with the actual
medical visits and related price. I'm making an application in vb2005
that
connects to the .mdb file (and it already works), and permits at the
operators the updating, deleting and importing new records as needed.
At this point, after reading your answer, I really don't know if using
FK is
what I really need. Effectively, using relations, I noted the pk issues
you
described and, because the operators may need to change either the price
and
the type of medical visit when needed, I can realize that it's probably
not
possible using relations.
The question is: which different methods can I use to achieve this?
I'm sorry with RobinS if I omitted this long explanation.
Thanks,
Max
<snip>
Max wrote:
Well, the max number of visits (5) in a single day has decided by the boss
of the office, so it would be ok to me, too. So, it's not a problem of mine.
:) I don't understand why, if I have less than five visits, I don't have ID
synchronized between tables. The five tables are identicals from the
beginning, and the ID is always the same in the other tables, so every
changes in a record in one table should replicate to the others...
It will always depend on how you generate these IDs (I must warn you
that there are lots of lines of thought in this matter, every one
claiming to be the definitive one).
Personaly, I prefer having a table's ID/PK as an identity column
managed by the system (an autoincrement column, in Access), . Other
people may prefer to generate the keys themselves. Others still will
prefer to use something completely meaningless, say, a GUID, or
whatever (btw, I am, too, among the ones that think that the PK is
meant *only* to pinpoint a given record, but I prefer using a
light-weight identity column for that).
Autoincrement IDs are completely ruled out, for you. Because they're
managed by the DB engine, as soon as you have a situation with, say,
three visits only, the two other tables will become out of synch with
the other three. And it will only get worse after that.
GUIDs are ruled out also, because, while autoincrement IDs are local to
each table, GUIDs are globally unique among every table, so you can't
have the same GUID for different tables (or so it seems. Forgive me if
I'm mistaken).
Finally, your only choice seems to be to generate your own keys, or
have a mix of tables with hand-made keys and one of the tables with
auto-generated keys (but which one?). Hand generated IDs have their
charm, but have some caveats. Most importantly, their generation logic
is left to the user, instead of remaining hidden inside the DB, and
this may give rise to inconsistencies and, ultimately, warts,
dizzyness, loss of sexual performance, the hell on earth... (=))) you
see I'm *a little* biased, here).
About the visit pricing: there are more than 70 different type of medical
visits, and each one has a price.
So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.
Is this the table with information about each type of visit?
If the boss decides to change the prices of, for i.e., 45 type of medical
visits, the changes must reflect to the other four tables.
Forgive my dumbness: I still don't get it. Are these prices supposed to
change (if they ever do) for a given visit, only? Or would they change
for a whole category of visits? Or, still, would the prices change only
for a given day (a Christmass promo, for instance =))) ?
More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits, the
other combo box will be blank);
Doctor's name.
That's it.
I just need to change the Price column in one table, and make automatically
the changes (update, delete or insert) in the other four tables.
It sounds simple...
<snip>
It seems you want the price to be the same for all the visits of a
given patient in a given day.
Idealy, this would call for a master/detail relation:
Visit table: VisitID, PatientID, Price, Date
VisitDetail table: VisitDetailID, VisitID (the FK), VisitType, DoctorID
Alternatively (oh, the blasphemy!), you could have:
VisitID, PatientID, Price, Date, Visit1Type, Visit1Doctor, ... ,
Visit5Type, Visit5Doctor
HTH.
Regards,
Branco.
Branco,
Do you allow me another approach,
A VisitTable with in it at least
PatientID related as child to PatientTable
DoctorID related as child to DoctorTable
DateTimeOfVisit
NumberOfVisits from 1 to 5 (just to make it understandable)
PrijsID related as child to PriceTable
The other tables including PriceTable where in the latter should not be
forgotten the starting date of the price as a column. Otherwise the system
is to dependend from actual time and date that those are added to the table.
All is than working using the relation although the price should be use a
dataview to get the correct one.
Seems to me a quiet standard approach for a small database.
Cor
"Branco Medeiros" <br*************@gmail.comschreef in bericht
news:11*********************@a3g2000cwd.googlegrou ps.com...
Max wrote:
<back posted/>
It seems to me that there are many approaches to your problem. The
separate tables, although looking practical, would have some serious
issues. One of them is the inhability to automatically update all the
other tables in response to changes in one of the them. Other issue is
the possibility of a patient having more than five visists in a given
day (who knows, sh*te happens...). Even if there are less than five
visits, you'd have the issue of the ID not being synchronized between
tables. A maintenance hell, IMHO.
I didn't understand very well the issue of the visit pricing, but it
seems you may keep everything in a single Visit table and decouple the
items that would be common to all the visits of a given patient in a
single day and put then in a separate table. Something in the likes of:
Visit table:
VisitID (PK)
PatientID (FK to the patients table)
VisitKind (probably an FK to a simple visit description table)
VisitInfoID (an FK to a VisitInfo table)
... other fields unique to a visit, such as visit time, doctor name,
duration, etc
VisitInfo table:
VisitInfoID (PK)
VisitDate
VisitPrice
... other fields common to all visits of a patient in a given day
HTH.
Regards,
Branco.
>Thanks Branco,
probably I'm talking about FK without knowing exactly what a FK is. But,
if
it is the case, it's a misunderstanding coming from a lot of confusion
generated by reading too many manuals, newsgroups, online help and much
more.
I wish to explain why I need five identicals tables. I want write an
application based on medical issues; in a single medical session, a
single
patient could have many different medical visits (I extimated a max
number
of five), because in the medical office there are many different doctors
making different visits. So, for example, Mr. Smith, in the same day,
could
have three different visits (cardiologist, dentist and dermatologist). In
a
form, I have a box for the patients (coming from a 'patient' table), the
date, and the five possible visits that the patient could have in that
day.
In these five tables (called tblPrest1... tblPrest5), there are three
columns: a progressive ID of the medical visit, the kind of medical
visit,
and the price related. If the boss wants to change the price of most of
the
medical visits, for example in the new year, I want that the operator
changes it just in a table and replicate the change in all other four
tables, without going to change the price one table at a time.
The five tables was already created in Access and filled with the actual
medical visits and related price. I'm making an application in vb2005
that
connects to the .mdb file (and it already works), and permits at the
operators the updating, deleting and importing new records as needed.
At this point, after reading your answer, I really don't know if using FK
is
what I really need. Effectively, using relations, I noted the pk issues
you
described and, because the operators may need to change either the price
and
the type of medical visit when needed, I can realize that it's probably
not
possible using relations.
The question is: which different methods can I use to achieve this?
I'm sorry with RobinS if I omitted this long explanation.
Thanks,
Max
<snip>
"Branco Medeiros" It will always depend on how you generate these IDs (I
must warn you
that there are lots of lines of thought in this matter, every one
claiming to be the definitive one).
Personaly, I prefer having a table's ID/PK as an identity column
managed by the system (an autoincrement column, in Access), . Other
people may prefer to generate the keys themselves. Others still will
prefer to use something completely meaningless, say, a GUID, or
whatever (btw, I am, too, among the ones that think that the PK is
meant *only* to pinpoint a given record, but I prefer using a
light-weight identity column for that).
Autoincrement IDs are completely ruled out, for you. Because they're
managed by the DB engine, as soon as you have a situation with, say,
three visits only, the two other tables will become out of synch with
the other three. And it will only get worse after that.
Probably I cannot explain as I'd like. I think autoID is ok for me, because
the five tables must be changed independently from the rest of the
application. The visit tables, called tblPrest1... tblPrest5 (formed by
three columns: ID, Price, and type of visit) are just like a collection of
about 70 records. I mean, I have a menu--->tools--->Change visit table; it
loads a form with the first table; I make all the changes on it, then save
on the binding navigator. At this point, changes must be reflected on the
other four tables.
Next, when I want to open the Visit form, which writes in the Visit table, I
see my five drop down combobox, showing all the records loaded from the
visit table; next, I choose the type of visit, choose how many visits has
been made to that patient by clicking in combobox and leaving the other
combobox blank if visits are < 5, choose date, patient, doctor and stop.
Saving now writes a new record in the Visit table, which has an ID
independent from the other tables.
Thanks for your patience.
>
GUIDs are ruled out also, because, while autoincrement IDs are local to
each table, GUIDs are globally unique among every table, so you can't
have the same GUID for different tables (or so it seems. Forgive me if
I'm mistaken).
Finally, your only choice seems to be to generate your own keys, or
have a mix of tables with hand-made keys and one of the tables with
auto-generated keys (but which one?). Hand generated IDs have their
charm, but have some caveats. Most importantly, their generation logic
is left to the user, instead of remaining hidden inside the DB, and
this may give rise to inconsistencies and, ultimately, warts,
dizzyness, loss of sexual performance, the hell on earth... (=))) you
see I'm *a little* biased, here).
>About the visit pricing: there are more than 70 different type of medical
visits, and each one has a price.
So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.
Is this the table with information about each type of visit?
>If the boss decides to change the prices of, for i.e., 45 type of medical
visits, the changes must reflect to the other four tables.
Forgive my dumbness: I still don't get it. Are these prices supposed to
change (if they ever do) for a given visit, only? Or would they change
for a whole category of visits? Or, still, would the prices change only
for a given day (a Christmass promo, for instance =))) ?
>More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits,
the
other combo box will be blank);
Doctor's name.
That's it.
I just need to change the Price column in one table, and make
automatically
the changes (update, delete or insert) in the other four tables.
It sounds simple...
<snip>
It seems you want the price to be the same for all the visits of a
given patient in a given day.
Idealy, this would call for a master/detail relation:
Visit table: VisitID, PatientID, Price, Date
VisitDetail table: VisitDetailID, VisitID (the FK), VisitType, DoctorID
Alternatively (oh, the blasphemy!), you could have:
VisitID, PatientID, Price, Date, Visit1Type, Visit1Doctor, ... ,
Visit5Type, Visit5Doctor
HTH.
Regards,
Branco.
Max wrote:
<snip>
Probably I cannot explain as I'd like.
No problem. We'll eventually get there, I hope.
I think autoID is ok for me, because
the five tables must be changed independently from the rest of the
application. The visit tables, called tblPrest1... tblPrest5 (formed by
three columns: ID, Price, and type of visit) are just like a collection of
about 70 records. I mean, I have a menu--->tools--->Change visit table; it
loads a form with the first table; I make all the changes on it, then save
on the binding navigator. At this point, changes must be reflected on the
other four tables.
<snip>
The only way I can think of to keep these tables in synch without
having to deal with a logic nightmare is to truncate and overwrite the
other four tables as soon as you finish editing the first one. It's a
long time since I used Access, so I'm not familiar with its current SQL
capabilities, but it would be something like this:
DELETE * FROM tblPrest2;
INSERT INTO tblPrest2
SELECT * FROM tblPrest1;
...
DELETE * FROM tblPrest5;
INSERT INTO tblPrest5
SELECT * FROM tblPrest1;
For this to work, the IDs of tables tblPrest2...tblPrest5 can't be
autoincrement, but just regular ints (or so I suppose. You'd need to
perform some tests). Besides, I seem to recall that Access doesn't
allow multiple statements in a SQL command (I hope I'm wrong). If this
is the case, then you'll have to issue the commands *one by one*... =P
So, after having table tblPrest1 updated, this is the kind of code
you'd have to use to synchronize the tables:
<aircode>
'Assuming an *open* OleDbConnection Con
Dim Tables() As String = New String() { _
"tblPrest2", "tblPrest3", "tblPrest4", "tblPrest5"}
Using Cmd As New OleDb.OleDbCommand()
Cmd.Connection = Con
For Each Table As String In Tables
Cmd.CommandText = "DELETE * FROM " & Table
Cmd.ExecuteNonQuery()
Cmd.CommandText = "INSERT INTO " & Table _
& " SELECT * FROM tblPrest1"
Cmd.ExecuteNonQuery()
Next
End Using
</aircode>
And it can only become uglier than this (or so it seems, given my
limited knowledge).
Another way to keep the tables in synch is quite obvious (don't laugh,
please): instead of having five tables, create just *one* table and
have other four views (or "queries" in Access parlance) mapping to the
first (and only) table:
tblPrest1: ID, Price, VisitType
tblPrest2 ... tblPrest5: four select queries created in Access with the
following SQL:
"SELECT * FROM tblPrest1"
This way you'd never have to update those "tables", and they'll be
permanently in synch. =)
HTH.
Regards,
Branco.
Max,
I really don't understand why you want 5 tables of the same rows instead of
one table with indified rows for each dokter, why are you doing that?
Cor
"Max" <no****@devnull.spamcop.netschreef in bericht
news:45*********************@news.tiscali.it...
>
"Branco Medeiros" It will always depend on how you generate these IDs (I
must warn you
>that there are lots of lines of thought in this matter, every one
claiming to be the definitive one).
Personaly, I prefer having a table's ID/PK as an identity column
managed by the system (an autoincrement column, in Access), . Other
people may prefer to generate the keys themselves. Others still will
prefer to use something completely meaningless, say, a GUID, or
whatever (btw, I am, too, among the ones that think that the PK is
meant *only* to pinpoint a given record, but I prefer using a
light-weight identity column for that).
Autoincrement IDs are completely ruled out, for you. Because they're
managed by the DB engine, as soon as you have a situation with, say,
three visits only, the two other tables will become out of synch with
the other three. And it will only get worse after that.
Probably I cannot explain as I'd like. I think autoID is ok for me,
because the five tables must be changed independently from the rest of the
application. The visit tables, called tblPrest1... tblPrest5 (formed by
three columns: ID, Price, and type of visit) are just like a collection of
about 70 records. I mean, I have a menu--->tools--->Change visit table; it
loads a form with the first table; I make all the changes on it, then save
on the binding navigator. At this point, changes must be reflected on the
other four tables.
Next, when I want to open the Visit form, which writes in the Visit table,
I see my five drop down combobox, showing all the records loaded from the
visit table; next, I choose the type of visit, choose how many visits has
been made to that patient by clicking in combobox and leaving the other
combobox blank if visits are < 5, choose date, patient, doctor and stop.
Saving now writes a new record in the Visit table, which has an ID
independent from the other tables.
Thanks for your patience.
>>
GUIDs are ruled out also, because, while autoincrement IDs are local to
each table, GUIDs are globally unique among every table, so you can't
have the same GUID for different tables (or so it seems. Forgive me if
I'm mistaken).
Finally, your only choice seems to be to generate your own keys, or
have a mix of tables with hand-made keys and one of the tables with
auto-generated keys (but which one?). Hand generated IDs have their
charm, but have some caveats. Most importantly, their generation logic
is left to the user, instead of remaining hidden inside the DB, and
this may give rise to inconsistencies and, ultimately, warts,
dizzyness, loss of sexual performance, the hell on earth... (=))) you
see I'm *a little* biased, here).
>>About the visit pricing: there are more than 70 different type of
medical
visits, and each one has a price.
So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.
Is this the table with information about each type of visit?
>>If the boss decides to change the prices of, for i.e., 45 type of
medical
visits, the changes must reflect to the other four tables.
Forgive my dumbness: I still don't get it. Are these prices supposed to
change (if they ever do) for a given visit, only? Or would they change
for a whole category of visits? Or, still, would the prices change only
for a given day (a Christmass promo, for instance =))) ?
>>More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits,
the
other combo box will be blank);
Doctor's name.
That's it.
I just need to change the Price column in one table, and make
automatically
the changes (update, delete or insert) in the other four tables.
It sounds simple...
<snip>
It seems you want the price to be the same for all the visits of a
given patient in a given day.
Idealy, this would call for a master/detail relation:
Visit table: VisitID, PatientID, Price, Date
VisitDetail table: VisitDetailID, VisitID (the FK), VisitType, DoctorID
Alternatively (oh, the blasphemy!), you could have:
VisitID, PatientID, Price, Date, Visit1Type, Visit1Doctor, ... ,
Visit5Type, Visit5Doctor
HTH.
Regards,
Branco.
Cor,
as you may have understood, I'm really at the beginning with ado.net and
db's in vb. If the suggestion of Branco works fine, I would be very happy to
forget about FK and company; anyway, now, I'd like to understand how this
issue could be solved using five tables...
I'm learning, and I appreciate all of your suggestions.
I'll let you know.
"Cor Ligthert [MVP]" <no************@planet.nlha scritto nel messaggio
news:Ow****************@TK2MSFTNGP04.phx.gbl...
Max,
I really don't understand why you want 5 tables of the same rows instead
of one table with indified rows for each dokter, why are you doing that?
Cor
"Max" <no****@devnull.spamcop.netschreef in bericht
news:45*********************@news.tiscali.it...
>>
"Branco Medeiros" It will always depend on how you generate these IDs
(I must warn you
>>that there are lots of lines of thought in this matter, every one
claiming to be the definitive one).
Personaly, I prefer having a table's ID/PK as an identity column
managed by the system (an autoincrement column, in Access), . Other
people may prefer to generate the keys themselves. Others still will
prefer to use something completely meaningless, say, a GUID, or
whatever (btw, I am, too, among the ones that think that the PK is
meant *only* to pinpoint a given record, but I prefer using a
light-weight identity column for that).
Autoincrement IDs are completely ruled out, for you. Because they're
managed by the DB engine, as soon as you have a situation with, say,
three visits only, the two other tables will become out of synch with
the other three. And it will only get worse after that.
Probably I cannot explain as I'd like. I think autoID is ok for me,
because the five tables must be changed independently from the rest of
the application. The visit tables, called tblPrest1... tblPrest5 (formed
by three columns: ID, Price, and type of visit) are just like a
collection of about 70 records. I mean, I have a menu--->tools--->Change
visit table; it loads a form with the first table; I make all the changes
on it, then save on the binding navigator. At this point, changes must be
reflected on the other four tables.
Next, when I want to open the Visit form, which writes in the Visit
table, I see my five drop down combobox, showing all the records loaded
from the visit table; next, I choose the type of visit, choose how many
visits has been made to that patient by clicking in combobox and leaving
the other combobox blank if visits are < 5, choose date, patient, doctor
and stop. Saving now writes a new record in the Visit table, which has an
ID independent from the other tables.
Thanks for your patience.
>>>
GUIDs are ruled out also, because, while autoincrement IDs are local to
each table, GUIDs are globally unique among every table, so you can't
have the same GUID for different tables (or so it seems. Forgive me if
I'm mistaken).
Finally, your only choice seems to be to generate your own keys, or
have a mix of tables with hand-made keys and one of the tables with
auto-generated keys (but which one?). Hand generated IDs have their
charm, but have some caveats. Most importantly, their generation logic
is left to the user, instead of remaining hidden inside the DB, and
this may give rise to inconsistencies and, ultimately, warts,
dizzyness, loss of sexual performance, the hell on earth... (=))) you
see I'm *a little* biased, here).
About the visit pricing: there are more than 70 different type of
medical
visits, and each one has a price.
So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.
Is this the table with information about each type of visit?
If the boss decides to change the prices of, for i.e., 45 type of
medical
visits, the changes must reflect to the other four tables.
Forgive my dumbness: I still don't get it. Are these prices supposed to
change (if they ever do) for a given visit, only? Or would they change
for a whole category of visits? Or, still, would the prices change only
for a given day (a Christmass promo, for instance =))) ?
More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits,
the
other combo box will be blank);
Doctor's name.
That's it.
I just need to change the Price column in one table, and make
automatically
the changes (update, delete or insert) in the other four tables.
It sounds simple...
<snip>
It seems you want the price to be the same for all the visits of a
given patient in a given day.
Idealy, this would call for a master/detail relation:
Visit table: VisitID, PatientID, Price, Date
VisitDetail table: VisitDetailID, VisitID (the FK), VisitType, DoctorID
Alternatively (oh, the blasphemy!), you could have:
VisitID, PatientID, Price, Date, Visit1Type, Visit1Doctor, ... ,
Visit5Type, Visit5Doctor
HTH.
Regards,
Branco.
I want to try both your suggestions and I'll let you know...
"Branco Medeiros" <br*************@gmail.comha scritto nel messaggio
news:11**********************@h40g2000cwb.googlegr oups.com...
Max wrote:
<snip>
>Probably I cannot explain as I'd like.
No problem. We'll eventually get there, I hope.
>I think autoID is ok for me, because
the five tables must be changed independently from the rest of the
application. The visit tables, called tblPrest1... tblPrest5 (formed by
three columns: ID, Price, and type of visit) are just like a collection
of
about 70 records. I mean, I have a menu--->tools--->Change visit table;
it
loads a form with the first table; I make all the changes on it, then
save
on the binding navigator. At this point, changes must be reflected on the
other four tables.
<snip>
The only way I can think of to keep these tables in synch without
having to deal with a logic nightmare is to truncate and overwrite the
other four tables as soon as you finish editing the first one. It's a
long time since I used Access, so I'm not familiar with its current SQL
capabilities, but it would be something like this:
DELETE * FROM tblPrest2;
INSERT INTO tblPrest2
SELECT * FROM tblPrest1;
...
DELETE * FROM tblPrest5;
INSERT INTO tblPrest5
SELECT * FROM tblPrest1;
For this to work, the IDs of tables tblPrest2...tblPrest5 can't be
autoincrement, but just regular ints (or so I suppose. You'd need to
perform some tests). Besides, I seem to recall that Access doesn't
allow multiple statements in a SQL command (I hope I'm wrong). If this
is the case, then you'll have to issue the commands *one by one*... =P
So, after having table tblPrest1 updated, this is the kind of code
you'd have to use to synchronize the tables:
<aircode>
'Assuming an *open* OleDbConnection Con
Dim Tables() As String = New String() { _
"tblPrest2", "tblPrest3", "tblPrest4", "tblPrest5"}
Using Cmd As New OleDb.OleDbCommand()
Cmd.Connection = Con
For Each Table As String In Tables
Cmd.CommandText = "DELETE * FROM " & Table
Cmd.ExecuteNonQuery()
Cmd.CommandText = "INSERT INTO " & Table _
& " SELECT * FROM tblPrest1"
Cmd.ExecuteNonQuery()
Next
End Using
</aircode>
And it can only become uglier than this (or so it seems, given my
limited knowledge).
Another way to keep the tables in synch is quite obvious (don't laugh,
please): instead of having five tables, create just *one* table and
have other four views (or "queries" in Access parlance) mapping to the
first (and only) table:
tblPrest1: ID, Price, VisitType
tblPrest2 ... tblPrest5: four select queries created in Access with the
following SQL:
"SELECT * FROM tblPrest1"
This way you'd never have to update those "tables", and they'll be
permanently in synch. =)
HTH.
Regards,
Branco.
"Max" <no****@devnull.spamcop.netwrote:
>I want to try both your suggestions and I'll let you know...
>"Branco Medeiros" <br*************@gmail.comwrote:
<snip>
>The only way I can think of to keep these tables in synch without
having to deal with a logic nightmare is to truncate and overwrite the
other four tables as soon as you finish editing the first one. It's a
long time since I used Access, so I'm not familiar with its current SQL
capabilities, but it would be something like this:
DELETE * FROM tblPrest2;
INSERT INTO tblPrest2
SELECT * FROM tblPrest1;
...
DELETE * FROM tblPrest5;
INSERT INTO tblPrest5
SELECT * FROM tblPrest1;
For this to work, the IDs of tables tblPrest2...tblPrest5 can't be
autoincrement, but just regular ints (or so I suppose. You'd need to
perform some tests). Besides, I seem to recall that Access doesn't
allow multiple statements in a SQL command (I hope I'm wrong). If this
is the case, then you'll have to issue the commands *one by one*... =P
So, after having table tblPrest1 updated, this is the kind of code
you'd have to use to synchronize the tables:
<aircode>
'Assuming an *open* OleDbConnection Con
Dim Tables() As String = New String() { _
"tblPrest2", "tblPrest3", "tblPrest4", "tblPrest5"}
Using Cmd As New OleDb.OleDbCommand()
Cmd.Connection = Con
For Each Table As String In Tables
Cmd.CommandText = "DELETE * FROM " & Table
Cmd.ExecuteNonQuery()
Cmd.CommandText = "INSERT INTO " & Table _
& " SELECT * FROM tblPrest1"
Cmd.ExecuteNonQuery()
Next
End Using
</aircode>
And it can only become uglier than this (or so it seems, given my
limited knowledge).
IT WORKS! It's GREAT for all five tables in one step!
>Another way to keep the tables in synch is quite obvious (don't laugh,
please): instead of having five tables, create just *one* table and
have other four views (or "queries" in Access parlance) mapping to the
first (and only) table:
tblPrest1: ID, Price, VisitType
tblPrest2 ... tblPrest5: four select queries created in Access with the
following SQL:
"SELECT * FROM tblPrest1"
This way you'd never have to update those "tables", and they'll be
permanently in synch. =)
HTH.
Regards,
Branco.
I'm gonna try also this (more elegant) method, and let you know.
Thanks a lot!
Max wrote:
<snip>
Next, when I want to open the Visit form, which writes in the Visit table, I
see my five drop down combobox, showing all the records loaded from the
visit table; next, I choose the type of visit, choose how many visits has
been made to that patient by clicking in combobox and leaving the other
combobox blank if visits are < 5, choose date, patient, doctor and stop.
Saving now writes a new record in the Visit table, which has an ID
independent from the other tables.
<snip>
Oh, *now* I got it!
You don't need five tables. You only need one (tblPrest1).
I suppose you're working with the Visits table in record view (as
opposed to grid view).
If this is the case, open the Visit form and drag the tblPrest1 table
from the Data Sources panel *over* the combo box of the VisitType1
field that is in the form. Then select the combo box and set the
DisplayMember property to the name of the field from tblPrest1 you want
to be displayed (probably the visit type). Set the ValueMember property
to the name of the field from tblPrest1 that you want to *store*
(probably its ID).
Do exactly the same thing with the other four Combos: drag *tblPrest1*
from the data sources panel over the combo box, set the fields, etc.
You're all set to go.
HTH.
Regards,
Branco.
Thanks Branco,
it works! But it also works your suggest in your previous post about SQL
statements to Access db:
************************************
'Assuming an *open* OleDbConnection Con
Dim Tables() As String = New String() { _
"tblPrest2", "tblPrest3", "tblPrest4", "tblPrest5"}
Using Cmd As New OleDb.OleDbCommand()
Cmd.Connection = Con
For Each Table As String In Tables
Cmd.CommandText = "DELETE * FROM " & Table
Cmd.ExecuteNonQuery()
Cmd.CommandText = "INSERT INTO " & Table _
& " SELECT * FROM tblPrest1"
Cmd.ExecuteNonQuery()
Next
End Using
************************************
By now, I don't want know anymore else about FK, because I risk to be
involved in the patient table of my db, and in a 'psychiatric' medical
visit... :)))
Thanks a lot, and happy to know there are so many nice people, helping
'absolute beginner' as mine...
You'll see me in my next issue! :)
Max
"Branco Medeiros" <br*************@gmail.comha scritto nel messaggio
news:11**********************@48g2000cwx.googlegro ups.com...
Max wrote:
<snip>
>Next, when I want to open the Visit form, which writes in the Visit
table, I
see my five drop down combobox, showing all the records loaded from the
visit table; next, I choose the type of visit, choose how many visits has
been made to that patient by clicking in combobox and leaving the other
combobox blank if visits are < 5, choose date, patient, doctor and stop.
Saving now writes a new record in the Visit table, which has an ID
independent from the other tables.
<snip>
Oh, *now* I got it!
You don't need five tables. You only need one (tblPrest1).
I suppose you're working with the Visits table in record view (as
opposed to grid view).
If this is the case, open the Visit form and drag the tblPrest1 table
from the Data Sources panel *over* the combo box of the VisitType1
field that is in the form. Then select the combo box and set the
DisplayMember property to the name of the field from tblPrest1 you want
to be displayed (probably the visit type). Set the ValueMember property
to the name of the field from tblPrest1 that you want to *store*
(probably its ID).
Do exactly the same thing with the other four Combos: drag *tblPrest1*
from the data sources panel over the combo box, set the fields, etc.
You're all set to go.
HTH.
Regards,
Branco.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: I_AM_DON_AND_YOU? |
last post by:
There is one more problem I am facing but didn't get the solution. In my
Setup Program I am not been able to create 2 things (when the program is
intalled on the client machine ) : (1) create...
|
by: Agnes |
last post by:
Please be kind to take a look
dsSeaInvInfo.Clear()
daSeaInvInfo.SelectCommand = New SqlCommand
daSeaInvInfo.SelectCommand.Connection = conSea
daSeaInvInfo.TableMappings.Add("Table",...
|
by: jaYPee |
last post by:
I have created 3 sqldataadapter. each adapter.selectcommand has a
parameter. I have added a textbox to supply a value for each
parameter. My problem is when I press enter the second time to fire up...
|
by: David A. Osborn |
last post by:
I keep getting the following message when adding a new record to a database
that has two tables. On table is a parent to the other. I am adding a new
parent record that has child records and I...
|
by: dbuchanan |
last post by:
Hello,
Here is the error message;
----------------------------
Exception Message:
ForeignKeyConstraint Lkp_tbl040Cmpt_lkp302SensorType requires the child
key values (5) to exist in the...
|
by: Steven Bethard |
last post by:
I've updated the PEP based on a number of comments on comp.lang.python.
The most updated versions are still at:
http://ucsu.colorado.edu/~bethard/py/pep_create_statement.txt...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
