[RESOLVED] How to access inner joins?

[TrickyNick]

Dim dbCMD As New OleDb.OleDbCommand With {.Connection = dbConn, .CommandText = _
            <SQL>
                SELECT *
                FROM (tblServices
                INNER JOIN tblAgents AS Agent1 ON tblServices.SentByID = Agent1.AgentID)
                INNER JOIN tblAgents AS Agent2 ON tblServices.AssignedToID = Agent2.AgentID
                WHERE ServiceDateTime
                    BETWEEN ? AND ?
                    AND IsActive = True
                ORDER BY ServiceDateTime
            </SQL>.Value}
 
        Dim pDate1 As New OleDbParameter With {.DbType = DbType.DateTime, .ParameterName = "@P1"}
        Dim pDate2 As New OleDbParameter With {.DbType = DbType.DateTime, .ParameterName = "@P2"}
        Dim dbReader As OleDbDataReader
 
******other code note posted******
 
                reportData.Append("                        <td>")
                reportData.Append(dbReader.Item("Agent1").ToString)
                reportData.Append("</td>")
                reportData.AppendLine()
 
                reportData.Append("                        <td>")
                reportData.Append(dbReader.Item("Agent2").ToString)
                reportData.Append("</td>")
                reportData.AppendLine()

How can I get the info? Where I have dbReader.Item("Agent1").ToString gives me the error:

System.IndexOutOfRangeException: Agent1
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.OleDb.OleDbDataReader.GetOrdinal(String name)
at System.Data.OleDb.OleDbDataReader.get_Item(String name)
at Service_Assignments.frmEmail.setData() in C:\Downloads\Service Assignments\Forms\Reports\frmEmail.vb:line 100

I understand the error, Agent1 doesn't exist for the name. My question is, what is the name then?

[Shaggy Hiker]

Using SELECT * is quite quick to write, but quite limitting to work with. For one thing, it is a somewhat slow construct, for another, nobody can tell which fields you are actually getting back, and what they are called. Ideally, you shouldn't be just returning EVERYTHING from that query, but only returning those fields that are needed, as the rest is surplus effort for no gain. Even if what you need is EVERYTHING from the query, you still get better performance if you write them out.

It would also solve this problem. Agent1 appears to be nothing more than the alias you give to the table. It isn't a field in itself. So what did you expect it to do? Either you thought that it might be a field, which it appears you didn't, or you expected to get all the fields returned from that table, which certainly won't work, or you are misunderstanding JOIN.

[TrickyNick]

I was expecting it to be a field.

Now, honestly, I don't know how it works or anything. That SQL was given to me by someone else in the forums over a year ago and it worked. I am now redesigning my program from scratch.

I know the results, just not how it works. What it does is get the SentByID then querys tblAgents to get the Name of that agent and returns their name.

Oh fudge, the database has changed and I don't know how to rewrite that SQL part.

Before, I had the agent name (first and last) in one field, they are now in separate fields.

Basically what I want it to do is take SentByID, and return the Agent's Full Name (first & last) and then do the same thing for AssignedToID. (I will post this in the database forums for help in rewriting my SQL)

As for the *, I didn't realize there was a performance hit. I did use it that way for quickness and I am pulling in more fields then I need. I will write them out.

But back to the question at hand, say I change to SELECT ServiceDateTime, Brand, RAN, SentByID, AssignedToID, Notes. Do I do I access it by using SentByID or something else entirely?

[Shaggy Hiker]

That doesn't sound like the whole set of fields you want. If you want the full agent name, you would want that as a field in the return. In fact, you might not even care so much about the AssignedToID if you had the full agent name.

Now, you can get the full agent name in a single field, but there is a bit of a problem, as you are linking in the table that looks like it would have that name, twice, and it looks like you want the name from the AssignedTo version of the table, which would be Agent2. Since you have the tables joined in twice, you will have to explicitly state which table you want the name from. So, I think it would look like this to get the full name:

SELECT (Agent2.FirstName + ' ' + Agent2.LastName) As AgentName, <other fields here>

I am winging it on the field names, but you can probably recognize them and correct as needed. You can see that I am decorating the field names with the table name (Agent2) to instruct SQL which table you want those fields to be drawn from. I am also adding in a space between the first and last name, which I may be doing incorrectly. Ultimately, I am giving this new, combined, field an alias of AgentName. That would allow you to get the field later on with:

reportData.Append(dbReader.Item("AgentName").ToString)

[TrickyNick]

Here is my new code...it works ...

Dim dbCMD As New OleDb.OleDbCommand With {.Connection = dbConn, .CommandText = _
            <SQL>
                SELECT 
                    ServiceDateTime, ('(' + Brand + ') ' + RANumber) as bRAN, 
                    (Agent1.FName + ' ' + Agent1.LName) as SentAgent, 
                    (Agent2.Fname + ' ' + Agent2.LName) as AssignedAgent,
                    Notes
                FROM (tblServices
                INNER JOIN tblAgents AS Agent1 ON tblServices.SentByID = Agent1.AgentID)
                INNER JOIN tblAgents AS Agent2 ON tblServices.AssignedToID = Agent2.AgentID
                WHERE ServiceDateTime
                    BETWEEN ? AND ?
                    AND IsActive = True
                ORDER BY ServiceDateTime
            </SQL>.Value}

Thank you Shaggy.

[Shaggy Hiker]

Looks like you extrapolated from my post to make a totally better query. Nice job.

[TrickyNick]

How did it work before? It did exactly what I wanted it to do, but don't know how. Do you know how it worked the way I wanted it to in the first iteration of my code?

[Shaggy Hiker]

The code you posted in the first snippet worked as posted? I see no way for that to be possible without there being a field in the SELECT portion of the query called Agent1 and one called Agent2. Since you used a *, I can't see whether such a field exists, but it sure doesn't look like it. You also mentioned code that was not posted, so perhaps there was something that added those fields, but I quite frankly can't see how that would be possible.

[TrickyNick]

Well, I think it worked because of the *

Originally I accessed them via their index instead of their name. Since I retrieved everything, I managed to get what I wanted and worked. It's the only way I can see how it worked. Now that I've corrected it I can understand what it's doing.

[Shaggy Hiker]

Oh, that's certainly a solution. Accessing via ordinal (that's the name used in the methods, but index is quite the same) wouldn't care what the name was. It's risky, though, in that any change to the query that results in different fields, or a different order to the fields, would produce different results. However, I have always felt (without any specific testing to prove or disprove the point) that accessing the fields by ordinal is inherently faster, so I sometimes do that, though only when I have written out the fields and feel certain that they will never change.