Results 1 to 12 of 12

Thread: How to add NULL to INT field

  1. #1
    Join Date
    May 2007
    Posts
    10

    How to add NULL to INT field

    Hi all,

    I have a table, named systems, with fields as follows.

    field datatype
    ___________________
    system varchar(50)
    modelID int
    .... ....
    ... .... etc...

    The modelID field is related to the Models table. Therefore, the value it expects is a number that already exists in a Models.modelID field.

    In the systems table, I allow nulls for the model ID field. If I edit the table directly through SQL front end it allows me to enter a system name for example and leave modelID value as NULL.

    My problem occurs when I try inserting into the table from ASP. Whether I leave the value I'm passing to the stored procedure for insert blank or NULL it gives me problems.

    Is this a typical issue that can be resolved with a simple property change?

    Please help me resolve this, I need to have the ability to leave certain fields empty even though they are related to other tables. Is that possible?

    Thank you for any help in advance.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    It shouldn't ask for value in this case. What's your insert statement in asp? What was the error?

  3. #3
    Join Date
    May 2007
    Posts
    10
    Thank you for your quick reply.

    From within ASP I'm executing a stored procedure.

    sql = "EXEC insertSystems '"&system&"','"&m&"'"
    objConn.execute sql, , 129
    This is what the actual stored procedure looks like.

    ALTER PROCEDURE [dbo].[insertSystems]
    @s varchar(50),
    @model int
    AS
    BEGIN


    IF EXISTS(SELECT * FROM Systems WHERE SystemName = @s)
    RAISERROR('Duplicate system names not allowed', 11, 1)
    ELSE
    INSERT INTO Systems (SystemName,ModelID)
    Values (@s,@model)
    END

    Do I maybe have to add more logic to the actual stored procedure to detect if a value is empty?

    Here is the error SQL returns.

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Systems_Models". The conflict occurred in database "DatabaseMachineInfo", table "dbo.Models", column 'ModelID'.

    That makes it sound like anything but an existing ModelID is invalid. However, as I mentioned in my previous post, the SQL front end allows NULL.

    I just realized that if I erase NULL from the field and leave a blank space (from SQL front end) it challenges me and requires me to press ESC to revert to the default NULL value. If I type in NULL all in uppercase, it automatically changes it to the italics NULL.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Did you put any index on that column? Is it related to a pkey column in other table? If so, ensure the null value exists in that pkey column.

  5. #5
    Join Date
    May 2007
    Posts
    10
    What do you mean by putting an index on the column?

    The field ModelID in systems table is related to ModelID in Models table and yes Models.ModelID is the primary key.

    That field is set to automatically increment by one, only accepting numbers. How do I add NULL to that column?

    Thanks again for your help.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Just check if you created unique index on ModelID column in systems table. The issue is on ModelID column in Models table, you can't add null value to identity column and values added to systems.ModelID should exist in Models.ModelID.

  7. #7
    Join Date
    May 2007
    Posts
    10
    That's exactly my point and frustration.

    Why does SQL Server 2005 front end allow me to add NULL value to that column but the method I'm trying, using ASP to execute a stored procedure, does not?

  8. #8
    Join Date
    Jun 2007
    Posts
    1
    ALTER PROCEDURE [dbo].[insertSystems]
    @s varchar(50),
    @model int = null

    add = null to the @model parameter => it would work

  9. #9
    Join Date
    May 2007
    Posts
    10
    That doesn't seem to work. It still wants a value that exists in the related column.

    Is it possible to dynamically build an insert statement in SQL Stored Procedure?

    For example:

    INSERT INTO systems (@columns)
    Values(@values)

    Where @columns is a variable consisting of something like "systemName,ModelID,etc.." and @values would consist of "@s,@model,etc.."?

    That way I could see if the value is equal to nothing and I just won't include it in the insert statement.

    If i use INSERT INTO systems(systemName) values(@s) - that works fine. It inserts just the system name and leaves ModelID as NULL like I want it. So if I could have some logic in there that builds the INSERT statement prior to it executing that would resolve my problem.

    Is that at all possible to do?

    Thanks for all your help thus far.

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    You can try with dynamic sql, like this:

    declare @columns varchar(100)
    declare @values varchar(100)
    declare @sql varchar(250)

    set @sql = 'INSERT INTO systems (' + @columns + ') Values(' + @values + ')'
    exec(@sql)

  11. #11
    Join Date
    Sep 2005
    Posts
    168
    The catch is in the way you build the statement
    executed within asp

    sql = "EXEC insertSystems '"&system&"','"&m&"'"
    when m is empty then the statement is:
    EXEC insertSystems 'mysystemname','' . (Pay attention at the empty string...)

    Now,try the following in QA
    DECLARE @test INT
    SET @test = ''
    SELECT @test
    --surprise !!! it displays 0 !!!!
    so, each time m is not defined, the @model variable in InsertSystems procedure is 0!!
    I guess, there is no modelID with 0 value in your models master table...

    Try to pass NULL (not in quotes) to the stored procedure from within asp when m is empty or length(m) = 0 etc..

    or set @modelID = NULL (stored procedure definition)
    and use two statements within asp

    Sql = "EXEC insertSystems '"&system&"'

    IF (m is defined [with any statement you like])
    Sql = sql & ", "&m

    --HTH--
    Last edited by mikr0s; 06-15-2007 at 07:28 AM.

  12. #12
    Join Date
    Jul 2007
    Posts
    5
    You just need to do one thing, assign any negative value (for e.g. -9999) to your modelid column when it is null from ASP, and check in SP that the value of modelid para is -9999 then
    if @ModelID = -9999
    set @ModelID = null

    Insert into Systems (system, modelid) values (@System, @ModelID)

    this will definitely work

    All the best

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •