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

Answer 1

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.

Answer 2

Use ALTER TABLE and ON UPDATE SET DEFAULT

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

Answer 3

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.

Answer 4

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.