• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

Int field in SQL Server not accepting blank information

I am working with SQL server 2000 and I have a table that has int fields in it. When I submit a form and insert the data into the fields, the int field will not accept empty information. I have set the field to allow nulls when I designed the table. I have written the code now to submit a 0 in the field if the user leaves the text box blank that corresponds to the field in the database, but wondering if there is a way to SQL Server accept blank data in an integer field.

0
tarrigo
Asked:
tarrigo
  • 4
  • 3
1 Solution
 
ChumadCommented:
Yes, sql server will allow nulls on int fields. Though, to get some more information, can you supply some more info?

1- What is your sql statement that is to insert empty/null values?
2- Do you get an error when attempting to run the statement with null values?
0
 
tarrigoAuthor Commented:
Well, when I go to insert the form data for text fields I usually just use something like:



objRS("INTFIELD") = Request.FORM("INTFIELD")

I typically have to write some logic that says to add an integer like 0 if the text box was empty. But, there must be something I am doing wrong.
0
 
ZberteocCommented:
You can NOT insert empty data in a integer column, ampty data "" or '' (also called zero length string) is of alphanumeric (char, varchar, nvarchar) type so it is not compatible with integer, which is a numeric type. If you dont want NULLs than you can set 0 value as defult on the column. Another rway is to cast the value in the insert statement.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ChumadCommented:
Is this classic asp?

Maybe something like this:

objRS("INTFIELD") = null
0
 
ZberteocCommented:
I also recommend you to build the SQL statement first:

Dim strSQL

strSQL = "INSERT INTO tablename (intcolumn) VALUES (isnull(cast(" & Request.FORM("INTFIELD") & " AS integer),0))"

objConn.Execute strSQL

this construct will solve your problem.
0
 
ZberteocCommented:
However I don't see the problem of validating that field and if is empty change it to "0".

0
 
ChumadCommented:
Zberteoc,

What if 0 is a valid value?  Using 0 to represent "nothing" can backfire in some cases - say for example it's an app that wants to know how many theatre rooms are in a house... 0 would be a valid entry.

You are best to simply save null to the db building the sql string as suggested by Zberteoc or by setting the field to null:

objRS("INTFIELD") = null  '(I don't recall, it could also be Nothing instead of null)

or

objRS("INTFIELD") = DBNull.Value  //(if this is a .net app)
0
 
ZberteocCommented:
I agree, but he has to decide what way to go.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now