Null values in UPDATE queries causing errors

[mavalon]

I have an access database. I set all fields to "not required" and "allow zero length". However, when the form fields are empty, my SQL statement runs into an error. How do I code it so that it "understands" that null is the value being entered. Currently, I'm using this:

Dim minamt = Request.Form("amt&quot
Dim intVID = Request.Form("vid&quot

set objRS = Server.CreateObject("ADODB.Recordset&quot
strSQL = "UPDATE tblLoans SET " & _
"MinAmount = " & Minamt & _
"WHERE vid = " & intVID
objRS.Open strSQL, strConn, adOpenDynamic, adLockPessimistic
'objRS.close
set objRS = nothing

It works as long as long as I enter something into the form fields. If nothing is entered into the "amt" field, the strSQL is parsed as "UPDATE tblLoans SET MinAmount = WHERE vid = [somenumber]" which obviously causes an error. How do I get around this?

 

[Chopstik]

What data type are you using? Just a guess, but am thinking that if it is set to Number, can you have a <null> in that field? If so, could you set it to 0 (or whatever else you might want) as a default if there is no value otherwise being input? HTH

Everything is absolute. Everything else is relative.

 

[mavalon]

The errors occur when it's Number or perhaps anything other than a string (i.e., without quotes surrounding it). If the field is blank, i don't want any default value. I did have a check that went as follows:

If IsEmpty(minamt) Then
minamt = 0
End If

..but this is not really what I want. Also, I have hundreds of fields in the entire application, and I would hate to have to use this for every field every time I want to update a record.

 

[Chopstik]

Try this:

Set the default value in the design view of your Access table to Null.

Everything is absolute. Everything else is relative.

 

[onn99]

I'm afraid that without the check you will always have that problem for SQL. you need to put something there which in your case will need to be 0. I had the same problem, in my case I put blah = &quot;NULL&quot;. SQL doesn't seem to like null values even though it allow it. Oh by the way are you using SQL server or just access dba or something similar?

 

[mavalon]

That didn't work. I really believe the problem is that when there is no value in a field, my sql statement is, in fact, invalid:

&quot;UPDATE tblLoans SET MinAmount = WHERE vid = [somenumber]&quot;

(Notice i'm setting MinAmount to Where...)

 

[mavalon]

I'm using Access.

 

[Genimuse]

Unfortunately you really will have to make those checks everywhere. That is, when SELECTing you have to make certain everything in the resulting SQL statement is valid, and warn the user that a value is required when it is. When performing INSERTs and UPDATEs if the database allows nulls in the field then your SQL literally has to insert NULL (not in quotes).

Coding is 10% coming up with the clever logic for the page and 90% writing error- and bug-catching code, unfortunately.

 

[Chopstik]

I assume that you are trying to update more than just this single field? If not, why not simply avoid doing the update if there is no data in the field to be updated?

Another thought might be going back to your conversion process. Possibly create a function that you can access anywhere in your application like you showed up above.

function ConvertThis(minamt)
If IsEmpty(minamt) Then
minamt = 0
End If
end function

Then, in your SQL code, do like this:

strSQL = &quot;UPDATE tblLoans SET &quot; & _
&quot;MinAmount = &quot; & ConvertThis(Minamt) & _
&quot;WHERE vid = &quot; & intVID

No way to test this, nor am I certain this is the most efficient, but could be a start.

Everything is absolute. Everything else is relative.

 

[Genimuse]

Since strings need the single-quotes if you're not inserting a null, but need no quotes at all if you're inserting a null ('NULL' is a string, while NULL is null), you'd probably want either a few functions or one with a flags, like this one:

'ValToCheck is the value it should replace
'StringFlag should be set to True if it's a string value,
'  False if it's not
Function FixNulls(ValToCheck, StringFlag)
  If StringFlag Then
    If IsEmpty(ValToCheck) Then
      FixNulls = "NULL"
    Else
      FixNulls = "'" & ValToCheck & "'"
    End If
  Else
    If IsEmpty(ValToCheck) Then
      FixNulls = "NULL"
    Else
      FixNulls = ValToCheck
    End If
  End If
End Function

Then you'd simply replace, for example,

 MinAmt

with

 FixNulls(MinAmt, False)

.

This can't be used in a WHERE clause, though, because then things get more complex. You can't say

 = NULL

, you have to say

 IS NULL

(and

 IS NOT NULL

instead of

 <> NULL

), and you certainly can't say things like

 > NULL

.

Lots of time coding error stuff. Lots and lots and lots.

This quick function is off the top of my head, mind you. There may well be a more expedient way to do this.

 

[mavalon]

The function works fine.

Thanks.

(now, if only I could figure out how to update checkboxes! But, i guess that'll be my next thread.)