Dates and Delphi 7 and Access 97 and TADO*

OK. I am trying to insert some information into a table
I have dates in it.

so I have a table

emp_id, birthdate, name

I have a TADOConnection and a TADOQuery. I can successfully select using the query. I am now trying to insert.

I have tried
ADOQuery1.SQL.Add('INSERT into mytable emp_id, birthdate, name VALUES('
ADOQuery1.SQL.Add(QuotedStr('10')+',');
ADOQuery1.SQL.Add(QuotedStr('10/10/2001')+',');
ADOQuery1.SQL.Add(QuotedStr('My name')+')');
ExecSQL;

And I get an error like this:
---------------------------
Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class EOleException with message 'Data type mismatch in criteria expression'. Process stopped. Use Step or Run to continue.
---------------------------
OK   Help  
---------------------------


What does that mean? Any idea how I fix that?

Thanks
WormsWormsAsked:
Who is Participating?
 
mocartsConnect With a Mentor Commented:
there must be braces after table name and before Values keyword and with access it's better to use ISO standard date format (YYYYMMDD) i.e. '20011010':

ADOQuery1.SQL.Add('INSERT into mytable (emp_id, birthdate, name) VALUES('
ADOQuery1.SQL.Add(QuotedStr('10')+',');
ADOQuery1.SQL.Add(QuotedStr('20011010')+',');
ADOQuery1.SQL.Add(QuotedStr('My name')+')');

wbr, mo.
0
 
WormsWormsAuthor Commented:
I have also tried using the Parameters.Param... stuff

If you have an example doing it both ways that works, I will add in an extra 125 points.

Which is the better way to do it?

Thanks
0
 
mocartsCommented:
and what type is emp_id in database?
if some numeric then you must ommit QuotedStr:
ADOQuery1.SQL.Add('INSERT into mytable (emp_id, birthdate, name) VALUES(10,'
ADOQuery1.SQL.Add(QuotedStr('20011010')+',');
ADOQuery1.SQL.Add(QuotedStr('My name')+')');

or if this is some autoincrement then ommit this field at all.
mo.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
WormsWormsAuthor Commented:
Sorry, I didn't copy my example very well. I have the statement as

INSERT into mytable (emp_id....) VALUES (....)
0
 
WormsWormsAuthor Commented:
I also tried ommitting the QuotedStr for my integer, but it also doesn't work.

My actual query is very long. So I am running this test query to try to get the date in. I am positive it is the date.

I will try that format that you suggest.
0
 
mocartsCommented:
by using Parameters..
ADOQuery1.SQL.Text := 'INSERT into mytable (emp_id, birthdate, name) VALUES (:emp_id, :birthdate, :name)';
with ADOQuery1.Parameters do begin
  ParamByName('emp_id').Value := '10';
  ParamByName('birthdate').Value := '20011010';
  ParamByName('name').Value := 'My name';
end;
ADOQuery1.ExecSQL;

wbr, mo.
0
 
WormsWormsAuthor Commented:
Switching to the YYYYMMDD still doesn't help.

WormsWorms is frustrated.

I can insert an integer and a string. I just can't insert anything with a date.
0
 
WormsWormsAuthor Commented:
The field is a DateTime field in Access 97 if that matters
0
 
WormsWormsAuthor Commented:
with ADOQuery1 do begin
    Close;
    SQL.Clear;
    SQL.Add('INSERT INTO table1 (field,intfield,datefield) VALUES (:FIELD, :INTFIELD, :DATEFIELD)');
    Parameters.ParamByName('FIELD').Value := QuotedStr(edit1.text);
    Parameters.ParamByName('INTFIELD').Value := '10';
    Parameters.ParamByName('DATEFIELD').Value := '20021010';
    ExecSQL;
  end;


This is exactly what I have.
0
 
WormsWormsAuthor Commented:
sorry, the above works perfect without the date stuff, as soon as I put the date stuff in it dies with that same error.
0
 
mocartsCommented:
if emp_id is integer then use

with ADOQuery1.Parameters do begin
 ParamByName('emp_id').Value := 10;

optionally, if problem remains, you can explicitly set parameter data type (I thin you need it for date type only):
ParamByName('birthdate').DataType := ftDate;
ParamByName('birthdate').Value := '20011010';

benefits of using parmeters - you  you get better readable code (no such QuotedStr etc.)
mo.
0
 
mocartsCommented:
then try to set parameter datatype to ftDateTime
mo.
0
 
WormsWormsAuthor Commented:
I now get this error:
---------------------------
Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class EOleException with message 'Application uses a value of the wrong type for the current operation'. Process stopped. Use Step or Run to continue.
---------------------------
OK   Help  
---------------------------

When I use the following code:

with ADOQuery1 do begin
    Close;
    SQL.Clear;
    SQL.Add('INSERT INTO table1 (field,intfield,datefield) VALUES (:FIELD, :INTFIELD, :DATEFIELD)');
    Parameters.ParamByName('FIELD').Value := QuotedStr(edit1.text);
    Parameters.ParamByName('INTFIELD').Value := '10';
    Parameters.ParamByName('DATEFIELD').DataType := ftDate;
    Parameters.ParamByName('DATEFIELD').Value := '20021010';
    ExecSQL;
  end;


I am not understanding why this doesn't work. I have tried all kinds of variations on this to no avail.
I appreciate your helping mocarts. Any more ideas?
0
 
WormsWormsAuthor Commented:
ftDateTime results in the same error as above.

grrrrrrrrrrrrrrrrrrr
0
 
geobulCommented:
0
 
WormsWormsAuthor Commented:
What is the issue geobul?

Do you know the answer?
I can give you points if you want. I really don't care.
0
 
WormsWormsAuthor Commented:
There, you got your points.

Now, can you help me with this?
0
 
geobulCommented:
I don't care about points. I haven't said that I should get any points. It's a matter of ... (you name it, I wont). You asked a question and received so many good examples. I don't think the experts here would like to be used that way. I take it as incorrect behaviour.
0
 
WormsWormsAuthor Commented:
Yah. Whatever.

mocarts, I will give you some points cuz you helped lots, even if we didnt' get it to work.


WormsWorms
0
 
kretzschmarConnect With a Mentor Commented:
hi,

i ususal used this

  qryTagesSum.Close;
  qryTagesSum.Parameters[0].Value := formatDateTime('yyyy/mm/dd',tblZeiten.FieldByName('ZEITEN_DATUM').AsDateTime);
  qryTagesSum.Parameters[1].Value := tblZeiten.FieldByName('ZEITEN_PERSON_KZ').AsString;
  qryTagesSum.Open;

whereas qryTagesSum holds as SQL

    SQL.Strings = (
      'select zeiten_person_kz, '
      '          zeiten_datum,'
      '          sum(zdetail_landing-zdetail_airb) as flugzeit,'
      '          sum(zdetail_onbl-zdetail_offbl) as Blockzeit'
      '  from zeiten, '
      '          zdetail, '
      '          zeit_typ'
      'where zeiten_person_kz = zdetail_person_kz '
      '   and zdetail_zeiten_typ = zeit_typ_id'
      '   and zeit_typ_kurz = false'
      '   and zdetail_datum = :ZEITEN_DATUM'
      '   and zdetail_person_kz = :ZEITEN_PERSON_KZ'
      'group by zeiten_person_kz, '
      '          zeiten_datum')


just from a project of mine,
maybe it helps a bit

meikl ;-)

0
 
kretzschmarCommented:
appendix,

the parametertype of the dateparameter is ftString

meikl ;-)
0
 
kretzschmarCommented:
btw. i used access2000,
hope this doesn't matter ;-)
0
 
bogiboyConnect With a Mentor Commented:
Try something like this
qry.SQL.Add('INSERT INTO Table1 (ID,[Name],BDate) VALUES(25,'+QuotedStr('name1')+',#'+DateToStr(Now)+'#)')

Works for me.
0
 
CleanupPingCommented:
WormsWorms:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
All Courses

From novice to tech pro — start learning today.