Solved

SQL 2005 - Default Values - a problem occurs retrieving data back after the commit

Posted on 2008-06-22
5
1,346 Views
Last Modified: 2008-07-17
I have set some default values for my database columns. For example zero for an int data type, GetDate() for a dateTime column etc. However, when I try to add a new row I am informed (with the attached error message) that I have to re-run the query in order to save the data. I do not want to do this because the fields are used in a form and the default values should be auto-populated for new rows. Please could someone offer some suggestions. I am using a full edition of SQL Server 2005.
0
Comment
Question by:teustace
  • 3
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21840308
>I am using a full edition of SQL Server 2005.
for the database, but not for the "form" application.

so, the problem is this:
when you specify a value (ie NULL is also a value) for a new row, it will NOT take the default.
so, for any field left empty, you shall NOT specify the column at all for the insert.

now, after the insert, you will have to query for the row to get the values (including the ones populated with defaults)
0
 

Author Comment

by:teustace
ID: 21842389
Hi Angellll, many thanks for your reply. You are really amazing, always out there :-)
Maybe I haven't explained the problem, or maybe I don't understand your reply,but forget my statement about the front end, lets just stay with SQL database.

All I have done was give a column a default value of zero. When I fill in a new record and don't place a value in the field that holds my default value, I expect to move off the row with the field populated with a zero. I should not have to refresh the table to get this value populated. It is a default value. But this does not happen and I get the red button informing me to refresh. Cheres, T
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21844008
I see, you use the Enterprise Manager / Management Studio for data entry.
which indeed does not fetch the default values (or values changed by triggers) when "moving off" a new record.
there is nothing you can do, except rerun the query...
0
 

Accepted Solution

by:
teustace earned 0 total points
ID: 21892220
Hi Angellll, apologies for the delayed reply to your comments.  (I'm trying to get this project finshed as users are testing it next week!)

Well, this is a bummer, and I have had a hard time accepting this is the case, but actaully I'm not so bother about this if I could only get the default values to insert from the front end. I don't know how.

My required form set-up is this:  When a user starts a new project they give it a name and this generates the new ID. Then I want the current date (for "Project Started" to be auto-populated along with another field called "Project Status" which is to have a default of "6". I do not want the user to enter these values. In fact, I would like them hdden when the user clicks on the "Add" button, and visable when the user is just looking through all the records. But I guess this may be a bit fancy for my skills and I will probably make them read only. IF i can get these defaults to work.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21893568
all these defaults should be set in the client application, actually.
note: the new ID should NOT be generated by the application, but only retrieved from sql server after the insert (select scope_identity())
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now