[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 814
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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