Could not be found in the collection corresponding to the requested name or ordinal

[ssabc103]

Hello:

I have the following code in .NET 2017. It returns correctly and runs fine in SQL, however in my program I get the error 'could not be found in the collection corresponding to the requested name or ordinal'

    Private Function GetHexID(ByVal _Filename As String) As String
        Dim connectionString As String = "Provider=sqloledb;Data Source=Cadserv;Initial Catalog=Waconia;User Id=sa;Password=DaPam8wf;"

        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset

        Dim sql1 As String = "SELECT CONVERT(VARBINARY(8), d.DocumentID) " &
                             "FROM DocumentsInProjects As dip " &
                             "INNER JOIN Documents As d " &
                             "INNER JOIN Users On d.UserID = Users.UserID ON dip.DocumentID = d.DocumentID " &
                             "WHERE(d.Filename = '" & _Filename & "') "

        Debug.WriteLine("@GetHexID: " & sql1)
        MessageBox.Show(rs.Fields(0).Value) ' could not be found in the collection corresponding to the requested name or ordinal

        cn.Open(connectionString)
        rs.Open(sql1, cn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockReadOnly)

        Try
            rs.MoveFirst()
            Return (rs.Fields(0).Value)

        Catch

        End Try

    End Function

It's got to be something simple.

[PlausiblyDamp]

Have you tried printing the value of sql1 to the debug window and seeing if it looks right? Try copying the generated code and pasting it into whichever sql tool you use and run it, does it work then?

[ssabc103]

Yes, and pasted it back into SQL, no problem

[OptionBase1]

Assuming you are getting the error on this line:

MessageBox.Show(rs.Fields(0).Value)

When that line of code is executed, you haven't even opened a connection to the database yet, much less executed the query. How could rs contain any field values? It can't, thus the error.

[ssabc103]

Yes, that makes sense. rs.Open needs to some first, and I am an idiot on that one.
I am concerned though, because I am returning rs(0), which I believe is the only field I'm seeking.
This query in SQL works great:

SELECT CONVERT (VARBINARY(8), d.DocumentID)
       FROM DocumentsInProjects As dip 
	   INNER JOIN Documents As d 
	   INNER JOIN Users On d.UserID = Users.UserID ON dip.DocumentID = d.DocumentID 
WHERE(d.Filename = 'E7126038.SLDDRW')

Attachment 194540



Modified Code....

    Private Function GetHexID(ByVal _Filename As String) As Object
        Dim connectionString As String = "Provider=sqloledb;Data Source=Cadserv;Initial Catalog=Waconia;User Id=sa;Password=DaPam8wf;"

        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset

        Dim sql1 As String = "SELECT CONVERT(VARBINARY(8), d.DocumentID) " &
                             "FROM DocumentsInProjects As dip " &
                             "INNER JOIN Documents As d " &
                             "INNER JOIN Users On d.UserID = Users.UserID ON dip.DocumentID = d.DocumentID " &
                             "WHERE(d.Filename = '" & _Filename & "') "

        Debug.WriteLine("@GetHexID: " & sql1)

        cn.Open(connectionString)
        rs.Open(sql1, cn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockReadOnly)

        Debug.WriteLine(rs.Fields(0).Value) ' could not be found in the collection corresponding to the requested name or ordinal

        Try
            rs.MoveFirst()
            Return (rs.Fields(0).Value)

        Catch

        End Try

        rs.Close()
        cn.Close()

    End Function

[OptionBase1]

I'm not sure how alias "scope" or visibility is handled in a SQL statement, but your convert statement uses "d" before it is defined later as an alias for Documents. It also isn't clear why that convert statement is necessary.

If I were in your shoes, I would change the d.DocumentID inside the convert statement to Documents.DocumentID and see if that makes any difference. Actually, I would simplify the query completely and just start out with a simple select statement that doesn't do any joins at all just to make sure the basic stuff (connection string, etc.) was correct.

Also, if you did actually post the real sa credentials to your SQL server, I would recommend making sure your resume is updated.

[ssabc103]

Thanks for the info.
Password isn't live.

[Zvoni]

Select convert (varbinary(8), d.documentid)
from documentsinprojects as dip
inner join documents as d
inner join users on d.userid = users.userid on dip.documentid = d.documentid
where(d.filename = 'e7126038.slddrw')

seriously????

[ssabc103]

Ask yourself this question, are your responses helpful? Why does this forum exist? I cannot say that I understand. You all seem to be bunch of holier than thou coders who get off on putting people down. I will not be asking any more questions here. The query works in SQL but not in VB.NET, so yes, seriously, this is what I am trying to do. The convert statement is necessary to get the Hex ID of a value, this is for SolidWorks PDM to deal with how they organize the raw files in their folder structure. Yes this is serious business. It's not the joke you might be referring to, but then again your signature suggests coding is a joke.

[si_the_geek]

Zvoni was pointing out (using colours) that this section:

from documentsinprojects as dip
inner join documents as d
inner join users on d.userid = users.userid on dip.documentid = d.documentid

...should actually be like this:

from documentsinprojects as dip
inner join documents as d on dip.documentid = d.documentid
inner join users on d.userid = users.userid

Changing that might make a difference to the results you get.

[Zvoni]

Zvoni was pointing out (using colours) that this section:

from documentsinprojects as dip
inner join documents as d
inner join users on d.userid = users.userid on dip.documentid = d.documentid

...should actually be like this:

from documentsinprojects as dip
inner join documents as d on dip.documentid = d.documentid
inner join users on d.userid = users.userid

Changing that might make a difference to the results you get.

Thx, Si

I wouldn't know of a single DBMS in which OP's SQL-Statement would be legal.....
And i'm working with Databases and SQL for the last 30 years

[sapator]

And the funny thing is that it actually works!

I've tried on a user database that we have (just joining whatever columns) and it brings out a result!

Select *
from cognetic_members_membershipBalance dip 
inner join zz_delete_memberpoins as d
inner join cognetic_members_membership on d.membershipBalance_balanceTypeid = cognetic_members_membership.membership_clubid on dip.membershipBalance_membershipid = d. membershipBalance_membershipid

Maybe simulate a union of tables? I don't have a clue tho

[sapator]

This?

SELECT *
FROM cognetic_members_membershipBalance dip
INNER JOIN zz_delete_memberpoins AS d
  ON dip.membershipBalance_membershipid = d.membershipBalance_membershipid
INNER JOIN cognetic_members_membership
  ON d.membershipBalance_balanceTypeid = cognetic_members_membership.membership_clubid

[Zvoni]

And the funny thing is that it actually works!

I've tried on a user database that we have (just joining whatever columns) and it brings out a result!

Maybe simulate a union of tables? I don't have a clue tho

Yeah. Through research i found out, that it's really a legal statement for INNER JOINs in MS-SQL-Server.
Why does M$ always have to do their own B***S***? *sigh*

All said: I apologize to OP.
I'd probably set a Breakpoint directly after the rs.open, and inspect the Recordset-Object, FieldCount and whatever else can be found there.
The Error-Message implies, there are no Fields in the Recordset, since a rs.Fields(0) should exist, if the Query was successful.