Why am I getting this parsing error with my insert...???

[angelleye]

Here's my insert command:

INSERT INTO Customers (BillingName, BillingAddress1, BillingAddress2, BillingCity, BillingState, BillingZipCode, BillingCountry, PhoneNumber1, BillingCompanyName, PhoneNumber2, ShippingName, ShippingAddress1, ShippingAddress2, ShippingCity, Shipping State, ShippingZipCode, ShippingCountry, EmailAddress) VALUES (Andrew Angell, 805 E. 14th Ave., House, Lee's Summit, MO, 64030, United States, (816) 256-3866, Angell EYE Designs, (816) 256-3866, Andrew Angell, 805 E. 14th Ave., House, Lee's Summit, MO, 64030, United States, [email protected])

And I'm getting:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)

[DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Parse Error in SQL

Any info on this would be great. thanks!

Edited April 28, 2006 by Guest

[angelleye]

Nevermind I figured it out...sorry..

[IdealData]

Forgive my ignorance as I have no experience in ODBC, however just some comments on your sample data:

The field "Shipping State" has a space in the field name, however all the others do not. Do SQL fields permit a space?

There's an apostophe in "Lee's", could this be interpreted as a delimiter?

I checked the FMP help and the sample INSERT command shows text based fields as 'text', thereby using the single quote (apostrophe) as a delimiter. Numbers have no delimiters.

You sample data appears to have no delimiters, except in "Lee's", so I think each of your VALUES needs to be quoted in single quotes. I don't know how you handle the apostrophe in "Lee's"

[IdealData]

Just for interest, how do you handle the "Lee's" entry?

[angelleye]

Everything you said is correct. The space is a killer. I got this Filemaker setup from a client and I've been changing the names of all their bad fields...I missed that one and didn't even realize for a little while. You're also correct about the single quotes surrounding text values in insert commands. That was my main problem.

As for the apostrophy in Lee's, it seems to be taking care of itself as it's populated. I'm really not sure if it's the single quotes around it that keep it active or what, but I'm using ASP VBScript to generate this with the following:

insertCustomerSQL = "INSERT INTO Customers (BillingName, BillingAddress1, BillingAddress2, BillingCity, BillingState, BillingZipCode, BillingCountry, PhoneNumber1, BillingCompanyName, PhoneNumber2, ShippingName, ShippingAddress1, ShippingAddress2, ShippingCity, ShippingState, ShippingZipCode, ShippingCountry, EmailAddress) VALUES ('"&billingName&"', '"&billingAddress&"', '"&billingAddress2&"', '"&billingCity&"', '"&billingState&"', "&billingPostalCode&", '"&billingCountry&"', '"&billingPhoneNumber&"', '"&billingCompany&"', '"&shippingPhoneNumber&"', '"&shippingName&"', '"&shippingAddress&"', '"&shippingAddress2&"', '"&shippingCity&"', '"&shippingState&"', '"&shippingPostalCode&"', '"&shippingCountry&"', '"&emailAddress&"')"

Of course, all of those variables are populated above the insert.