[RESOLVED] Help with recordset result!

[doctrin13th]

I have 3 table

1. cat_Tbl = category table which consist of fields:
catNo = category number (primary key)
catDesc = category description

2. prod_Tbl = product table which consist of fields:
pIndex = index number of a product (primary key)
pText = product description

3 FM_Tbl = items ordered(in an order slip) table which consist of fields:
OSno = Order slip number
FMitemNo = item number
Pindex = index number of an ordered product
FMqty = quantity ordered

Now, I have an order slip record #12704. in that slip, i ordered several items, each has its unique item number. but when I run the code below, the result set exceeds the number of items i actually ordered. when I check the result, few items were duplicated. this is my code:

strSQL = "SELECT cat_Tbl.catDesc,FM_Tbl.FMitemNo,FM_Tbl.Pindex,FM_Tbl.FMqty,prod_Tbl.Ptext,prod_Tbl.Pum,prod_Tbl.Pprice FROM cat_Tbl,FM_Tbl,prod_Tbl WHERE (FM_Tbl.OSno = " & lngNum & ") AND FM_Tbl.Pindex=prod_Tbl.Pindex AND cat_Tbl.catNo=prod_Tbl.Pcat ORDER BY FM_Tbl.FMitemNo"
    
    'clear order table
    grdOrder.Rows = 1
    
    rsMain.Open strSQL, cnxMain, adOpenForwardOnly, adLockReadOnly, adCmdText
    Do Until rsMain.EOF
        With grdOrder
            .AddItem ""
            .TextMatrix(.Rows - 1, 0) = rsMain!FMitemNo 'Item no.
            .TextMatrix(.Rows - 1, 1) = rsMain!catDesc 'Category
            .TextMatrix(.Rows - 1, 2) = rsMain!Ptext 'Description
            .TextMatrix(.Rows - 1, 3) = rsMain!FMqty    'Qty
            .TextMatrix(.Rows - 1, 4) = rsMain!Pum 'Unit of measurement
            .TextMatrix(.Rows - 1, 5) = rsMain!Pprice 'Unit Price'Total
            .TextMatrix(.Rows - 1, 6) = rsMain!Pindex   'Product id
        End With
        rsMain.MoveNext
    Loop
    rsMain.Close

Is there a problem with the strSQL? What's wrong with my code?

What I want is to display just what I ordered; exact number of items, exact items. and just referencing to other tables its category and product description.

[honeybee]

I can't run the code (have moved far away from VB6), but try and use JOINs explicitly. Perform an INNER JOIN on the tables and then add the WHERE clause to it.

Please post your results here after you have tried it.

.

[doctrin13th]

Can you compose the sql statement based on my code? Please... Thnx

[doctrin13th]

Anyone??

[honeybee]

I have 3 table

1. cat_Tbl = category table which consist of fields:
catNo = category number (primary key)
catDesc = category description

2. prod_Tbl = product table which consist of fields:
pIndex = index number of a product (primary key)
pText = product description

3 FM_Tbl = items ordered(in an order slip) table which consist of fields:
OSno = Order slip number
FMitemNo = item number
Pindex = index number of an ordered product
FMqty = quantity ordered

My apologies. I should have mentioned this earlier itself. The three tables you have mentioned should be related to each other. While the FM_Tbl and prod_Tbl are linked through the Pindex, there is no link between the prod_Tbl and the cat_Tbl (at least I don't see one in your post above). Ideally the prod_Tbl should have another column catNo which is a foreign key to the catNo (PK) column of your cat_Tbl table.

Now, I have an order slip record #12704. in that slip, i ordered several items, each has its unique item number. but when I run the code below, the result set exceeds the number of items i actually ordered. when I check the result, few items were duplicated. this is my code:

strSQL = "SELECT cat_Tbl.catDesc,FM_Tbl.FMitemNo,FM_Tbl.Pindex,FM_Tbl.FMqty,prod_Tbl.Ptext,prod_Tbl.Pum,prod_Tbl.Pprice 
FROM cat_Tbl,FM_Tbl,prod_Tbl WHERE 
(FM_Tbl.OSno = " & lngNum & ") AND FM_Tbl.Pindex=prod_Tbl.Pindex AND cat_Tbl.catNo=prod_Tbl.Pcat ORDER BY FM_Tbl.FMitemNo"
    
    ...

Is there a problem with the strSQL? What's wrong with my code?

What I want is to display just what I ordered; exact number of items, exact items. and just referencing to other tables its category and product description.

The query would then look something like this:

strSQL = "SELECT cat_Tbl.catDesc,FM_Tbl.FMitemNo,FM_Tbl.Pindex,FM_Tbl.FMqty,prod_Tbl.Ptext,prod_Tbl.Pum,prod_Tbl.Pprice 
FROM FM_Tbl inner join prod_Tbl On FM_Tbl.Pindex = prod_Tbl.pIndex INNER JOIN cat_Tbl on prod_Tbl.catNo = cat_Tbl.catNo 
WHERE (FM_Tbl.OSno = " & lngNum & ")"
    
    ...

.

[curtiende]

SELECT cat_Tbl.catDesc,FM_Tbl.FMitemNo,FM_Tbl.Pindex,FM_Tbl.FMqty,prod_Tbl.Ptext,prod_Tbl.Pum,prod_Tbl.Ppri ce FROM cat_Tbl,FM_Tbl,prod_Tbl WHERE (FM_Tbl.OSno = " & lngNum & ") AND FM_Tbl.Pindex=prod_Tbl.Pindex AND cat_Tbl.catNo=prod_Tbl.Pcat ORDER BY FM_Tbl.FMitemNo

try using this code in an ms access query and see if it works. if yes then check the codes for showing the data in the grid. If it does not produce the records then check the records. They ma have been duplicated during the adding process.

Or another reason for the dublication is the relation ships. The data is being fetched from different tables but you have not specified any join or anything to indicate to vb6 whether there is any relationship among the records from the tables.

[doctrin13th]

My apologies. I should have mentioned this earlier itself. The three tables you have mentioned should be related to each other. While the FM_Tbl and prod_Tbl are linked through the Pindex, there is no link between the prod_Tbl and the cat_Tbl (at least I don't see one in your post above). Ideally the prod_Tbl should have another column catNo which is a foreign key to the catNo (PK) column of your cat_Tbl table.



The query would then look something like this:

strSQL = "SELECT cat_Tbl.catDesc,FM_Tbl.FMitemNo,FM_Tbl.Pindex,FM_Tbl.FMqty,prod_Tbl.Ptext,prod_Tbl.Pum,prod_Tbl.Pprice 
FROM FM_Tbl inner join prod_Tbl On FM_Tbl.Pindex = prod_Tbl.pIndex INNER JOIN cat_Tbl on prod_Tbl.catNo = cat_Tbl.catNo 
WHERE (FM_Tbl.OSno = " & lngNum & ")"
    
    ...

.

My apology; I didn't include prod_Tbl.Pcat (the category number of a product) in my above listing. But it is included in the query, however.

Do the keys have to be identical in name in different tables to have a relationship(i.e. Pcat in prod_Tbl, catNo in cat_Tbl)?

I tried this query:

SELECT cat_Tbl.catDesc,FM_Tbl.FMitemNo,FM_Tbl.Pindex,FM_Tbl.FMqty,prod_Tbl.Ptext,prod_Tbl.Pum,prod_Tbl.Pprice FROM FM_Tbl INNER JOIN prod_Tbl ON prod_Tbl.Pindex=FM_Tbl.Pindex INNER JOIN cat_Tbl ON cat_Tbl.catNo=prod_Tbl.Pcat WHERE (FM_Tbl.OSno = " & lngNum & ") ORDER BY FM_Tbl.FMitemNo

and this error appears:

Error No. -2147217900
Syntax error (missing operator) in query expression 'prod_Tbl.Pindex=FM_Tbl.Pindex INNER JOIN cat_Tbl ON cat_Tbl.catNo=prod_Tbl.Pcat'.

What could cause the problem?

[DataMiser]

I've never tried this but I would think the problem is that you have the inner join in there twice.

http://msdn.microsoft.com/en-us/libr...v=sql.80).aspx

[honeybee]

@doctrin13th, I suggest you compose the sql query in the database that you are using, so the query gives you the desired results, and then incorporate the query in your code. This will be much faster both in terms of development time and in terms of debugging.

.

[doctrin13th]

My apologies to all of you. I think I've wasted your time unintentionally, helping me with my problem.
I found the culprit!
I manually dig through my database and found out that there are accounts with duplicate OS number (OSno field). Upon changing the duplicate OS number, the desired output is obtained, finally, without error or any problem.
I appreciate all your help very much. I'm very grateful for your efforts and I'm very sorry if I've been stupid not trying enough ways before asking.

Again, thank you and I apologize!

[honeybee]

Oh, no need to apologize, we are all here to help and we aren't perfect either.

.