Null Field

[svsuthar1]

I have a check on my Action form where it checks for the field to be not 0 to add to the database... is it possible to be Null... I am not haveing any luck on that...I have tried the following things...

not ISnull(field) and field <> ""

please advise

thanks,

Samir

 

[Chopstik]

You want to make certain that the field is neither null nor empty and, if so, then to do something? If so, then you could simply say what you have above.

if not isnull(field) and field <> "" then
  'do something here
end if

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[svsuthar1]

I don't want to check for both..., but I have tried that code but I am still getting Type mismatch string "" error at my sql.

Samir

 

[svsuthar1]

It works fine when onload the value is set at 0, but I would rather have nothing there.

 

[Chopstik]

I'm sorry, but I am not sure I understand exactly what it is you are hoping to achieve. Could you be more specific? Perhaps show more of the relevant code along with an explanation?

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[svsuthar1]

The code below is where I am checking for strQuantity to <> "".. It works fine when Quantity field pre-filled with 0 then it works fine... but I don't want that field to be pre-filled with 0 it looks tacky...

If strQuantity <> "" Then
SQL = "Insert into tblInventoryData (fldProductID, fldPrice, fldQuantity, fldActionID, fldDate) values (" _
& "'" & strProductID & "', " _
& "'" & strPrice & "', " _
& "'" & strQuantity & "', " _
& "'" & strAction & "', " _
& "'" & strDate & "')"
objConn.Execute(SQL)

 

[pkailas]

in your database, are you allowing null values?

 

[svsuthar1]

Yes.. I have tried this with both allow and not allow, but it still giving the error...I am not sure if you know or not, but strQuantity comes from a from... that is assigned to this variable.

Samir

 

[Genimuse]

SQL understands NULL just fine, so you'd just use:

If strQuantity <> "" Then     
        SQL = "Insert into tblInventoryData (fldProductID, fldPrice, fldQuantity, fldActionID, fldDate) values (" _            
            & "'" & strProductID & "', " _
            & "'" & strPrice & "', " _
            & "NULL, " _
            & "'" & strAction & "', " _
            & "'" & strDate & "')"        
        objConn.Execute(SQL)

Don't put single quotes around NULL because that just makes it a string of N U L L. Just the simple NULL and your database will understand it (as noted, the field must allow null values in the database).

 

[Genimuse]

Oops, that first line should be

If strQuantity [b][COLOR=red]=[/color][/b] "" Then

 

[svsuthar1]

I don't want to save null values... your code indicates I want to save null values...

 

[Genimuse]

And to clarify, you actually don't need to check the value of strQuantity until you're ready to assemble that bit of the SQL, like this:

SQL = "Insert into tblInventoryData (fldProductID, fldPrice, fldQuantity, fldActionID, fldDate) values (" _            
& "'" & strProductID & "', " _
& "'" & strPrice & "', "
[b]If strQuantity = "" Then
    SQL = SQL & "NULL, "
Else
    SQL = SQL & "'" & strQuantity & "', "
End If[/b]
SQL = SQL & "'" & strAction & "', " _
& "'" & strDate & "')"

 

[Chopstik]

So if it is null, what do you want it to do?

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[Genimuse]

Ok, then count me in with the group that doesn't understand what you want to do. Can you try explaining it from scratch, along the lines of "If strQuantity isn't empty then I want to do ______. If strQuantity is empty then I want to do ______.

 

[svsuthar1]

Not to save it to the table. If the value of that field is NULL or 0 don't save all the fields to the database.

Samir

 

[Chopstik]

Not to save that field or not to save anything at all?

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[pkailas]

Ok, what do you WANT to have stored? I take it if the quantity is 0 you don't want to insert anything, right? If quantity is zero then strQuantity <> "" won't test for that.

I guess we're all confused at what this data is going to look like. Could you provide some recordsets that you want stored and some possible records that you want ignored.

 

[svsuthar1]

I am sorry If i sounded like an ass... I am glad that you are helping, I am sorry if i did sound like an ass.

Here is what I want....

I don't want it to do anything if the value is NULL or 0, but wanted to save the information if the value is greater then 0 The code below is what I have.

Dim iLoop
For iLoop = 0 To iCount
strProductID = Request(iLoop & ".ID")
strPrice = Request(iLoop & ".Price")
strQuantity = Request(iLoop & ".Quantity")
strAction = Request(iLoop & ".Action")
strDate = Request(iLoop & ".Date")

If strQuantity <> NULL Then
SQL = "Insert into tblInventoryData (fldProductID, fldPrice, fldQuantity, fldActionID, fldDate) values (" _
& "'" & strProductID & "', " _
& "'" & strPrice & "', " _
& "'" & strQuantity & "', " _
& "'" & strAction & "', " _
& "'" & strDate & "')"
objConn.Execute(SQL)
End If
Next

 

[Genimuse]

Ok, so if the value of strQuantity is "" then you don't want to insert a record at all, but if it has something then you want to insert the record.

What is not working with the code you posted. The

If Not IsNull(strQuantity) And strQuantity <> "" Then
    'Do the insert here
End If

thing should be working just fine. What's not working?

 

[Chopstik]

Try this:

Dim iLoop
For iLoop = 0 To iCount
    strProductID = Request(iLoop & ".ID")
    strPrice = Request(iLoop & ".Price")
    strQuantity = Request(iLoop & ".Quantity")
    strAction = Request(iLoop & ".Action")
    strDate = Request(iLoop & ".Date")
    
    If [COLOR=red]not isNull(strQuantity) and strQuantity <> 0[/color] Then     
        SQL = "Insert into tblInventoryData (fldProductID, fldPrice, fldQuantity, fldActionID, fldDate) values (" _            
            & "'" & strProductID & "', " _
            & "'" & strPrice & "', " _
            & "'" & strQuantity & "', " _
            & "'" & strAction & "', " _
            & "'" & strDate & "')"        
        objConn.Execute(SQL)
    End If    
Next

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous