Post reply

Default NULL not working

  • April 19, 2007 at 11:49 am

    #701537

    You don't have to be going to a stored procedure to use parameter inputs and the fact the data is being set tells me you aren't actually passing null in.

    C#.NET

    string strSQL = "SELECT * FROM Table_Name WHERE Int_Column_Name = @IntColumnValue";

    cmdSQL.Connection = cnSQL;

    cmdSQL.CommandType = CommandType.Text;

    cmdSQL.CommandText = strSQL;

    cmdSQL.Parameters.Add(new SqlParameter("@IntColumnValue",SqlDbType.Int,4,ParameterDirection.Input,true,0,0,"IntColumnValue",DataRowVersion.Default,null));

    if (Request.Form("IntColumnValue").length > 0

    cmdSQL.Parameters["@IntColumnValue"].value = Request.Form("IntColumnValue").value;

  • April 19, 2007 at 11:50 am

    #701538

    RIght because the parameter is apparently being set to '' and not null.

  • April 19, 2007 at 12:23 pm

    #701550

    There used to be a problem where ADO would take Null parameters from the code and turn them into empty strings. I have no clue if it still is the case, but I believe the workaround was to pass DBNull.Value.

  • April 19, 2007 at 12:37 pm

    #701561

    Also, a default value is only inserted if you don't provide a value for that column.  In your insert statement, a value is being provided, which defeats the purpose of the default.

     

  • April 20, 2007 at 3:05 am

    #701652

    I think you should look at your application code, to see what happens when the form is processed prior to updating the database.  To me, it looks very likely that the form is setting all values to their 'natural' defaults (i.e. 0 for numerics, empty string for character) prior to the database INSERT. 

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • April 20, 2007 at 7:06 am

    #701707

    Why would you try to put an empty string into an INT column? An empty string is not NULL.

    --Andrew

  • April 20, 2007 at 7:41 am

    #701720

    As Andrew says, an empty string is not NULL in SQL Server, as per the ANSI SQL standard.  In Oracle, an empty string is NULL, but Oracle is outside the standard for this behaviour.

    If vmrao is used to Oracle, this could be the cause of the confusion.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 7 posts - 16 through 21 (of 21 total)

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