Nil RecordSet

Databases
1

I am at a stand still on this.

dim peopleRS as RecordSet peopleRS = DB.SQLSelect(sql)

When trying to run my project im stopped and it is telling me that peopleRS Nil under my variable.

Here is the entire code for this method.

[code]Sub Open()

dim dr as new DatabaseRecord
dr = new DatabaseRecord
dim sql as string = “select LastName,FirstName,MiddleInitial,Address,Address2,City,State,Zip,Phone,Email,DOB,SocialSecurity,MedicareNumber,PartAEffDate,PartBEffDate,Company,PlanType,PlanName,EffDate,Premium from people”

dim peopleRS as RecordSet
peopleRS = DB.SQLSelect(sql)

If DBError Then Return

// Fetch each client from the RecordSet and add it
// to the Listbox.
If peopleRS <> Nil Then
while not peopleRS.eof
lstPeople.AddRow(peopleRS.field(“LastName”).StringValue)
lstPeople.Cell(lstPeople.LastIndex, 1) = peopleRS.field(“FirstName”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 2) = peopleRS.field(“MiddleInitial”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 3) = peopleRS.field(“DOB”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 4) = peopleRS.field(“SocialSecurity”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 5) = peopleRS.field(“Phone”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 6) = peopleRS.field(“Email”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 7) = peopleRS.field(“Address”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 8) = peopleRS.field(“Address2”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 9) = peopleRS.field(“City”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 10) = peopleRS.field(“State”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 11) = peopleRS.field(“Zip”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 12) = peopleRS.field(“MedicareNumber”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 13) = peopleRS.field(“PartAEffDate”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 14) = peopleRS.field(“PartBEffDate”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 15) = peopleRS.field(“Company”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 16) = peopleRS.field(“PlanType”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 17) = peopleRS.field(“PlanName”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 18) = peopleRS.field(“EffDate”).StringValue
lstPeople.Cell(lstPeople.LastIndex, 19) = peopleRS.field(“Premium”).StringValue

peopleRS.MoveNext

wend
peopleRS.Close
End If
End Sub[/code]

2

On what line is it giving you the error? Are you sure it isn’t DB that’s nil?

3

nil recordset = bad sql query
display db.errormessage after db.sqlselect
may be one column that has an error in its name ?

4

Or if that list of fields is all (or most) of the fields in the table, just simplify the query

SELECT * FROM PEOPLE
5 
peopleRS = DB.SQLSelect(sql)

Where do you initialise DB?

6

My 2¢:
If you do not display an error, how can you know where the error is located ?

Example:

If DB.Error Then // You forgot the dot here MsgBox "Error: DB is Nil." + Str(DB.ErrorCode) + ": " +DB.ErrorMessage Return End If

7

My best bet too. DB “must” be NIL… :slight_smile:

8

you would get a nil exception. here the OP said a nil recordset …

10

Almost certainly this turn out to be a case of
‘I have an error but I will show you some code which is not the actual code I am using’

despite this:

because if the compiler allows
If DBError
then DBError is a global variable or a function, or the code wouldnt work long enough to debug the recordset.

The OP doesn’t actually say there is an error. Just that peopleRS is null
If any database error is ignored, then the test for nil will just skip around the wend construct without an error.

Im curious to know what kind of people exist in this people table
They appear to have properties of PartAEffDate,PartBEffDate,Company,PlanType,PlanName
Maybe (hopefully) people is a view joining several tables that link people with orders and plans.

11

Calvin, please give us the exact error message, because your description above is ambiguous.

As others have pointed out, most likely the problem is that “DB” is not initialized properly and therefore is Nil.

12

13

sorry for some reason i wasn’t receiving notifications that there were replies.

14

Click on the NilObjectException link in the variables list and it will tell you what the exact problem is.

15

@Jay Madren I did that and nothing is displayed

16

Please post the code the initiates DB… most likely this is where you problem lies.

DB does not have the table specified or the table does exist and one or more fields do not exist and/or are misspelled.

17

Correct. Either DB is not defined or initialized or is out of scope.

No, none of these problems would produce the NilObjectException. They would produce a DB.Error.

18

Called it.
The code in use wasn’t the code that was posted.

In the screenshot version PeopleRS is dimmed and set all on one line.
AT the point that it errors it is definitely nil
But that is not the problem.
The DB.SQLSelect is failing and is not being tested for error

Dave is correct:

[quote]DB does not have the table specified or the table does exist and one or more fields do not exist and/or are misspelled.
[/quote]

19

20

You don’t have code to assign that database to db?

21

@Alberto De Poo

Ok the crazy thing is I have used this exact same code before in the past with a client manager that only had 10 fields and it works great. I am going crazy trying to figure out how it works on the other program and since i have added 10 more fields it doesnt work on this one.