Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002

    Unanswered: Syntax rs![Tablename.Fieldname] does not work

    I'm just upsizing an VB6 application from Access to SQL Server 2000.

    I use ADO with the connection string
    "Provider='SQLOLEDB';Data Source='<computer>';Initial Catalog='<database>';User ID='<login>';Password='<password>';"

    I have opened a recordset with 2 tables like
    select * from table1 inner join table2 on ...

    Both tables have a field 'creationdate'. With Access the following works:
    debug.print rs![table1.creationdate]

    With SQL Server the recordset does not contain this field, but it seems to have two fields with the same name 'creationdate'.

    Is there any setting in SQL Server, to decide if it uses the syntax
    Any other suggestions?

    Of course I could work with aliases (AS), or change all fieldnames... :-(

    Thanks for any answers.

  2. #2
    Join Date
    Jul 2002
    I have a general routine as included below, don't know if it helps.
    It displays all fields for all record sets.

    strSQL = "select * from mytable1"

    Set rs = New ADODB.Recordset
    rs.Open strSQL, myconn, adOpenStatic, adLockOptimistic, adCmdText

    Do Until rs Is Nothing

    intrscount = intrscount + 1
    txtoutput_add ("Recordset no: " & CStr(intrscount))

    Do Until rs.EOF
    txtoutput_add ("*** Ny record: ***")
    For intloop = 0 To rs.Fields.Count - 1

    txtoutput_add (rs(intloop).Name & " = " & rs(intloop))

    Next intloop

    Set rs = rs.NextRecordset


  3. #3
    Join Date
    Feb 2002
    The only problem is that the recordset generated has 2 fields with the same name and I believe it picks up the last reference. No table information is stored with the field either. So basically you have to explicity define what you want in your select statement (which you should do anyway - * is a bad thing).

    You could use the ordinal reference combined with the number of repeating field names you have and programmatically loop through which table you want.

    Is is always the first table's field you want or could it be tableX ?

    Good luck.

  4. #4
    Join Date
    Nov 2002
    I have solved the problem by working with AS for the needed fields.
    So I wrote "SELECT ..., table1.attrib AS attrib1, table2.attrib AS attrib2 FROM table1 INNER JOIN table2 ON ..."

    Thanks for your suggestions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts