var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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:
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.
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))
Set rs = rs.NextRecordset
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 ?
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.