Connect Livecode to DB

[Abeja]

Hi,
I am new in Livecode programming and I am struggling
I have to connect a project in Livecode to SQlite DB which has worked well. The problem was when I wanted to select the elements from my DB-table which are similar to a field content in a card ( user input) it gives me an error.
This is my script:

On mouseup
put revDataFromQuery(tab, return,gConnID, "SELECT * FROM tbl_Events WHERE City= '"& gCityfield &"' AND Date = '"& gDatefield &"',") into tConxn
end mouseup

Where City and Date are columns in my DB-table and I have previously put "Cityfield" and "Datefield" ( user input) in global Variable to be able to use them in any card of the stack. I am almost sure it is a syntax error.
would be great if somebody could help

[Klaus]

Hi Abeja,

welcome to the forum!

What does the error say?
Please always provide as much info as possible!

Quick guess:
In LC you need to declare a global, and I presume gConnID is supposed to be a global variable, in every script you use it! So maybe this is all you need?

Code: Select all

global gConnID
## Same for all scripts where you use this variable!

On mouseup
   put revDataFromQuery(tab, return,gConnID, "SELECT * FROM tbl_Events WHERE City= '"& gCityfield &"' AND Date = '"& gDatefield &"',") into tConxn
end mouseup

Best

Klaus

[Abeja]

Hi Klaus,
thnak you very much for your answer! Sorry no to be very precise.
I declared all the gVariables on top.Actually it is:

global gConnID
global gCityfield
global gDate
on mouseUp
put fld "Cityfield" into gCityfield
put fld "Date" into gDate
put revDataFromQuery(tab, return,gConnID, "SELECT * FROM tbl_Events WHERE City = '"& gCityfield &"' AND Date = '"& gDate &"',") into tConxn
put tConxn into field 1
end mouseUp
the error is: button "SearchButton": execution error at line 5 (Chunk: no such object), char 1

[Klaus]

OK, just wanted to be sure.

Hm, the error tells you that there is no field named "Cityfield" on the card where the "mouseup" is being executed. Is there?

Sorry, have to guess...

[Abeja]

yes field "Cityfield" is there and has a script:
on mouseup
set the locktext of me to "false"
put "" into me
select before me
end mouseup
So The idea is that the user could inter a city's name in the field and after clicking the button it switches to the next card and dispalys the result in field1.

[ghettocottage]

This may not be related, but I am seeing

Code: Select all

gDatefield

and

Code: Select all

gDate

being used intermittently...are these meant to be the same thing?

[Abeja]

Hi,
yes, I have always used gDate which is a global vat´riable where I saved the user input of field "Date".
g Datefield is a Tipo. But thank you for trying

[AxWald]

Hi,

put revDataFromQuery(tab, return,gConnID, "SELECT * FROM tbl_Events WHERE City= '"& gCityfield &"' AND Date = '"& gDatefield &"',") into tConxn

This results in an SQL query like that:

Code: Select all

SELECT * FROM  tbl_Events WHERE City= 'Hell Central'  AND Date = '2019-01-30',

Check the last char - this query will fail unavoidably. You might want to try to use a semicolon ;-)

Have fun!

[Abeja]

Hi,
thank you Form your answer! but I want to Select from my DB-table the attributes which are similar to the user's answer in field " Cityfield". Your example works perfectly because am already connected to the DB but this ist not what I need.

[Abeja]

Hi there, thank you very much! The semicolon was the failure, now it works