TSQLQuery - ParamByName and the AsDateTime

[Ned]

Hi,

Not sure if this has been covered anywhere in the newsgroups. Couldn't see
anything. I spent the last day trying to see why a number of BCB 6 project we
had no longer work when the parameters are set in a query.

I found that calling ParmByName("paramNameToSet")->AsDateTime = aDateTimeObject,
did not actually set the parameter when ExecSQL is called. The parameter appears
to be set when you check it but it is not passed to the SQL Server. The error
shown is

... raised exception class EDatabaseError with message 'No Value for parameter
'pEndDate".

I am using SQL Server 2005 and the enterprise supplied DBExpress dbxmss30.dll
driver.

It all works if the aDateTimeObject above is converted to an AnsiString and the
AsDateTime call is replaced with AsString. Both options shown below in code.

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "DatabaseAccess.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
#define USE_TDATETIME

TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
//
// Connect to the database using the built in SQL Enterprise driver
//
dbProduction->ConnectionName = "MSSQLConnection";
dbProduction->DriverName = "MSSQL";
dbProduction->LoginPrompt = false;
dbProduction->GetDriverFunc = "getSQLDriverMSSQL";
dbProduction->LibraryName = "dbxmss30.dll";
dbProduction->VendorLib = "oledb";
dbProduction->Params->Add("HostName=");
dbProduction->Params->Values["HostName"] = "SQLExpress\\SQLExpress";
dbProduction->Params->Values["User_Name"] = "userName";
dbProduction->Params->Values["Password"] = "password";
dbProduction->Params->Values["DataBase"] = "Orders";
dbProduction->Open();
//
// Test the query operation with TDateTime
//
AnsiString sParam[5];
float fParam[5];
TDateTime currentTime(Now());
AnsiString endDate;
AnsiString startDate;

endDate = AnsiString(currentTime.DateTimeString());
startDate = AnsiString(currentTime.DateTimeString());

#ifdef USE_TDATETIME
qryAddProduction->Prepared = true;
qryAddProduction->ParamByName("pEndDate")->AsDateTime = currentTime;
qryAddProduction->ParamByName("pStartDate")->AsDateTime = currentTime;
#else
qryAddProduction->ParamByName("pEndDate")->AsString = endDate;
qryAddProduction->ParamByName("pStartDate")->AsString = startDate;
#endif

qryAddProduction->ParamByName("pOrderNo")->AsString = "99999";
qryAddProduction->ParamByName("pInputOrderNo")->AsString = "99999";
qryAddProduction->ParamByName("pCompleted")->AsInteger = false;
qryAddProduction->ExecSQL();
}
//---------------------------------------------------------------------------

[Nicholas Hugh]

try

ParmByName("paramNameToSet")->AsDate = aDateTimeObject, ie
AsDateTime ->AsDate

"Ned" <nema...@yahoo.com> wrote in message
news:4799437b$1...@newsgroups.borland.com...