Reading data from a database query

[polar]

Hi,

I have a database query that I am reading data from into a form. I can read one column into a listbox without any problems.
Where I get into difficulty is when I try to use the data read into the listbox to get the related rows.

I keep getting the following error:

"Missing PrimaryKey Was Unhanled Exception"
"Table doesn't have a primary key"

The query has four fields and one of them is a primary key. Is reading from queries very different to reading from tables??

Here is the code I have;

 Dim objConnection As New OleDb.OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= FYP.mdb")
Dim objAbsentNumberDA As New OleDb.OleDbDataAdapter("Select * from NEWB", objConnection)
    Dim objAbsentNumberCB As New OleDb.OleDbCommandBuilder(objAbsentNumberDA)
  Dim strNumber As String
    Dim StudentName As String
    Dim ClassName As String

under the form load:

 objAbsentNumberDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
objDS.Clear()
objAbsentNumberDA.FillSchema(objDS, SchemaType.Source, "NEWB")
        objAbsentNumberDA.Fill(objDS, "NEWB")

  lstNumberAbsent.Items.Clear()
        FillNumberAbsent()
        FillNamesNeeded()

 Private Sub FillNumberAbsent()
        Dim i As Integer
        For i = 1 To objDS.Tables("NEWB").Rows.Count
            strNumber = objDS.Tables("NEWB").Rows(i - 1).Item("Absent")
            lstNumberAbsent.Items.Add(strNumber)
        Next
    End Sub

    Private Sub FillNamesNeeded()
        Dim objRow2 As DataRow
        objRow2 = objDS.Tables("NEWB").Rows.Find(strNumber)
        StudentName = objRow2.Item("Student")

FillNumberAbsent() works fine. It's in FillNamesNeeded() that I get the error at the line: objRow2 = objDS.Tables("NEWB").Rows.Find(strNumber)

Am I doing something wrong, or has it something to do with the fact that I'm using queries?

Thanks

 

[polar]

Hi, I resolved the error I was getting earlier.

I set the Primary Key by going:

objAbsentNumberDA.FillSchema(objDS, SchemaType.Source, "NEWB")
objAbsentNumberDA.Fill(objDS, "NEWB")
objDS.Tables("NEWB").PrimaryKey = New DataColumn()
{objDS.Tables("NEWB").Columns("StudentID")}


But then I keep getting the "Object reference not set to an instance
of an object." error in relation to the following line of code:
StudentName = objRow2.Item("Student") - from FillNamesNeeded()

Private Sub FillNamesNeeded()
Dim objRow2 As DataRow
objRow2 = objDS.Tables("NEWB").Rows.Find(strNumber)
StudentName = objRow2.Item("Student")
End Sub


I've been messing with this all morning - any ideas as to what's
wrong???


Thanks

 

[cjard]

You seem to be making things difficult for yourself Post your database and I'll take 5 minutes out to do you an example project

 

[polar]

Sorted!!

Thanks, but I actually managed to get the thing working for me earlier this evening
Private Sub FillNumberAbsent()
Dim i As Integer
For i = 1 To objDS.Tables("NEWB").Rows.Count
strNumber = objDS.Tables("NEWB").Rows(i - 1).Item("Absent")
lstNumberAbsent.Items.Add(strNumber)
StudentName = objDS.Tables("NEWB").Rows(i - 1).Item("Student")
lstStudentNames.Items.Add(StudentName)
ClassName = objDS.Tables("NEWB").Rows(i - 1).Item("Class Name")
lstClassNames.Items.Add(ClassName)
Next
End Sub