Syntax Error With ASP & SQL 1

[AllenRitch]

I have an HTM form passing values to an ASP page that creates a new record in an SQL database. And for some reason, I receive the following error after clicking the form’s submit button:

Database Errors Occured
-2147217913 : Syntax error converting the varchar value 'asp_qust17' to a column of data type smallint.

What could be causing this error? Below is more information on what I have.

All my form fields are radio buttons (frm_qust_1 through frm_qust_17) except for the final two which are a list menu (frm_qust_18) and text box (frm_cmmnts); total of 19 form fields. All the radio buttons contain one digit numbers as the value. My SQL database has a total of 20 fields (primary_key, db_qust1 through db_qust18 and db_cmmnts). The first field is an Autonumber (type int), the next 17 are of type smallint and the final two are varchar and text respectively. Below is the ASP code I’m using to assign and pass the form values. Please note that I’m not passing anything to first SQL field since it’s an Autonumber. Could this be causing the problem?

' Declare Form Variables
Dim asp_qust1, asp_qust2, asp_qust3, asp_qust4, asp_qust5, asp_qust6, asp_qust7, asp_qust8, asp_qust9, asp_qust10
Dim asp_qust11, asp_qust12, asp_qust13, asp_qust14, asp_qust15, asp_qust16, asp_qust17, asp_qust18, asp_commnts

' Assign Values
asp_qust1 = request.form("frm_qust_1&quot
asp_qust2 = request.form("frm_qust_2&quot
asp_qust3 = request.form("frm_qust_3&quot
asp_qust4 = request.form("frm_qust_4&quot
asp_qust5 = request.form("frm_qust_5&quot
asp_qust6 = request.form("frm_qust_6&quot
asp_qust7 = request.form("frm_qust_7&quot
asp_qust8 = request.form("frm_qust_8&quot
asp_qust9 = request.form("frm_qust_9&quot
asp_qust10 = request.form("frm_qust_10&quot
asp_qust11 = request.form("frm_qust_11&quot
asp_qust12 = request.form("frm_qust_12&quot
asp_qust13 = request.form("frm_qust_13&quot
asp_qust14 = request.form("frm_qust_14&quot
asp_qust15 = request.form("frm_qust_15&quot
asp_qust16 = request.form("frm_qust_16&quot
asp_qust17 = request.form("frm_qust_17&quot
asp_qust18 = request.form("frm_qust_18&quot
asp_commnts = request.form("frm_commnts&quot

' SQL Update Statement
SQL = "INSERT INTO tblFPA_Survey (db_qust1, db_qust2, db_qust3, db_qust4, db_qust5, db_qust6, db_qust7, "
SQL = SQL + "db_qust8, db_qust9, db_qust10, db_qust11, db_qust12, db_qust13, db_qust14, db_qust15, db_qust16, "
SQL = SQL + "db_qust17, db_qust18, db_commnts) VALUES ('asp_qust1', 'asp_qust2', 'asp_qust3', 'asp_qust4', 'asp_qust5', "
SQL = SQL + "'asp_qust6', 'asp_qust7', 'asp_qust8', 'asp_qust9', 'asp_qust10', 'asp_qust11', 'asp_qust12', 'asp_qust13', "
SQL = SQL + "'asp_qust14', 'asp_qust15', 'asp_qust16', 'asp_qust17', 'asp_qust18', 'asp_commnts')"

' Executes Data Update
Set RDSet = DBConn.execute(SQL)

 

[jsteph]

Are those the values you want to insert? 'Asp_qust2', etc?

It looks more like like you mean to insert the variables, ie:
sq = "VALUES ('" & ASP_QUST1 & "','" & ASP_QUST2 & '","...ETC

--JSTEPH

 

[AllenRitch]

jsteph, thanks a million! I think I would've been at this all day, but your suggestion did the trick.

Thanks again.

 

[AllenRitch]

After getting it to work, I found that if the user places an apostrophe in the comments field, I recieve an error like the one below.

Database Errors Occured
INSERT INTO tblFPA_Survey (db_qust1, db_qust2, db_qust3, db_qust4, db_qust5, db_qust6, db_qust7, db_qust8, db_qust9, db_qust10, db_qust11, db_qust12, db_qust13, db_qust14, db_qust15, db_qust16, db_qust17, db_qust18, db_commnts) VALUES ('0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '', 'Choose an area', 'Just an ex'ample of an apostraphe')
-2147217900 : Line 1: Incorrect syntax near 'ample'.
-2147217900 : Unclosed quotation mark before the character string ')'.

Is there anyway to work around this?

 

[jsteph]

Yes, you can 'escape' it by using double-single quote for every embedded single quote:

update blah, blah,...set somefield = 'Joseph O''Donnel' where....blah
--jsteph

 

[AllenRitch]

Thanks again jsteph. I actually ended up going with the onsubmit attribute of the form tag and used javascript to validate the field contents. However, I seem to have a problem with my response.redirect statement. The htm form uses frames. Because of this, the results or thank you page won't show up in a full window after the user submits the form data.

Normally I would use target="_parent", but I think the syntax is a little different in VBScript. Do you know how this would be written? I presently have the following:

response.redirect "

http://intranet/floract/thank_you.htm"

 

[jsteph]

Have you tried putting a function in the onclick of the forms button:

<input type=button name=thanks value=Finish onclick=showthanks()>

then is javascript on the page

function showthanks(){
window.open('

http://intranet/floract/thank_you.htm');

}

There are other options for the window.open method, but this is just a basic example.
--jsteph

 

[AllenRitch]

The user form only had two buttons. One to submit data and one to clear the form. And instead of using the response.redirect statement, added the following javascript to the bottom of my ASP page and it works great.

<script type=&quot;text/javascript&quot; language=&quot;JavaScript&quot;>
parent.location.href='

http://intranet/floract/thank_you.htm';

</script>

Thanks for your assistance.