Get webpopupmenu values from SQLite DB

Databases
1

Hi everybody,

I have an SQLite database included into my xojo project. I have created a webpage with a popupmenu and I would like to get the values from a table in my SQLite DB to show them when I select the popupmenu. I have created an ‘open’ event handler and I include this code:

[code]If DB.Connect = true then

Dim rs As RecordSet = DB.SQLSelect("Select StdServices.ServiceName From StdServices")

If rs <> nil and rs.RecordCount > 0 then
  
  While not rs.EOF
    Me.AddRow ("")
    rs.MoveNext
  Wend
  rs.Close
End if

End if[/code]

Where is the error in my code? Could anyone help me?

Thanks.
Sergio

2

Me.Addrow ("") adds a blank item

Try
Me.Addrow rs.Field(“Whatever”).StringValue

3

Another possible error is that your SQL statement has an error and you don’t know it. rs will only be nil if there is an error.

[code]Dim rs As RecordSet = DB.SQLSelect(“Select StdServices.ServiceName From StdServices”)

if db.error then
msgbox db.errormessage
return
end[/code]

You should get into the habit of ALWAYS checking for the database error after every db action.

4

Thanks for your fast answer Bob but It doesn’t work.

I have a doubt, to launch this code, Do I need to include it in the ‘Open’ event or in 'ContextualMenuAction?

When I click on the popupmenu, I don’t get any value.

5

I have used this code now:

[code]If DB.Connect = true then

Dim rs As RecordSet = DB.SQLSelect("Select StdServices.ServiceName From StdServices")

if DB.error then
  msgbox DB.errormessage
  return
end

If rs <> nil and rs.RecordCount > 0 then
  
  While not rs.EOF
    Me.Addrow rs.Field("ServiceName").StringValue
    rs.MoveNext
  Wend
  rs.Close
End if

End if[/code]

6

Open event should work. You can simplify the code as shown below since if there are no records (i.e. record count = 0) rs.eof will be set to true.

[code]If DB.Connect = true then

Dim rs As RecordSet = DB.SQLSelect("Select StdServices.ServiceName From StdServices")

if DB.error then
  msgbox DB.errormessage
  return
end

 
  While not rs.EOF
    Me.Addrow rs.Field("ServiceName").StringValue
    rs.MoveNext
  Wend
  rs.Close

End if[/code]

7

Ok, I have changed the code with your comments. It doesn’t work yet.

I add an operator to evaluate the code but in debug mode when I click on the popupmenu, it happens nothing.

Better you see my example: DBTest

8

I don’t know If the problem is the SQLite DB. I don’t create the DB in Xojo, I select an SQLite DB existing in the same folder.

9

I made one change where I removed the “DB” object from the project, and loaded it dynamically in the App.Open event. Works fine then. I do not add databases to my projects like you did so I’m not sure why it’s not working like you wanted.

https://dl.dropboxusercontent.com/u/24111541/DBTest%20-%20BK.zip

10

Thanks Bob, It’s true, your example works perfectly. I am newbie with Xojo and I will study your example to connect to my database like you. I don’t understand why it doesn’t work selecting an existing DB but I’m sure that your option it’s better… and works.

Thank you very much.
Sergio

11

You might find a subscription to my Xojo training videos to be useful. We have over 60 hours of Xojo videos (about 200 videos and most come with project files) at show a lot of how we do things in Xojo (been doing this for about 15 years). http://xojo.bkeeney.com/XojoTraining/. We have a couple of start to finish desktop projects and one complete start to finish web application.

12

Thank Bob, I have already seen your subscription to your training videos. The price is reasonable but in this moment I cannot do this spending maybe the next year. I think so that firstly it’s better I get to know Xojo to take full advantage of your subscription.

Thanks for all.

Sergio

13

Sure, It will still be there whenever you can get to it.