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

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.
teustaceAsked:
Who is Participating?
 
teustaceConnect With a Mentor Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
teustaceAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.