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

 

[johnwm]

Can you clarify exactly what datatype all your fields are?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[DotNetGnat]

jordan, lets do some debugging here by introducing response.write statements at every place:

sub Updatequestions ()

For each item in Request.Form()

[red][b]Response.write request.form(item)[/b][/red]

select case  left(request.form(item),1) 
    case ""
        
    case "Submit"
        
    case "Next"
        
    case "q"
    
    case request("physicianid")

case "1"
[red][b]Response.write "case 1 is selected" [/b][/red]

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

[red][b]Response.write item [/b][/red]
[red][b]Response.write res [/b][/red]
[red][b]Response.write physicianid [/b][/red]

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

[red][b]Response.write txtsql [/b][/red]


  case "2"      

[red][b]Response.write "case 2 is selected" [/b][/red]

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

 [red][b]Response.write txtsql [/b][/red]

case "3"

[red][b]Response.write "case 3 is selected [/b][/red]

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

[red][b]Response.write txtsql [/b][/red]

    end select

[red][b]Response.write "Actual query executed is:" [/b][/red]

[red][b]Response.write txtsql [/b][/red]

conn.Execute txtsql


next

[red][b]Response.write "going next..." [/b][/red]

-DNG

 

[jordan11]

Thanks for the replys,
I managed to get it to update by changing
by the way I got it to update by changing

select case left(request.form(item),1)
to
select case left(request.form(item),6)

but the problem I am having now is the check box causes the error message when you check more than one value.
Microsoft VBScript runtime error '800a000d'

Type mismatch: '[string: "1, 2, "]'

/career/questions66.asp, line 131

and line 131 is

select case left(request.form(item),6)


this is the part of the code that should get a total for check box box values for each question.

For each item in Request.Form()

select case left(request.form(item),6)

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 & "')"
conn.Execute txtsql


Also for some reason if the value for the check box . select box and radio button is over 4 it will not update it is only updating values 1-3.

thanks