mukunth
asked on
Field Name with alias
I'm using a recordset in ADO to access data form multiple tables,say,with the following structure:
Table Name : One
Field Names : name
Table Name : One
Field Names : name
Now I say,
rs.Open "select one.name,two.name from one,two",cn
where rs=recordset name
I want to access this recordset with the fieldname(not with ordinal position) as rs("name") etc..It gives me only the first table's value.Is there any way to retrieve using the alias name,ie,for the second table?
Table Name : One
Field Names : name
Table Name : One
Field Names : name
Now I say,
rs.Open "select one.name,two.name from one,two",cn
where rs=recordset name
I want to access this recordset with the fieldname(not with ordinal position) as rs("name") etc..It gives me only the first table's value.Is there any way to retrieve using the alias name,ie,for the second table?
The following code, allows me to use the fully qualified field name:
Dim rstADO As New ADODB.Recordset
Dim cnnADO As New ADODB.Connection
cnnADO.ConnectionString = "Provider=Microsoft.Jet.OL
cnnADO.Open
rstADO.Open "select MyTable.Name,MyTable1.Name
MsgBox rstADO.Fields("MyTable.Nam
rstADO.Close
cnnADO.Close
Set rstADO = Nothing
Set cnnADO = Nothing
Dim rstADO As New ADODB.Recordset
Dim cnnADO As New ADODB.Connection
cnnADO.ConnectionString = "Provider=Microsoft.Jet.OL
cnnADO.Open
rstADO.Open "select MyTable.Name,MyTable1.Name
MsgBox rstADO.Fields("MyTable.Nam
rstADO.Close
cnnADO.Close
Set rstADO = Nothing
Set cnnADO = Nothing
ASKER
to angelIII : Yes,I can "alias" the field name itself but is there any property through which I can retrieve the table aliases?
to TimCottee : rs("tablename.fieldname") doesn't work.It says "Item cannot be found in the collection corresponding to the requested ordinal or position"
to TimCottee : rs("tablename.fieldname") doesn't work.It says "Item cannot be found in the collection corresponding to the requested ordinal or position"
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Yes, you can:
rs("name_one").Properties(
rs("name_one").Properties(
Should give you the info you are looking for...
Cheers
rs("name_one").Properties(
rs("name_one").Properties(
Should give you the info you are looking for...
Cheers
ASKER
To angelIII :
I tried but it didn't return anything.ie.it returns NULL value.Is the following code correct?:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "provider=sqloledb;data source=(local);initial catalog=pubs;uid=sa;pwd=;"
rs.Open "select t.i,t1.i from t,t1", cn
Dim i As Integer
For i = 0 To rs("i").Properties.Count - 1
List1.AddItem rs("i").Properties(i).Name
Next
The above adds ALL the properties in the listbox,but most of the properties have NULL as their value including BASETABLENAME and BASECOLUMNNAME.So,what goes?
I tried but it didn't return anything.ie.it returns NULL value.Is the following code correct?:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "provider=sqloledb;data source=(local);initial catalog=pubs;uid=sa;pwd=;"
rs.Open "select t.i,t1.i from t,t1", cn
Dim i As Integer
For i = 0 To rs("i").Properties.Count - 1
List1.AddItem rs("i").Properties(i).Name
Next
The above adds ALL the properties in the listbox,but most of the properties have NULL as their value including BASETABLENAME and BASECOLUMNNAME.So,what goes?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Now you can access the fields with
rs("name_one") and rs("name_two")
You might however simply try to access the fields by their indexes:
rs(1)
Cheers