Update table column with the columns default value

Question

System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection();
dataConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;

long MachineGroupID = Convert.ToInt64(Request.QueryString["node"]);
dataCommand.CommandText = "UPDATE [MachineGroups] SET [MachineGroupName]=@MachineGroupName,[MachineGroupDesc]=@MachineGroupDesc WHERE [MachineGroupID]= @MachineGroupID";

//add our parameters to our command object  
dataCommand.Parameters.AddWithValue("@MachineGroupName", MachineGroupName);
dataCommand.Parameters.AddWithValue("@MachineGroupDesc", MachineGroupDesc);

dataCommand.Parameters.AddWithValue("@MachineGroupID", MachineGroupID);

dataConnection.Open();
dataCommand.ExecuteNonQuery();
dataConnection.Close();

here the MachineGroups table has a column "tval" which was first inserted with user value and then on updating it should put the default value set by the SQl value when created...

for example

[tval] [int] NOT NULL DEFAULT ((2147483647))

this is how the column was created, and now i want to put the value 2147483647 on updating.

also i cannot fix the value as "2147483647" coz that might change..

any suggestions??

thanks

Bill Karwin · Accepted Answer · 2010-01-07 20:19:49Z

5

Have you tried to use the DEFAULT keyword? For example:

UPDATE [MachineGroups] SET [tval] = DEFAULT 
WHERE [MachineGroupID] = @MachineGroupID

This is supported by the SQL standard, and the syntax docs for UPDATE indicate that Microsoft SQL Server supports this usage of the DEFAULT keyword.

answered Jan 7, 2010 at 20:19

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrew · Accepted Answer · 2010-01-07 20:24:43Z

2

If your SQL server is MS SQL then you might try:

UPDATE [MachineGroups] 
SET [MachineGroupName]=@MachineGroupName
,[MachineGroupDesc]=@MachineGroupDesc 
,[tval]=DEFAULT
WHERE [MachineGroupID]= @MachineGroupID

Other SQL servers will probably have a similar syntax.

answered Jan 7, 2010 at 20:24

Andrew

4,62429 silver badges31 bronze badges

Comments

GalacticJello · Accepted Answer · 2010-01-07 20:30:54Z

1

If your intention is to always use the default value for [tval] whenever you update a row, just include that in your update statement:

dataCommand.CommandText = "UPDATE [MachineGroups] SET [MachineGroupName]=@MachineGroupName,[MachineGroupDesc]=@MachineGroupDesc, [tval] = default** WHERE [MachineGroupID]= @MachineGroupID";

That will always use your defined default whenever you call your update.

answered Jan 7, 2010 at 20:30

GalacticJello

11.5k2 gold badges28 silver badges35 bronze badges

Comments

Fredrik E · Accepted Answer · 2010-01-07 20:22:54Z

0

Use ALTER TABLE and ON UPDATE SET DEFAULT

http://technet.microsoft.com/en-us/library/ms174123.aspx

answered Jan 7, 2010 at 20:22

Fredrik E

1,83613 silver badges18 bronze badges

Collectives™ on Stack Overflow

Update table column with the columns default value

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related