Set Default Values (1 Viewer)

NearImpossible

Registered User.
Local time
Today, 06:46
Joined
Jul 12, 2019
Messages
225
I'm thinking I already know the answer, but Is it possible to set a multi line default value for a column in a SQL table?

i.e.

Clients:
Servers:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:46
Joined
Oct 29, 2018
Messages
21,357
You mean like include/add the new line character in the data?
 

NearImpossible

Registered User.
Local time
Today, 06:46
Joined
Jul 12, 2019
Messages
225
Whenever I add a new record, I would like the default value for the column to populate with

Clients:
Server:

I'm using Access for the FE and tried to set it on the default value there as well but haven't had any luck so far.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:46
Joined
Oct 29, 2018
Messages
21,357
Whenever I add a new record, I would like the default value for the column to populate with

Clients:
Server:

I'm using Access for the FE and tried to set it on the default value there as well but haven't had any luck so far.
What is the column type?
 

sonic8

AWF VIP
Local time
Today, 12:46
Joined
Oct 27, 2015
Messages
998
I'm thinking I already know the answer, but Is it possible to set a multi line default value for a column in a SQL table?
Of course it is:
Code:
ALTER TABLE yourTableName    
    ADD CONSTRAINT yourDefaultName 
        DEFAULT 'Clients:' + char(13) + char(10) + 'Servers:' 
            FOR yourFieldName ;
But, I think the default values from the server might not show up in your form. So, maybe, you rather want to add the default value to the control in the Access form. Just paste this into the Default Value property:
Code:
"Clients:" & Chr(13) & Chr(10) & "Servers:"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:46
Joined
Oct 29, 2018
Messages
21,357
currently nvarchar(Max) but I can make it whatever as there is no need for formatting
Hi. I was able to use the following in SSMS:
Code:
alter table test add constraint notes_def default 'client:'+char(13)+char(10)+'server:' for notes;
Edit: Oops, too slow...
 

NearImpossible

Registered User.
Local time
Today, 06:46
Joined
Jul 12, 2019
Messages
225
Thanks to both sonic8 and theDBguy.

Setting it in access didn't work, but running the command in SSMS did the trick.

Thanks again !!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:46
Joined
Oct 29, 2018
Messages
21,357
Thanks to both sonic8 and theDBguy.

Setting it in access didn't work, but running the command in SSMS did the trick.

Thanks again !!
Hi. Glad to hear you got it sorted out. Phil and I were happy to assist. Good luck with your project.
 

NearImpossible

Registered User.
Local time
Today, 06:46
Joined
Jul 12, 2019
Messages
225
Is it possible to setup multiple column defaults?

EX:

Column 1 : Defaults X, Y, Z
Column 3 : Defaults 1,2

I tried using 2 different Alter Tables statements in SSMS, which completed successfully, but now I am getting a "String or binary data would be truncated" error when I try to add a new record

Please advise
 

NearImpossible

Registered User.
Local time
Today, 06:46
Joined
Jul 12, 2019
Messages
225
Can you post your final SQL statement? Thanks for sharing.

I don't have access to it right now but I just basically repeated the whole Alter Table statement for each column that I wanted to specify a default criteria. I just changed the constraint name to match the column, added DEF ,for default, behind it and then specified the criteria. I have 6 columns all with different default criteria now.

Code:
ALTER TABLE [COLOR="Red"]Table1[/COLOR]    
    ADD CONSTRAINT [COLOR="red"]Column1DEF[/COLOR] 
        DEFAULT 'Criteria 1:' + char(13) + char(10) + 'Criteria 2:' 
            FOR [COLOR="red"]Column1[/COLOR] ;

ALTER TABLE [COLOR="red"]Table1[/COLOR]    
    ADD CONSTRAINT [COLOR="red"]Column2DEF[/COLOR] 
        DEFAULT 'Criteria 1:' + char(13) + char(10) + 'Criteria 2:' + char(13) + char(10) + 'Criteria 3:'
            FOR [COLOR="red"]Column2[/COLOR] ;

ALTER TABLE [COLOR="red"]Table1[/COLOR]    
    ADD CONSTRAINT [COLOR="red"]Column4DEF[/COLOR] 
        DEFAULT 'Criteria 1:' + char(13) + char(10) + 'Criteria 2:' + char(13) + char(10) + '    Criteria 3:' + char(10) + '    Criteria 4:'
            FOR [COLOR="red"]Column4[/COLOR] ;


The above query gives me the following output everytime I add a new record:

Code:
|   Column1   |   Column2   |   Column3   |     Column4     |
|-------------|-------------|-------------|-----------------|
| Criteria 1: | Criteria 1: |             | Criteria 1:     |
| Criteria 2: | Criteria 2: |             | Criteria 2:     |
|             | Criteria 3: |             |     Criteria 3: |
|             |             |             |     Criteria 4: |
|-------------|-------------|-------------|-----------------|

The one thing I noticed was if I changed anything on the table after running the query, i.e. Adding/Removing/Renaming a column, I had to rerun the query to reassign the values for the above columns, even if they weren't the ones modified.

I was getting the truncating error because the column I originally specified was limited at 20 characters.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 04:46
Joined
Oct 29, 2018
Messages
21,357
The one thing I noticed was if I changed anything on the table after running the query, i.e. Adding/Removing/Renaming a column, I had to rerun the query to reassign the values for the above columns, even if they weren't the ones modified.
Hi. If you're running the ADD constraint again, it might be a good idea to DROP it first before adding it again. Just a thought...
 

NearImpossible

Registered User.
Local time
Today, 06:46
Joined
Jul 12, 2019
Messages
225
Hi. If you're running the ADD constraint again, it might be a good idea to DROP it first before adding it again. Just a thought...

From what I gathered after adding a new column to the table or renaming an existing column, or changing the type, it automatically dropped everything as I wouldn't get any defaults when adding a new record, unless I reran the query.

Not sure if there's another way of doing it, but I wasn't able to add multiple column defaults without doing the Add Constraint for each each column.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:46
Joined
Oct 29, 2018
Messages
21,357
From what I gathered after adding a new column to the table or renaming an existing column, or changing the type, it automatically dropped everything as I wouldn't get any defaults when adding a new record, unless I reran the query.

Not sure if there's another way of doing it, but I wasn't able to add multiple column defaults without doing the Add Constraint for each each column.
Hi. What do you get if you run this SQL?
Code:
SELECT * FROM sys.default_constraints;
You can compare the results after you modify the table to add a new column.
 

NearImpossible

Registered User.
Local time
Today, 06:46
Joined
Jul 12, 2019
Messages
225
Hi. What do you get if you run this SQL?
Code:
SELECT * FROM sys.default_constraints;
You can compare the results after you modify the table to add a new column.

I'll test it tomorrow, both before and after and report back!!
 

NearImpossible

Registered User.
Local time
Today, 06:46
Joined
Jul 12, 2019
Messages
225
As I suspected, the constraints specified in the query disappeared after adding a new column, however anything set with a default value in the table setup, i.e. bit field set to not allow nulls and a default value of ((0)) remained. (see attachments)

On a side note, I noticed there are still several of the defaults listed that were setup on a table, that no longer exists. Is it possible to clear those out or will it affect anything if i just leave them alone?
 

Attachments

  • After Change.PNG
    After Change.PNG
    12.4 KB · Views: 123
  • WithConstraints.PNG
    WithConstraints.PNG
    42 KB · Views: 122
Last edited:

Users who are viewing this thread

Top Bottom