Syntax error in INSERT INTO statement

[Jeff]

I've been trying to figure out what the problem is with
the following code. I've ruled all the fields out except
the CountryID field, which seems to be producing the
error. It's a numeric field, but I'm converting a country
name posted from a form into a numeric value in the
database.

<% if request.form("country") = "Belgium" then
countryid = 2
else
countryid = 5
end if

fp_sQry="INSERT INTO Retailers
(Username,Password,ContactFirst,ContactLast,Name,Address1,
Address2,City,Region,Postal,Tel,Fax,Email,Website,Newslett
er,CountryID) VALUES
('::Username::', ':assword::', '::ContactFirst::', '::Co
ntactLast::', '::Name::', '::Address1::', '::Address2::',
'::City::', '::Region::', ':ostal::', '::Tel::', '::Fax:
:', '::Email::', '::Website::', ::Newsletter::, ::CountryI
D:"

 

[jon spivey]

Hi,
do you only have 2 contryids, Belgium and anywhere else?
if so you could do
select * from table
where countryid= iif('::country::' = 'Belgium', 2, 5)
fill in the rest of your fields as needed.

IIF is a handy little function in access - well worth learning about

 

[jon spivey]

In that case I'd probably generate a dropdown of countries on your form page
like this
<select name="countryid">
<option value="2">Belgium</option>
etc.....
you could generate this from a database table if you have one or hard code
the countries/countryids. This way you can just do
insert into table(countryid) values:countryid:

 

[MD WebsUnlimited.com]

Hi Jeff,

I've assumed that you've added this code to the page in the hopes that it
will change the ::CountryID:: field value which it won't.

The SQL statement is modified by the intrinsic code to replace the
::FieldName:: values with the names of the form fields. For you to modify
that code you'll need to intercept the FP intrinsic code and do the replace
prior to FP doing it.

--
Mike -- FrontPage MVP '97 - '02
http://www.websunlimited.com
Our latest products "At Your Command" and IncludeASP
http://www.websunlimited.com/order/Product/AYC/ayc.htm
http://www.websunlimited.com/order/product/includeASP/includeASP.htm

 

[Guest]

Mike,

Okay, I think that makes sense. How do I do that?

Would I need to change the name of the form field or the
name of the database field, or is there another way. I've
removed all the "webbot" code, which allowed me to edit
the SQL statement.

Thanks in advance.

 

[Guest]

No, I actually have about 30 countries. I was only using
these two so I could make it work with a simplified "if
then" statement before using the full one.

 

[Guest]

That would be logical, except on two of the previous
pages, I needed to use the real name of the country to
display to the user, since a number for their country
would be irrelevant. I think my only solution here is to
make this page work after assigning values to each
country name. BTW, I've changed the field name "country"
in the form that posts to this page to "countryid" so
that everything is consistent. That still hasn't solved
it. The only other issue I can think of is the field
format in Access. Right now, it's set as "long integer"
with "no indexing" and "not required". I changed that
from having indexing before.

Any help is MUCH appreciated.

Jeff