Problem with a simple sql condition

hello group, I have a problem with a sql … if the data exists ok, if it doesn’t exist it tells me that it exists all the same … where am I wrong? Perhaps it is so simple that it is difficult for me to find the error.

rows = db.SelectSQL("SELECT * FROM Scadenziario where NumeroFattura="+ Scadenziario.TextField1.text)   'Identifica una sola pagina della fattura


if rows=NIL then
  messagebox " INFO NOT PRESENT"
else
  messagebox " INFO PRESENT"
end if

This returns either ‘some rows’ or ‘no rows’
Both are valid.
But Rows will not be NIL unless the SQL causes an error.

The difference is record count.
If there is dats, then there will be (n) records.

I dont know what version of Xojo and APi you are using… recent ones you might check the AfterLastRow property … it should be true if there are no records.
Older versions use the EOF property

if you want to know in advance, use

SELECT count(1) FROM Scadenziario

That will always return a number, and if it is 0, then no point in getting actual data.

You haven’t quoted your text string. Better anyway is:

rows = db.SelectSQL("SELECT * FROM Scadenziario where NumeroFattura=?", Scadenziario.TextField1.text)   'Identifica una sola pagina della fattura

even so, either there is or there isn’t, he tells me that there is!!

rows = db.SelectSQL(“SELECT count(1) FROM Scadenziario where NumeroFattura=”+ Scadenziario.TextField1.Text) 'Identifica una sola pagina della fattura

messagebox rows.RowCount.ToString

???

1 Like

As Jeff says, use RowCount

For example:

If rows.RowCount = 0 Then
// No Record
Else
// Do the job
End If

I use db access, not is supported rowcount.

What ODBC driver (etc.) are-you using ?

Still from Jeff, and:

SELECT count(1) FROM Scadenziario

?

rows = db.SelectSQL("SELECT * FROM Scadenziario where NumeroFattura=?", Scadenziario.TextField1.text," and  NomeDitta=?",Scadenziario.TextField3.text, " and DataFattura=#" + ConvertToDateTime(TextField2.Text).ShortDate + "#")

Dim i as integer=0
While Not rows.AfterLastRow
  i=i+1
  rows.MoveToNextRow
Wend


If i = 0 Then
  messagebox " NO RECORD"
Else
  messagebox "RECORDS"
End If

it seems that I solved it like this !! What do you think ? Can it work?

How about using quotes around your variable text !

1 Like
rows = db.SelectSQL("SELECT * FROM Scadenziario where NumeroFattura=?1 and NomeDitta=?2 and DataFattura like '%" + ConvertToDateTime(TextField2.Text).ShortDate + "%'", Scadenziario.TextField1.text.ToInteger, Scadenziario.TextField3.text)
if  (rows<>Nil and rows.RowCount()>0)  then
  msgbox ("Records")
else
  msgbox ("No records")
end if

Since you say AfterLastRow works for you

Why not

if rows.AfterLastRow then
//no rows
else
//do something with the data
end if
1 Like

It helps if you tell us which database fields are numeric and which are text.

Immagine

What database are you using?

He says - above - Access…

ACCESS

Ah yes. Well I know nothing of Access so (most) of my suggestions may be wrong. :frowning_face:

Guys, I deleted the database and entered everything from scratch, it seems to work. I don’t know what happened … but it’s the search method that I use on all tables and it works … on this one I don’t know why it gave me an error. Anyway I will continue with my research… hopefully it works. Thank you all for the support.

I would use:

rows = db.SelectSQL("SELECT * FROM Scadenziario where NumeroFattura="+ Scadenziario.TextField1.text)   'Identifica una sola pagina della fattura


if rows.AfterLastRow then
  messagebox " INFO NOT PRESENT"
else
  messagebox " INFO PRESENT"
end if

[/quote]

1 Like