[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 899
  • Last Modified:

DateTime insert problem with access database

Hi

The following query works when run from within MSAccess environment but fails when called using a TQuery object.
What am I missing here.

Access Query.
Insert into F_AnimalTrans (AN_ID, AN_TAG, AN_Group, AN_Transponder, AN_Mass, AN_Age, AN_Condition, AN_Destination, AN_Section, AN_Gender, AN_Type, AN_Height, AN_Length, AN_Width, AN_Program, AN_Process, AN_Implant, AN_Transaction, AN_TransactionDate, AN_Gate, AN_PenFrom, AN_PenTo, AN_Disease, AN_Treatment, AN_Status, AN_TransControl  ) values (5344189, 'YEL4189', '', '', 152, 'W', 'M', '', '', 'S', 'V', 111, 0, 0, 'NOR', 'NOR', '', 'REG', #04/20/2005 10:10:10 AM#,1, '', '', '', '', 'NSP', 4)


Delphi Code
  GetLocaleFormatSettings(0,FormatSettings);
  FormatSettings.LongDateFormat := 'DD/MM/YYYY hh:mm:ss AM/PM';
  FormatSettings.ShortDateFormat:= 'DD/MM/YYYY';

  NewDateTime := StrToDateTime(DataIn.v_TRANSACTION_DATE,FormatSettings );

  FormatSettings.ShortDateFormat:= 'MM/DD/YYYY';
  FormatSettings.LongDateFormat := 'MM/DD/YYYY hh:mm:ss AM/PM';

  NewMonth := MidStr(DataIn.v_TRANSACTION_DATE,4,2);
  NewDay := MidStr(DataIn.v_TRANSACTION_DATE,1,2);
  NewYear := MidStr(DataIn.v_TRANSACTION_DATE,7,4);
  NewDate := NewMonth + '/' + NewDay + '/' + NewYear;

  qImportBody    := TQuery.Create(self);
  qImportBody.DatabaseName  := 'MoveData';
  qImportBody.SQl.Add('Insert into F_AnimalTrans (');
  qImportBody.SQl.Add('AN_ID, AN_TAG, AN_Group, AN_Transponder, ');
  qImportBody.SQl.Add('AN_Mass, AN_Age, AN_Condition, AN_Destination, AN_Section, ');
  qImportBody.SQl.Add('AN_Gender, AN_Type, AN_Height, AN_Length, AN_Width, AN_Program, AN_Process, ');
  qImportBody.SQl.Add('AN_Implant, AN_Transaction, AN_TransactionDate, AN_Gate, AN_PenFrom, ');
  qImportBody.SQl.Add('AN_PenTo, AN_Disease, AN_Treatment, AN_Status, AN_TransControl  ) values (');
  qImportBody.SQl.Add(IntTostr(DataIn.v_ID) + ', ''' + DataIn.v_TAG + ''', ''' + DataIn.v_GROUP + ''', ' + '''' + DataIn.v_TRANSPONDER + ''', ');
  qImportBody.SQL.Add(DataIn.v_MASS + ', ''' + DataIn.v_AGE + ''', ''' + DataIn.v_CONDITION + ''', '''  + DataIn.v_FEEDLOT + ''', ''' + DataIn.v_SECTION + ''', ');
  qImportBody.SQl.Add('''' + DataIn.v_SEX + ''', ''' + DataIn.v_TYPE + ''', ' + DataIn.v_HEIGHT + ', ' + DataIn.v_LENGTH + ', ' + DataIn.v_WYD + ', ''' + DataIn.v_PLAN + ''', ''' + DataIn.v_PROCESS + ''', ');
  qImportBody.SQl.Add('''' + DataIn.v_IMPLANT + ''', ''' + DataIn.v_TRANSACTION_TYPE  + ''', #' + DateTimeToStr(NewDateTime,FormatSettings) + '#,' +  DataIn.v_Gate + ', ');
  qImportBody.SQl.Add('''' + DataIn.V_PENFROM + ''', ''' + DataIn.V_PENTO + ''', ''' + DataIn.V_DISEASE + ''', ''' + DataIn.V_TREATMENT + ''', ''' + DataIn.V_STATUS + ''', ' + DataIn.V_TRANSCONTROL + ')' );
  try
    qImportBody.ExecSQL;
  except
    KeyError := KeyError +1;
  end;
  qImportBody.Free;

Much thanks


tcss
0
tcss
Asked:
tcss
  • 3
  • 3
  • 2
1 Solution
 
tcssAuthor Commented:
Me Again,

supose I should mention the error too. It fails to recognise the time portion of the timestamp. (or part of it)
eg Field 10:10 is of unknown type (Re access query for timestampp field)

tcss
0
 
mikelittlewoodCommented:
You passing the date in as a string so you will need quotes around it just like the rest of the strings
I wouldnt use all the '''' everywhere. Much easier to use QuotedStr('YourString').
QuotedStr(DateTimeToStr(NewDateTime,FormatSettings)) < I think this is your issue
0
 
kretzschmarCommented:
usual i would transform your on the fly statment into a static-statement with parameters

best results i had with the format yyyy/mm/dd hh:mm:ss without #-sign and 24hours (no AM/PM)

if you use parameters, then you can supply the parameter with the datetimevalue (float-Type)

but usual i would guess your code should also work, maybe a problem with your values you supply?

meikl ;-)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kretzschmarCommented:
good point, mike ;-)
0
 
tcssAuthor Commented:
Thanks for the input guys.

Just a little bit of clarification. the access query/insert above is a cut and paste from the delphi debuggers watch window. Those are actual values that the application is using that worked with in access. I will try thye quoted string but have my doubts as the field in the database is a datetime field.
Will get back soon

tcss
0
 
mikelittlewoodCommented:
Im assuming everywhere you have ''' is a string value not an int, so I would remake this to look like:

qImportBody.SQl.Add(IntTostr(DataIn.v_ID) + ', ' + QuotedStr(DataIn.v_TAG) + ', ' + QuotedStr(DataIn.v_GROUP) + ', ' + QuotedStr(DataIn.v_TRANSPONDER) + ', ');
qImportBody.SQL.Add(DataIn.v_MASS + ', ' + QuotedStr(DataIn.v_AGE) + ', ' + QuotedStr(DataIn.v_CONDITION) + ', ' + QuotedStr(DataIn.v_FEEDLOT) + ', ' + QuotedStr(DataIn.v_SECTION) + ', ');
  qImportBody.SQl.Add(QuotedStr(DataIn.v_SEX) + ', ' + QuotedStr(DataIn.v_TYPE) + ', ' + DataIn.v_HEIGHT + ', ' + DataIn.v_LENGTH + ', ' + DataIn.v_WYD + ', ' + QuotedStr(DataIn.v_PLAN) + ', ' + QuotedStr(DataIn.v_PROCESS) + ', ');
qImportBody.SQl.Add(QuotedStr(DataIn.v_IMPLANT) + ', ' + QuotedStr(DataIn.v_TRANSACTION_TYPE) + ', ' + QuotedStr(DateTimeToStr(NewDateTime,FormatSettings)) + ',' +  DataIn.v_Gate + ', ');
qImportBody.SQl.Add(QuotedStr(DataIn.V_PENFROM) + ', ' + QuotedStr(DataIn.V_PENTO) + ', ' + QuotedStr(DataIn.V_DISEASE) + ', ' + QuotedStr(DataIn.V_TREATMENT) + ', ' + QuotedStr(DataIn.V_STATUS) + ', ' + DataIn.V_TRANSCONTROL + ')' );

Might need to check it a little closer to make sure I havent missed out a comma or something.
0
 
mikelittlewoodCommented:
It doesnt matter if it is a datetime field in the database, if you correctly format a string to the database format and put quoted strings around it, it will work.
I would myself prefer to use FormatDateTime though

FormatDateTime('dd/mm/yyyy hh:nn:ss', <YourDate>);

Just change the 'dd/mm/yyyy hh:nn:ss' to your database format
0
 
tcssAuthor Commented:
Done the quoted string thing and taken out the '#' which is used within access to delimit dates and it works.

The quotedstr also makes the code a lot more readable. Did not know about that function.

Thanks guys.

tcss
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now