Syntax error in INSERT INTO statement 1

[bjm027]

I am getting an error with the syntax of my Insert statement. I tried it with just a couple of fields first (this worked) and then when I added all of the fields I needed, it of course wont work now.
If anyone can see something wrong with my insert into statement please help.
I had wordwrap on when I pasted it.
Any Help appreciated.....


<%
Function Change(strDesc)
'This function replace a single apostrophe with a ^ so the field can be saved to the database.
Change = strDesc
Change = Replace(Change, &quot;'&quot;, &quot;^&quot
End Function
%>

<%
Dim Conn
Dim strSQL
SET Conn = server.createobject(&quot;ADODB.Connection&quot
Conn.Open &quot;MyDataBase&quot;

strSQL = &quot;INSERT INTO Survey(Social Activities, Other Types, Professional Development, Other Types 2, Community Involvement, Charities, In Mind for Future, Remove, Skills and Talents) VALUES ('&quot;+ Change(Request.Form(&quot;Social Activities&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Other Types&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Professional Development&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Other Types 2&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Community Involvement&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Charities&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;In Mind for Future&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Remove&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Skills and Talents&quot) +&quot;')&quot;

Conn.Execute(strSQL)

 

[onpnt]

are those +'s suppose to be &'s or are you using jscript?

_____________________________________________________________________
Please help! I'm falling asleep over here!

http://www.onpntwebdesigns.com

 

[onpnt]

I guess I should just say cahnge them sense you are using vbscript as your ASP language there.

_____________________________________________________________________
Please help! I'm falling asleep over here!

http://www.onpntwebdesigns.com

 

[bjm027]

I have been using the +'s and they work. Thats what I learned to use. But the code I posted above is pretty much all of the code I have on my surveysave.asp page is which i get the error.
???

 

[dmhirsch]

If it's a SQL error, you might try enclosing your field names in [square brackets], since the spaces may be causing problems. If it's an ASP syntax error, I dunno.

 

[bjm027]

I'll try the [square brackets].
I thought it might have to do something with the spaces but wasnt sure.
But ill give it a try and let you know.
Thanks.

 

[Veep]

Try
response.write(strSQL)
response.end()
and you'll no doubt see the problem.

 

[bjm027]

I tried the brackets and now got this erroe statement.

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/yep/testsurveysave.asp, line 56

strSQL = &quot;INSERT INTO Survey(Social Activities, Other Types, Professional Development, Other Types 2, Community Involvement, Charities, In Mind for Future, Remove, Skills and Talents) VALUES ('&quot;+ Change(Request.Form[&quot;Social Activities&quot;]) +&quot;', '&quot;+ Change(Request.Form[&quot;Other Types&quot;]) +&quot;', '&quot;+ Change(Request.Form[&quot;Professional Development&quot;]) +&quot;', '&quot;+ Change(Request.Form[&quot;Other Types 2&quot;]) +&quot;', '&quot;+ Change(Request.Form[&quot;Community Involvement&quot;]) +&quot;', '&quot;+ Change(Request.Form[&quot;Charities&quot;]) +&quot;', '&quot;+ Change(Request.Form[&quot;In Mind for Future&quot;]) +&quot;', '&quot;+ Change(Request.Form[&quot;Remove&quot;]) +&quot;', '&quot;+ Change(Request.Form[&quot;Skills and Talents&quot;]) +&quot;')&quot;

 

[bjm027]

oops...i have brackets around the names.

I dont know why they didnt show up.

 

[dmhirsch]

Hmmm... the original code looked good for the Request.Form stuff, but what I meant about brackets was on the SQL side, so:
strSQL = &quot;INSERT INTO Survey([Social Activities], [Other Types], [Professional Development], [Other Types 2], [Community Involvement], [Charities], [In Mind for Future], [Remove], [Skills and Talents]) VALUES ('&quot;+ Change(Request.Form(&quot;Social Activities&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Other Types&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Professional Development&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Other Types 2&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Community Involvement&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Charities&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;In Mind for Future&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Remove&quot) +&quot;', '&quot;+ Change(Request.Form(&quot;Skills and Talents&quot) +&quot;')&quot;

 

[bjm027]

dmhirsch,

Accept my applogy. I will try that. Its been a long day for me.

 

[bjm027]

dmhirsch,

Then I get this error. Well this is what comes up on the page when I submit my survey.

INSERT INTO Survey([Social Activities], [Other Types], [Professional Development], [Other Types 2], [Community Involvement], [Charities], [In Mind for Future], [Remove], [Skills and Talents]) VALUES ('', '', '', '', '', '', '', '', '')

 

[dmhirsch]

I hope it works! I like Veep's suggestion to look at the resulting SQL if it doesn't. FYI, if it's under your control, spaces in database field names are a not-particularly-good idea.

 

[dmhirsch]

OK, so that means that Request.Form doesn't have the objects in it that you are using. Double-check the name of the objects in the calling page... Now that I think about it, I don't think you can use spaces in object names in HTML?

 

[Veep]

You're right. That's a showstopper.

 

[intranetgeeky]

If you can, take out all of the spaces in the object and in the database. To differentiate between words, use upper cases, such as: SocialActivities. That might help out.

 

[Veep]

Hey, you know what? It's not a show stopper. I just tried it by naming a text box &quot;my text box&quot;. This came over on the post: my text box = This and that

Regardless, I'd never name my objects that way.

 

[bjm027]

I had a feeling the spaces might be the problem. I will go back and remove them. But I could of sworn that I tried it earlier with a couple of fields that had spaces and it worked. But like i said its been a long day.

I noticed when I used the brackets that it got the entry into the database but wouldnt pass any of the values. So all of the fields were blank, but a new line for a new entry in the table???

I dont know what to make of that.

 

[dmhirsch]

Interesting, Veep. BJM, the insert is not NOT failing, it's just inserting empty strings for all of the values.

 

[bjm027]

Veep,

Before I try the removing spaces, where would this statement go:

response.write(strSQL)
response.end()

...after i execute the sql???