Solved

Item cannot be found in the collection corresponding to the requested name or ordinal

Posted on 2008-06-22
10
1,218 Views
Last Modified: 2008-06-22
Ok guys i cant work out why i keepp getting this message, i have check the debug.print out putin access and it works with no problems, and i have checked to make sure that this information is in the database and all the spelling is correct and it is, i guess  if it wasnt it wouldnt be working in access the out put i am getting in the debug windows is

 select * from [Student_Outcomes] where [OutcomeDate]  = #10/09/2007# And [StudentID] = 60

and then a message

A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in adodb.dll

Thanks in advance guys

Dim rs As New ADODB.Recordset

        Dim sql As String

        sql = "select * from [Student_Outcomes] where [OutcomeDate]  = #" & ListView2.SelectedItems.Item(0).SubItems(1).Text & "# And [StudentID] = " & TextBox7.Text

        Debug.Print(sql)

        studentnotes.Text = ""

        studentnotes.Text = (rs.Fields("OutcomeNotes").Value)

        rs.Close()

Open in new window

0
Comment
Question by:awolarczuk
  • 4
  • 4
  • 2
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21840017
where is your rs.open statement?
0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21840019
Your code snippet is incomplete, so it is difficult to tell what code is elsewhere with regards to the database connection.  However, while you declare the sql string, there is no declaration to actually populate the rs recordset with the result of this query?

Code should be something like attached (I presume the bits I've added at the front you already have).
' Connect to database

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\Northwind.mdb"

Dim conn As Connection = New Connection

Dim connMode As Integer = ConnectModeEnum.adModeUnknown

conn.CursorLocation = CursorLocationEnum.adUseServer

conn.Open(ConnectionString, "", "", connMode)

Dim cmdType As Integer = CommandTypeEnum.adCmdText
 
 

Dim sql As String

sql = "select * from [Student_Outcomes] where [OutcomeDate]  = #" & ListView2.SelectedItems.Item(0).SubItems(1).Text & "# And [StudentID] = " & TextBox7.Text

Debug.Print(sql)

' Run query

Dim rs As _Recordset = conn.Execute(sql)

studentnotes.Text = ""

studentnotes.Text = (rs.Fields("OutcomeNotes").Value)

rs.Close()

Open in new window

0
 

Author Comment

by:awolarczuk
ID: 21840093
ok guys i have got it working now thanks so much, how could i have miss that but now i am getting another error that i thought the code i am using should have fixed

{"Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."}

is below
Private Sub ListView2_MouseHover(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView2.MouseHover

        Dim rs As New ADODB.Recordset

        Dim sql As String

        sql = "select * from [Student_Outcomes] where [OutcomeDate]  = #" & ListView2.SelectedItems.Item(0).SubItems(1).Text & "# And [StudentID] = " & TextBox7.Text

        Debug.Print(sql)

        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

        studentnotes.Text = ""

        If Not DBNull.Value.Equals(rs.Fields("OutcomeNotes").Value) Then

            studentnotes.Text = ""

            studentnotes.Text = rs.Fields("OutcomeNotes").Value

        Else

            studentnotes.Text = "Currently There are no notes to Display for this Student"

            rs.Close()

        End If
 

        rs.Close()

    End Sub

Open in new window

0
 

Author Comment

by:awolarczuk
ID: 21840096
i have also tried it like this
    Private Sub ListView2_MouseHover(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView2.MouseHover
        Dim rs As New ADODB.Recordset
        Dim sql As String
        sql = "select * from [Student_Outcomes] where [OutcomeDate]  = #" & ListView2.SelectedItems.Item(0).SubItems(1).Text & "# And [StudentID] = " & TextBox7.Text
        Debug.Print(sql)
        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
        studentnotes.Text = ""
        studentnotes.Text = rs.Fields("OutcomeNotes").Value
        rs.Close()
    End Sub
and get a type 'DBNull' to type 'string' is not valid
    Private Sub ListView2_MouseHover(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView2.MouseHover

        Dim rs As New ADODB.Recordset

        Dim sql As String

        sql = "select * from [Student_Outcomes] where [OutcomeDate]  = #" & ListView2.SelectedItems.Item(0).SubItems(1).Text & "# And [StudentID] = " & TextBox7.Text

        Debug.Print(sql)

        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

        studentnotes.Text = ""

        studentnotes.Text = rs.Fields("OutcomeNotes").Value

        rs.Close()

    End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21840189
BOF or EOF true basically means that your SQL statement isn't returning any data.

You have a debug statement for the sql.  Please paste the generated SQL here - either there is a problem with your generation of the SQL, or there is actually no data that matches your query.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21840191
that error means that you got no records back:
  Private Sub ListView2_MouseHover(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView2.MouseHover

        Dim rs As New ADODB.Recordset

        Dim sql As String

        sql = "select * from [Student_Outcomes] where [OutcomeDate]  = #" & ListView2.SelectedItems.Item(0).SubItems(1).Text & "# And [StudentID] = " & TextBox7.Text

        Debug.Print(sql)

        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly)

        studentnotes.Text = ""

        if not (rs.eof and rs.bof) then

           studentnotes.Text = rs.Fields("OutcomeNotes").Value

        end if

        rs.Close()

    End Sub

Open in new window

0
 

Author Comment

by:awolarczuk
ID: 21840200
ok mate thanks so much i am more then happy to give you 500 point for the extra help but bnow i am gettting

Conversion from type 'DBNull' to type 'String' is not valid.
on hte same line

any help mate please
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21840209
that means that the value returned could be NULL.
you have 2 options:

* make the NULL value replaced by the query

sql = "select NZ(OutcomeNotes, 'N/A') OutcomeNotes from [Student_Outcomes] where [OutcomeDate]  = #" & ListView2.SelectedItems.Item(0).SubItems(1).Text & "# And [StudentID] = " & TextBox7.Text
 

* handle the NULL value in the .net code

Private Sub ListView2_MouseHover(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView2.MouseHover

        Dim rs As New ADODB.Recordset

        Dim sql As String

        sql = "select * from [Student_Outcomes] where [OutcomeDate]  = #" & ListView2.SelectedItems.Item(0).SubItems(1).Text & "# And [StudentID] = " & TextBox7.Text

        Debug.Print(sql)

        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly)

        studentnotes.Text = ""

        if not (rs.eof and rs.bof) then

          if not IsDBNull(rs.Fields("OutcomeNotes").Value)

           studentnotes.Text = rs.Fields("OutcomeNotes").Value

          end if

        end if

        rs.Close()

    End Sub

Open in new window

0
 

Author Comment

by:awolarczuk
ID: 21840253
ok i think the code is working but it is changing all my date back to us format how can i keep them the way they are or state that they are aus format
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21840272
ms access uses the US format for "storing/recognizing" the dates.
for display in another format, you need to format the date value as needed, in access query that could be the Formatdatetime() function:
http://office.microsoft.com/en-us/access/HA012288411033.aspx
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Visual Studio installation 5 39
Visual Studio Code with .Net Core 5 17
How Can I fix my MouseHover Event? 14 28
Get String split 5 33
Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now