Updating a field automatically when keying in one field

Question

Updating a field automatically when keying in one field

denimblue

Mr or Mrs. 500

Points: 594
More actions
February 16, 2007 at 1:24 pm

#176073

This is what I think I want, I realize the statement is not correct, just wanted to see if anyone understand what I'm looking for.
update table.Test
set field.name2 = field.name1 + 25)
Table Test
Field 1 Field 2
10 35
User types 10 in Field 1 and Field 2 automatically fills with 35

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

in sql server , you can use a calculated column; (not available in oracle...you'd have to create a trigger to do the same thing); note i added logic to watch for nulls.

example:

CREATE TABLE TEST( FIELD1 int, FIELD2 AS ISNULL(FIELD1,0) + 25 )

INSERT INTO TEST(FIELD1) VALUES (6)

INSERT INTO TEST(FIELD1) VALUES (NULL)

INSERT INTO TEST(FIELD1) VALUES (21)

SELECT * FROM TEST

results:

FIELD1 FIELD2

----------- -----------

6 31

NULL 25

21 46

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 2

create a computed column in your table. syntax would be similar to below:

CREATE TABLE Test( field1 int, field2 AS (field1 + 25))

Lowell SSC Guru Points: 323518 More actions · Answer 3

hehe... same example, but i beat your post by a whole minute!

great minds and all that.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 4

i realized that right after i posted, good to know that i had a valid thought for once!

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 5

Thank you for the help. I can run the new table query in the database and it works, but when I use my client application, I get a Database error -271? I'm not sure if it's a permissions issue or something to do with computed columns. Any suggestions are greatly appreciated.

Lowell SSC Guru Points: 323518 More actions · Answer 6

i believe your code is trying to update the calculated field, maybe by trying to update it's current value with the same value. it might be automatic. make sure the clietn application is not putting a value int he field from another source, like a datatable or something...you'd relaly need to paste the exact code to get meaningful help.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 7

I copied your code exactly leaving out the insert and select * statement. I uploaded a very simple screen on the client side. It's when I key in field1 I get -271.

Lowell SSC Guru Points: 323518 More actions · Answer 8

what is the specific error message you get for error -271; i googled it and couldn't find an example.

ok...you said you were loading the data into a data table. when using your data access object, how are you loading the datatable? is it something like

dataAccessObj.LoadDataTable("SELECT * FROM TEST",myDataTable)

or is it

dataAccessObj.LoadDataTable("SELECT FIELD1 FROM TEST",myDataTable)

typically, behind the scenes, the dataaccess layer will create a suite of SQL statements to handle INSERT/UPDATE/DELETE. run sql profiler against your server, run your simple screen to add/update, and see the actual SQL statement being built by your application. if the *, or all columns are named, then the behind-the-schenes update/insert statements might specifically name the columns that are calculated.

Not sure about your situation, but you could also create a view with the calculated column that is based on the original table, if you only need it for display purposes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!