My current SQL - Githubissues

begin sItem := edtItem.Text; sStorage := edtStorage.Text; sInSeason := edtInSeason.Text; sInStock := edtInStock.Text; sPrice := edtPricePerKg.Text; iVAT := strtoint(edtVAT.Text); sVAT := iVAT tblStock['Price-per-kg ']; ADOQuery1.SQL.Clear; ADOQuery1.SQL.Add('SELECT (Stock.Price-per-kg ' + iVAT + ') FROM Stock, tobeorder where Stock.item = tobeorder.item ORDER BY Item-ID'); ADOQuery1.Open; ADOQuery1.Last; iItemID := strtoint(ADOQuery1['Item-ID']) + 1; // Insert an item from stock table ADOQuery1.SQL.Clear; ADOQuery1.SQL.Add('INSERT INTO Stock'); { ADOQuery1.SQL.Add( '(Item,Storage,Kg-in-stock,Price-per-kg,In-season)'); } ADOQuery1.SQL.Add('Values ("' + sItem + '","' + sStorage + '","' + sInStock + '","' + sPrice + '", "' + sInSeason + '", "' + sVAT + '" + "' + IntToStr(iItemID) + '")'); ADOQuery1.ExecSQL; ADOQuery1.SQL.Clear; ADOQuery1.SQL.Add('Select * From Stock order by Item'); ADOQuery1.Open; dbStock.Repaint; dbStock.Update; end;

Yo D, Your SQL is not too bad; even so consider the following:

SQL statements embedded as string inside a programming language is just what it implies: "plain text" - however, as you know from what I can see, you can concatenate your programming logic together with a string in order to produce dynamic SQL statements. Taking this into consideration you need to be careful how you construct your query - and remember the resulting SQL query is plain text that is sent (dispatched) to the database management system (which runs as a service either locally on the same server or remotely on a different server) - from which you'll get a response. If the query is malformed you will get an error in SQL statement response, or the query will just fail and halt your program (app) - depending on how your programming language database-driver handles errors.

I'm not sure why your Stock table has a column named Price-per-kg as in my experience - naming columns with a - (dash) in the name causes issues; try renaming the columns either with _ (underscore) or just use camelCase. (camel-case isWrittenLikeThis)

Keeping the above in mind, I see you have valid (and invalid) SQL statements in your code:

valid: Select * From Stock order by Item
invalid: SELECT (Stock.Price-per-kg * ' + iVAT + ') FROM Stock, tobeorder where Stock.item = tobeorder.item ORDER BY Item-ID

In the above (invalid?) query, i'm guessing you're trying to add the iVAT variable's value to the result of the query? AFAIK (As Far As I Know) this approach does not work exactly like that; you'll have better success when you add the iVAT value to the result of your query.

Try the following instead, or tweak it to what you need: "SELECT Stock.Price_per_kg FROM Stock AS toBeOrdered WHERE Stock.item = '"+ someVariable +"' ORDER BY Stock.Item_ID ASC LIMIT 1"

From the above query note that:

the Price-per-kg column is renamed for compatibility reasons, you should actually rename this column in the database before attempting to run this "as-is" (or use the dashes as in your query if it works)
the Item-ID was also renamed for the same reasons as mentioned
the AS clause will produce a record-set (list of key-value objects) in the result where the value will be contained within that toBeOrdered key-name, but you can leave it out and it will be automatically named as the column-name instead.
the WHERE clause, this is where the "lookup" happens, as in "what you want to retrieve from the Stock table's item column" .. be aware that if this item is not a number (i.e: a string) then you need to wrap that in a pair of single-quotes; if your whole query string is in single-quotes then you'll need to escape them like this: 'WHERE Stock.item = \''+ someVariable +'\' ORDER BY ... blah blah'
the ORDER BY clause needs to indicate "how to order" Ascending or Descending, if not indicated "how" then Ascending is assumed automatically, but showing this in your SQL code makes it more readable for yourself (and others working on your code).
the LIMIT 1 clause is simply for speed optimization, it will stop searching when it found at least 1 record.

When you have a result from running the query - you can then multiply (asterisk? maybe + to add?) your iVAT value with the first item's toBeOrdered value. .. obviously this should be done in a loop when you have many records, but I hope this helps.

Hi Charl, Sorry for only coming back to you today, I had hockey practice yesterday and I was immensely tired. Thanks for the tips, I'll quickly make the changes and then update the GitHub page.

On Mon, May 7, 2018 at 10:12 AM, Charl Steynberg notifications@github.com wrote:

Yo D, Your SQL is not too bad; even so consider the following:

SQL statements embedded as string inside a programming language is just what it implies: "plain text" - however, as you know from what I can see, you can concatenate your programming logic together with a string in order to produce dynamic SQL statements. Taking this into consideration you need to be careful how you construct your query - and remember the resulting SQL query is plain text that is sent (dispatched) to the database management system (which runs as a service either locally on the same server or remotely on a different server) - from which you'll get a response. If the query is malformed you will get an error in SQL statement response, or the query will just fail and halt your program (app) - depending on how your programming language database-driver handles errors.

I'm not sure why your Stock table has a column named Price-per-kg as in my experience - naming columns with a - (dash) in the name causes issues; try renaming the columns either with _ (underscore) or just use camelCase. (camel-case isWrittenLikeThis)

Keeping the above in mind, I see you have valid (and invalid) SQL statements in your code:

valid: Select * From Stock order by Item

invalid: SELECT (Stock.Price-per-kg * ' + iVAT + ') FROM Stock, tobeorder where Stock.item = tobeorder.item ORDER BY Item-ID

In the above (invalid?) query, i'm guessing you're trying to add the iVAT variable's value to the result of the query? AFAIK (As Far As I Know) this approach does not work exactly like that; you'll have better success when you add the iVAT value to the result of your query.

Try the following instead, or tweak it to what you need: "SELECT Stock.Price_per_kg FROM Stock AS toBeOrdered WHERE Stock.item = '"+ someVariable +"' ORDER BY Stock.Item_ID ASC LIMIT 1"

From the above query note that:

the Price-per-kg column is renamed for compatibility reasons, you should actually rename this column in the database before attempting to run this "as-is" (or use the dashes as in your query if it works)

the Item-ID was also renamed for the same reasons as mentioned

the AS clause will produce a record-set (list of key-value objects) in the result where the value will be contained within that toBeOrdered key-name, but you can leave it out and it will be automatically named as the column-name instead.

the WHERE clause, this is where the "lookup" happens, as in "what you want to retrieve from the Stock table's item column" .. be aware that if this item is not a number (i.e: a string) then you need to wrap that in a pair of single-quotes; if your whole query string is in single-quotes then you'll need to escape them like this: 'WHERE Stock.item = \''+ someVariable +'\' ORDER BY ... blah blah'

the ORDER BY clause needs to indicate "how to order" Ascending or Descending, if not indicated "how" then Ascending is assumed automatically, but showing this in your SQL code makes it more readable for yourself (and others working on your code).

the LIMIT 1 clause is simply for speed optimization, it will stop searching when it found at least 1 record.

When you have a result from running the query - you can then multiply (asterisk? maybe + to add?) your iVAT value with the first item's toBeOrdered value. .. obviously this should be done in a loop when you have many records, but I hope this helps.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/DynamicDragon3313/School-Project/issues/1#issuecomment-386992210, or mute the thread https://github.com/notifications/unsubscribe-auth/AlO-46zHNJp7Utx8vgolNZWORRHRD9-pks5twAHvgaJpZM4T0ZeP .

begin
  sItem := edtItem.Text;
  sStorage := edtStorage.Text;
  sInSeason := edtInSeason.Text;
  sInStock := edtInStock.Text;
  sPrice := edtPricePerKg.Text;
  rVAT := StrToFloat(edtVAT.Text);
  sVAT := rVAT * tblStock['Price_Per_Kg '];
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(
    'SELECT Stock.Price_per_kg FROM Stock AS toBeOrdered WHERE Stock.item = '
      + sItem + ' ORDER BY Stock.Item_ID ASC LIMIT 1');
  ADOQuery1.Open;
  ADOQuery1.Last;
  iItemID := strtoint(ADOQuery1['Item_ID']) + 1;
  // Insert an item from stock table
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('INSERT INTO Stock');
  { ADOQuery1.SQL.Add(
    '(Item,Storage,Kg-in-stock,Price-per-kg,In-season)'); }
  ADOQuery1.SQL.Add('Values ("' + sItem + '","' + sStorage + '","' +
      sInStock + '","' + sPrice + '", "' + sInSeason + '", "' + sVAT +
      '" + "' + IntToStr(iItemID) + '")');
  ADOQuery1.ExecSQL;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('Select * From Stock order by Item');
  ADOQuery1.Open;
  dbStock.Repaint;
  dbStock.Update;
  // This code adds items to the database via user input
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('INSERT INTO ToBeOrdered');
  { ADOQuery1.SQL.Add(
    '(Item,Storage,Kg-in-stock,Price-per-kg,In-season)'); }
  ADOQuery1.SQL.Add('Values ("' + sItem + '")');
  ADOQuery1.ExecSQL;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('Select * From ToBeOrdered order by Item');
  ADOQuery1.Open;
  dbStock.Repaint;
  dbStock.Update;
end;

This is the code that I have updated, but it says that it cannot find Price_Per_Kg in the Stock, so my question is: Would it be fixed if I change it from sVAT := rVAT tblStock['Price_Per_Kg ']; to sVAT := rVAT edtPrice.text?

Hey D, Well done learning Markdown! (pure awesomeness)

As mentioned in the explanation above, you will need to rename the column in the database if you want to use the underscored column-names (instead of the dashed column-names) .. if you know it actually works with the dashed-names then you don't need to use the underscored names.

Truthfully I have no idea what your programming logic above is supposed to do, I will need to read all the code involved; but on top of that - I don't really know Delphi that well (at all) - so I can only help with the SQL parts in this case; however; post it as a question on StackOverflow -where there are hundreds of Delphi developers that can have a look at it for you and they will post an answer to your question; you can use Markdown in your questions/answers there too, and grow reputation (points).

Using StackOverflow is free (as in free beer) - just remember to up-vote stuff you find useful (you'll need to sign-up in order to post questions or vote. Many peeps just use it and never show gratitude for the time it took the guys to answer questions (dipsh!ts) .. -oh by the way, if you do the StackOverflow thing, just let me know when you post a question (with the link to your posted question), and i'll up-vote it (so you can get reputation points) - which is good for your career - StackOverflow also gets you a job, either working from home or in any country .. but you need at least like 500 points before anyone will be interested (job wise).

Hi Charl, Thanks for all the help, I really appreciate it. I'll upload my code to stackoverflow tomorrow morning when I am at school.

On Tue, 08 May 2018, 10:06 Charl Steynberg notifications@github.com wrote:

Hey D, Well done learning Markdown! (pure awesomeness)

As mentioned in the explanation above, you will need to rename the column in the database if you want to use the underscored column-names (instead of the dashed column-names) .. if you know it actually works with the dashed-names then you don't need to use the underscored names.

Truthfully I have no idea what your programming logic above is supposed to do, I will need to read all the code involved; but on top of that - I don't really know Delphi that well (at all) - so I can only help with the SQL parts in this case; however; post it as a question on StackOverflow https://stackoverflow.com/ -where there are hundreds of Delphi developers that can have a look at it for you and post an answer to your question; you can use Markdown in your questions/answers there too, and grow reputation (points).

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/DynamicDragon3313/School-Project/issues/1#issuecomment-387320304, or mute the thread https://github.com/notifications/unsubscribe-auth/AlO-4wbwi3I3i-GFe0l5grG4uDxODp5-ks5twVH4gaJpZM4T0ZeP .

DynamicDragon3313 / School-Project

My current SQL #1