SQL insert date problem

[ermingut]

I have SQL on Access database like this:

insert into customer (id, name, date) values (12,'Max', #12/12/1999#)

Actually I have more complicated SQL string but my problem is here.

With this SQL string I want to insert record with date is null. So I generate next sql sentence:

insert into customer (id, name, date) values (12,'Max', ##) which (of course) return error.

What should I do?

Thnx, Ermin

[Bigley]

Have you tried using empty single quotes or even the keyword NULL?

I'm not sure but one of these might work.

[RogerH]

Keyword NULL works (at least with Access).

Roger

[ermingut]

Yes, null works.

My SQL looks like this:

"insert into table (ID, dat) value (12, #" & myDate & "#)"

So I changed myDate string:

if len(mydate)=0 then
myDate="null"
else
mydate="#" & mydate & "#"
end if

an sql string looks like this:

"insert into table (ID, dat) value (12," & myDate & ")"

and it works.

Thanx anyway!

Ermin

[JohnAtWork]

You do realize that you're passing a text string named null and not an actual null value?

Just calling your attention to it; it might not matter for you in these circumstances.

If you want to do it properly, I think you would need the statement to read:

myDate = Null

[Mongo]

Perhaps I'm missing something; does not "insert into customer (id, name) values (12,'Max')" leave unreferenced fields empty?

[JHausmann]

Depends on the DBMS. Access, for instance, will assign default values for those fields that have them...

[Chris]

why not using the VbNull.

[ermingut]

That wasn't really my problem, which I already solved.

I had very, very long insert sql sentence and I yust set string

if len(mydate)=0 then
myDate="null"
else
mydate="#" & mydate & "#"
end if

because sometimes I need date and sometimes I don't and I have to use only one sql statement.

Finally my sql loks like this:

insert into table (id, dat) values (12," & myDate & ")"


If myDate is null:

insert into table (id, dat) values (12, null)

if myDate is OK:

insert into table (id, dat) values (12, #12-jan-2000#)


[This message has been edited by ermingut (edited 02-21-2000).]

[This message has been edited by ermingut (edited 02-21-2000).]

[VorTechS]

The best answer is to build your SQL statement in an appropriate manner, setting variables as necessary.

ie

Dim strSQL as string
Dim myDate as date

IF <whatever your criteria is to set myDate> then
mydate = <your value>
endif

strSQL = "INSERT INTO <mytable> (id, dat) VALUES ("
strSQL = strSQL & lngCustomerID
strSQL = strSQL & ", " & iif(myDate > nothing, "#" & mydate & "#","NULL")
strsql = strsql & ")"

dbMyDatabase.execute(strsql, dbfailonerror)

Hope this helps..
VorTechS

[ermingut]

I don't know how could I miss IIF command. I didn't try it jet, but as I can see in your code, it is exactly command I've been looking for.

Thanks, Ermin

[JHausmann]

Use IIF sparingly, if at all. It is significantly slower than any alternative, it always evaluates the entire statement and if any part of the statement fails, the whole statement will fail (even if the part that fails isn't being used).

I would do Vortech's code, without using IIF, like:

Dim strSQL as string
Dim myDate as string

myDate=vbNull

IF <whatever your criteria is to set myDate> then
myDate = <your value>
endif


strSQL = "INSERT INTO <mytable> (id, dat) VALUES ("
strSQL = strSQL & lngCustomerID
strSQL = strSQL & ", " &
if isnull(myDate) then
strSQL = strSQL & "NULL"
else
strSQL = strSQL & "#" & mydate & "#"
endif
strsql = strsql & ")"

[JohnAtWork]

As an FYI:

In order to use Null in VB, the variables must be declared as a Variants.

Pg. 109 in Microsoft Visual Basic 6.0 Programmer's Guide.

[JHausmann]

As an FYI:

In order to use Null in VB, the variables must be declared as a Variants.

Pg. 109 in Microsoft Visual Basic 6.0 Programmer's Guide.



Same behavior in VB5. The use of the constant vbNull sets the string value to "1", which is not what was intended (although the code and SQL should work. Instead of the date field having null as a value you'd have "1" or a valid date value).