Solved

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

Posted on 2008-06-22
5
1,348 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

863 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

21 Experts available now in Live!

Get 1:1 Help Now