Board index » delphi » Problems with default values in SQL Server

Problems with default values in SQL Server

I am very new to SQL server, so bear with me please.....

I am having trouble creating records in SQL server with the following
defined sample table:

CREATE TABLE MyTable
(   AFieldName1  varchar(15) NOT NULL,
    AFieldName2  varchar(25) NOT NULL,
    AFieldName3  varchar(30) NOT NULL,
    AFieldName4  varchar(30) NOT NULL,
    AFieldName5  varchar(1) NULL,
    AFieldName6  bit NOT NULL DEFAULT 0
)

go

CREATE UNIQUE INDEX PI_Field1 ON MyTable
(AFieldName1)

The problem arises with AFieldName6.  I had assumed that by setting a
default value in SQL Server that I would not have to explicitly set
the field's value, but that is exactly what I am having to do.  What
am I doing wrong in the table definition?  I COULD explicitly set the
field, but I would really like to know if what I want is possible (I
can't believe that it's not), and what I am doing wrong.

Thanks in advance

Brandon Lilly

 

Re:Problems with default values in SQL Server


Hi Brandon

Defaults work fine, but you can't include the field in your insert
statement.

Insert Into MyTable (AFieldName1, AFieldName2, AFieldName3, AFieldName4,
AFieldName5) Values ('string1', 'string2', 'string3', 'string4', 'A')

will put 0 in the AFieldName6 field. Explicitly specifying a value
(including null) will not activate the default.

HTH
Regards
Clint.

Quote
Brandon Lilly wrote:

> I am very new to SQL server, so bear with me please.....

> I am having trouble creating records in SQL server with the following
> defined sample table:

> CREATE TABLE MyTable
> (   AFieldName1  varchar(15) NOT NULL,
>     AFieldName2  varchar(25) NOT NULL,
>     AFieldName3  varchar(30) NOT NULL,
>     AFieldName4  varchar(30) NOT NULL,
>     AFieldName5  varchar(1) NULL,
>     AFieldName6  bit NOT NULL DEFAULT 0
> )

> go

> CREATE UNIQUE INDEX PI_Field1 ON MyTable
> (AFieldName1)

> The problem arises with AFieldName6.  I had assumed that by setting a

Re:Problems with default values in SQL Server


Hello and thanks, but the problem is occuring when using tables
(haven't really tried using SQL inserts yet)....   So statements like:

Insert
FieldByName('AFieldName1').AsString := 'yada';
FieldByName('AFieldName2').AsString := 'yada';
FieldByName('AFieldName3').AsString := 'yada';
FieldByName('AFieldName4').AsString := 'yada';
FieldByName('AFieldName5').AsString := 'y';
Post

will not work because it is saying we need a value for AFieldName6
which is a bit that is supposed to default to 0.

Any ideas?

Brandon

On Fri, 14 Aug 1998 11:58:37 +1100, Clint Good <cli...@vipnet.com.au>
wrote:

Quote
>Hi Brandon

>Defaults work fine, but you can't include the field in your insert
>statement.

>Insert Into MyTable (AFieldName1, AFieldName2, AFieldName3, AFieldName4,
>AFieldName5) Values ('string1', 'string2', 'string3', 'string4', 'A')

>will put 0 in the AFieldName6 field. Explicitly specifying a value
>(including null) will not activate the default.

>HTH
>Regards
>Clint.

>Brandon Lilly wrote:

>> I am very new to SQL server, so bear with me please.....

>> I am having trouble creating records in SQL server with the following
>> defined sample table:

>> CREATE TABLE MyTable
>> (   AFieldName1  varchar(15) NOT NULL,
>>     AFieldName2  varchar(25) NOT NULL,
>>     AFieldName3  varchar(30) NOT NULL,
>>     AFieldName4  varchar(30) NOT NULL,
>>     AFieldName5  varchar(1) NULL,
>>     AFieldName6  bit NOT NULL DEFAULT 0
>> )

>> go

>> CREATE UNIQUE INDEX PI_Field1 ON MyTable
>> (AFieldName1)

>> The problem arises with AFieldName6.  I had assumed that by setting a

Re:Problems with default values in SQL Server


Hi Brandon

If you are using a ttable (without cachedUpdates), the automatically
generated insert / updates / delete statements will include the field
which has a default value ('AFieldName6'). The default value will
therefore never be needed.

Use CachedUpdates, generate the insert/update... statments, and then
manually remove 'AFieldName6' from the insert clause.

The default will then be used, however, a (possibly big) drawback is
that the default won't be visible to the client until the dataset is
refreshed.

Regards
Clint.

Quote
Brandon Lilly wrote:

> Hello and thanks, but the problem is occuring when using tables
> (haven't really tried using SQL inserts yet)....   So statements like:

> Insert
> FieldByName('AFieldName1').AsString := 'yada';
> FieldByName('AFieldName2').AsString := 'yada';
> FieldByName('AFieldName3').AsString := 'yada';
> FieldByName('AFieldName4').AsString := 'yada';
> FieldByName('AFieldName5').AsString := 'y';
> Post

> will not work because it is saying we need a value for AFieldName6
> which is a bit that is supposed to default to 0.

> Any ideas?

> Brandon

Re:Problems with default values in SQL Server


Brandon,

If you set a field NOT NULL in SQL Server and have a default value, then
make sure that in Delphi you set the associated TField as 'Required =
False'. Otherwise SQL Server will insert the default value, but Delphi will
not know that the value has been inserted, so you get an error. By setting
the field as 'Required = False' you leave the error checking to SQL Server
where it really belongs.

Hope this is of help with your problem.

Regards,
Richard

Other Threads