[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2008-06-22
5
Medium Priority
?
1,360 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

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 143

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 143

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 shrink a transaction log file down to a reasonable size.

656 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