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?
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
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
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.
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 wont 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.
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.