[dba-VB] Open a filtered form

Charlotte Foust cfoust at infostatsystems.com
Tue Dec 11 14:17:11 CST 2007


 1. > >Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)

>>Does this actually LOAD the data?  Why pass in strSQL if not?
No it creates a new instance of an OleDbDataAdapter and sets the SQL it
will use and the connection, that's all.  The dataadapter is the conduit
for loading the data into a dataset.

2. >>>da.Fill(ds, "Employees")

>>This obviously fills the data adapter, but is "Employees" the NAME of
the table internal to the data adapter?  Or is it the name of the table
in the database?  Is it just a reference to a dataset inside of the DA?


Right, this is where the dataSET is filled USING the dataadapter's fill
method.  Employees is the name of the table in the dataadapter, which
usually translates to the name of a table in the database, but not
necessarily.  A dataadapter can contain multiple tables, so you need to
indicate which one to use.

Charlotte


-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, December 11, 2007 12:07 PM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] Open a filtered form

OK, but do you understand ADO enough to do it without all of that stuff?
Let me say that I am not an ADO kinda guy yet, and then let me show you
what I am up against.  Here is some actual "example code" for doing this
stuff:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

' Creating connection and command sting
Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data
source=c:\\northwind.mdb"
Dim sqlStr As String = "SELECT * FROM Employees"
' Create connection object
Dim conn As OleDbConnection = New OleDbConnection(conStr) ' Create data
adapter object Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr,
conn) ' Create a dataset object and fill with data using data adapter's
Fill method Dim ds As DataSet = New DataSet da.Fill(ds, "Employees") '
Attach dataset's DefaultView to the datagrid control
DataGrid1.DataSource = ds.DefaultViewManager End Sub

Now let me ask some questions so that you can perhaps understand how I
am confused...

>Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)

Does this actually LOAD the data?  Why pass in strSQL if not?

>da.Fill(ds, "Employees")

This obviously fills the data adapter, but is "Employees" the NAME of
the table internal to the data adapter?  Or is it the name of the table
in the database?  Is it just a reference to a dataset inside of the DA?


Intellisense says that I am providing "srcTable" which would indicate
that I am asking the data adapter to go look for "Employees" in SQL
Server and fill DS with that.  If that is the case then why did we do a
"SELECT * FROM EMPLOYEES" in the SqlStr?

This example is just a mess, and I haven't really found examples where
they discuss what is actually happening in any depth.

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: Tuesday, December 11, 2007 1:17 PM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] Open a filtered form

We are, but a lot of our code would make no sense outside the context of
our framework.  Our dataentities (classes that wrap and implement a
typeddataset) return a dataset with defaults for the primary keys when
appropriate.  They also enforce the business rules that prevent saving a
record without filling in required fields.  We use a NewRecord routine
in the form when we need to create a new datarow and populate those
values and any other required values before adding the row to the
dataset.  

You can hide columns in the grid without removing them from the dataset.
And you can use SQL and ADO to create a recordset with only the specific
fields provided for in the commandtext.  Our OleDbProvider project
includes the standard SQL for filling datasets as defined by the
interface classes in the data project.  It also includes data provider
classes that implement the interfaces and actually return the filled
datasets to the data entities.

So, in short, code in the form sets the datasource of the grid to the
dataset object, but the dataset object is filled in the OleDbProvider
classes and handed back to the dataentity class bound to the form.  We
usually bind a grid to a dataview because it's easy to filter down to
just the records you need and it gives you lots of control over the
behavior.

In many ways it's just like Access, especially if you have a rich data
layer to program against instead of going straight against the objects
exposed in Server Explorer.  You can program against that with full
intellisense without being attached to a database until you run the
application.

Charlotte Foust

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, December 11, 2007 8:51 AM
To: dba-vb at databaseadvisors.com
Subject: [dba-VB] Open a filtered form

Someone here must be opening forms filtered down to a set of records?  I
need to select a record in a main form and then open another form
filtered to records related to the main form.  The equivalent of the
popup filtered forms from Access.

Can anyone show code to cause this to happen?  

Let's assume for the moment a grid control in the popup form.

I assume code in the form will create a data set object and bind the
grid to that.  I don't want the whole table though, I only want a set of
records, a WHERE.  And if I enter records I need the PKID from the
parent table automagically entered in the new records I am creating.
And I don't want the FK field from the parent object displayed in the
grid.

You know, "just like Access does it", and of course "just like you would
logically expect it to be done".

Is anyone doing this stuff?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com


_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com

_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com





More information about the dba-VB mailing list