Board index » delphi » Data/Time fields in access and no data

Data/Time fields in access and no data

Hi
    I am using Microsoft Access 97 and have a table called Table1 with two
fields that i am using to test:
        field 'test' of type text
        field 'datetest' of type Date/Time

I am trying to add new records to the table as shown below:

     DataModule1.ADOConnection1.Connected := true;
         DataModule1.ADOConnection1.BeginTrans;
         DataModule1.ADOCommand1.commandtext := 'INSERT INTO Table1 (test,
datetest) Values(:test, :datetest)';
          DataModule1.ADOCommand1.Parameters.Items[0].Value := 'test';
          DataModule1.ADOCommand1.Parameters.Items[1].value := '12/12/12';
         DataModule1.ADOCommand1.Execute;
        DataModule1.ADOConnection1.CommitTrans;
    DataModule1.ADOConnection1.Connected := false;

This works as it is, but in the actual table that i want to do add records
to, there are a lot of Date/Time fields, most of these fields are not
mandatory and the user may not enter anything to go in them. How can i
insert records with no data for the 'datetest' field? if i do:
    DataModule1.ADOCommand1.Parameters.Items[1].value := '';
an exception is raised. I don't want to have multiple sql querys because of
the amount of data time fields there are.

Thankyou

Charlie

 

Re:Data/Time fields in access and no data


Use
DataModule1.ADOCommand1.Parameters.Items[1].value := Null;
and include the variants unit in the uses clause.

Quote
Charlie Grosvenor wrote:
> Hi
>     I am using Microsoft Access 97 and have a table called Table1 with two
> fields that i am using to test:
>         field 'test' of type text
>         field 'datetest' of type Date/Time

> I am trying to add new records to the table as shown below:

>      DataModule1.ADOConnection1.Connected := true;
>          DataModule1.ADOConnection1.BeginTrans;
>          DataModule1.ADOCommand1.commandtext := 'INSERT INTO Table1 (test,
> datetest) Values(:test, :datetest)';
>           DataModule1.ADOCommand1.Parameters.Items[0].Value := 'test';
>           DataModule1.ADOCommand1.Parameters.Items[1].value := '12/12/12';
>          DataModule1.ADOCommand1.Execute;
>         DataModule1.ADOConnection1.CommitTrans;
>     DataModule1.ADOConnection1.Connected := false;

> This works as it is, but in the actual table that i want to do add records
> to, there are a lot of Date/Time fields, most of these fields are not
> mandatory and the user may not enter anything to go in them. How can i
> insert records with no data for the 'datetest' field? if i do:
>     DataModule1.ADOCommand1.Parameters.Items[1].value := '';
> an exception is raised. I don't want to have multiple sql querys because of
> the amount of data time fields there are.

> Thankyou

> Charlie

--
Guillermo Casta?o Acevedo
Gerente de Sistemas - Grupo Millennium Ltda
Guiller...@GrupoMillennium.com
www.GrupoMillennium.com

Re:Data/Time fields in access and no data


Hi
    Could somebody tell me why the following code doesn't work. I have the
variants unit in the uses clause.

                                DataModule1.ADOCommand1.commandtext :=
'INSERT INTO PREMIUMDATA(AGENTID, YEAR, MONTH, AGENTCONTRACTID, TYPE,
CERTPOL, SECTION, ASSURED, INCEPTION, EXPIRY, ZIP, COUNTY, STATE, SUMINS,
NJNAIC, GROSS, DEDUCTIONS, NET, CEDING) VALUES (''' +
RecordType1.CoverHolder + ''',''' + IntToStr(RecordType1.Year) + ''',''' +
IntToStr(RecordType1.Month) + ''',''' + RecordType1.AgentContractID + ''',
''' + RecordType1.Transaction +''',''' + RecordType2.CertPol +''',''' +
FloatToStr(RecordType2.Section) + ''',''' + RecordType2.Assured + ''',
:Inception , :Expiry
,'''+RecordType2.Zip+''','''+RecordType2.County+''','''+RecordType2.State+''
','''+FloatToStr(RecordType2.SumIns)+''','''+RecordType2.NJNAIC+''','''+Floa
tToStr(RecordType2.Gross)+''','''+FloatToStr(RecordType2.Debuctions)+''','''
+FloatToStr(RecordType2.Net)+''','''+RecordType2.Ceding+''')';
                                if (RecordType2.Inception='') then

DataModule1.ADOCommand1.Parameters.ParamValues['Inception'] := Null
                                else

DataModule1.ADOCommand1.Parameters.ParamValues['Inception'] :=
RecordType2.Inception;
                                if (RecordType2.Expiry='') then

DataModule1.ADOCommand1.Parameters.ParamValues['Expiry'] := Null
                                else

DataModule1.ADOCommand1.Parameters.ParamValues['Expiry'] :=
RecordType2.Expiry;
                                DataModule1.ADOCommand1.Execute;

Thankyou

Charlie
"Guillermo Casta?o A" <Guiller...@GrupoMillennium.com> wrote in message
news:3BE44B13.A1E0E5B3@GrupoMillennium.com...

Quote
> Use
> DataModule1.ADOCommand1.Parameters.Items[1].value := Null;
> and include the variants unit in the uses clause.

> Charlie Grosvenor wrote:

> > Hi
> >     I am using Microsoft Access 97 and have a table called Table1 with
two
> > fields that i am using to test:
> >         field 'test' of type text
> >         field 'datetest' of type Date/Time

> > I am trying to add new records to the table as shown below:

> >      DataModule1.ADOConnection1.Connected := true;
> >          DataModule1.ADOConnection1.BeginTrans;
> >          DataModule1.ADOCommand1.commandtext := 'INSERT INTO Table1
(test,
> > datetest) Values(:test, :datetest)';
> >           DataModule1.ADOCommand1.Parameters.Items[0].Value := 'test';
> >           DataModule1.ADOCommand1.Parameters.Items[1].value :=
'12/12/12';
> >          DataModule1.ADOCommand1.Execute;
> >         DataModule1.ADOConnection1.CommitTrans;
> >     DataModule1.ADOConnection1.Connected := false;

> > This works as it is, but in the actual table that i want to do add
records
> > to, there are a lot of Date/Time fields, most of these fields are not
> > mandatory and the user may not enter anything to go in them. How can i
> > insert records with no data for the 'datetest' field? if i do:
> >     DataModule1.ADOCommand1.Parameters.Items[1].value := '';
> > an exception is raised. I don't want to have multiple sql querys because
of
> > the amount of data time fields there are.

> > Thankyou

> > Charlie

> --
> Guillermo Casta?o Acevedo
> Gerente de Sistemas - Grupo Millennium Ltda
> Guiller...@GrupoMillennium.com
> www.GrupoMillennium.com

Re:Data/Time fields in access and no data


Hi
    Could somebody tell me why the following code doesn't work. I have the
variants unit in the uses clause.

                                DataModule1.ADOCommand1.commandtext :=
'INSERT INTO PREMIUMDATA(AGENTID, YEAR, MONTH, AGENTCONTRACTID, TYPE,
CERTPOL, SECTION, ASSURED, INCEPTION, EXPIRY, ZIP, COUNTY, STATE, SUMINS,
NJNAIC, GROSS, DEDUCTIONS, NET, CEDING) VALUES (''' +
RecordType1.CoverHolder + ''',''' + IntToStr(RecordType1.Year) + ''',''' +
IntToStr(RecordType1.Month) + ''',''' + RecordType1.AgentContractID + ''',
''' + RecordType1.Transaction +''',''' + RecordType2.CertPol +''',''' +
FloatToStr(RecordType2.Section) + ''',''' + RecordType2.Assured + ''',
:Inception , :Expiry
,'''+RecordType2.Zip+''','''+RecordType2.County+''','''+RecordType2.State+''
','''+FloatToStr(RecordType2.SumIns)+''','''+RecordType2.NJNAIC+''','''+Floa
tToStr(RecordType2.Gross)+''','''+FloatToStr(RecordType2.Debuctions)+''','''
+FloatToStr(RecordType2.Net)+''','''+RecordType2.Ceding+''')';
                                if (RecordType2.Inception='') then

DataModule1.ADOCommand1.Parameters.ParamValues['Inception'] := Null
                                else

DataModule1.ADOCommand1.Parameters.ParamValues['Inception'] :=
RecordType2.Inception;
                                if (RecordType2.Expiry='') then

DataModule1.ADOCommand1.Parameters.ParamValues['Expiry'] := Null
                                else

DataModule1.ADOCommand1.Parameters.ParamValues['Expiry'] :=
RecordType2.Expiry;
                                DataModule1.ADOCommand1.Execute;

Thankyou

Charlie

"Guillermo Casta?o A" <Guiller...@GrupoMillennium.com> wrote in message
news:3BE44B13.A1E0E5B3@GrupoMillennium.com...

Quote
> Use
> DataModule1.ADOCommand1.Parameters.Items[1].value := Null;
> and include the variants unit in the uses clause.

> Charlie Grosvenor wrote:

> > Hi
> >     I am using Microsoft Access 97 and have a table called Table1 with
two
> > fields that i am using to test:
> >         field 'test' of type text
> >         field 'datetest' of type Date/Time

> > I am trying to add new records to the table as shown below:

> >      DataModule1.ADOConnection1.Connected := true;
> >          DataModule1.ADOConnection1.BeginTrans;
> >          DataModule1.ADOCommand1.commandtext := 'INSERT INTO Table1
(test,
> > datetest) Values(:test, :datetest)';
> >           DataModule1.ADOCommand1.Parameters.Items[0].Value := 'test';
> >           DataModule1.ADOCommand1.Parameters.Items[1].value :=
'12/12/12';
> >          DataModule1.ADOCommand1.Execute;
> >         DataModule1.ADOConnection1.CommitTrans;
> >     DataModule1.ADOConnection1.Connected := false;

> > This works as it is, but in the actual table that i want to do add
records
> > to, there are a lot of Date/Time fields, most of these fields are not
> > mandatory and the user may not enter anything to go in them. How can i
> > insert records with no data for the 'datetest' field? if i do:
> >     DataModule1.ADOCommand1.Parameters.Items[1].value := '';
> > an exception is raised. I don't want to have multiple sql querys because
of
> > the amount of data time fields there are.

> > Thankyou

> > Charlie

> --
> Guillermo Casta?o Acevedo
> Gerente de Sistemas - Grupo Millennium Ltda
> Guiller...@GrupoMillennium.com
> www.GrupoMillennium.com

Re:Data/Time fields in access and no data


Hi Charlie,

Quote
> Could somebody tell me why the following code doesn't work. I have the
> variants unit in the uses clause.

It's hard to answer your question without more information:
- what do you get as error message?
- does Guillermo's suggestion work in the simple example you mentioned?
(I suppose this is a follow-up of his post).

Maybe try to build the SQL without parameters, first with null values
for the two fields, and then with non null values. This should help you
to trace the problem. I suspect that it might come from the cases when
the date is not null (formatting problem).

Thrse

Re:Data/Time fields in access and no data


The error message i get is exception is class EOleException with message
'Parameter object is improperly defined. Inconsistent of incomplete
information was provided'

Thankyou

Charlie

Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3be54bc6_1@dnews...
Quote
> Hi Charlie,

> > Could somebody tell me why the following code doesn't work. I have the
> > variants unit in the uses clause.

> It's hard to answer your question without more information:
> - what do you get as error message?
> - does Guillermo's suggestion work in the simple example you mentioned?
> (I suppose this is a follow-up of his post).

> Maybe try to build the SQL without parameters, first with null values
> for the two fields, and then with non null values. This should help you
> to trace the problem. I suspect that it might come from the cases when
> the date is not null (formatting problem).

> Thrse

Re:Data/Time fields in access and no data


With the simple example i get exactly the same error message:

    DataModule1.ADOConnection1.Connected := true;
         DataModule1.ADOConnection1.BeginTrans;
         DataModule1.ADOCommand1.commandtext := 'INSERT INTO Table1 (test,
datetest) Values(:test, :datetest)';
          DataModule1.ADOCommand1.Parameters.Items[0].Value := 'test';
          DataModule1.ADOCommand1.Parameters.Items[1].value := Null;
         DataModule1.ADOCommand1.Execute;
        DataModule1.ADOConnection1.CommitTrans;
    DataModule1.ADOConnection1.Connected := false;

Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3be54bc6_1@dnews...
Quote
> Hi Charlie,

> > Could somebody tell me why the following code doesn't work. I have the
> > variants unit in the uses clause.

> It's hard to answer your question without more information:
> - what do you get as error message?
> - does Guillermo's suggestion work in the simple example you mentioned?
> (I suppose this is a follow-up of his post).

> Maybe try to build the SQL without parameters, first with null values
> for the two fields, and then with non null values. This should help you
> to trace the problem. I suspect that it might come from the cases when
> the date is not null (formatting problem).

> Thrse

Re:Data/Time fields in access and no data


Hi Charlie,

Simpler than what I previously suggested: first test with the parameters
null, and then with random values. That way you should be able to
eliminate a possible cause of the problem.

There have been problems with Access when dates are provided in
dd/mm/yyyy format (it expects mm/dd/yyyy). Also, in INSERT statements,
Access expects # delimiters around the date. I don't know how parameters
manage to translate the values into the right format.

Thrse

Re:Data/Time fields in access and no data


Then it has nothing to do with setting the parameter value to null, but
because you're not defining the datatypes and direction of the parameters.
It'll fail even without the null assignation.
See the delphi help under the parameters keyword, there's an example of how to
properly create them.

Quote
Charlie Grosvenor wrote:
> The error message i get is exception is class EOleException with message
> 'Parameter object is improperly defined. Inconsistent of incomplete
> information was provided'

> Thankyou

> Charlie

> "Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message
> news:3be54bc6_1@dnews...
> > Hi Charlie,

> > > Could somebody tell me why the following code doesn't work. I have the
> > > variants unit in the uses clause.

> > It's hard to answer your question without more information:
> > - what do you get as error message?
> > - does Guillermo's suggestion work in the simple example you mentioned?
> > (I suppose this is a follow-up of his post).

> > Maybe try to build the SQL without parameters, first with null values
> > for the two fields, and then with non null values. This should help you
> > to trace the problem. I suspect that it might come from the cases when
> > the date is not null (formatting problem).

> > Thrse

--
Guillermo Casta?o Acevedo
Gerente de Sistemas - Grupo Millennium Ltda
Guiller...@GrupoMillennium.com
www.GrupoMillennium.com

Re:Data/Time fields in access and no data


Hi Charlie,

Quote
>           DataModule1.ADOCommand1.Parameters.Items[1].value := Null;

Try replacing Null by Unassigned.

Also it might be safer to use the names of the parameters rather than
their order, for example:

DataModule1.ADOCommand1.Parameters.FindParam('datetest').Value :=
Unassigned;

Thrse

Re:Data/Time fields in access and no data


The error message that i get is 'Parameter object is improperly defined.
Inconsistent or incomplete information was provided' The code that i am
using is:

    DataModule1.ADOConnection1.Connected := true;
         DataModule1.ADOConnection1.BeginTrans;
         DataModule1.ADOCommand1.commandtext := 'INSERT INTO Table1 (test,
datetest) Values(''test'', :datetest)';
          DataModule1.ADOCommand1.Parameters.FindParam('datetest').Value  :=
Unassigned;
         DataModule1.ADOCommand1.Execute;
        DataModule1.ADOConnection1.CommitTrans;
    DataModule1.ADOConnection1.Connected := false;

How do i define the parameter object?

Thankyou

Charlie

Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3be57d90_1@dnews...
Quote
> Hi Charlie,

> >           DataModule1.ADOCommand1.Parameters.Items[1].value := Null;

> Try replacing Null by Unassigned.

> Also it might be safer to use the names of the parameters rather than
> their order, for example:

> DataModule1.ADOCommand1.Parameters.FindParam('datetest').Value :=
> Unassigned;

> Thrse

Re:Data/Time fields in access and no data


Hi Charlie,

Quote
> The error message that i get is 'Parameter object is improperly
defined.
> Inconsistent or incomplete information was provided'

I will try to reproduce your problem. Meanwhile what you can always do
is use the insert statement without the value you want to be null, e.g.:

  DataModule1.ADOCommand1.commandtext := 'INSERT INTO Table1 (test)
values (''test'');

If I understand well, you could use that in the more complicated
statement you indicate, e.g. build two temporary string

FieldNamesString := '';
ValuesString := '';

If not (RecordType2.Inception='') then
 begin
 FieldNamesString := ', INCEPTION';
 ValuesString := ', #' + FormatDateTime('mm/dd/yyyy',
RecordType2.Inception) + '#';
 end;

If not (RecordType2.Expiry='') then
 begin
 FieldNamesString := FieldNamesString +', EXPIRY';
 ValuesString := ValuesString  + ', #'
                         + FormatDateTime('mm/dd/yyyy',
RecordType2.Expiry) + '#';
 end;

DataModule1.ADOCommand1.commandtext :=
'INSERT INTO PREMIUMDATA(AGENTID, YEAR, MONTH, AGENTCONTRACTID, TYPE,
CERTPOL, SECTION, ASSURED'
+ FieldNamesString + ', ZIP, COUNTY, STATE, SUMINS,
NJNAIC, GROSS, DEDUCTIONS, NET, CEDING) VALUES (''' +
RecordType1.CoverHolder + ''',''' + IntToStr(RecordType1.Year) + ''','''
+
IntToStr(RecordType1.Month) + ''',''' + RecordType1.AgentContractID +
''',
''' + RecordType1.Transaction +''',''' + RecordType2.CertPol +''',''' +
FloatToStr(RecordType2.Section) + ''',''' + RecordType2.Assured +
ValuesString +
','''+RecordType2.Zip+''','''+RecordType2.County+''','''+RecordType2.Sta
te+''
','''+FloatToStr(RecordType2.SumIns)+''','''+RecordType2.NJNAIC+''','''+
Floa
tToStr(RecordType2.Gross)+''','''+FloatToStr(RecordType2.Debuctions)+'''
,'''
+FloatToStr(RecordType2.Net)+''','''+RecordType2.Ceding+''')';

Thrse

Re:Data/Time fields in access and no data


How do i create a parameter? What datatype?

Thankyou

Charlie
"Guillermo Casta?o A" <Guiller...@GrupoMillennium.com> wrote in message
news:3BE5657B.A467470D@GrupoMillennium.com...

Quote
> Then it has nothing to do with setting the parameter value to null, but
> because you're not defining the datatypes and direction of the parameters.
> It'll fail even without the null assignation.
> See the delphi help under the parameters keyword, there's an example of
how to
> properly create them.

> Charlie Grosvenor wrote:

> > The error message i get is exception is class EOleException with message
> > 'Parameter object is improperly defined. Inconsistent of incomplete
> > information was provided'

> > Thankyou

> > Charlie

> > "Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message
> > news:3be54bc6_1@dnews...
> > > Hi Charlie,

> > > > Could somebody tell me why the following code doesn't work. I have
the
> > > > variants unit in the uses clause.

> > > It's hard to answer your question without more information:
> > > - what do you get as error message?
> > > - does Guillermo's suggestion work in the simple example you
mentioned?
> > > (I suppose this is a follow-up of his post).

> > > Maybe try to build the SQL without parameters, first with null values
> > > for the two fields, and then with non null values. This should help
you
> > > to trace the problem. I suspect that it might come from the cases when
> > > the date is not null (formatting problem).

> > > Thrse

> --
> Guillermo Casta?o Acevedo
> Gerente de Sistemas - Grupo Millennium Ltda
> Guiller...@GrupoMillennium.com
> www.GrupoMillennium.com

Re:Data/Time fields in access and no data


Extracted from Delphi help:

with ADOStoredProc1.AddParameter do begin
  DataType := ftString;
  Direction := pdInput;
  Value := 'malathion';
end;

Try something like that... with your query instead the stored procedure. Create
all the parameters and then assign the values as you need.
bye

Quote
Charlie Grosvenor wrote:
> How do i create a parameter? What datatype?

> Thankyou

> Charlie
> "Guillermo Casta?o A" <Guiller...@GrupoMillennium.com> wrote in message
> news:3BE5657B.A467470D@GrupoMillennium.com...
> > Then it has nothing to do with setting the parameter value to null, but
> > because you're not defining the datatypes and direction of the parameters.
> > It'll fail even without the null assignation.
> > See the delphi help under the parameters keyword, there's an example of
> how to
> > properly create them.

> > Charlie Grosvenor wrote:

> > > The error message i get is exception is class EOleException with message
> > > 'Parameter object is improperly defined. Inconsistent of incomplete
> > > information was provided'

> > > Thankyou

> > > Charlie

> > > "Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message
> > > news:3be54bc6_1@dnews...
> > > > Hi Charlie,

> > > > > Could somebody tell me why the following code doesn't work. I have
> the
> > > > > variants unit in the uses clause.

> > > > It's hard to answer your question without more information:
> > > > - what do you get as error message?
> > > > - does Guillermo's suggestion work in the simple example you
> mentioned?
> > > > (I suppose this is a follow-up of his post).

> > > > Maybe try to build the SQL without parameters, first with null values
> > > > for the two fields, and then with non null values. This should help
> you
> > > > to trace the problem. I suspect that it might come from the cases when
> > > > the date is not null (formatting problem).

> > > > Thrse

> > --
> > Guillermo Casta?o Acevedo
> > Gerente de Sistemas - Grupo Millennium Ltda
> > Guiller...@GrupoMillennium.com
> > www.GrupoMillennium.com

--
Guillermo Casta?o Acevedo
Gerente de Sistemas - Grupo Millennium Ltda
Guiller...@GrupoMillennium.com
www.GrupoMillennium.com

Re:Data/Time fields in access and no data


Charlie,

Quote
> How do i create a parameter? What datatype?

If you field is a date/time field in Access, then your parameter must
probably have ftDateTime as DataType.

Thrse

Go to page: [1] [2]

Other Threads