datetime parameter with query

Dears technechians
am trying to send datetime to sql DB within adoquery, but i does not work 
correctly, and when put the datetime form tdatetipmepicker into datetime variable, it gives me '01/01/1900' what ever was the date
am asking about the right way to send datetime to sql DB through adoquery>
thank you in advance

the code am using
//////////////////////////////////////////////////////////////////////////////////
    with QCollect do
    begin
      Close;
      SQL.Clear;
      SQL.Add('Select * From Core.Hiring');
       SQL.Add('Where (Emp = ' + IntToStr(EmpHistoryRec.Emp) + ')');
      SQL.Add('And (DDate Between ' + FormatDateTime('MM-dd-yyyy hh:mm:ss',
          FrmDTP.DateTime) + ' And ' + FormatDateTime('MM-dd-yyyy hh:mm:ss',
          ToDTP.DateTime) + ')');
      Prepared := True;
      Open;
// some code
end;
0
Elmothana
4/7/2015 12:39:37 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

6 Replies
1654 Views

Similar Articles

[PageSpeed] 38

You should not use Date/Number formats inside your query but use parameters instead.

QCollect.SQL.Add('Select * from mytable where id= :paramID and date= :dateParam' and name= :nameParam);

//For every field value you choose a parameter name like paramID, dateParam and nameParam.
//Inside the query you use the ":" sign for telling the query that a param is declared. so, :paramID and :dateParam
//After the SQL you declare what the value of the parameters are:

QCollect.ParamByName('paramID ').AsInteger :=10;
QCollect.ParamByName('dateParam').AsDateTime:=FrmDTP.DateTime;
QCollect.ParamByName('nameParam').AsString :='Elmothana Khogali';

With Strings and Integers you can't often make mistakes inside the query but with Float and Dates you
do because every database has it's own date and float format. (Country, Region settings)

Example:
http://stackoverflow.com/questions/3826210/using-parameters-with-ado-query-mysql-myconnector

Edited by: Robert Triest on Apr 7, 2015 3:04 PM
0
Robert
4/7/2015 1:04:48 PM
thank you Dear Robert  for replay
i rewrite my code as bellow, but it gives me "incorrect comman near '@P3' "
after opne command.

/////////////////////////////////////
    with QCollect do
    begin
      Close;
      SQL.Clear;
      SQL.BeginUpdate;
      SQL.Add('Select * From Core.Hiring');
      SQL.Add('Where (Emp = :EEmp');
      SQL.Add('And (DDate Between :FDate and :TDate');
      SQL.EndUpdate;
      Parameters.ParamByName('EEmp').Value := EmpHistoryRec.Emp;
      Parameters.ParamByName('FDate').Value := FrmDTP.DateTime;
      Parameters.ParamByName('TDate').Value := ToDTP.DateTime;
      Prepared := True;
      Open;

end;
0
Elmothana
4/7/2015 1:44:35 PM
thank you Dear Robert  for replay
i rewrite my code as bellow, but it gives me "incorrect comman near '@P3' "
after opne command.

/////////////////////////////////////
    with QCollect do
    begin
      Close;
      SQL.Clear;
      SQL.BeginUpdate;
      SQL.Add('Select * From Core.Hiring');
      SQL.Add('Where (Emp = :EEmp');
      SQL.Add('And (DDate Between :FDate and :TDate)');
      SQL.EndUpdate;
      Parameters.ParamByName('EEmp').Value := EmpHistoryRec.Emp;
      Parameters.ParamByName('FDate').Value := FrmDTP.DateTime;
      Parameters.ParamByName('TDate').Value := ToDTP.DateTime;
      Prepared := True;
      Open;

end;

Edited by: Elmothana Khogali on Apr 7, 2015 6:58 AM
0
Elmothana
4/7/2015 1:59:18 PM
>       SQL.Add('Where (Emp = :EEmp');
>       SQL.Add('And (DDate Between :FDate and :TDate)');

I think you are missing a ")" at the end of your queryl. You don't close the  (Emp = :EEmp
0
Robert
4/7/2015 2:02:17 PM
>       SQL.Add('Where (Emp = :EEmp');
>       SQL.Add('And (DDate Between :FDate and :TDate)');

I think you are missing a ")" at the end of your queryl. You don't close the  (Emp = :EEmp


SQL.Add('Select * From Core.Hiring Where (Emp = :EEmp) And (DDate Between :FDate and :TDate)');

Edited by: Robert Triest on Apr 7, 2015 4:03 PM
0
Robert
4/7/2015 2:04:36 PM
thank you so much dear  Robert Triest
for help
its know ok.
you have learnt me how to fish, not gave a fish!
thanks alot
0
Elmothana
4/7/2015 3:34:16 PM
Reply:

Similar Artilces:

C# Ado.net Sql query parameter query
Hi All, I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read. So can anyone help as I'm really stuck on this one. The code is as follows : public void DisplayUserInfo() { ArrayList UserIdArrayList = IdentifyUserID(); foreach(string ShowUserIDString in UserIdArrayList) { try { SqlConnection SqlConn = new SqlConnection(DBConnString)...

Convert Delphi 5 ADO App to Delphi 2007 ADO.NET
I have an appliction tha that is writen in Delphi 5 with ADOConnetion, ADOTable, and ADOQuery components. I need to upgrade to Delphi 2007 and use ADO.NET. I have the program building in with Delphi 2007. Is there anything I need to do to convert the ADO to ADO.NET? > I need to upgrade to Delphi 2007 and use ADO.NET. Why ? -- With best regards, Dmitry Arefiev AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL, Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX, ODBC, InMem - universal high-speed native data access engine Reasons for ...

Queries with parameters fail in Delphi XE3
Hello Folks! I have been attempting to move a Delphi XE2 DataSnap server & client project to Delphi XE3. I had to do some minor tweaks like adding the unit IPPeerServer.pas to the uses clause of the server container class but what has really caused me headaches are the parameterised queries. If I try to open a ClientDataSet on the client-side I run into a "Catastrophic failure". Here is the call stack on the client-side at the time: System._DynArrayAddRef(???) System.Classes.MoveData((0, 0, 0, ... System.Classes.TMemoryStream.Write((0, 0, 0, ... System.Classes.TStream.Wr...

Parameter query
Hi: I am trying to build maste/detail page.  1.  I have on my page drop down control based on sqldata sources with the concataned fields as follows:   SELECT DISTINCT LastName + ' ' + FirstName AS Expr1 FROM dbo.Table1 It displays First Name space and Last name in the box and works fine. 2. I placed on my form a grIdview In data source I am trying to use parameter filter in SQL select query and the same query as for drop down as follows: SELECT     LastName + ' ' + SiteFirstName AS Expr1, field1, field2, etc.........

Getting parameter not found error at Delphi-SQLite query [Edit]
I am getting error like 'parameter not found' with following SQL query at Delphi In table emp usergroup_id :- integer and label, description are string data type. My code is as follows {code} gid: Integer; sdescription,ldescription: String; SQLQuery31: TSQLQuery; SQLQuery31 := TSQLQuery.Create(nil); sdescription := RzEdit1.Text; ldescription := RzMemo1.Text; gid := Integer(RzComboBox1.Items.Objects[RzComboBox1.Items.IndexOf(gname)]); try SQLConnection1.Connected := true;...

Problem with Delphi XE2 using parameters in SQL query [Edit]
Hello, I have problem with using SQL with parameters. This part of code works fine on Delphi 2007, but have Debugger Exception Notification in Delphi XE2 with ADOTmp do *//works fine on Delphi2007 and Delphi XE2, date value is inserted* begin SQL.Clear; *SQL.Add('UPDATE Table SET Date = ''2012-04-03 13:13:13'' ');* ExecSQL; end; with ADOTmp do *//works fine on Delphi2007* begin SQL.Clear; *SQL.Add('UPDATE Table SET Date = :D1 ');* Parameters.ParamByName('D1').DataType := ftDateTime; Parame...

Query SQL DateTime column using ADO.NET Entities Framework
I have a SQL table called Slots with a Datetime column named Date.  I would like to query the db and return all rows where the date is equal to a date provided by the user on an asp.net form.  I have tried various ways to covert the datatime column, but always get an exception.  Any suggestions how to query a SQL datetime field where the date column is equal to a date specified by the user?    using (DBEntities1 myDB  new DBEntities1())   {      try        {  string querySt...

How to create a Delphi 7 ADO "LIVE" (auto update) query to SQL Server
How to create a Delphi 7 ADO "LIVE" query (one SQL Server table only) that once a table field is edited - the underlying query results are updated. Kind of like the "LIVE" query concept from the BDE etc. Below is the ADO query that I am using and it generally *seems* to work well where once a field is edited (via TADOQuery.Edit and .Post), the query result are also changed. I think it is true that after an edit the change is always visible in the same program using the same query object. {code}procedure ExecuteQryTable( Connection: TADOConnection; Qry: TADOQuery...

How to create a Delphi 7 ADO "LIVE" (auto update) query to SQL Server [Edit]
How to create a Delphi 7 ADO "LIVE" query (one SQL Server table only) that once a table field is edited - the underlying query results are updated. Kind of like the "LIVE" query concept from the BDE etc. Below is the ADO query that I am using and it generally *seems* to work well where once a field is edited (via TADOQuery.Edit, .Post etc), the query result are also changed. I think it is true that after an edit the change is always visible in the same program using the same query object. {code}procedure ExecuteQryTable( Connection: TADOConnection; Qry: TADOQuer...

Problem building SQL text query with parameters / "Syntax error converting datetime from character string" error message
Hi folks, I keep looking at these SQL text query and I cannot figure out what I am doing wrong.If I include wrap the dates with quotes, I get the "Syntax error converting datetime from character string" error message, but I don't include them I don't get anything which is also wrong, because there is some data to be displayed. Bottom line, what do you think I am doing wrong when building the SQL query?1 DateTime dateFrom = txtDateFrom.SelectedDate;2 DateTime from = new DateTime(dateFrom.Year, dateFrom.Month, dateFrom.Day, 0, 0, 0);3 ...

Delphi 6 to Delphi 2010 upgrade and ADO
We have an application written in Delphi 6 Professional that uses ADO to access databases servers that may be on other PCs in our network. We want to move to Delphi 2010 but can't decide if we need Professional or Enterprise. 2010 Pro has dbGO - will this give us the same functionality as the ADO components we use in Delphi 6 Pro? Thanks, Keith > 2010 Pro has dbGO - will this give us the same functionality as the > ADO components we use in Delphi 6 Pro? Yes. > {quote:title=Joachim Uersfeld wrote:}{quote} > > 2010 Pro has dbGO - will this give us the same func...

SQL Query: Finding records between datetime inside datetime
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table: ID (int)     |    stamp_start (Type: DateTime)        |      stamp_end (Type: DateTime)           |      Username (varchar)0             |    ...

ASA7 ADO & Delphi ADO ?? Anyone is using it?
Any comments? Is it working? Any Problems? Thanks Hi, PR <noemail@no.com> schrieb in im Newsbeitrag: BoTThJaq$GA.89@forums.sybase.com... > Any comments? Is it working? Any Problems? I tried to connect to ASA7 via the new OLE DB - Provider and ADOExpress from Delphi 5.01. No chance. Only the error message 'Invalid Enum Value'. About the new provider I haven't heard anything good until now... Regards Frank Frank, I've experienced the same problem with the ASA 7 OLEDB Provider. I'm not sure if this is a bug in the AsaProvider or Delp...

datetime trouble in ADO Microsoft ADO .NET Connection
Hi, All! MS SQL 200 PB 10.2.1 build 9557 In PB DB Painter: select dateevent from AuditInternal where id_event = 201865; Result: ADO Microsoft ADO .NET Connection: 214:35:45:633 OLE Microsoft OLE DB Connection: 01.03.2006 12:35:45:633 It has begun in 9537 In 9557 same. In 9004 all this it was good thanks, Alexey ...

Web resources about - datetime parameter with query - embarcadero.delphi.ado

Parameter - Wikipedia, the free encyclopedia
A parameter (from the Ancient Greek παρά , "para", meaning "beside, subsidiary" and μέτρον , "metron", meaning "measure"), in its common meaning, ...

fb_source Parameter - Facebook-Entwickler
This document lists the complete list of values for the fb_source parameter that is returned with th...

Google Webmaster Tools Parameter Handling - Flickr - Photo Sharing!
You aren't signed in Sign In Help Home The Tour Sign Up Explore Explore Home Last 7 Days Interesting Popular Tags Calendar Most Recent Uploads ...

Cat 8: TIA performance standards committee chairman previews parameters of forthcoming twisted-pair cable ...
Category 8, the next-generation twisted-pair cabling specification, is still in development, but the outlook looks quite positive that it will ...

Philippine president asks for 'parameters' of Mayon Volcano tours
Philippine president asks for 'parameters' of Mayon Volcano tours People's Daily Online ... 26, 27, 28, 29, 30, 31. Jan, Feb, Mar, Apr, May, ...

Alternatives to boolean parameters
... ) { PrintLine ( item . Name ); if ( printDetails ) { PrintLine ( item . Description ); } } } Aha! Now can see that the boolean parameter ...

Exporting Error (Error in Parameter...: Apple Support Communities
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability ...

Apple tech monitors device aging, adjusts operating parameters to ensure optimal performance
... describing a method of monitoring the aging of a device's electronics, comparing the data with a global average and modifying operating parameters ...

Is it poor programming practice to pass parameters as objects?
... a free, community-powered network of 100+ Q&A sites . Risser asked: So, we've got a guy who likes to write methods that take Objects as parameters, ...

Parameter Short Film Competition sponsored by Grolsch
Grolsch presents Parameter The A.V. Club Short Film Competition Please Enter Your Date of Birth Ineligible entry Enter For people over the age ...

Resources last updated: 11/21/2015 10:36:55 AM