How to set DEFAULT in SQL Parameter.AddWithValue

Question

I'm basically tyring to have this sql statement have a value for ZipCode or the word DEFAULT.

 public static void AddEmployee(SB oSB)
  {
     string sSql = "INSERT INTO Employee (EmployeeID,ZipCode) " +
                   "VALUES (@EmployeeID,@ZipCode); " ;

     SqlCommand cmd = new SqlCommand(sSql, GetConnection());
     cmd.Parameters.AddWithValue("@EmployeeID", oSB.EmployeeID);

     string sZipCode = (oSB.ZipCode == "") ? "DEFAULT":oSB.ZipCode; 

     cmd.Parameters.AddWithValue("@ZipCode", sZipCode);
    ...
  }

This wont work as the word DEFAULT is a string and not the RESERVED WORD DEFAULT

So...I see from the previously asked question that I cant use the RESERVED WORD DEFAULT as a VALUE. I also saw where you could just rewrite the SQL...that would work but could get hairy with multiple fields.

Which RDBMS? You can always change the sql command to "INSERT INTO Employee (EmployeeID)" + "VALUES (@EmployeeID); when you want to pass the default value. Have you tried not passing anything for the default parameter, that might work? checkout this post: https://stackoverflow.com/questions/8777362/how-to-insert-default-values-in-sql-table — Hooman Bahreini, Nov 12 '17 at 02:58
@Chris - Why do you think this won't work. Have you tried? As long as `@ZipCode` parameter is string in your database it should work. What error you are getting while running your code snippet? — RBT, Nov 12 '17 at 02:59
Related post - [How do you specify 'DEFAULT' as a SQL parameter value in ADO.NET?](https://stackoverflow.com/q/2970516/465053). — RBT, Nov 12 '17 at 03:09

Ousmane D. · Accepted Answer · 2017-11-12T03:21:57.883

You don't need to do this at all. This type of thing should be done at the database level. you'll need to make sure that the column in your database table has a DEFAULT constraint which will be added to all new records IF no value is specified.

that said, there are two options which I can think of.

if you've decided to add a default constrait on your database table then include an if statement within the provided code where if oSB.ZipCode == "" then don't insert the value at all and let the database handle it.

example:

if(oSB.ZipCode == "")
{
     string anotherSql = "INSERT INTO Employee (EmployeeID) " +
                         "VALUES (@EmployeeID); " ;
     ...
     ...
}

instead of creating a default constraint on the database table, create a stored procedure which takes two parameters one of which has the default value if the parameter ZipCode is not specified.

example with SQL Server:

CREATE PROCEDURE spr_AddNewRecord  
   @EmployeeID INT,
   @ZipCode VARCHAR(10) = 'GX11 1AA' // Default value here
AS  
BEGIN    
   INSERT INTO Employee (EmployeeID, ZipCode)
   VALUES (@EmployeeID, @ZipCode)
END

now all you have to do is call the stored procedure from your code and pass in any number of parameters within the range of 1-2.

I like the stored procedure idea and it got me thinking...why not just get the DEFAULT_VALUE from the schema table. — Chris Catignani, Nov 12 '17 at 14:45

How to set DEFAULT in SQL Parameter.AddWithValue

1 Answers1