[2005] is there a special VB/Access null value?

[Desolator144]

I remember something from college about there being some special null value that you have to use when writing to an Access database. Right now a bunch of text fields in the DB are Allow Zero Length: No. So when I try and send it this statement:

INSERT INTO [DOG DATA] ([OWNER ID],BREED,SEX,[SEX ENTRY],[CALL NAME],[DATE OF BIRTH],[PLACE OF BIRTH],BREEDER,SIRE,DAM, [DOG HEIGHT],[NADAC REG NUM],[NADAC DOG HEIGHT],[NADAC MEASURED JUMP HEIGHT]) VALUES (379,'border collie','Female','2','Taka','02/14/2001','','','','',21,'07-04215',21,'16')

it gives me an error about the value of those red ones being a zero length string. I'm using string variables to build the SQL statement's values right now. Is there some value I can assign them that it will accept like "NULL" or something? I recall using something like VBNULL or DBNULL or something a long time ago but nothing I've tried worked so far.

[.paul.]

try

nothing

[Shaggy Hiker]

DBNull

[Desolator144]

If I use nothing doesn't that wipe it from memory completely and you can't use the variable after that? And DBNull sounds more familiar but do I just do somestringvar = DBNull?

[jmcilhinney]

If you're writing a null value as a literal in an SQL statement then it's just NULL, with no double or single quotes. If you are inserting a value into a parameter or field in VB code then you want the DBNull.Value property.

[Desolator144]

ohhhh I gotcha. Hopefully Access isn't too retarded to take NULL. I dunno if it'll still consider that a zero length string but I'll find out. I really, really don't want to write a dozen if statements that remove that field from the insert statement if they're blank so hopefully it will work

[jmcilhinney]

NULL is not a zero length string and vice versa. In VB, is an empty string the same as Nothing? No it's not. One is an object containing no characters and the other is no object at all. The same is true in databases.

[Desolator144]

I'm pretty sure if I assigned a string variable nothing then appended it to my SQL statement string, it'd tear a hole in the universe. Either that or crash or append nothing. Either way, I addressed the little problem of needing single quotes when there is a value and needing them not there if it's null and now it works yay!

[jmcilhinney]

I did not say that you should assign Nothing anywhere. I was using the difference between Nothing and an empty string in VB to illustrate the difference between NULL and an empty string in a database. I already explained what you needed to do in post #5.