Bugged by an easy problem for someone

This should be really easy to answer for someone, but its bugging me as to
whats wrong here. I've summarised the code somewhat for clarity but this is
the important part

sSQL = "SELECT Products.ProdID, Products.Name, DrinkType.Name......<snip>."

Set oData = Server.CreateObject("ADODB.Recordset")
sConString = "dsn=MySQLCon;uid=User;pwd=Pass;"

With oData
.ActiveConnection = sConString
.Source = sSQL
.CursorType = 0
.CursorLocation = 2
.LockType = 1
.Open()
End with

Ok this part of the code is all working fine and returns the recordset
populated with a number of records as expected.

However if use the line :

Response.write(oData.fields("Products.Name").value)

I get the error "ADODB.Recordset (0x800A0CC1) Item cannot be found in the
collection corresponding to the requested name or ordinal"

If I change that line to read :

Response.write(oData.fields(1).value)

The data is written correctly

If I change the SQL statement to sSQL = "SELECT Products.ProdID,
Products.Name AS ProdName......" and use the line:

Response.write(oData.fields("ProdName").value)

The data is written correctly.

Question is why cannot I refer to the field in question useing
oData.fields("Products.Name").value?


TIA
Sean


.

Relevant Pages

  • RE: DoCmd.RunSQL Problem
    ... before and editing the connection string fixed my bug i think. ... Dim cnn As ADODB.Connection ... > Dim sSQL As String ... >> open the recordset, get your data, close the recordset, and then reopen a new ...
    (microsoft.public.access.formscoding)
  • RE: DoCmd.RunSQL Problem
    ... > Dim rs2 As ADODB.Recordset ... > Dim sSQL As String ... >> open the recordset, get your data, close the recordset, and then reopen a new ... >> On Error GoTo hell ...
    (microsoft.public.access.formscoding)
  • Re: Dynamic dimming
    ... ssql's value *at the time a recordset is opened* is all that matters. ... Should I use different SSQL statements if I want to have ... dim as String ...
    (microsoft.public.access.modulesdaovba)
  • RE: DoCmd.RunSQL Problem
    ... Dim rs2 As ADODB.Recordset ... Dim sSQL As String ... Set rs2 = New ADODB.Recordset ... > open the recordset, get your data, close the recordset, and then reopen a new ...
    (microsoft.public.access.formscoding)
  • Re: Bugged by an easy problem for someone
    ... >> Question is why cannot I refer to the field in question useing ... > Because "Products" is the Table Name and I imagine that is stripped as ... I also grabbed the field names from the recordset as a test and the field ...
    (microsoft.public.data.ado)