1 Topic by samtorn (edited by samtorn 2024-03-18 10:50:49)

  • Registered: 2024-03-18
  • Posts: 11

Topic: any idea for count queries

i have this query

if SQLExecute("SELECT COUNT(*) FROM student WHERE date = '" + student.DateTimePicker1.sqlDate + "' AND id_Teachers = CAST('" + student.ComboBox1.sqlValue + "' AS INTEGER)") <> 0 then

here I want to check the date if this date is already available for specific teacher for that I used 

id_Teachers = CAST('" + student.ComboBox1.sqlValue + "' AS INTEGER)"

to check only if the date is available for that teacher (id_Teachers) and this is a forging key , but the code is not working ! any idea please …

I have changed the student.ComboBox1.sqlValue  to integer as I see that it is a string .

2 Reply by sparrow (edited by sparrow 2024-03-18 11:12:05)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 895

Re: any idea for count queries

The SQLExecute function has the form 

SQLExecute( ' SELECT ....  ' );

sqlDate -  Returns the date value of the component for use in SQL queries. The property value already contains escape quotes. In the case of an empty value, it will return NULL.

example:  SQLExecute ('INSERT INTO tablename (fieldname) VALUES ( ' + Form1.DateTimePicker1.sqlDate + ' )');

sqlValue - Returns the id of the selected record in the component, for use in SQL queries. In case of empty value, it returns NULL string. The property value already contains escape quotes.

example: SQLExecute('INSERT INTO tablename (fieldname) VALUES ( ' + Form1.ComboBox1.sqlValue + ' )');.

The use of CAST is not required.
Requires familiarity with MVD, SQLite.

https://myvisualdatabase.com/help_en/Co … ndEve.html

https://www.sqlite.org/index.html

https://myvisualdatabase.com/forum/view … ?pid=50515

https://myvisualdatabase.com/forum/view … ?pid=50517

3 Reply by tcoton (edited by tcoton 2024-03-18 15:12:21)

  • Registered: 2014-06-25
  • Posts: 863

Re: any idea for count queries

Please keep ids as integer type in the database as that is what it indeed is especially for auto increment. Sometimes, in some querys you might need to transform an integer to string and vice versa which can be accomplished by using strtoint(your field here) or inttostr(your field here). That is especially true with sqlite when comparing data in the same query that must match a certain type.

What would help is to have the real error message as « the code does not work » is vague. To help you further, when a sql error is produced in MVD you can display the query to see where the error is.