Insert problem

[jordan11]

Hi everyone,

I am getting this error message

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

But not sure why when there seems to be the right amount this is my code now.

sub Updatequestions ()

For each item in Request.Form()

select case left(request.form(item),1)
case ""

case "Submit"

case "Next"

case "q"

case request("physicianid")

case 1

arr = split(request.form(item),",")
for cnt = 0 to ubound(arr)
'response.Write arr(cnt) & "<br>"
res = res + cint(arr(cnt))
next


txtsql ="INSERT INTO answers (questionid,ID,value)"_
& "VALUES ('" & right(item,len(item) - instr( item,"." )) & ","_
& trim(request("id")) & ","_
& res & "')"



case 2
txtsql ="INSERT INTO answers (questionid,ID,value)"_
& "VALUES ('" & right(item,len(item) - instr( item,"." )) & ","_
& trim(request("id")) & ","_
& right(request.form(item), 2) & "')"


case 3
txtsql ="INSERT INTO answers (questionid,ID,value)"_
& "VALUES ('" & right(item,len(item) - instr( item,"." )) & ","_
& trim(request("id")) & ","_
& right(request.form(item), 2) & "')"




end select

conn.Execute txtsql


next



Session("physicianID") = Request.Form("PhysicianID")

Response.Redirect "physicianpreference.asp?ID=" & trim(session("PhysicianID"))


end sub
%>

</body>
<!--#include file="disconnect.asp" -->
</html>



Thanks

 

[Kendel]

Why don't you response.write your txtsql to see what it looks like?

 

[DotNetGnat]

yes i would suggest you to response.write your sql statement...

also ID and value are reserved words so use square parens around them..something like [ID] and [value]

-DNG

 

[jordan11]

I get this error when I response .write

Microsoft OLE DB Provider for ODBC Drivers error '80040e0c'

Command text was not set for the command object.

 

[DotNetGnat]

did you correctly do:

response.write txtsql

-DNG

 

[Kendel]

Before you execute it, do a response.write like this:

response.write(txtsql)
conn.Execute txtsql

 

[jordan11]

yes that is what I did


sql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue) " _
& "VALUES ('" & right(item,len(item) - instr( item,"." )) & ","_
& trim(request("physicianid")) & ","_
& res & "')"



case 2
sql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue)"_
& "VALUES ('" & right(request.form(item), 2) & ","_
& trim(request("physicianid")) & ","_
& right(request.form(item), 2) & "')"


case 3
sql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue)"_
& "VALUES ('" & right(item,len(item) - instr( item,"." )) & ","_
& trim(request("physicianid")) & ","_
& right(request.form(item), 2) & "')"




end select
response.write(sql)
conn.Execute sql
response.end

 

[DotNetGnat]

is your case variable getting populated correctly..what happens is..if the case variable is empty then your sql string will be empty causing that error you posted...

-DNG

 

[jordan11]

I think that is what the problem is as i created another asp page to see which results I am getting and everthing I need to update is there.
Does anyone have any suggestion on how I can capture the information and then update as I do not mind changing the method I am using The information I am capturing are from a multiple checkbox(1), selectbox(2) and radio buttons(3).

Thanks for all the help

 

[Creto]

Possible problem...

Add the bold parts... (if you can't see em cause it's hard to see the single quotes, look for em before and after each item, they're there)


sql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue) " _
& "VALUES ('" & right(item,len(item) - instr( item,"." )) & "','"_
& trim(request("physicianid")) & "','"_
& res & "')"



case 2
sql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue)"_
& "VALUES ('" & right(request.form(item), 2) & "','"_
& trim(request("physicianid")) & "','"_
& right(request.form(item), 2) & "')"


case 3
sql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue)"_
& "VALUES ('" & right(item,len(item) - instr( item,"." )) & "','"_
& trim(request("physicianid")) & "','"_
& right(request.form(item), 2) & "')"


What was happening was you were telling it to update 3 columns and then with your single quote, it was treating everything you were passing as only one column.

 

[jordan11]

ok i will give that a try

Thank you

 

[jordan11]

I made those changes and the error message i got was

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'PhysicianId' to a column of data type int.

/career/questions6.asp, line 174

this was my revised code

sub Updatequestions ()

For each item in Request.Form()

select case left(request.form(item),1)
case ""

case "Submit"

case "Next"

case "q"

case request("physicianid")

case "1"

arr = split(request.form(item),",")
for cnt = 0 to ubound(arr)
'response.Write arr(cnt) & "<br>"
res = res + cint(arr(cnt))
next


txtsql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue) " _
& "VALUES ('" & right(item,len(item) - instr( item,"'.'" )) & "','"_
& trim(request("physicianid")) & "','"_
& res & "')"



case "2"
txtsql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue)"_
& "VALUES ('" & right(request.form(item), 2) & "','"_
& trim(request("physicianid")) & "','"_
& right(request.form(item), 2) & "')"


case "3"
txtsql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue)"_
& "VALUES ('" & right(item,len(item) - instr( item,"'.'" )) & "','"_
& trim(request("physicianid")) & "','"_
& right(request.form(item), 2) & "')"




end select

conn.Execute txtsql


next

 

[DotNetGnat]

try this:

txtsql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue) " _
     & "VALUES ('" & right(item,len(item) - instr( item,"'.'" )) &  "', "_
     & " trim(request("physicianid")) & ", "_
     & '" & res & "')"

change at all the places
-DNG

 

[DotNetGnat]

oops...missed quotes and ampersand...try this:

txtsql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue)" _
     & " VALUES ('" & right(item,len(item) - instr( item,"'.'" )) &  "',"_
     & " "&trim(request("physicianid"))&","_
     & " '" & res & "')"

-DNG

 

[jordan11]

I am still getting the error message

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'PhysicianId' to a column of data type int.

could the problem be coming from somewhere else

 

[jordan11]

even when i hard code the physicianid to 128 i still get


Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'PhysicianId' to a column of data type int.

/career/questions6.asp, line 174

 

[DotNetGnat]

oh your physicianid is a string...try this:

txtsql ="INSERT INTO physiciananswers (questionid,physicianID,pselvalue)" _
     & " VALUES ('" & right(item,len(item) - instr( item,"'.'" )) &  "',"_
     & " '"&trim(request("physicianid"))&"',"_
     & " '" & res & "')"

when you hardcode it...use '128' instead of 128

-DNG

 

[jordan11]

no physicinid is int

 

[DotNetGnat]

ok...sorry..i am confused now...
when you write out...does your sql string look like this..

INSERT INTO physiciananswers (questionid,physicianID,pselvalue) VALUES ('111', 128, 'somevalue')

-DNG

 

[jordan11]

yes