While loop

[Paulnewyork]

the correct version:

database | fields
========================================================
sell     |  price, price, id, name, volume, sold, date, start
table    | id, price, volume
buy      | check, date, buy_id,  price, start, volume


Why is double better? i changed extended now to double

[TRon]

the correct version:

Thank you.

Note that in your snippet a query to table is using "table 2" (note the space)

Why is double better? i changed extended now to double

It is not that single, double, float or currency is better, extended is legacy and requires a co-processor (it is deprecated to rely in that)

[Paulnewyork]

the space is not in my code, just here in this code, which I posted.
I changed the name of the  table....

[TRon]

the space is not in my code, just here in this code, which I posted.
I changed the name of the  table....

hmz, weird.

Well, it will take me some time to setup the new tables and i have to do some outside tours, so please don't hold your breath 

[Paulnewyork]

I changed in MYSQL the data typ for price from varchar to double....
Now i get the correct MIN(price)  in MySQL...my tests go on

now i get the message:    " is an invalid float

It is crazy

[af0815]

not crazy, its per design. SCNR

you are crossposting with the german forum as Daniel_Berlin and here paulnewyork.

[Paulnewyork]

Yes i post also in german

[TRon]

good to know af0815, thx

@paulnewyork
That you refrain from providing a compilable example is one thing, but you keep on updating your post that contained code with your original problem. That means that your actual problem keeps changing/shifting

Not only that, you do not notify us that you did change your (original) snippet.

It is nearly impossible to provide help that way. Your code shows that you are making some basic fundamental and logical errors, and there is no way to address those this way and/or offer any guidance in those matters.

instead of guessing what it is you want, i ask: what is it that you want ? A ready to go for your use case working example is not my virtue. You can hire people to do that for you.

I'm in no means upset or otherwise, but you clearly have no idea how to ask a question properly and/or provide the information required to be able to help. I did see that from your original post, and that is why i responded as nobody else in his/her right mind would. I like a challenge (once in a while) :-)

And to show what i meant, you corrected me:

the correct version:

database | fields
========================================================
sell     |  price, price, id, name, volume, sold, date, start
table    | id, price, volume
buy      | check, date, buy_id,  price, start, volume

Which is perfectly ok, you'd better :-)

however, in your original posted example you used both price and preis as field inside your sell table.Now i must update my table(s) again to match that. Or in short, you are wasting (my) time.

Don't let that hold you back, but please be informative and whatever you do, never change posted snippets unless specifically mentioning with that changed snippet what/why you did it. It is must more preferable to post a new snippet in a new post. If someone reads this thread afterwards, the whole thread doesn't make any sense any more (wasting more of peoples time). Besides that your actions could shed a wrong light on the person attempting to help you.

With regards to the latter, i personally don't care, because as i said before: i do not speak sql so this whole thing is a learning exercise for me 

[TRon]

btw i am unable to create a field with the name check because it being a sql constraint. Is there another way to use a query to create a field with that name or is it better to avoid such (reserved) names at all ?

Code: Pascal  [Select][+][-]

1. DBQuery.SQL.Add('CREATE TABLE test ( check BOOLEAN )');
2. DBQuery.ExecSQL;
3.  

edit: nevermind. it is expected as a string-literal according to sqlite docs

Code: Pascal  [Select][+][-]

1. DBQuery.SQL.Add('CREATE TABLE test ( "check" BOOLEAN )');
2. DBQuery.ExecSQL;
3.  

[TRon]

Well, i am sorry but am not going to look at that for now.

As said before, your (code) logic is flawed 

Besides that, i still think it is much easier to approach this the other way around and instead of using coded criteria, to put those in a SQL statement and iterate through the results.

I have implemented your algorithm, and as i suspected:

Code: [Select]

>> Creating table "sell"
>> Filling table "sell" with data
>> Creating table "buy"
>> Filling table "buy" with data
>> Creating table "table"
Name of 3 tables:
'buy'
'sell'
'table'
LowestSellPrice = 100.00
SafetyCounter = 1
RecordCount = 1
No matched criteria, increasing SellPrice
SafetyCounter = 2
RecordCount = 0
Criteria matched, updating records
An unhandled exception occurred at $0009B15C:
Note the line where it states "RecordCount = 0"

Now, this can happen because i do not work with the same data as you do. However, you increase the sellingprice by 0.1 and expect to query to find a match in the query Table. The mentioned line shows what happens if there is no such value inside the table.

So the question becomes are we going left, right, or do you decide to go upside-down ? 

edit: minor wording correction

[Paulnewyork]

so i have to make an if else statement after i check if there is a higher price in the table.
if there is no higher price in the table i have to add an IF ELSE  condition for that case to increase the sellingprice again?

Like

Code: Pascal  [Select][+][-]

1.      IF (sellPrice = " ")
2.         THEN
3.         BEGIN
4.         sellingPrice := sellingPrice + 0.1;
5.         sellPrice:=FloatToStr(floatSellPrice);
6.         END    
7.  

is the condition for empty correct???
sellPrice was the string and a space between the quotation marks?

[TRon]

Before the code reaches that location (where you wish to apply the fix), the program has already executed code assuming that the record is present. That includes reading the sellingprice from an empty record (it beats me why the program didn't throw an exception for that, the message "Criteria matched, updating records" is printed after the if statement, thus after reading the empty record).

I am not sure about sqlite and it's implementation for Free Pascal, but knowing a little about Free Pascal, if things are empty that means undetermined, behaviour is not consistent, etc. etc.

If you wish to follow this path then i have to give it some more thought on what would be the best approach/solution (that is, besides the use of goto's).

[Paulnewyork]

Is it possible to put the IF ELSE
inside the loop after

Code: Pascal  [Select][+][-]

1. Form2.SQLQuery1.SQL.Text := 'SELECT * from sell WHERE price ='''+SellPrice+''';';
2. Form2.SQLQuery1.Open;
3. SellPrice := Form2.SQLQuery1.FieldByName('price').AsString;
4. // or as alternative
5. // floatSellPrice := Form2.SQLQuery1.FieldByName('price').AsFloat;
6.  

[TRon]

Is it possible to put the IF ELSE
inside the loop after

Yes, but to fix the problem entirely it would then have to become a while loop. e.g. increase the selling-price until it matches one in the table.

Are you already starting to see why i said it would be easier to implement if doing it the other way around ? 

Since your actions seem to suggest you are a little impatient, the quickest thing i can think of atm is to just take the next record and fetch it's selling-price (*)

edit (*)
That was a bit vague, sorry i will elaborate:

What i meant by that is having a query that selects the price in an orderly fashion, then iterates through the results from lowest to highest.

[Paulnewyork]

Since your actions seem to suggest you are a little impatient, the quickest thing i can think of atm is to just take the next record and fetch it's selling-price (*)
--> that sounds pretty good.

the second while loop make sense

sorry, that i am impatient. I just want to help und suggest some possibilites