Data Grid Query 1

[ManicMark]

Hi All,

I am new to this ADO and DataGrid procedure but have created a query to populate the DataGrid on the form, this I found easy but now I wish to do the same on another form but this one is not so simple, I wish to retrieve data from two separate tables (joined by 1 to Many relationship) I have one table Product and the other Sold. I have noticed that the DatGrid is connected to the adodc which is connected to the database and the product form.

How, using the adodc, DataGrids and SQL do I get the info from two tables.

Thanking You

Regards

M

 

[dvannoy]

Simply join the two tables then set the grids datasource = to the recordset..bind the grid using code..

Select table.field ETC.. From
Table
Inner Join
Table on Table.Field = Table.Field

Then open the recordset

Then Set DataGrid1.DataSource = Recordset

 

[ManicMark]

Thankyou dvannoy,

The tables I have are Product and Sold, I am completely new to this and stumbled on it by chance but am so impressed with what it does I thought I'd try to learn more. Could you please explain a little clearer especially the part:
"Simply join the two tables then set the grids datasource = to the recordset..bind the grid using code.."

I have to create a simple stock control system for a local business as part of a project, everything was great until I had to add the extra table (the project requires a relational database) I was hoping it was going to be as simple as it was with just one table, I am sure it's just the way I am looking at it, or is it?

Regards

M

 

[Sideman]

I use Access2000 and VB6 SP6.
I'm not an expert, so this is likely to be subject to correction, but here goes...

...first a simple join of the two tables...
adodc.RecordSource= "SELECT * FROM Products, Sold WHERE Product.ProductID = Sold.ProductID"
adodc.Refresh

...If you only need specific fields retrieved then the SELECT portion could be...
SELECT Field1, Field2, Field3, etc. FROM...

...then just assign DataGrid...
Set DataGrid.DataSource = adodc

Right click on the DataGrid and set up the properties to display the fields you need the way you like.

Simplistic, but it works for me...hope it helps you.


Of all the things I've lost, I miss my mind the most!
Sideman

 

[ManicMark]

Thanks Sideman,

Do I put the code in a Data Environment or double click the form and put it there? If I put it in A new Data Environment how do I set the adodc for the Data Environment?

I am sorry if I appear thick but this is not at all clear to me.

I am going to put something like:

SELECT Manufacturer, Description, Quantity
FROM Product, Sold

in the Data Environment but how then do I set Data Environment to read from the 2 tables.

your eg. "adodc.RecordSource= "SELECT * FROM Products, Sold WHERE Product.ProductID = Sold.ProductID"
adodc.Refresh" but where do I put this code, do I double click the new adodc and put the code there?

Basically what I want is to set the adodc to

adodc = Product & Sold but I can only seem to be able to connect to one table. As you can see I am getting more confused as time goes on. I like the sound of dvannoys INNER JOIN but he has not made it that clear, his advise is OK for someone who already knows what they are doing.

Idealistically I need a tutorial from somewhere but I am finding this impossible to locate, all the books I have looked at just give the example with 1 table.

M

 

[dvannoy]

All my connections, recordsets etc are all done by code..this way I have full control of what I want to do. I am not sure how you are connecting and to what DB your connecting to. The below example will show how to connect to a SQL DB. check here for more ADO connection strings..

http://www.able-consulting.com/ADO_Conn.htm

Dim cn As ADODB.Connection
Dim strCn As String

strCn = "Provider=SQLOLEDB;" & _
"Data Source=server;" & _
"Initial Catalog=db;" & _
"User Id=;" & _
"Password="
Set cn = New ADODB.Connection
cn.Open strCn

The above code opens the connection to the DB..

Now the recordset

Dim rs As ADODB.Recordset
Dim strRs As String

you should not get into the habit of "SELECT * FROM TABLE" only ask for the fields you really need to select.

strRs = "SELECT Products.ProductName, Products.ProductID FROM Products INNER JOIN Sold on Products.ProductID = Sold.ProdcutsID"

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Open strRs, strCn

In the strRs you can Select whatever you want, I just gave you something simple.

Then bind the datagrid

Set DataGrid.DataSource = rs

You should also put some type of error handler incase theres no records returned by the SELECT statement..Theres a few different ways to do that as well..e.g.

if rs.RecordCount = 0 then
msgbox "blaaa"
exit sub
end if


I just gave you something simple...the other guys on here probally can give you examples as well..

hope this helps

 

[ManicMark]

Thank you dvannoy I am sure what you have put is what I need, what I have done is create the database using Access 2000. I have connected the VB form to the DB using adodc, the ado Connection String is connected to the entire DB and the Record Source is set to Product. I have then created a Data Environment with the ConnectionSource to the DB. The DataGrid is then connected to the Data Environment.

Looking at it again I think I may have confused the issue and myself with the adodc which I don't think is necessary with the Data Environment. Basically I think all I need is the SQL that will select the Manufacturer and the Description from the Product table and the Quantity from the Sold table, my tables are connected 1 Product to many Sold so I think all I require is the SQL and I think your INNER JOIN command, this is the bit I am not sure about. I have to put something like:

SELECT Product.Manufacturer, Product.Description, Sold.Quantity

FROM Products

INNER JOIN this is where I come unstuck getting the Sold.Quantity value in to the equation, once this is done all I have to do is set the Data Source for the DataGrid to the Data Environment created with the SQL.

I think my problem is my lack of understanding with SQL and multiple tables, the connecting bit.

Thanks for all the other code you have gave me but most of that is quantum physics to me.

M

 

[dvannoy]

ok, first of all you have to have something in common with the two tables to do an inner join. Does your products have a ID#?? if so, does that ID # get written to both tables? I'm not talking about a primary key but rather a number that can be joined to both tables that are the same?

"SELECT Product.Manufacturer, Product.Description, Sold.Quantity FROM Products INNER JOIN Sold on Products.ProductID = Sold.ProdcutsID"

if you want to INNER JOIN Based on whats on the form then do..

"SELECT Product.Manufacturer, Product.Description, Sold.Quantity FROM Products INNER JOIN Sold on Products.ProductID = Sold.ProdcutsID WHERE Products.ProductID = '" & Text1.Text & "'"

 

[ManicMark]

I reckon that what you have typed:

"SELECT Product.Manufacturer, Product.Description, Sold.Quantity FROM Products INNER JOIN Sold on Products.ProductID = Sold.ProdcutsID"

might just do it, I have ProductID in both tables (Primary Key in Product & Foreign Key in Sold). I will have to try it out now. Thanks for your patience I know it can be trying when the recipient is beyond comprehension. I thought I might be a little out of depth when I first discovered this ADO malarky but when I tried it and found that it virtually sorted out my project without any lenghthy coding I was well impressed.

A question I have about any type of data control is that the DB has to be on the local drive for communication between the form and the DB, OK if I am just using it on my PC but if I use another PC I have to change the directory path to suit for the program to function.

Q. Once the project is complete and I create the VB6Project.exe and then the Package & Deployment Wizard how will it work on another PC? the directory path for the DB is going to change from PC to PC, will I have to install VB6 & Access 2k on the clients host PC as this is not really an option?

Regards

M

 

[dvannoy]

your best bet is to put the DB on a network drive..with ADO in code you would use the app.path as the location for the DB..just include the DB file when you use the package and Depolyment wizard and once you install it onto another machine the application will know where to find the DB.