ASP and SQL

[Beattie]

2 related questions.... first, if I want to insert a null into a database what do I call it? Like I say

INSERT INTO table (one two three)
VALUES ('one', 'NULL', 'three')

what do I call that null? will SQL server understand the 'NULL'?

Now, the reverse. If I say

SELECT * FROM table where id = 1
and lets say one of the fields where id = 1 is null in the database, how do I compare in ASP?

if response("field") = "" then

or

if response("field") = nothing then
if response("field") = "NULL"

what's the right way?

 

[Mucman]

Oh woops! I just realized you want to insert a null not check to see if a returned value is null.

 

[Mucman]

I just perused through my ASP code and I have never inserted a NULL value into a database. I do have many tables that have the default value of the field set to NULL if an insert is done without specifying that variable.

Anyways... checking for NULL goes as follows:

isNull(rs("somefield"))

*edit* This is a guess but maybe using NULL without quotes in the VALUES array would do it. The INSERT is more of a SQL syntax problem than an ASP one.

 

[cyberia]

To your first, SQL question: Either of the following two approaches should work if the second field allows nulls:

INSERT INTO table (one two three)
VALUES ('one', NULL, 'three')

or

INSERT INTO table (one three)
VALUES ('one', 'three')

 

[shutterFly]

For the first part:

INSERT INTO table(one, three)
VALUES('one', 'three')

For the second part, it's been a while since I've worked with classic ASP but you might be able to use the ISNULL function to assign a value to the field when a null is encountered if comparing to an empty string doesn't work:

SELECT field1, field2, ISNULL(field3, 0) AS field3
WHERE id = 1

That will return a '0' for field3 where it is null. You could then use compare:

If response("field3") = "0" Then ...

Hope this helps.

EDIT: Whoops, cyberia beat me to the first part...

 

[cyberia]

Originally posted by: shutterFly
Whoops, cyberia beat me to the first part...

Actually, you got it "more right" than I did. I was missing the coma in the fields list.