?
Solved

Dates and Delphi 7 and Access 97 and TADO*

Posted on 2003-03-18
26
Medium Priority
?
1,226 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:WormsWorms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
  • 3
  • +3
26 Comments
 

Author Comment

by:WormsWorms
ID: 8159750
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
 
LVL 9

Accepted Solution

by:
mocarts earned 172 total points
ID: 8159783
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
 
LVL 9

Expert Comment

by:mocarts
ID: 8159802
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:WormsWorms
ID: 8159858
Sorry, I didn't copy my example very well. I have the statement as

INSERT into mytable (emp_id....) VALUES (....)
0
 

Author Comment

by:WormsWorms
ID: 8159868
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
 
LVL 9

Expert Comment

by:mocarts
ID: 8159874
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
 

Author Comment

by:WormsWorms
ID: 8159892
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
 

Author Comment

by:WormsWorms
ID: 8159894
The field is a DateTime field in Access 97 if that matters
0
 

Author Comment

by:WormsWorms
ID: 8159920
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
 

Author Comment

by:WormsWorms
ID: 8159932
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
 
LVL 9

Expert Comment

by:mocarts
ID: 8159948
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
 
LVL 9

Expert Comment

by:mocarts
ID: 8159955
then try to set parameter datatype to ftDateTime
mo.
0
 

Author Comment

by:WormsWorms
ID: 8159970
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
 

Author Comment

by:WormsWorms
ID: 8159981
ftDateTime results in the same error as above.

grrrrrrrrrrrrrrrrrrr
0
 
LVL 17

Expert Comment

by:geobul
ID: 8160097
0
 

Author Comment

by:WormsWorms
ID: 8160121
What is the issue geobul?

Do you know the answer?
I can give you points if you want. I really don't care.
0
 

Author Comment

by:WormsWorms
ID: 8160137
There, you got your points.

Now, can you help me with this?
0
 
LVL 17

Expert Comment

by:geobul
ID: 8160285
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
 

Author Comment

by:WormsWorms
ID: 8160352
Yah. Whatever.

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


WormsWorms
0
 
LVL 27

Assisted Solution

by:kretzschmar
kretzschmar earned 164 total points
ID: 8160998
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8161013
appendix,

the parametertype of the dateparameter is ftString

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8161026
btw. i used access2000,
hope this doesn't matter ;-)
0
 
LVL 1

Assisted Solution

by:bogiboy
bogiboy earned 164 total points
ID: 8161084
Try something like this
qry.SQL.Add('INSERT INTO Table1 (ID,[Name],BDate) VALUES(25,'+QuotedStr('name1')+',#'+DateToStr(Now)+'#)')

Works for me.
0
 

Expert Comment

by:CleanupPing
ID: 9316696
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month11 days, left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question