display date and time in a list box

I am trying to display a date and time stored in a sqllite database. it is currently stored as a datetime field. Code follows:

if rs <> Nil then //Continue if there is data do until rs.EOF //continue until we reach the End Of File URLlist.AddRow URLlist.Cell(URLlist.LastIndex, 0) = Cstr(rs.Field("ID").IntegerValue) URLlist.Cell(URLlist.LastIndex, 1) = Cstr(rs.Field("url").stringvalue) URLlist.Cell(URLlist.LastIndex, 2) = rs.Field("comments").StringValue URLlist.Cell(URLlist.LastIndex, 3) = Cstr(rs.Field("datechange").StringValue) rs.MoveNext //move to the next recordset loop //get the next row of data else MsgBox "No RecordSet exists." Return end if

Can someone tells me what I’m doing wrong please? or point me to a good explanation on how to display date/time in a list box?

Thanks

I should add Im getting a nilObjectexceptions on this code.

1) do until ASSUMES you have at least 1 row which may not be true depending on the query
either use do until rs.eof so you check at the TOP of the loop or while rs.eof <> true that also checks at the top of the loop

nm this as I misread the code you had
you DO use a form that does not assume you have at least 1 row

  1. sqlite really doesn’t give a darn about what column type you stuck on a column
    basically everything is stored as strings but there are affinities so some column types will be treated like numbers (but still can be dealt with as strings) … It’s very loose if you’re used to a different sql database

  2. cstr is NOT required on strings

Dim now As New date

Dim db As New SQLITEdatabase

Call db.Connect

db.sqlexecute("create table urls( id, url, comments, datechange)")

db.sqlexecute("insert into urls( id, url, comments, datechange) values (1, ""http://foo.bar.com"", ""comment"" , """ + now.SQLDateTime + """ ) " )

Dim rs As recordset = db.sqlselect("select * from urls")

If rs <> Nil Then //Continue if there is data
  while rs.EOF <> true //continue until we reach the End Of File
    URLlist.AddRow
    URLlist.Cell(URLlist.LastIndex, 0) = CStr(rs.Field("ID").IntegerValue)
    URLlist.Cell(URLlist.LastIndex, 1) = rs.Field("url").stringvalue
    URLlist.Cell(URLlist.LastIndex, 2) = rs.Field("comments").StringValue
    URLlist.Cell(URLlist.LastIndex, 3) = rs.Field("datechange").StringValue
    rs.MoveNext //move to the next recordset
  wend //get the next row of data
Else
  MsgBox "No RecordSet exists."
  Return
End If

ALL this said what does your query look like that grabs the recordset ?
did you ask for all the columns you try to access ?
you can get a nilobjectexception on that one

If you would have the correct date and time format for each country use this:

dim MyDate as new Date
If rs <> Nil Then //Continue if there is data
while rs.EOF <> true //continue until we reach the End Of File
URLlist.AddRow
MyDate = rs.Field(“datechange”).DateValue
URLlist.Cell(URLlist.LastIndex, 3) = MyDate.LongDate + " " MyDate.LongTime // Or any other time format

First of all, a belated thank you all for your responses. after much frustration just trying to display a date I finally gave up. for several months obviously! Recently I have decided to try and become one with XOJO again. I converted my code to the example showing me to use string Value for the date but am still getiing a nilObject exception. My current code follows:
The changedate field is defined as DateTime in the database.

[code]Dim rs as RecordSet
rs = db.SQLSelect(“SELECT * FROM URLdata”) //Get all of the data from URLdata

if rs <> Nil then

do until rs.EOF //continue until we reach the End Of File
URLlist.AddRow
URLlist.Cell(URLlist.LastIndex, 0) = Cstr(rs.Field(“ID”).IntegerValue)
URLlist.Cell(URLlist.LastIndex, 1) = rs.Field(“url”).StringValue
URLlist.Cell(URLlist.LastIndex, 2) = rs.Field(“comments”).StringValue
URLlist.Cell(URLlist.LastIndex, 3) = rs.Field(“datechange”).StringValue
rs.MoveNext //move to the next recordset
loop //get the next row of data
else
MsgBox “No RecordSet exists.”
Return
end if[/code]

I know that my database has no null fields in it because I use a different program to add records (SQL Light Pro).
The first row in the database is null but I know my code handles that . What am I missing here? I don’t get any syntax errors on any of the code so I assume it is valid syntax.

You are referring to it as changedate then referencing “datechange” in the rs.field().
Make sure your column names are correct here.

Thank You! Duh! I guess I had better quit for the night! I’m starting to go cross-eyed. It works like I want it to now.

Why CStr() and not simply Str() ?

Is the Listbox # of Columns OK ?
(set to 3 in the IDE ?)

And as Norman pointed out, do not use Do … Loop but rather

While not rs.EOF // your code Wend

Else, if you receive an empty recordset, your code will try to get data that is not available.
rs will only be nil if your Select is wrong or if the connection to the database gets lost during the query. It is not a way to escape an empty recordset which won’t be nil but whose EOF property will be true on its first record already. So your code currently does not handle an empty result like you thought.

Ulrich, if the EOF property is true on the first record (empty recordset) then:

Do Until rs.EOF // this code will not run Loop

will be true at the Do check then no code will be executed, no?

I think this code will try to execute at least one:

Do // this code will Loop Until rs.EOF

Sorry if I’m wrong, I’m re-learning (and trying to remember) many things.

Edit: I did this test:

Dim i As Integer = 10 Do Until i >= 10 MsgBox i.ToText i = i + 1 Loop
The code inside the Do…Loop is not executed, but:

Dim i As Integer = 10 Do MsgBox i.ToText i = i + 1 Loop Until i >= 10
is executed once.

Oh sorry, I had not seen you checked in the Do-part. Yes, you’re right.
Anyway rs = nil is only true under the conditions written above.

I’m sorry too, I didn’t read Norman’s first 2 lines.

If I remember correctly (from more than 25 years), I used Do…Loop Until, so in fact it executed at least once. That’s why I read the docs and found that you can use Do Until … Loop, or Do…Loop Until.