Saving Null Fields to Database

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

SQL Server 2000 Forums

SQL Server Development (2000)

Saving Null Fields to Database

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-03 : 06:24:23

Hi there,

I'm sure this is a pretty common problem, but for some reason it seems to be above my amateurish skill level to be able to fix it....haha

I have an ASP web front end with an SQL Server 2000 database. Basically, I have a number of HTML Forms on the front end and, at the moment, if the user leaves a field blank when they click 'Save', it will return one of a number of errors depending on the type of field it is trying to insert the Null value into....(I don't think this is a problem for Varchar fields, as the problem seems to come when it can't convert the Null value into a format accepted by, for example, a numeric or datetime field)

I have tried:

-Allowing Null fields in the database for the necessary fields
-Setting default values for the database fields

Just can't seem to get it to work.....any help would be greatly appreciated!

Thanks!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 06:33:23

NULL is not the same as an empty string.
In your ASP page, check for DATETIME-TEXTBOX and see if .Text = "". If so, pass NULL as an argument or parameter instead, and allow NULLs in your database.

Peter Larsson
Helsingborg, Sweden

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-10-03 : 06:34:26

use the isnull or coalesce function in your sql code to return a "hard-coded" value instead of a null value

eg isnull(yourintval, 0) will return 0 if yourintval is null otherwise it will return yourintval if it is not null

Duane.

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-03 : 06:47:15

I think I tried passing NULL as a parameter but I couldn't get it to work for some reason.....perhaps I was actually passing the string "NULL" rather than a Null value.....I dunno.....anyway......

isnull sounds like a good option, could I use it with "NULL" being the other field? Something like this:

isnull(yourintval, '<NULL>')

....or something similar.....?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 06:54:52

Why don't you post the code you have, and we will comment and give suggestions.

Peter Larsson
Helsingborg, Sweden

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-03 : 07:09:05

Ok, the SQL statement is as follows:

Set RS = objConn.execute("INSERT INTO dbo.tContracts (ContractNo, ContractorID, OrderNo, SubCode, AreaCode, Type) VALUES ('" & ContractNo & "', '" & ContractorID & "', '" & OrderNo & "', '" & SubCode & "', '" & AreaCode & "', '" & ContractType & "')")

Obviously the variables are the values submitted from the form on the previous page. If, for example, OrderNo is left blank on the form, I would like to insert a NULL value into this field in the database (if possible?!).

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-03 : 07:12:58

ContractNo=request.form("ContractNo")
ContractorID=request.form("ContractorID")
OrderNo=request.form("OrderNo")
SubCode=request.form("SubCode")
AreaCode=request.form("AreaCode")
ContractType=request.form("Type")

That's all I used just to obtain the Values from the form and insert them into the variables....

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-03 : 07:14:49

IF OrderNo = "" THEN OrderNo = "NULL" ELSE OrderNo = "'" & OrderNo & "'"
Set RS = objConn.execute("INSERT ... () VALUES (" & OrderNo & ", ...)")

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

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-03 : 07:30:48

quote:
Originally posted by Lumbago

IF OrderNo = "" THEN OrderNo = "NULL" ELSE OrderNo = "'" & OrderNo & "'"
Set RS = objConn.execute("INSERT ... () VALUES (" & OrderNo & ", ...)")

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

This appears to submit the string "NULL" rather than an actual NULL value, as it produces an error saying it can't convert varchar to numeric (The OrderNo field in the database is a numeric field...forgot to mention that)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-03 : 07:33:42

If it's numeric it's alot easier (you can omit the ELSE) ->

IF OrderNo = "" THEN OrderNo = "NULL"
Set RS = objConn.execute("INSERT ... () VALUES (" & OrderNo & ", ...)")

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-03 : 07:38:58

When passing an unquoted NULL to the database NULL is actually "inserted" (nothing gets inserted) in the column of your choice...what I usually do in this scenario (which I rarely have) is something similar to this:

myNumber = Request.Form("myNumber")
myString = "'" & Request.Form("myString") & "'"

IF myNumber = "" THEN myNumber = "NULL"
IF Replace(myNumber, "'", "") = "" THEN myString = "NULL"

Set RS = objConn.execute("INSERT ... () VALUES (" & myNumber & ", " & myString & ")")

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

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-03 : 07:40:06

quote:
Originally posted by Lumbago

If it's numeric it's alot easier (you can omit the ELSE) ->

IF OrderNo = "" THEN OrderNo = "NULL"
Set RS = objConn.execute("INSERT ... () VALUES (" & OrderNo & ", ...)")

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

Unfortunately I already did omit the ELSE.....it still seems to submit it as a string though.....or at least that's the impression I get from the error it produces:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-03 : 07:40:58

Oh and one more thing...when doing an insert to the database there is no use for a recordset, it will just give you overhead. objConn.execute by itself should be plenty.

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

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-03 : 07:44:38

quote:
Originally posted by Lumbago

When passing an unquoted NULL to the database NULL is actually "inserted" (nothing gets inserted) in the column of your choice...what I usually do in this scenario (which I rarely have) is something similar to this:

myNumber = Request.Form("myNumber")
myString = "'" & Request.Form("myString") & "'"

IF myNumber = "" THEN myNumber = "NULL"
IF Replace(myNumber, "'", "") = "" THEN myString = "NULL"

Set RS = objConn.execute("INSERT ... () VALUES (" & myNumber & ", " & myString & ")")

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

ah, so would u use ("& myNumber &") in the SQL statement rather than ('"& myNumber &"') ...?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-03 : 07:45:10

I should probably advise you to use a completely different way of adding parameters (look up the ADO command-object) to your sql but my method will work if you use it properly. I suggest you assign your sql to a variable and print it out to see what is sent to the database. Alot easier to debug this way.

SQL = "INSERT ... () VALUES (" & OrderNo & ", ...)"
Response.Write(SQL)
Response.end
Set RS = objConn.execute(SQL)

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

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-03 : 07:45:31

quote:
Originally posted by Lumbago

Oh and one more thing...when doing an insert to the database there is no use for a recordset, it will just give you overhead. objConn.execute by itself should be plenty.

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

Yep, that's a good point....damn copy and paste!! haha

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-03 : 07:46:12

quote:
ah, so would u use ("& myNumber &") in the SQL statement rather than ('"& myNumber &"') ...?

Exactly

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

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-03 : 07:52:19

quote:
Originally posted by Lumbago

quote:
ah, so would u use ("& myNumber &") in the SQL statement rather than ('"& myNumber &"') ...?
Exactly

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

Yup, that worked

Thank you so much....and thanks for the other tips too, most useful

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 08:03:39

"Oh and one more thing...when doing an insert to the database there is no use for a recordset, it will just give you overhead."

Do you need the recordset to get the Errors collection?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 08:05:59

The Errors collection is stored under the connection variable, not the recordset variable.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 08:10:50

[code]ContractNo = "" & request.form("ContractNo")
If ContractNo = "" Then ContractNo = "NULL" Else ContractNo = "'" & ContractNo & "'"
ContractorID = "" & request.form("ContractorID")
If ContractorID = "" Then ContractorID = "NULL" Else ContractorID = "'" & ContractorID & "'"
OrderNo = "" & request.form("OrderNo")
If OrderNo = "" Then OrderNo = "NULL" Else OrderNo = "'" & OrderNo & "'"
SubCode = "" & request.form("SubCode")
If SubCode = "" Then SubCode = "NULL" Else SubCode = "'" & SubCode & "'"
AreaCode = "" & request.form("AreaCode")
If AreaCode = "" Then AreaCode = "NULL" Else AreaCode = "'" & AreaCode & "'"
ContractType = "" & request.form("Type")
If ContractType = "" Then ContractType = "NULL" Else ContractType = "'" & ContractType & "'"

Set RS = objConn.execute("INSERT INTO dbo.tContracts (ContractNo, ContractorID, OrderNo, SubCode, AreaCode, Type) VALUES (" & ContractNo & ", " & ContractorID & ", " & OrderNo & ", " & SubCode & ", " & AreaCode & ", " & ContractType & ")")[/code]
If any of the columns above is numeric, remove the ' before and after, and replace any comma with a dot.

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources