Solved

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

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

732 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