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

    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
    [table.field]?
    Any other suggestions?

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

    Thanks for any answers.
    Martin

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    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
    rs.MoveNext
    Loop

    Set rs = rs.NextRecordset

    Loop

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    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
    Posts
    2
    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.
    Martin

Posting Permissions

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