Trying to bind data from two different tables to two different controls
| Author |
Message |
|
Travis Spence
#1 / 7
|
 Trying to bind data from two different tables to two different controls Hello, Can you please help me solve this problem?
I am trying to bind the data in two different tables to two different combo
boxes. Should I: A. create one connection object and two record set objects
that share the connection, B. one implicit RS object that joins the two
tables and then bind the controls, or C. none of the above?
If B is the solution, can you please help me write the SQL. I don't know
very much about that stuff yet.
Note: I am using an Access database, VB 6, and ADO 2.0
Thanks,
Travis Spencer
Portland, OR. USA
|
| Sat, 26 Jul 2003 06:21:35 GMT |
|
 |
|
Nelson Lope
#2 / 7
|
Trying to bind data from two different tables to two different controls Travis, If the tables are in the same Access database then you only need one
connection object and one recordset. Open your connection to the database
and open your recordset with a join between the two tables on the primary
and foreign keys. Let's say that in one table you have CustomerID and in
the other table you also have CustomerID (to relate the two tables records).
Your recordset would be something like:
SELECT Field1,
Field2...etc
FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.CustomerID =
T2.CustomerID
WHERE (any criteria you may have)
Now your recordset will contain ALL records in Table1 and also records in
Table2 that have a matching CustomerID. If the record MUST be in Table1 and
Table2, then user an INNER JOIN.
Hope this helps!
Nelson Lopes
Senior VB Programmer/Analyst
Quote: > Hello, > Can you please help me solve this problem?
> I am trying to bind the data in two different tables to two different
combo
> boxes. Should I: A. create one connection object and two record set
objects
> that share the connection, B. one implicit RS object that joins the two
> tables and then bind the controls, or C. none of the above?
> If B is the solution, can you please help me write the SQL. I don't know
> very much about that stuff yet.
> Note: I am using an Access database, VB 6, and ADO 2.0
> Thanks,
> Travis Spencer
> Portland, OR. USA
|
| Sat, 26 Jul 2003 10:29:18 GMT |
|
|
|
Travis Spence
#3 / 7
|
Trying to bind data from two different tables to two different controls Hey Nelson, So if I am just going to read a bunch of data as fast as possible and bind
it to some controls, one implicit record set that joins the two tables will
do the trick. Is that bit about implicitness correct?
Regards,
Travis Spencer
Portland, OR. USA
Quote: > Travis, > If the tables are in the same Access database then you only need one
> connection object and one recordset. Open your connection to the database
> and open your recordset with a join between the two tables on the primary
> and foreign keys. Let's say that in one table you have CustomerID and in
> the other table you also have CustomerID (to relate the two tables
records).
> Your recordset would be something like:
> SELECT Field1,
> Field2...etc
> FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.CustomerID =
> T2.CustomerID
> WHERE (any criteria you may have)
> Now your recordset will contain ALL records in Table1 and also records in
> Table2 that have a matching CustomerID. If the record MUST be in Table1
and
> Table2, then user an INNER JOIN.
> Hope this helps!
> Nelson Lopes
> Senior VB Programmer/Analyst
> > Hello,
> > Can you please help me solve this problem?
> > I am trying to bind the data in two different tables to two different
> combo
> > boxes. Should I: A. create one connection object and two record set
> objects
> > that share the connection, B. one implicit RS object that joins the two
> > tables and then bind the controls, or C. none of the above?
> > If B is the solution, can you please help me write the SQL. I don't
know
> > very much about that stuff yet.
> > Note: I am using an Access database, VB 6, and ADO 2.0
> > Thanks,
> > Travis Spencer
> > Portland, OR. USA
|
| Sat, 26 Jul 2003 16:47:04 GMT |
|
|
|
Travis Spence
#4 / 7
|
Trying to bind data from two different tables to two different controls One more little question: I wrote the following code and got an error. I am not sure if it the fault
of my code, db, or what. Here is my code:
Option Explicit
Private Sub Form_Initialize()
'Instantiate the record set
Dim objRS As Recordset
Set objRS = New Recordset
'SQL
Dim strSQL As String
strSQL = "SELECT * FROM Categories LEFT OUTER JOIN Accounts ON
Categories.CategoryID = Accounts.AccountID"
'Connection
Dim strConn As String
strConn = "Provider=SQLOLEDB; Server=DoNot; Initial Catalog=EatThat;
User Id=Its; password=Poison;"
With objRS
.ActiveConnection = strConn
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Source = strSQL
.Open
End With
Do While Not objRS.EOF
cboCategory.AddItem (objRS.Fields("Category"))
cboAccount.AddItem (objRS.Fields("Account")) '<== Error here
objRS.MoveNext
Loop
objRS.Close
Set objRS = Nothing
End Sub
The error I got was
Runtime error '94':
Invalid use of null
Sorry for all the questions. I bet you are glad that you aren't a beginner
any more.
Thanks Again,
Travis Spencer
Portland, OR. USA
Quote: > Travis, > If the tables are in the same Access database then you only need one
> connection object and one recordset. Open your connection to the database
> and open your recordset with a join between the two tables on the primary
> and foreign keys. Let's say that in one table you have CustomerID and in
> the other table you also have CustomerID (to relate the two tables
records).
> Your recordset would be something like:
> SELECT Field1,
> Field2...etc
> FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.CustomerID =
> T2.CustomerID
> WHERE (any criteria you may have)
> Now your recordset will contain ALL records in Table1 and also records in
> Table2 that have a matching CustomerID. If the record MUST be in Table1
and
> Table2, then user an INNER JOIN.
> Hope this helps!
> Nelson Lopes
> Senior VB Programmer/Analyst
> > Hello,
> > Can you please help me solve this problem?
> > I am trying to bind the data in two different tables to two different
> combo
> > boxes. Should I: A. create one connection object and two record set
> objects
> > that share the connection, B. one implicit RS object that joins the two
> > tables and then bind the controls, or C. none of the above?
> > If B is the solution, can you please help me write the SQL. I don't
know
> > very much about that stuff yet.
> > Note: I am using an Access database, VB 6, and ADO 2.0
> > Thanks,
> > Travis Spencer
> > Portland, OR. USA
|
| Sat, 26 Jul 2003 17:09:16 GMT |
|
|
|
Travis Spence
#5 / 7
|
Trying to bind data from two different tables to two different controls I got it!!! Here is the code that made it work:
Do While Not objRS.EOF
cboCategory.AddItem (objRS.Fields("Category"))
If Not IsNull(objRS.Fields("Account")) Then
cboAccount.AddItem (objRS.Fields("Account"))
End If
objRS.MoveNext
Loop
That was hard. I couldn't have done it with out you Nelson and Sultan.
Now, may I ask one more question? Should this code be in an Active-X DLL,
the Form_Load event handler, or in the Form_Initialize event handler? From
what I have read, it seems that one of the primary purposes of COM
components is to create a layer in which all business rules are contained.
This isn't really a business rule, so maybe it should go in the form
instead. But if I did put it in an Active-X DLL, I could reuse the
component form my ASP pages.
It seems that figuring out my record set problem is only beginning!
Thanks Again,
Travis Spencer
Portland, OR. USA
Quote: > Travis, > Good job. Change your INNER JOIN back to a LEFT OUTER JOIN to get all the
> records from the Category table. Remember a LEFT OUTER JOIN gets all
> records from the "left" table and only the matching records in the "right"
> table (your Accounts table) based on your joining field (AccountsID).
> Also for the NULL, you can check to see if the record data is Null by the
> ISNULL function in VB and implicitly set Nulls to "" (empty string).
> Remember Null is not defined. It is not an empty string, it is not empty,
> and it is not 0. You might be able to fix this up on your database side
by
> not allowing NULL's in text fields.
> Let me know if this does it for you. You're almost there! :)
> Nelson Lopes
> Senior VB Programmer/Analyst
> AegonUSA
> > Hey Sultan,
> > Thanks for the tip. I fixed it with this code:
> > strSQL = "SELECT Categories.Category, Accounts.Account From Categories
> INNER
> > JOIN Accounts ON Categories.CategoryID = Accounts.AccountID;"
> > Now my controls are bound, but not exactly as I need them to be. The
> > situation is this: the Category table is about twice the size as the
> Account
> > table, so cboCategory only has 1/2 of its contents in it.
> > Any ideas? Again, sorry for all questions. Soon I will have this stuff
> > sorted out.
> > Many Thanks,
> > Travis Spencer
> > Portland, OR. USA
> > > > 'SQL
> > > > Dim strSQL As String
> > > > strSQL = "SELECT * FROM Categories LEFT OUTER JOIN Accounts ON
> > > > Categories.CategoryID = Accounts.AccountID"
> > > You're only selecting records from Categories and none from Accounts.
I
> > > believe that's the problem.
> > > Sultan
|
| Mon, 28 Jul 2003 02:33:35 GMT |
|
|
|
Nelson Lope
#6 / 7
|
Trying to bind data from two different tables to two different controls Travis, Yes, you could put it in a DLL if you are going to be reusing it a lot.
However you should not hardcode the connection, table names, etc.. into the
DLL. You would have to add these as properties and methods into a class in
you DLL. That way you can instantiate an object of that class (DLL) and
send it the connection parameters, tables, etc.. and have it do all the work
for you and return pass back results. This is actually very easy to do and
if you can read about this in a book by WROX Press called "Doing Business
Objects" that demonstrates this.
If you decide to leave the code in the form, I would place it in the
Form_Load event. But realize that if the data you are reading into the
recordset takes a long time, then your form will not display to the user
right away! :( Another place to put it is in the Form Activate event which
means the form is already loaded and displayed, however this event gets
triggered each time the form is activated (loses and regains focus) which
requires a little trick to make sure you don't reload your data (i.e
something like a static variable). Just make sure you let the user know
that the system is busy (make the cursor an hourglass Screen.Mousepointer =
vbHourglass).
Well, I'm glad you finally got it working! The IsNull did the trick there!
You're well on your way.
Nelson Lopes
Senior VB Programmer/Analyst
AegonUSA
Quote: > I got it!!! > Here is the code that made it work:
> Do While Not objRS.EOF
> cboCategory.AddItem (objRS.Fields("Category"))
> If Not IsNull(objRS.Fields("Account")) Then
> cboAccount.AddItem (objRS.Fields("Account"))
> End If
> objRS.MoveNext
> Loop
> That was hard. I couldn't have done it with out you Nelson and Sultan.
> Now, may I ask one more question? Should this code be in an Active-X DLL,
> the Form_Load event handler, or in the Form_Initialize event handler?
From
> what I have read, it seems that one of the primary purposes of COM
> components is to create a layer in which all business rules are contained.
> This isn't really a business rule, so maybe it should go in the form
> instead. But if I did put it in an Active-X DLL, I could reuse the
> component form my ASP pages.
> It seems that figuring out my record set problem is only beginning!
> Thanks Again,
> Travis Spencer
> Portland, OR. USA
> > Travis,
> > Good job. Change your INNER JOIN back to a LEFT OUTER JOIN to get all
the
> > records from the Category table. Remember a LEFT OUTER JOIN gets all
> > records from the "left" table and only the matching records in the
"right"
> > table (your Accounts table) based on your joining field (AccountsID).
> > Also for the NULL, you can check to see if the record data is Null by
the
> > ISNULL function in VB and implicitly set Nulls to "" (empty string).
> > Remember Null is not defined. It is not an empty string, it is not
empty,
> > and it is not 0. You might be able to fix this up on your database side
> by
> > not allowing NULL's in text fields.
> > Let me know if this does it for you. You're almost there! :)
> > Nelson Lopes
> > Senior VB Programmer/Analyst
> > AegonUSA
> > > Hey Sultan,
> > > Thanks for the tip. I fixed it with this code:
> > > strSQL = "SELECT Categories.Category, Accounts.Account From Categories
> > INNER
> > > JOIN Accounts ON Categories.CategoryID = Accounts.AccountID;"
> > > Now my controls are bound, but not exactly as I need them to be. The
> > > situation is this: the Category table is about twice the size as the
> > Account
> > > table, so cboCategory only has 1/2 of its contents in it.
> > > Any ideas? Again, sorry for all questions. Soon I will have this
stuff
> > > sorted out.
> > > Many Thanks,
> > > Travis Spencer
> > > Portland, OR. USA
> > > > > 'SQL
> > > > > Dim strSQL As String
> > > > > strSQL = "SELECT * FROM Categories LEFT OUTER JOIN Accounts ON
> > > > > Categories.CategoryID = Accounts.AccountID"
> > > > You're only selecting records from Categories and none from
Accounts.
> I
> > > > believe that's the problem.
> > > > Sultan
|
| Mon, 28 Jul 2003 11:22:17 GMT |
|
|
|
Travis Spence
#7 / 7
|
Trying to bind data from two different tables to two different controls Thanks Nelson, You are very nice to help me so much! I will go get that book and stop
plaguing you with questions. Have a good day.
Thanks Again,
Travis Spencer
Portland, OR. USA
Quote: > Travis, > Yes, you could put it in a DLL if you are going to be reusing it a lot.
> However you should not hardcode the connection, table names, etc.. into
the
> DLL. You would have to add these as properties and methods into a class
in
> you DLL. That way you can instantiate an object of that class (DLL) and
> send it the connection parameters, tables, etc.. and have it do all the
work
> for you and return pass back results. This is actually very easy to do
and
> if you can read about this in a book by WROX Press called "Doing Business
> Objects" that demonstrates this.
> If you decide to leave the code in the form, I would place it in the
> Form_Load event. But realize that if the data you are reading into the
> recordset takes a long time, then your form will not display to the user
> right away! :( Another place to put it is in the Form Activate event
which
> means the form is already loaded and displayed, however this event gets
> triggered each time the form is activated (loses and regains focus) which
> requires a little trick to make sure you don't reload your data (i.e
> something like a static variable). Just make sure you let the user know
> that the system is busy (make the cursor an hourglass Screen.Mousepointer
=
> vbHourglass).
> Well, I'm glad you finally got it working! The IsNull did the trick
there!
> You're well on your way.
> Nelson Lopes
> Senior VB Programmer/Analyst
> AegonUSA
> > I got it!!!
> > Here is the code that made it work:
> > Do While Not objRS.EOF
> > cboCategory.AddItem (objRS.Fields("Category"))
> > If Not IsNull(objRS.Fields("Account")) Then
> > cboAccount.AddItem (objRS.Fields("Account"))
> > End If
> > objRS.MoveNext
> > Loop
> > That was hard. I couldn't have done it with out you Nelson and Sultan.
> > Now, may I ask one more question? Should this code be in an Active-X
DLL,
> > the Form_Load event handler, or in the Form_Initialize event handler?
> From
> > what I have read, it seems that one of the primary purposes of COM
> > components is to create a layer in which all business rules are
contained.
> > This isn't really a business rule, so maybe it should go in the form
> > instead. But if I did put it in an Active-X DLL, I could reuse the
> > component form my ASP pages.
> > It seems that figuring out my record set problem is only beginning!
> > Thanks Again,
> > Travis Spencer
> > Portland, OR. USA
> > > Travis,
> > > Good job. Change your INNER JOIN back to a LEFT OUTER JOIN to get all
> the
> > > records from the Category table. Remember a LEFT OUTER JOIN gets all
> > > records from the "left" table and only the matching records in the
> "right"
> > > table (your Accounts table) based on your joining field (AccountsID).
> > > Also for the NULL, you can check to see if the record data is Null by
> the
> > > ISNULL function in VB and implicitly set Nulls to "" (empty string).
> > > Remember Null is not defined. It is not an empty string, it is not
> empty,
> > > and it is not 0. You might be able to fix this up on your database
side
> > by
> > > not allowing NULL's in text fields.
> > > Let me know if this does it for you. You're almost there! :)
> > > Nelson Lopes
> > > Senior VB Programmer/Analyst
> > > AegonUSA
> > > > Hey Sultan,
> > > > Thanks for the tip. I fixed it with this code:
> > > > strSQL = "SELECT Categories.Category, Accounts.Account From
Categories
> > > INNER
> > > > JOIN Accounts ON Categories.CategoryID = Accounts.AccountID;"
> > > > Now my controls are bound, but not exactly as I need them to be.
The
> > > > situation is this: the Category table is about twice the size as the
> > > Account
> > > > table, so cboCategory only has 1/2 of its contents in it.
> > > > Any ideas? Again, sorry for all questions. Soon I will have this
> stuff
> > > > sorted out.
> > > > Many Thanks,
> > > > Travis Spencer
> > > > Portland, OR. USA
> > > > > > 'SQL
> > > > > > Dim strSQL As String
> > > > > > strSQL = "SELECT * FROM Categories LEFT OUTER JOIN Accounts
ON
> > > > > > Categories.CategoryID = Accounts.AccountID"
> > > > > You're only selecting records from Categories and none from
> Accounts.
> > I
> > > > > believe that's the problem.
> > > > > Sultan
|
| Mon, 28 Jul 2003 15:56:08 GMT |
|
| |
|
