Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1112
  • Last Modified:

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?
0
mukunth
Asked:
mukunth
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
rs.Open "select one.name AS Name_One,two.name AS name_Two from one,two",cn

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
0
 
TimCotteeCommented:
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.OLEDB.4.0;Data Source=c:\testbed\cremosa2k.mdb;Persist Security Info=False"
    cnnADO.Open
    rstADO.Open "select MyTable.Name,MyTable1.Name From MyTable,MyTable1", cnnADO, adOpenStatic, adLockReadOnly
    MsgBox rstADO.Fields("MyTable.Name").Value & " / " & rstADO.Fields("MyTable1.Name").Value
    rstADO.Close
    cnnADO.Close
    Set rstADO = Nothing
    Set cnnADO = Nothing

0
 
mukunthAuthor Commented:
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"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Yes, you can:

rs("name_one").Properties("BASECOLUMNTABLE")
rs("name_one").Properties("BASECOLUMNNAME")

Should give you the info you are looking for...

Cheers
0
 
mukunthAuthor Commented:
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 & " = " & rs("i").Properties(i).Value
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


set cn = new ADODB.Connection
set rs = new ADODB.Recordset

cn.Open "provider=sqloledb;server=(local);initial catalog=pubs;uid=sa;pwd=;"

set rs.ActiveConnection = cn
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
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 & " = " & rs("i").Properties(i).Value
Next

This worked for me...
It seems that the locking value enforces ADO to get the underlying tablenames and column names...

Cheers

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now