INSERT nullstring INTO

[Dr_Michael]

I execute an SQL statement "INSERT INTO ... VALUES ...", but when one of the values is null string ("") the SQL fails. What can I do for this?
I am using VB6, ADO 2.7 and Access 2000.

[mendhak]

You can show us your SQL INSERT statement and tell us about the column involved, any constraints it may have, and other relevant stuff like that.

[Dr_Michael]

You can show us your SQL INSERT statement and tell us about the column involved, any constraints it may have, and other relevant stuff like that.

strSQL = "INSERT INTO TableName (GRCode, Name, AFM, Phone, Address, Date1, Bills, Amount1, Amount2, Amount3, Date2, Phones, YN, InvoiceNo, Date, Debt, Paid) VALUES ('" & strGR & "','" & strName & "','" & strAFM & "','" & strPhone & "','" & strAddress & "','" & strDate1 & "','" & strBills & "','" & strAmount1 & "','" & strAmount2 & "','" & strAmount3 & "','" & strDate2 & "','" & strPhones & "','" & strYN & "','" & strInvoiceNo & "','" & strDate & "','" & strDebt & "','" & strPaid & "')"

The SQL statement works fine if none of the values are null strings. Whenever it meets a null string it fails.
All the fields of the db table, are TEXT.

[Dr_Michael]

@Moderators: Please move the thread to the Clasic VB board or DB board. Nothing to do with ASP. My mistake, I apologise.

[mendhak]

Have you allowed NULLs when designing the table? Also, what is the error?

[Dr_Michael]

Have you allowed NULLs when designing the table? Also, what is the error?

Yes I have allowed NULLs in design of all fields.

The error is:
"Syntax Error in INSERT INTO statement"

And in debug window I printed the strSQL:

INSERT INTO TableName (GRCode, Name, AFM, Phone, Address, Date1, Bills, Amount1, Amount2, Amount3, Date2, Phones, YN, InvoiceNo, Date, Debt, Paid) VALUES ('GR00000053','A MENARINI','094322455','2109932416','Ë.ÂÏÕËÉÁÃÌÅÍÇÓ 575 TK16451 ÁÑÃÕÑÏÕÐÏËÇ','08/11/1999','1','585,3800','321,9800','263,4000','','2109944950;2109944951;2109944952','N','4321614','31/10/2004','263.40','0.00')

It fails in the null string (indicated with bold).

[Dr_Michael]

Resolved: I had a field with name DATE which conflicts with the DATE function of VB. I changed its name to BILLDATE and now it works no matter if there is null string or not.

[mendhak]

Ah, I hadn't seen that. IN case you wanted to continue using Date, the general practice is to enclose the fieldname with square brackets, like [Date].