Populating recordsets

[Horsegag9]

Hey All,

I am executing the following query in a vbscript, it obviously pulls info from various tables:

strFilter ="SELECT A.PDDOCO, A.PDAN8, B.A6AN8, C.ABAN8, C.ABAN85, C.ABALPH, C.ABDC, B.A6TRAP FROM PRODDTA.F4311 A, PRODDTA.F0101 C, PRODDTA.F0401 B WHERE A.PDAN8 = C.ABAN8 AND C.ABAN85 = B.A6AN8 AND (A.PDLNID = 1000) AND { fn UCASE(a.PDDOCO) } LIKE '%62597%' ORDER BY a.PDDOCO"

I am getting an error populating my record set. Attached is the code where this happens....


On Error goto 0

objRS.Open strFilter, objConn


RecordCount = objRS.RecordCount

MsgBox "RecordCount = " & RecordCount


If RecordCount = -1 Then
RecordCount = 0
End If

MsgBox "strFilter = " & strFilter

Select Case RecordCount

Case 0
MsgBox "No PO Numbers matched - Reenter Purchase Order Number"
This.Flagged = True

Case 1

DPONumber.Value = Trim(objRS.Fields("a.PDDOCO"))
VendorName.Value = Trim(objRS.Fields("c.ABALPH"))
VendorID.Value = Trim(objRS.Fields("c.ABAN8"))
VendorIDAP.Value = Trim(objRS.Fields("c.ABAN85"))
Terms.Value = Trim(objRs.Fields("b.A6TRAP"))


There is a Case Else, but it produces the same error which is "Item cannot be found in the collection corresponding to the requested name or ordinal.

Anyone got any ideas? I know it's a script issue and the ODBC driver is correct. I think I need to employ a JOIN but do not know the syntax.

Thanks in Advance
Horse

 

[Sheco]

"Item cannot be found in the collection corresponding to the requested name or ordinal"

This error message sounds not like a problem populating the recordset but an error retrieving fields from the recordset.

So I guess it could be a populating error, like if the things you want to be populated are not actually populated, but I wonder what you will see if you add this test:

Dim ofld
For Each ofld in objRS.Fields
  Response.Write ofld.Name & " = " & ofld.Value & "<BR>" 
Next

 

[gmmastros]

Here's an example of the joins...

SELECT 	A.PDDOCO, 
		A.PDAN8, 
		B.A6AN8, 
		C.ABAN8, 
		C.ABAN85, 
		C.ABALPH, 
		C.ABDC, 
		B.A6TRAP 
FROM 	PRODDTA.F4311 A, 
		Inner Join PRODDTA.F0101 C On A.PDAN8 = C.ABAN8
		Inner Join PRODDTA.F0401 B On C.ABAN85 = B.A6AN8
WHERE 	(A.PDLNID = 1000) AND { fn UCASE(a.PDDOCO) } LIKE '%62597%' 
ORDER BY a.PDDOCO

Sheco is right about the "Item cannot be found...." business.

I suggest you change your code to....

DPONumber.Value = Trim(objRS.Fields("PDDOCO"))
VendorName.Value = Trim(objRS.Fields("ABALPH"))
VendorID.Value = Trim(objRS.Fields("ABAN8"))
VendorIDAP.Value = Trim(objRS.Fields("ABAN85"))
Terms.Value = Trim(objRs.Fields("A6TRAP"))

Notice that the fields are not referencing the table aliases anymore.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom