Errors with null Integer values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Errors with null Integer values

Author

Topic

mgoomba
Starting Member

10 Posts

Posted - 2006-08-03 : 16:09:22

I am new to SQL Server and am trying to figure out why it behaves the way it is regarding integers with null values.

I have a table that contains integer datatypes which can be null. If i insert the record with a blank field i receive an INCORRECT SYNTAX NEAR ',' error. If i surround the form value with '', it inserts a 0. Why does SQL Server behave like this? And whats the proper way to handle inserts such as this?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-03 : 16:23:39

It is helpful to us if you post the code or a sample of the code...read the hint link in the bottom of my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-03 : 16:24:10

The correct syntax to insert a NULL is to use the keyword 'null':

insert into YourTable (yourColumn) values (Null)

Null is a special reserved word use to indicate the null value.

However, it sounds like you are concatenating together SQL strings and executing them. Do not do this. Use parameters.

see: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-03 : 16:29:09

I'll give you an example..just cut this into Query Analyzer and execute it


USE Northwind
GO

CREATE TABLE myTable99(Col1 char(1), Col2 int, Col3 datetime)
GO

INSERT INTO myTable99(Col1,Col2,Col3)
SELECT 'X'	,1	,null 	      UNION ALL
SELECT 'Y'	,null	,'1960-10-24' UNION ALL
SELECT null	,3	,'1999-12-31'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mgoomba
Starting Member

10 Posts

Posted - 2006-08-03 : 17:06:48

in the form field, the user is not required to enter a run_time.

INSERT INTO tblMovies(title, description, runtime)
VALUES ('#form.title#', '#form.description#', #form.run_time#)

so i take it if i want this to work right, i need to set my default form values to equal 'null' instead of ''.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-03 : 18:01:30

No .. you need to give us more information/code to show us what you are doing. What is the client language that you are using to connect to SQL Server with?

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-04 : 02:41:30

quote:
Originally posted by mgoomba

in the form field, the user is not required to enter a run_time.

INSERT INTO tblMovies(title, description, runtime)
VALUES ('#form.title#', '#form.description#', #form.run_time#)

Consider this pseudo code

IF #form.run_time# = ''
INSERT INTO tblMovies(title, description, runtime)
VALUES ('#form.title#', '#form.description#', null)
ELSE
INSERT INTO tblMovies(title, description, runtime)
VALUES ('#form.title#', '#form.description#', #form.run_time#)

Peter Larsson
Helsingborg, Sweden

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-04 : 03:12:59

>> in the form field, the user is not required to enter a run_time.

For the numeric fields that are not required do something like this before you run the sql (this is vb-script):

run_time = TRIM(Request.Form("run_time"))
IF run_time = "" THEN run_time = "NULL"
SQL = "INSERT ... VALUES(" & run_time & ")

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-04 : 09:01:17

quote:
Originally posted by Lumbago

>> in the form field, the user is not required to enter a run_time.

For the numeric fields that are not required do something like this before you run the sql (this is vb-script):

run_time = TRIM(Request.Form("run_time"))
IF run_time = "" THEN run_time = "NULL"
SQL = "INSERT ... VALUES(" & run_time & ")

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

I know that you are showing psuedo-code, but never concatenate SQL like this. Use parameters: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

It is a very bad idea to get into that habit; there is no reason not to use parameters and the benefits are better security, cleaner code, lack of delimiting and conversion issues, better NULL handling, and so on.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources