[SOLVED] Query1: Field Not Found - UPDATED
lazarus / 16-Databases

----------------------------------------------------------------------
guest48180 · 2014-11-13 21:08

I realize that I may be doing this wrong, but I'm just now getting my
feet wet with Lazarus and DB programming. I removed the DBNavigator
from my form because I wanted to make sure that the date was entered a
certain way, and I couldn't see how to do that with the DBNavigator
and DBGrid. So I set up various buttons for adding, editing, etc., the
records. The Add button pulls up a form with TEdits for the fields.
After the data's entered, I try to Post it and get an error telling me
that the field END_DATE can't be found. This is also the last field on
the Table. If I comment that field out and try to Post, the field that
is now the last field (START_DATE) can't be found.
My table schema looks like this:

Code: [Select]sqlite> .schema attendees
CREATE TABLE ATTENDEES(
                ATTENDEE_ID INTEGER PRIMARY KEY,
                L_NAME VARCHAR(15) NOT NULL,
                F_NAME VARCHAR(15) NOT NULL,
                AGE VARCHAR(2) NOT NULL,
                PHONE_NUMBER VARCHAR(14),
                ADDRESS VARCHAR(35) NOT NULL,
                CITY VARCHAR(20) NOT NULL,
                ST VARCHAR(2) NOT NULL,
                ZIPCODE VARCHAR(5) NOT NULL,
                START_DATE VARCHAR(8) NOT NULL,
                END_DATE VARCHAR(8) NOT NULL
);

The code that's failing is this:

Code: [Select]procedure TaddAttendeeForm.FormShow(Sender: TObject);
begin
  Edit1.Text:= 'Woogi';
  Edit2.Text:= 'Professor B.';
  Edit3.Text:= '52';
  Edit4.Text:= '817.234.2307';
  Edit5.Text:= '313 CR 805a';
  Edit6.Text:= 'AnyTown';
  Edit7.Text:= 'TX';
  Edit8.Text:= '98765';
  Edit9.Text:= DateTimeToStr(Date);
  Edit10.Text:= DateTimeToStr(Date);
end; 

procedure TaddAttendeeForm.btnPostClick(Sender: TObject);
begin
  //mainForm.Query1.Close;
  mainForm.Query1.Edit;
  mainForm.Query1.FieldByName('L_NAME').Value:=       edit1.text;
  mainForm.Query1.FieldByName('F_NAME').Value:=       edit2.text;
  mainForm.Query1.FieldByName('AGE').Value:=          edit3.text;
  mainForm.Query1.FieldByName('PHONE_NUMBER').Value:= edit4.text;
  mainForm.Query1.FieldByName('ADDRESS').Value:=      edit5.text;
  mainForm.Query1.FieldByName('CITY').Value:=         edit6.text;
  mainForm.Query1.FieldByName('ST').Value:=           edit7.text;
  mainForm.Query1.FieldByName('ZIPCODE').Value:=      edit8.text;
  mainForm.Query1.FieldByName('START_DATE').Value:=   edit9.text;
  mainForm.Query1.FieldByName('END_DATE').Value:=     edit10.text;
  mainForm.Query1.Close;
  mainForm.Query1.SQL.Text:= 'INSERT INTO ATTENDEES VALUES
(NULL,'+mainForm.Query1.FieldValues['L_NAME']+','+
              mainForm.Query1.FieldValues['F_NAME']+','+mainForm.Query
1.FieldValues['AGE']+','+
              mainForm.Query1.FieldValues['PHONE_NUMBER']+','+mainForm
.Query1.FieldValues['ADRESS']+','+
              mainForm.Query1.FieldValues['CITY']+','+mainForm.Query1.
FieldValues['ST']+','+
              mainForm.Query1.FieldValues['ZIPCODE']+','+mainForm.Quer
y1.FieldValues['START_DATE']+','+
              mainForm.Query1.FieldValues['END_DATE']+')';
  mainForm.Query1.ExecSQL;

And when I run it, I get the error: Query1: Field Not Found:
'END_DATE'

Like I say, I know this may be unorthodox, but I can't find anything
in the Lazarus Wikis on how to manually insert field values into a
record. And I can't even test what I have all the way out because of
the error telling me the last field of my record can't be found when I
post.
Again, I'm only doing it this way because I want to use either the
SQL-defined date or the computer date (DateTimeToStr(Date)) for the
START_DATE and END_DATE fields and can't figure out how to do that 
with the DBNavigator and DBGrid.

I'm open to any and all suggestions.

Thanks,
Landslyde

----------------------------------------------------------------------
ahiggins · 2014-11-14 00:19

not sure if this is what you are after

mainForm.Query1.SQL.Clear;
mainForm.Query1.SQL.add('Select * from MYTABLE);
mainForm.Query1.Open;
mainForm.Query1.Edit;
mainForm.Query1.FieldByName('END_DATE').asString;
mainForm.Query1.Post;
mainForm.Query1.ApplyUpdate;
SQLTransaction1.Commit;
mainForm.Query1.Close;

----------------------------------------------------------------------
guest48180 · 2014-11-14 00:39

ahiggins:  I don't think that's it, but I appreciate your input.

Thanks,
Landslyde

----------------------------------------------------------------------
jesusr · 2014-11-14 06:09

Quote from: Landslyde on November 13, 2014, 10:08:47 pm
Code: [Select]procedure TaddAttendeeForm.FormShow(Sender: TObject);
begin
  Edit1.Text:= 'Woogi';
  ..... ETC.
  Edit9.Text:= DateTimeToStr(Date);
  Edit10.Text:= DateTimeToStr(Date);
end; 

At this point you have some edit controls with some values, ok.

Quote from: Landslyde on November 13, 2014, 10:08:47 pmCode:
[Select]procedure TaddAttendeeForm.btnPostClick(Sender: TObject);
begin
  //mainForm.Query1.Close;
  mainForm.Query1.Edit;
  mainForm.Query1.FieldByName('L_NAME').Value:=       edit1.text;
  ... ETC.
  mainForm.Query1.FieldByName('ZIPCODE').Value:=      edit8.text;

At this point, you have filled some fields with the edit values, ok.


Quote from: Landslyde on November 13, 2014, 10:08:47 pmCode: [Select] 
mainForm.Query1.FieldByName('START_DATE').Value:=   edit9.text;
  mainForm.Query1.FieldByName('END_DATE').Value:=     edit10.text;

At this point you have tried to fill some date fields with the dates
contained in the edit9 and edit10 controls
in order to avoid problems with locale format settings for dates you
better try to do this using this other
variant:

Code: [Select]mainForm.Query1FieldByName('START_DATE').AsDateTime :=
Date; // here Date could be replaced by Now if you want also Date &
Time
mainForm.Query1FieldByName('END_DATE').AsDateTime := Date;
We continue ...

Quote from: Landslyde on November 13, 2014, 10:08:47 pmCode: [Select] 
mainForm.Query1.Close;
Here you now closed the query, whatever was in the just edited record
has been lost!
Probably you need to replace that (or add before that) this:

Code: [Select]mainForm.Query1.Post;
which will save the edits in this record into the dataset. Depending
on what kind of dataset
is mainForm.Query1 this will save also the dataset to the database on
disk/server or not.
an applyupdates could be missing.

As the code above didn't work, you tried to do this other way.
first you need the previous instruction closing the query as to change
SQL.Text it must be closed.

Quote from: Landslyde on November 13, 2014, 10:08:47 pmCode: [Select] 
mainForm.Query1.Close;
  mainForm.Query1.SQL.Text:= 'INSERT INTO ATTENDEES VALUES
(NULL,'+mainForm.Query1.FieldValues['L_NAME']+','+
              mainForm.Query1.FieldValues['F_NAME']+','+mainForm.Query
1.FieldValues['AGE']+','+
              mainForm.Query1.FieldValues['PHONE_NUMBER']+','+mainForm
.Query1.FieldValues['ADRESS']+','+
              mainForm.Query1.FieldValues['CITY']+','+mainForm.Query1.
FieldValues['ST']+','+
              mainForm.Query1.FieldValues['ZIPCODE']+','+mainForm.Quer
y1.FieldValues['START_DATE']+','+
              mainForm.Query1.FieldValues['END_DATE']+')';
  mainForm.Query1.ExecSQL;

the problem here is that Query1 is closed!!, you cannot extract data
from it so
when query is closed no field is found in it.
instead of using Query1.FieldValues try using the controls EditXXX
values

----------------------------------------------------------------------
guest48180 · 2014-11-14 06:43

jesusr:  Thank you very much for taking the time to comb through all I
put out there. You were clear and made a lot of sense to me. I got so
caught up in the tangle I made that I never even noticed I had closed
the Query, hence making my fields unavailable   I was getting ready to
go to sleep when I saw your post...now I'll be here fixing all of this
until it's done. Again, thank you for your help, your voice of
experience.

Much appreciated,
Landslyde

----------------------------------------------------------------------
guest48180 · 2014-11-14 16:43

UPDATE:

I did as jesusr suggested and set my SQL statement in a simpler form:

Code: [Select]procedure TaddAttendeeForm.btnPostClick(Sender:
TObject);
begin
  try
    mainForm.Query1.Close;
    mainForm.Query1.SQL.Text:= 'INSERT INTO ATTENDEES
VALUES(NULL,'+Edit1.Text+','+Edit2.Text+','+Edit3.Text+','+
          Edit4.Text+','+Edit5.Text+','+Edit6.Text+','+Edit7.Text+','+
Edit8.Text+','+Edit9.Text+','+Edit10.Text+')';
    mainForm.Query1.ExecSQL;
    mainForm.Query1.ApplyUpdates;
    mainForm.Query1.Open;
    Close;
  except
    on E: EDatabaseError do
      begin
        MessageDlg('Error','A Database Error Occured. Technical
Message: '+
                    E.Message,mtError,[mbOK],0);
      end;
  end;
end;    

And again, here are the supplied TEdit values:

Code: [Select]procedure TaddAttendeeForm.FormShow(Sender: TObject);
begin
  Edit1.Text:= 'Woogi';
  Edit2.Text:= 'Professor';
  Edit3.Text:= '52';
  Edit4.Text:= '888.888.8888';
  Edit5.Text:= '313 CR 805a';
  Edit6.Text:= 'AnyTown';
  Edit7.Text:= 'TX';
  Edit8.Text:= '98765';
  Edit9.Text:= DateTimeToStr(Date);
  Edit10.Text:= DateTimeToStr(Date);
end;   

Keep in mind that I have one record in this table that I'm able to see
in my DBGid. So when I run this, I get the following error:

Code: [Select]A Database Error Occured. Technical Message: near
".8888": syntax error
So I see that it's freaking out on the text in Edit4. I also see that
my table has been closed because I no  longer see the one record in
the DBGrid, so I have to think that this "syntax error" is from my SQL
statement. So I go to Edit4 and take out all the ".". It now looks
like this '8888888888'.
So I run it again and get this error:

Code: [Select]A Database Error Occured. Technical Message: near "CR":
syntax error
So I take all the spaces out of Edit5 so it now looks like this:
'313CR805a'. And here's where it gets really crazy! When I run this,
my new error is:

Code: [Select]A Database Error Occured. Technical Message:
unrecognized token: "313CR805a"
This is only text! Every field in my table is text except the primary
key! So why won't this code work? What is so wrong with it that I get
all these bizarre errors?! I hope someone can see my programming
flaw(s) and enlighten me. This is really frustrating!

Landslyde

----------------------------------------------------------------------
minesadorada · 2014-11-14 16:52

Just a thought - have you tried enclosing the fields in quotes so that
it is unambiguously treated as text by the SQL parser?

i.e:Code: [Select]'INSERT INTO ATTENDEES
VALUES(NULL,"'+Edit1.Text+'",'+....

Worth a try, as the Parser seems to be objecting to your 'numeric'
text fields...

----------------------------------------------------------------------
BigChimp · 2014-11-14 16:52

Basic troubleshooting: show the query's SQL.Text just before running
it (e.g. put the output in a memo)

Then copy/paste this output in a database management tool for your
database, and see if/what the error message is.

Hint: you need to quote string values (e.g. Edit1.Text) when inserting
data.
See e.g. http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Que
ry_with_Format_function
though it's probably much better to use parameters (same wiki page)

----------------------------------------------------------------------
guest48180 · 2014-11-14 18:36

BigChimp:

Naming my next kiddo after ya!  LilChimp   Using parameters was the
ticket. I know I looked at that Wiki and just overlooked its essence
there. But that's what getting in a hurry gets ya everytime! Thanks,
man. And thank you all for your input. Lazarus is a fun world, and all
of you guys here, with all your knowledge, make it that way 

Grateful in Texas,
Landslyde