Syntax error in an UPDATE statement 1

[codehead]

Hello All:

I can't seem to get the syntax correct on an UPDATE statement. The "pkey" value is an integer and the statement runs o.k. with an integer value, but not with my "request" statement nested in it.

updateStmt = conn.ExecuteSQL("UPDATE REQ_REQUIREMENTS SET ASSIGNED_POC = '"&request("assigned_poc")&"' WHERE pkey = "&request("pkey")&"")

What am I doing wrong?

Thanks!

 

[DotNetGnat]

how about this:

updateStmt = conn.ExecuteSQL("UPDATE REQ_REQUIREMENTS SET ASSIGNED_POC = '"&request("assigned_poc")&"' WHERE pkey = "&request("pkey"))

-DNG

 

[Creto]

For starters, you should specify what request objection you're pulling your variables from (form/querystring/etc).

Secondly, you've got an extra double quote at the very end.

Third, try response.write'ing the sql statement out and seeing if your sql server has any problems with it.

 

[Creto]

What DNG said instead of my second point... dang phone calls mid reply messing up my though process.

 

[codehead]

Hi DNG,

Don't you need a " to match the " at the ("UPDATE ?

 

[DotNetGnat]

nope you dont need it...

your query string should look something like this...

sql="update mytable set myfield='"&blah&"' where myid="&request("formid")

-DNG

 

[DotNetGnat]

BTW that last quote before ampersand sign(&) will account for the first opening quote...

-DNG

 

[codehead]

Hi Creto,

I'm requesting my variable from a form.

What I'm trying to figure out is how to pass "pkey" as an integer (I think it's passing through a character). Or, maybe it's just easier to change my data field to a VARCHAR2.

 

[DotNetGnat]

if pkey is integer then

updateStmt = conn.ExecuteSQL("UPDATE REQ_REQUIREMENTS SET ASSIGNED_POC = '"&request("assigned_poc")&"' WHERE pkey = "&request("pkey"))

if pkey is char then

updateStmt = conn.ExecuteSQL("UPDATE REQ_REQUIREMENTS SET ASSIGNED_POC = '"&request("assigned_poc")&"' WHERE pkey = '"&request("pkey")&"' ")

observe the quotes carefully

-DNG

 

[codehead]

DNG,

I get a "500" error. (ORA-00921:_unexpected_end_of_SQL_command)

 

[DotNetGnat]

i mean in the end we want our query to look something like this...

if pkey is integer then

WHERE pkey = 10

if pkey is char then

WHERE pkey = '10'

hope that clears the point...

-DNG

 

[DotNetGnat]

which one did you use...one with single quotes or one without...

also do a response.write on your sql query and post it here...

-DNG

 

[codehead]

I have to use the integer one:

updateStmt = conn.ExecuteSQL("UPDATE REQ_REQUIREMENTS SET ASSIGNED_POC = '"&request("assigned_poc")&"' WHERE pkey = "&request("pkey"))

because I'm using an auto-increment trigger.

What is the syntax for the response.write on my sql query?

Thanks.

 

[DotNetGnat]

you do this...

Response.Write ("UPDATE REQ_REQUIREMENTS SET ASSIGNED_POC = '"&request("assigned_poc")&"' WHERE pkey = "&request("pkey"))

or

sql= ("UPDATE REQ_REQUIREMENTS SET ASSIGNED_POC = '"&request("assigned_poc")&"' WHERE pkey = "&request("pkey"))

Response.Write sql

post the output here..

-DNG

 

[codehead]

UPDATE REQ_REQUIREMENTS SET ASSIGNED_POC = 'POC NAME' WHERE pkey =

Hmmm....looks like I'm getting a NULL for a pkey.....

 

[DotNetGnat]

thats the culprit...

may be you need to do...request.form("pkey") or request.querystring("pkey") to be specific...of course that may not be the reason...

just check why the pkey value is empty...

-DNG

 

[DotNetGnat]

were you able to get this problem fixed...

-DNG

 

[codehead]

Not yet. I'm trying to figure out why my "pkey" value isn't transfering from one page to the next.

Thanks.

 

[DotNetGnat]

post some of your related code...may be some here can help you out...

-DNG

 

[codehead]

Well, this is taking the "pkey" value passed from a prior page and pulling up the name of the current page:

set RequestName = conn.CreateDynaSet(_
"Select REQUEST_NAME from REQ_REQUIREMENTS where pkey= '"&Request.QueryString("pkey")&"'", Cint(0))


Then, I'm trying to pass the same "pkey" value on to the next page, to use it to store some data from the form on the current page. I just tried to put the "pkey" into a "hidden" form element, but that didn't work either:

<input name="pkey_value" type="hidden" value="<%=RequestName.Fields("pkey")%>">