How to Specify 'Default' as a SQL Parameter Value in Ado.Net

How do you specify 'DEFAULT' as a SQL parameter value in ADO.NET?

SQL query parameters take the place of literal values only.

You can't send an SQL keyword as the value of a parameter, just as you cannot send a table identifier, column identifier, list of values (e.g. for an IN predicate), or an expression. The value of the parameter is always interpreted as a literal value, as if you had included a quoted string literal or a numeric literal in your query.

Sorry, but you have to include an SQL keyword as part of the SQL query before you prepare that query.

How to set DEFAULT in SQL Parameter.AddWithValue

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.

1. 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); " ;
     ...
     ...
} 

2. 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.

Default sizes for SQL parameters in ADO.NET

From SqlParameter.Size Property doco

If not explicitly set, the size is inferred from the actual size of the specified parameter value.

There's an awesome reply by Peter Wone on 293315 that describes more detail what that actually means

How to assign default value in a parameter?

Try this one:

cmd.Parameters.AddWithValue("@Item", OleDbType.VarChar).Value = items.ItemName1;

And why are you using this again after ExecuteNonQuery()..?

cmd.Parameters.AddWithValue("@ItemID", addorder.tbItemID.Text);
cmd.Parameters.AddWithValue("@ItemName", addorder.tbName.Text);
cmd.Parameters.AddWithValue("@ItemPrice", addorder.tbPrice.Text);

Default value SqlCommand.Parameters.Add C# Issue

You could fix you code casting the DBNull.Value to object

command.Parameters.Add(new SqlParameter("@value", 
                     string.IsNullOrEmpty(value) 
                     ? (object)DBNull.Value
                     : CT_Intitule);

But I wish to discourage the usage of the overload that pass directly the value.

It is always better and more error safe to always specify the parameter type

command.Parameters.Add(new SqlParameter("@value", SqlDbType.NVarChar)).Value =
                     string.IsNullOrEmpty(value) 
                     ? (object)DBNull.Value
                     : CT_Intitule;

You could read more about the effects of the C# code on database performances here

How Data Access Code Affects Database Performance

How to give ADO.NET Parameters

In your case, it looks like you're using .NET. Using parameters is as easy as:

C#

 string sql = "SELECT empSalary from employee where salary = @salary";
 SqlConnection connection = new SqlConnection(/* connection info */);
 SqlCommand command = new SqlCommand(sql, connection);

 command.Parameters.AddWithValue("salary", txtSalary.Text);

Getting C# To Insert Defaults Into SQL Server

I actually used the INFORMATION_SCHEMA.COLUMNS table to pull back from SQL Server all of the Column Defaults. Then I just organized the defaults into a string[] and looped through it to insert defaults rather than nulls (some defaults are null).

---

Related Topics