Invalid Argument error when query returns 0 row.

on what row in the code, can you show code ?

Assuming you're using Delphi 5 and ADO components: Download ADOExpress Update Pack 2 from http://info.borland.com/devsupport/delphi/mdac26.html

This fix resolves issue: "...Most commonly, the following error is generated when closing an empty dataset: "Either EOF or BOF is True, or the current record has been deleted. ..."

I'm using Delphi 7 + Zeos DB Components.

The row I mentioned is the result of the query. I mean the search comes up with no result.

yea i understand , but what do you do in code when the error is raised

are you trying to select a value from a field ? you should check wheter there is a record or not. so check the Bof and Eof properties.

Most common mistake is reading frmo a record when there is none.

Let me know if you got a different situation

greetings'
arni

procedure TfrmMain.edtRHSearchOrderKeyPress(Sender: TObject;
  var Key: Char);
var
  conditionalSQL : TStringList;
begin
  if ((edtRHSearchOrder.Text <> '') AND (length(edtRHSearchOrder.Text) >= 4) AND (Key = #13)) then
  Begin
    conditionalSQL := TStringList.Create;
    try
      conditionalSQL.text := 'SELECT *'
        +'FROM'
        +'`customer`'
        +'INNER JOIN `orders` ON (`customer`.`order_number` = `orders`.`number`)'
        +'WHERE'
        +'(`customer`.`bpostcode` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`spostcode` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`bsurname` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`ssurname` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`card_number` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`email` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`order_number` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`bphone` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`sphone` LIKE "%'+edtRHSearchOrder.Text+'%")'
        +'GROUP BY `orders`.`number` ORDER BY `orders`.`number` DESC';
      dmQueries.queryRHViewOrders.Close;
      dmQueries.queryRHViewOrders.SQL.Clear;
      dmQueries.queryRHViewOrders.SQL.Text := conditionalSQL.Text;
      dmQueries.queryRHViewOrders.Open;
      edtRHSearchOrder.Text := '';
    finally
      conditionalSQL.Free;
    end;
  end;
end;

This is the query I'm running. (TZQuery). and there is DataSource related to this query and there is a string grid (DevExpress Quantum Grid) related to this dataset :):):):)

if the query can't find any record than it gives this error. if it finds at least one error, then there is no problem

what is that weird character you have all over the place?

+'`customer`'

this: `

have you tried taking those out?

if you run the application in the debugger where does it break when the exception is raised ?

ooo man, don't even think like that :)

have a look at this!

https://www.experts-exchange.com/questions/21287787/Refere-to-an-old-question.html

:)

I wonder if the Zeos components handle that character well then...

from this:
dmQueries.queryRHViewOrders.SQL.Text := conditionalSQL.Text;

you can get the actual query text (Ctrl+F7 on "conditionalSQL.Text" when debugging), can you copy and paste that value into MySQL Control Center or whatever you use to query your MySQL database and does it work?

"you can get the actual query text (Ctrl+F7 on "conditionalSQL.Text" when debugging), "

Can you explain that part please? I don't know how to use Ctrl+F7

when you are debugging, stepping through your code with F7, F8

and you get to that point you can do Ctrl+F7 in a variable and it will display the "Evaluate/Modify" window, from there you can read the contents of any variables in that scope, in this case "conditionalSQL.Text" has the text that was generated from:

conditionalSQL.text := 'SELECT *'
        +'FROM'
        +'`customer`'
....
....
dmQueries.queryRHViewOrders.Close;
      dmQueries.queryRHViewOrders.SQL.Clear;
      dmQueries.queryRHViewOrders.SQL.Text := conditionalSQL.Text; //put a breakpoint here, when it gets here, press Ctrl+F7 on the ConditionalSQL variable

that dialog allows you to copy the contents of that variable and you can paste it anywhere

there is nothing wrong with the SQL statement, b'cause I tried it with Navicat and PHPMuAdmin, and works perfect.

usual numberfields cannot be searched with like,
but as you said "there is nothing wrong with the SQL statement" . . .

yes I know but it works fine if it finds any record??

additional
as you have no aggregat field in your select, you can delete the group by clause

btw. happens this error also outside of the IDE
(it may be internal handled, but captured by the IDE)

1. Delphi Throws an exception in ZAbstractRODataset.Pas File

function TZAbstractRODataset.CreateResultSet(SQL: string;
  MaxRows: Integer): IZResultSet;
begin
  Connection.ShowSQLHourGlass;
  try
    if not Assigned(Statement) then
      Statement := CreateStatement(FSQL.Statements[0].SQL, Properties);
    SetStatementParams(Statement, FSQL.Statements[0].ParamNamesArray,
      FParams, FDataLink);
    if RequestLive then  ***************************** >>>> HERE
      Statement.SetResultSetConcurrency(rcUpdatable)
    else Statement.SetResultSetConcurrency(rcReadOnly);
    Statement.SetFetchDirection(fdForward);
    if IsUniDirectional then
      Statement.SetResultSetType(rtForwardOnly)
    else Statement.SetResultSetType(rtScrollInsensitive);
    if MaxRows > 0 then
      Statement.SetMaxRows(MaxRows);
    Result := Statement.ExecuteQueryPrepared;
  finally
    Connection.HideSQLHourGlass;
  end;
end;

2. "you get this error only on an empty resultset?
there may a translation-problem within zeos or delphi itself (usual a bug).

use a code like

try
  myQry.Open;
except
  //nothing or handle here the invalid argument error
end;
if myQry.IsEmpty then ....

meikl ;-)"

yes it only happent on an empty resultset and I did add the code you mentioned and nothing changed.

3. It happens both inside and outside of the IDE

>yes it only happent on an empty resultset and I did add
>the code you mentioned and nothing changed.

did you tried the change outside of the IDE?

>did you tried the change outside of the IDE?

try to change what?? I compiled the project, even copied to an other computer still "Invalid Argument" error.

well, this change had no effect -> problem must not be the empty resultset!

do you have any other datasources which are linked to the empty resultset like
LookupSources, Detail-Datasorces, etc.)?

meikl ;-)

No, that's the only Datasource.

If it is not Empty resultset  then how come it doesn't give any error. I will loose my hairssssssssss :(

In this instance, I would be checking ALL event handlers.

You may want to make a backup of the app and start removing forms/controls to see what can doesn't give you the error.

Do you have any event handlers dealing with parsing the result? Do you have any column formatting code, anything which HAS to access the data and may not be coded to deal with NO data?

I will start to check this right now. it will take so loooonnnggg :)

>I will loose my hairssssssssss

keep your hairs, was a guess.
furthermore guessing:
do you have assigned an afteropen, afterscroll, ondatachange-event to the dataset/datasource

btw. is the requestlive property true?

meikl ;-)

good point, RQuadling, didn't see your comment before ;-)

:):)

Yes, I'm now checking all event handlers associated with this query and datasource even with DevExporess Quantum grids event handlers.

yes, RequestLive is true.

Sorry. Been away for the weekend. Saw a LOT of movement here and didn't read all the comments. Sorry.

Thats what i asked you before bilgehanyildirim.

i asked "Are you sure there is no other code in the project thats expects an record after you called the open procedure "

didnt you check it before ?

I know arnismit, but I didn't have enough time during weekend...

Do you need the RequestLive!!! ????
turn it off, it will probably work then .....

greetingz
Arni

>turn it off, it will probably work then .....
that would be my next step, too slow :-))

same error...

THANK YOU VERY MUCH GUYS!!!

the problem was exactly you said. on AFTEROPEN eventhandler of that query there is another function which takes a parameter coming from result set. if the resultset is empty, this function crashes!!!!!!

You saved my hair :)

glad you got it sorted :-))

>You saved my hair :)
the last three one :-)) ?

meikl ;-)