INSERT nullstring INTO

Printable View

Jan 27th, 2005, 07:00 AM
Dr_Michael

INSERT nullstring INTO

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.
Jan 27th, 2005, 07:01 AM
mendhak

Re: INSERT nullstring INTO

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.
Jan 27th, 2005, 07:03 AM
Dr_Michael

Re: INSERT nullstring INTO

Quote:

Originally Posted by 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.

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.
Jan 27th, 2005, 07:04 AM
Dr_Michael

Re: INSERT nullstring INTO

@Moderators: Please move the thread to the Clasic VB board or DB board. Nothing to do with ASP. My mistake, I apologise. :(
Jan 27th, 2005, 07:06 AM
mendhak

Re: INSERT nullstring INTO

Have you allowed NULLs when designing the table? Also, what is the error?
Jan 27th, 2005, 07:13 AM
Dr_Michael

Re: INSERT nullstring INTO

Quote:

Originally Posted by mendhak

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).
Jan 27th, 2005, 08:21 AM
Dr_Michael

Re: INSERT nullstring INTO

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.
Jan 28th, 2005, 12:07 AM
mendhak

Re: INSERT nullstring INTO

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].