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.

It shouldn't ask for value in this case. What's your insert statement in asp? What was the error?

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.

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.

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.

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.

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?

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

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

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.

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)

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

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