awolarczuk
asked on
Item cannot be found in the collection corresponding to the requested name or ordinal
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.InteropSer vices.COME xception' occurred in adodb.dll
Thanks in advance guys
select * from [Student_Outcomes] where [OutcomeDate] = #10/09/2007# And [StudentID] = 60
and then a message
A first chance exception of type 'System.Runtime.InteropSer
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()
where is your rs.open statement?
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).
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()
ASKER
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
{"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
ASKER
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.It em(0).SubI tems(1).Te xt & "# And [StudentID] = " & TextBox7.Text
Debug.Print(sql)
rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpe nDynamic, ADODB.LockTypeEnum.adLockP essimistic )
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
Dim rs As New ADODB.Recordset
Dim sql As String
sql = "select * from [Student_Outcomes] where [OutcomeDate] = #" & ListView2.SelectedItems.It
Debug.Print(sql)
rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpe
studentnotes.Text = ""
studentnotes.Text = rs.Fields("OutcomeNotes").
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
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.
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.
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
ASKER
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
Conversion from type 'DBNull' to type 'String' is not valid.
on hte same line
any help mate please
that means that the value returned could be NULL.
you have 2 options:
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.