Problem with the conn.execute str(SQL) statment.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Problem with the conn.execute str(SQL) statment.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-06 : 08:35:52

Sandra writes "Hi,
i am having problems updating my database after a user update the quiz questions for a quiz. Please help me as i am still a rookie. Your help is greatly appreciated. Following is the error message I got and my program. Please help me as i couldn't get any response from other forums. Please pandon me if the question sounds silly to u. Thanks a lot!
Error message:
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'question_number = '1'update quiz1 set answer_a = 'Defining the Problem' where question_number = '1'update quiz1 set answer_b = 'Generating Ideas' where question_number = '1'update quiz1 set answer_c = 'Day Dream' where question_number = '1'update quiz1'.

The line that this error is referring to:
conn.Execute(strSQL)

My program:

Option Explicit

Dim sql, rsQuiz1, question_number, question_text, answer_a, answer_b, answer_c, answer_d, answer_e, answer_f, correct_answer, notfilled(6), badflag, count, adminno
if Request.Cookies("admin") = "True" then

question_number = Request.QueryString("question_number")
adminno = Request.Form("adminno")

'Assign form values to variables
question_number = Request.Form("question_number")
question_text = Request.Form("question_text")
answer_a = Request.Form("answer_a")
answer_b = Request.Form("answer_b")
answer_c = Request.Form("answer_c")
answer_d = Request.Form("answer_d")
answer_e = Request.Form("answer_e")
answer_f = Request.Form("answer_f")
correct_answer = Request.Form("correct_answer")

'Check everything's been filled in, badflag determines whether error function is called
if badflag = 0 then

if question_text = "" then
notfilled(0) = "Question text"
badflag = 1
end if
if answer_a = "" then
notfilled(1) = "Answer A"
badflag = 1
end if
if answer_b = "" then
notfilled(2) = "Answer B"
badflag = 1
end if
if answer_c = "" then
notfilled(3) = "Answer C"
badflag = 1
end if
if answer_d = "" then
notfilled(4) = "Answer D"
badflag = 1
end if
if correct_answer = "" then
notfilled(5) = "Correct answer"
badflag = 1
end if

if badflag = 1 then
submiterror()
end if
end if
'Open connection and insert user details into the database



dim strSQL

'Don't split the line.
strSQL = "update quiz1 set question_text = '"&question_text&"' where question_number = '" &question_number & "'"
'Whole strSQL statement should be in one line. Same for all strSQL statements.
strSQL = strSQL + "update quiz1 set answer_a = '"&answer_a&"' where question_number = '" &question_number & "'"
strSQL = strSQL + "update quiz1 set answer_b = '"&answer_b&"' where question_number = '" &question_number & "'"
strSQL = strSQL + "update quiz1 set answer_c = '"&answer_c&"' where question_number = '" &question_number & "'"
strSQL = strSQL + "update quiz1 set answer_d = '"&answer_d&"' where question_number = '" &question_number & "'"
strSQL = strSQL + "update quiz1 set answer_e = '"&answer_e&"' where question_number = '" &question_number & "'"
strSQL = strSQL + "update quiz1 set answer_f = '"&answer_f&"' where question_number = '" &question_number & "'"
strSQL = strSQL + "update quiz1 set correct_answer = '"&correct_answer&"' where question_number = '" &question_number & "'"

conn.Execute(strSQL)

rsQuiz1.close
set rsQuiz1 = nothing
conn.close
set conn = nothing

Response.Redirect("editquestion1.asp?question_number="& question_number & "&updated=true")

else
Response.Redirect("admin.asp")
end if

Function submiterror()
<html>
<head>
<title>Edit Question</title>
</head>
<body bgcolor="#FFFFFF" link="#DD0000" vlink="#DD0000"
alink="#000000">
<font face="arial,helvetica" size=2>

Nazim
A custom title

1408 Posts

Posted - 2002-03-06 : 09:05:29

You need to work on your code a little .

strSQL = "update quiz1 set question_text = '"&question_text&"'
,answer_a='"&answer_a&"',answer_b='"&answer_b&"',answer_c='"&answer_c&"',answer_d='"&answer_d&"',answer_e='"&answer_e&"',answer_f='"&answer_f&"',correct_answer='"&correct_answer &"' where question_number = '" &question_number & "'"

HTH

--------------------------------------------------------------

Nazim
A custom title

1408 Posts

Posted - 2002-03-06 : 10:23:47

you could be better off using a stored procedure doing
this

Create Procedure Upd_quizinfo(@mQno int, @mquestion varchar(8000),@mqa char,@mqb char,@mqc char,@mqd char,@mqe char,@mcc char)
as
Set no count on
update quiz1 set question=@mquestion,answer_a=@mqa,answer_b=@mqb,answer_c=@mqc,
answer_d=@mqd,answer_e=@mqe,correct_answer=@mcc
where question_number=@mQno

Go

you calling String should be something like this.

strSQL = "exec Upd_Quizinfo "& '" &question_number & "'"&question_text&"','"&answer_a&"','"&answer_b&"','"&answer_c&"','"&answer_d&"','"&answer_e&"','"&answer_f&"','"&correct_answer&" '" &question_number & "'

HTH

--------------------------------------------------------------

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-06 : 10:42:19

and if you are using asp I would stay away from executing command directly from a connection. You are better off using a paramaterized stored procedure call with the command object.

Try this - (dont forget to include adovbs.inc")


Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = conn
cmd.CommandText = "Upd_quizinfo"
cmd.Parameters.Append cmd.CreateParameter("@mQno", adInteger, adParamInput)
cmd.Parameters.Append cmd.CreateParameter("@mquestion", adVarChar, adParamInput, 8000)
cmd.Parameters.Append cmd.CreateParameter("@mqa", adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@mqb", adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@mqc", adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@mqd", adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@mqe", adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@mqf", adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@mcorrect", adVarChar, adParamInput, 255)

cmd.Execute

I do this in VB Com all of the time and should be easily transferable to ASP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources