VB6 Stored Procedure Syntax to get values of 2 tables with same fieldnames

[todd.kauffman]

How do I reference the field names correctly using the "!FieldName" method when my stored procedure is returning 2 joined tables with same field names other than table1.field and table2.field??

I thought it might be ![TableName].[FieldName] but it isnt giving me anything.

Thanks!

[SamOscarBrown]

What do you mean "it isnt (sic) giving me anything"?
Got some code, an example?

[todd.kauffman]

Yes, here is a snippet: The result would have DPO.start_time and DPS.start_time field names so when i just put start_time it isnt showing a value just empty, if I rename the field with an AS in the stored proc it shows the value, but I got a lot of fields to rename and am being lazy :-)

Set mobjRst = mobjCmd.Execute

With mobjRst

Do Until .EOF

date_entered = !start_time

MsgBox date_entered

[MarkT]

If you have ambiguous field names, they should be aliased.

[todd.kauffman]

OK, thats what I thought, time to get to renaming :-) Thanks!

[MarkT]

You only have to alias the columns that are ambiguous.

[dilettante]

You might try dumping the names of the returned fields first, for example iteerate over the Fields collection and Debug.Print each Field's Name property.

For example this works fine:

Option Explicit

Private Sub Form_Load()
    Dim C As New Collection

    C.Add 3, "This.That"
    MsgBox C![This.That]
End Sub

So if your query is returning names qualified by table name this could be your answer.

[Phill.W]

You don't.
You add aliases into your query to make the returned field names unique.

select name as original_name 
,      name as updatable_name 
from   table_of_names as as t1 
where  id = ?

Regards, Phill W.

[dilettante]

*sigh* Well I tried to offer a hint to see if anyone might actually try it and see, but...

As far as I can recall SQL query result sets have worked this way since the beginning, back in the 1970s. And as one would expect ODBC, OLE DB, ADO, or even DAO had no reason to alter this behavior and so they never have. A trivial demo shows this pretty clearly:

Duplicated field names are qualified just as one would expect

Whether doing things that way in your programs is a smart idea or not depends entirely on context. However it is perfectly feasible, works just fine, and as I said it would be a rare SQL-based query engine that fails to deal with it since it has been standard behavior since the beginning.

So given that the names can be qualified, you can expect the Fields collection to use these qualified names as column key values as well. This answers the original question instead of directing the OP off into the weeds.

[MarkT]

So are you suggesting that aliasing ambiguous columns isn't the better and more accepted way of dealing with ambiguous column names? I would disagree that suggesting the use of an alias is directing the OP off into the weeds.

[dilettante]

If the stored procedure returns qualified names it probably isn't worth the trouble to layer on aliasing, and depending on the query engine and stored procedure it may not even be possible.

Besides, he asked how to get at values from the returned fields so telling him to go do something else doesn't answer the question.

As for "better" or "more accepted" I think you've been drinking far too much kool-aid. There is nothing to suggest such value judgements apply or that they're even a smart thing to do in every circumstance.

[MarkT]

No point in dragging this through the mud. I guess we'll just agree to disagree.

[FunkyDexter]

I'm going to drag it through the mud just a little further (sorry)

I would argue that adding aliases is certainly better if you have the luxury of doing so. If, for example, this stored procedure is already in place and being called from dozens of places in dozens of apps then it's probably better to live with the two part qualifiers than destabilise the whole lot.

So both are good suggestions, depending on the context.

[MarkT]

I guess my point would be that I think most would agree if you're writing a stored procedure that that listing the return fields is better practice than Select *. With that, if you are listing the fields you should know ahead of time if you will have ambiguous columns and could alias them at that point. Eliminate the problem from the start.

[FunkyDexter]

if you're writing a stored procedure that that listing the return fields is better practice than Select *.

Now that I agree with 100%

[dilettante]

However that is also 100% useless in answering the original question.

If somebody asks for help eating scrambled eggs with chopsticks telling him to boil the eggs isn't much of an answer.

[MarkT]

So you're saying if you give an answer that's offering alternative solutions to the original question that will deliver the same results than that answer is useless?
If that's the case then why do any of us respond to posts by salsa31?

[Tech99]

...my stored procedure is returning 2 joined tables with same field names other than table1.field and table2.field?

You can use AS clause, for example;

Select table1.field as tbl1Field, table2.field as tbl2field from ...

also

Select table1.field, table2.field from ...

should work, so maybe there is some other thing preventing result set from returning?