Changing a Query at Runtime
Changing a Query at Runtime
(OP)
I'm back! My database holds several different things (don't blame me for the database design, someone else did that!). There are three distinct types of things. They all haver some of the fields, but some of the items, the fields are blank.
I'm trying to use a single CRDBGrid to display each different item.
I have a ListBox1 with the 3 types Outsourced, Local, Other.
procedure doDisplay;
var
TheType : integer;
begin
TheType := ListBox1.ItemIndex;
Case TheType of
0 : begin
MyQuery2.SQL.Clear;
MyQuery2.SQL.Text := ''SELECT it_type, itno, itname, itsup1, itsup2, itcst, itrate, itin FROM ItemList WHERE it_type = "Outsourced" ';
// Here I setup the Columns for the CRDBGrid
CRDBGrid1.Clear;
CRDBGrid1.Columns.Add.FieldNeme := 'it_type';
//etc for each column
MyQuery2.Open;
MyQuery2.Execute;
CRDBGrid1.Refresh;
end;
1 : begin
MyQuery2.SQL.Clear;
MyQuery2.SQL.Text := ''SELECT it_type, itno, itname, itcst, part1, part2, ithrs, itrate, itin FROM ItemList WHERE it_type = "Local" ';
// Here I setup the Columns for the CRDBGrid
CRDBGrid1.Clear;
CRDBGrid1.Columns.Add.FieldNeme := 'it_type';
//etc for each column
MyQuery2.Open;
MyQuery2.Execute;
CRDBGrid1.Refresh;
end;
end;
Type Outsourced is correct. If I select Local and Refresh Button, (which calls DoDisplay, the Columns change perfectly. but the information shown never changes. I've tested all the SQL SELECT statements. they work 100%. I've stepped through the code, everything seems OK, but I'm obviously missing something - Open and Execute don't seem to work. What have I missed?
I'm trying to use a single CRDBGrid to display each different item.
I have a ListBox1 with the 3 types Outsourced, Local, Other.
procedure doDisplay;
var
TheType : integer;
begin
TheType := ListBox1.ItemIndex;
Case TheType of
0 : begin
MyQuery2.SQL.Clear;
MyQuery2.SQL.Text := ''SELECT it_type, itno, itname, itsup1, itsup2, itcst, itrate, itin FROM ItemList WHERE it_type = "Outsourced" ';
// Here I setup the Columns for the CRDBGrid
CRDBGrid1.Clear;
CRDBGrid1.Columns.Add.FieldNeme := 'it_type';
//etc for each column
MyQuery2.Open;
MyQuery2.Execute;
CRDBGrid1.Refresh;
end;
1 : begin
MyQuery2.SQL.Clear;
MyQuery2.SQL.Text := ''SELECT it_type, itno, itname, itcst, part1, part2, ithrs, itrate, itin FROM ItemList WHERE it_type = "Local" ';
// Here I setup the Columns for the CRDBGrid
CRDBGrid1.Clear;
CRDBGrid1.Columns.Add.FieldNeme := 'it_type';
//etc for each column
MyQuery2.Open;
MyQuery2.Execute;
CRDBGrid1.Refresh;
end;
end;
Type Outsourced is correct. If I select Local and Refresh Button, (which calls DoDisplay, the Columns change perfectly. but the information shown never changes. I've tested all the SQL SELECT statements. they work 100%. I've stepped through the code, everything seems OK, but I'm obviously missing something - Open and Execute don't seem to work. What have I missed?
Talk To Other Members
RE: Changing a Query at Runtime
So try commenting out the .Execute line.
And just to confirm that the CRDBGrid1 data source is connected to the query?
I wouldn't be surprised if there was a way to add all of the columns with one command.
RE: Changing a Query at Runtime
The query is definitely connected to the table and the CRDBGrid is definitely connected to the query. The columns change 100% but not the query.
I have stepped through the code. The query text is correct, It passes it to the Query, but for some reason it isn't getting executed.
There may be an easier way to setup the columns, but I need to get this very simple bit of code working. After that I can spend some time trying to improve it.
Neither Execute nor Post works. All the queries are correct. All are being sent to MyQuery2. There must be something I'm missing.
RE: Changing a Query at Runtime
RE: Changing a Query at Runtime
MyQuery2.Open;
MyQuery2.Execute;
is unnecessary. Just replace with MyQuery2.ExecSQL Works like a dream.
A few more hours and I should be finished with this job. Waiting for the next one! My Delphi mindset is starting to come back. I didn't do a lot of work with databases (other than the NDS one) and that wasn't any known interface.
RE: Changing a Query at Runtime
I would have a look at this post if you're going to be doing database work:
http://www.tek-tips.com/viewthread.cfm?qid=1647984
I found that it made working with databases and queries much much easier and my code easier to write.
RE: Changing a Query at Runtime
There is one thing that I'm really struggling with. - Because of Typos, I'm trying to clean up this big table. Badly designed, monolithic, but that was someone else.
I can write Update queries easily with HeidiSQL. I did sort of try parameters, but couldný get it to work, but the worst part is, that because of all the typos I need to use LIKE
UPDATE stItems SET iName = 'xxx' WHERE bName LIKE "%Frxxxxx";
UPDATE stItems SET wloc = 'abcd' WHERE bName LIKE "%Grexxxxx%";
UPDATE stItems SET iName = 'xyxz' WHERE bName LIKE "- 40087%";
I thought, simple, just create a form with a couple of EditBoxes, Then fill in the correct values and use something like
UPDATE stItems SET iName = 'edtName.text' WHERE bName LIKE "'%'+edtCval.text+'%'";
There seems to be no way to use a LIKE statement in Delphi. I've searched the web for hours, but no mention of how to pass a LIKE statement to a database using Delphi.
RE: Changing a Query at Runtime
Looking at one of your update statements it looks like you want something like:
sql := 'UPDATE stItems SET iName=''' + edtName.text + ''' WHERE bName LIKE ''%' + edtCval.text + '%'' ';
RE: Changing a Query at Runtime
The most important thing is Description. This seems to have been cut and pasted from a catalog (not written by an English speaker it would seem) lots of spelling and typos. It is a LongText field.
In every case, it is just a single line in a TMemo box. I get an error "Unknown column memDescrip.lines in field list.
There's also a Float field with a max of 255.00 but it isn't money, it would be nice to know how to handle both of these as well.
Thanks for all your much valued help. It's starting to get easier with coding now, but still struggling a bit with SQL and how it works with Delphi.
Now I tried changing this to :
UPDATE stItems SET iDesc = ''' + memDesc.Lines.Text + ''' WHERE iName LIKE ''%' + edtCval.text + '%'' ';
Now I get text is too long for column probably because it's longtext and not text as a string of 255 max. How does one get around that?
RE: Changing a Query at Runtime
Based on the error message your trying to search for a field called memDescrip.lines instead of the value of the variable/control.
You didn't include the whole line so I can't tell if you're missing an apostrophe and if you should be using quotes or apostrophes.
sql := 'UPDATE stItems SET iDesc = "' + memDesc.Lines.Text + '" WHERE iName LIKE "%' + edtCval.text + '%" ';
For floats you'll want to use something like BETWEEN eg: Where TheFloatField BETWEEN LowValue AND HighValue
If you know the maximum length of your iDesc field then you can use SubString to limit then length of text that you're sending to that field.
RE: Changing a Query at Runtime
Here is the full Line :
MyQuery1.SQL.Text := ' UPDATE stItems SET iDesc = ''' + memDesc.Lines.Text + ''' WHERE iName like ''%' + edtCval.Text + '%'' ' ;
It seems that it doesn't like LongText. it's certainly a lot longer than 255 chars. I think MidText would have done the job, but hey, I didn't design this database! ALL the text, no matter how long, is a single line.
The text in this field can vary from a single line to over a page. It's great to have all the other bits working, but that's an awful lot of text to even contemplate re-writing.
Using HeidiSQL has a problem with apostrophes, so I would have to either encapsulate each one with more apostrophes or delete the apostrophes, which isn't improving the already poor English.
RE: Changing a Query at Runtime
CODE --> Delphi
RE: Changing a Query at Runtime
Remember the definition SQL is LongText.
Let me test your suggestion but I have doubts that it will work because string us a max of 255 as I remember.
RE: Changing a Query at Runtime
RE: Changing a Query at Runtime
RE: Changing a Query at Runtime
I would be really tempted to have you make use of the query interface. It makes working with parameters easy and you don't have to worry about memory issues. It would look like this:
USAGE:
procedure DoSomeQuery;
var
Qry : IIADOQuery;
begin
Qry := TIADOQuery.Create(MyDBConnection); // MyDBConnection is your TADOConnection
// delete/insert/update statements
Qry.UpdateQuery('DELETE FROM DaTable');
// using parameters
Qry.ParseQuery('SELECT name FROM DaTable WHERE id=:id';
Qry.Parameter('id', 10);
Qry.SelectParsedQuery;
ShowMessage(Qry.AsString('name'));
end;
So your code would be something like:
Qry := TIADOQuery.Create(MyDBConnection);
Qry.ParseQuery('UPDATE stItems SET iDesc=:newDesc WHERE iName LIKE "%:itemName%");
Qry.Parameter('newDesc', memDesc.Lines.Text);
Qry.Parameter('itemName', edtCval.Text);
Qry.UpdateParsedQuery;
Using parameters is a little different than working directly with SQL statements but it is a better way of working with the SQL statements.
RE: Changing a Query at Runtime
var
SearchValue: string;
begin
SearchValue := Copy(memDesc.Lines.Text,1,30); //Or whatever the maximum length is.
//QStr is a function that I made up that will return a string with the characters that MariaDB uses (which seems to be ' ), but not for like
MyQuery1.SQL.Text := 'UPDATE stItems SET iDesc=' + QStr(SearchValue) + ' WHERE iName LIKE "%' + edtCVal.Text + '%" ';
Works fine, but there's always a catch. The string can contain multiple apostrophes (such as there's), so all that I need to do is replace every occurrence of ' with ''.
I tried StringReplace and others, they all seem to work with any character EXCEPT quotation marks. I seem to recall that back in the day, there was a JEDI function to do this. I installed the newest JEDI, but nothing seems to do the job.
Seems that fast forward instead of making things easier, they just become more complicated.