Problem: sql-Select with request.form

[leifoet]

application 1:

<form name = "list" onSubmit = "return validation (this);" METHOD = "POST" ACTION = "application2.asp"

Application2 => 'receives':

request.form ("ActID")

the value of request.form ("ActID") is (for example) 5

lngRecordNo = Request.Form("ActId")

This SQL works perfectly

StrSQL = "SELECT * FROM table_H INNER JOIN activity21 ON activity21.actID = table_H.actAnId WHERE ActId = 5 and membernr =" & Session ("uid")

if I replace 5 with the variable lngRecordNo i get an error message

StrSQL = "SELECT * FROM table_H INNER JOIN activity21 ON activity21.actID = table_H.actAnId WHERE ActId =" & lngRecordNo & "and membernr =" & Session ("uid")

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ActId= and membernr=13'.

I also tried replacing the variable lngRecordNo with Request.Form("ActId") => same problem

Thanks for tips on how to fix this.

---
have now tested this :
response.write Request.Form("ActId") => result = 5
response.write isnumeric(Request.Form("ActId")) => true

 

[GriffMG]

You are missing a space before "and membernr"

StrSQL = "SELECT * FROM table_H INNER JOIN activity21 ON activity21.actID = table_H.actAnId WHERE ActId =" & lngRecordNo & " and membernr =" & Session ("uid")

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[leifoet]

Thanks - this is now working.

However, if I insert conditions in this application, I get the same error message again:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ActId= and menbernr=13'.

My inserted condition: 'if execution is not done on time' => two input possibilities: Stop or Message (else => continue the working ;-) application)

I coded the conditions as follows (simplified without styling elements - name of complete file : updA.asp )

<%if var_time<=now() then%>
	
	<%if Request.Form("after") = "Stop" then%>
    	        <FORM METHOD="post" ACTION="Strt.asp">

	<%elseif Request.Form("after") = "Message" then%>
   		<FORM METHOD="post" ACTION="UpdB.asp">

  	<%end if%>
	
<FORM ACTION="updA.asp" method="post">


<table border="0" width="49%">
	<tr>
...
<INPUT TYPE="submit" Name="after" VALUE="Stop"></td></tr>
	
	<tr> ...
      <input type="hidden" name="Rec" value="<% = rs("AnwId") %>">
      <input type="hidden" name="NrAct" value="<% = rs("ActId") %>">
      (the same hidden input copied from the working one)
<INPUT TYPE="submit" Name="after" VALUE="Message"></td></tr>

</table>
</FORM>

<%else
(here is the application that works without the conditions)

I think there is something wrong with these condition syntax ? - given that it works unconditionally
Thanks for tips.

 

[GriffMG]

Hi
You have two form elements called "after", how is you server side supposed to know which one you are taking about?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[leifoet]

I renamed the 'after' elements in 'after1' and 'after2' => error message remains the same (as in my first post of this topic):
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ActId= and membernr=13'.

Nevertheless updA.asp 'receives' the correct variable ActId as Request.Form("ActId")

Other possible causes I can test?
Thanks for tips.

 

[GriffMG]

In your code Request.Form("ActID") will return a blank string

Request.Form("NrAct") might be more useful

If this is going out in the wild, you might want to try and protect yourself from SQL injections
too, perhaps examine the contents of your request.forms before dropping them into your SQL selects.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[leifoet]

In your code Request.Form("ActID") will return a blank string

... in my first post of this topic I wrote:
have now tested this :
response.write Request.Form("ActId") => result = 5
response.write isnumeric(Request.Form("ActId")) => true

 

[GriffMG]

I think you would do better to show a bit more of your code, because there is nothing in it at the moment to show
where ActID is in a form and it is clear from your SQL error that it is arriving there as a blank

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[leifoet]

i am trying to clarify my variable problem

to show where ActID is in a form

Request.Form("ActId")

is generated by <FORM METHOD = "post" ACTION = "updA.asp"> in the selection file : Selection.asp
As shown below, the variable is received by updA.asp

lngRecordNo = Request.Form("ActId")

+Server connection code

StrSQL = "SELECT * FROM table_H INNER JOIN activity21 ON activity21.actID = table_H.actAnId WHERE ActId =" & lngRecordNo & "and membernr =" & Session ("uid") 
rs.Open strSQL, adoCon

<%
Endtime = rs("ActEndDT")
LTtime = DateAdd("n",15,Endtime)
%>

<%if LTtijd<=now() then%>

	<%if Request.Form("after1") = "Stop" then%>
    	<FORM METHOD="post" ACTION="Strt.asp">

	<%elseif Request.Form("after2") = "Message" then%>
   		<FORM METHOD="post" ACTION="updB.asp"> 

  	<%end if%>

	<FORM METHOD="post" ACTION="updA.asp">

<table border="0" width="49%">
	<tr>
...
<INPUT TYPE="submit" Name="after1" VALUE="Stop"></td></tr>
	
	<tr> ...
      <input type="hidden" name="Rec" value="<% = rs("AnwId") %>">
      <input type="hidden" name="NrAct" value="<% = rs("ActId") %>">
      (the same hidden input copied from the working one)
<INPUT TYPE="submit" Name="after2" VALUE="Message"></td></tr>

</table>
</FORM>

<%else%>

after 'else' starts execution (without the time condition) - based on the previously received Request.Form("ActId")

it is clear from your SQL error that it is arriving there as a blank

Not blank, because the execution of updA.asp without the time condition runs perfectly.

If I manually assign a value to the variable ActId in the Select query then the time condition will be executed without error.

StrSQL = "SELECT * FROM table_H INNER JOIN activity21 ON activity21.actID = table_H.actAnId WHERE ActId = 5 and membernr =" & Session ("uid")

There is no problem with the variable membernr.

My problems:
(1) I don't understand (yet) why the variable ActId used in the Select query and regular execution without condition becomes 'blank' when running the condition ?
(1) Where could that happen?
Thanks for tips.

 

[GriffMG]

Well the first thing I see is that after1 will always be equal to "Stop" and after2 always "Message"

What did you mean to do there?

Why not pop a response.write "[" & lngRecordNo & "]<br>" in there somewhere to see what it's receiving

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[leifoet]

Why not pop a response.write "[" & lngRecordNo & "]<br>" in there somewhere to see what it's receiving

in my first post of this topic and also in the post dated 19 Apr 21 07:52 I wrote:
have now tested this :
response.write Request.Form("ActId") => result not blank
response.write isnumeric(Request.Form("ActId")) => true

Also tested
response.write (lngRecordNo) => result not blank
response.end

At the beginning of updA.asp, actId has a value, later when executing the conditions not more ... why?

What did you mean to do there?

	<%if Request.Form("after1") = "Stop" then%>
    	<FORM METHOD="post" ACTION="Strt.asp">

=goes to a certain page (without further consequence)

<%elseif Request.Form("after2") = "Message" then%>
   	<FORM METHOD="post" ACTION="updB.asp">

=goes to the application to update one field of a database table => If I manually assigned a value to the variable ActId in the Select query -as test- then the time condition and also this part will be executed without error.
(similar to the unconditional version after "else" = update multiple fields - but without 'manual intervention' as you already know)

(Condition-syntax correct ?)

Thanks for Tips

 

[GriffMG]

But there is no code to allow the value of after1 or after2 to change, their values will always be "Stop" and "Message" respectively
no matter which one you click on, unless you have some javascript to change them... so far as I can see, if the code gets that far
it will end up in a form, which is part nested in another one but has no closure </FORM>

Have you looked at the resultant page source?

And, as I said before, your SQL statement has an error in it, you need a space before the and membernr :

StrSQL = "SELECT * FROM table_H INNER JOIN activity21 ON activity21.actID = table_H.actAnId WHERE ActId =" & lngRecordNo & "and membernr =" & Session ("uid")

should be

StrSQL = "SELECT * FROM table_H INNER JOIN activity21 ON activity21.actID = table_H.actAnId WHERE ActId =" & lngRecordNo & " and membernr =" & Session ("uid")

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[leifoet]

But there is no code to allow the value of after1 or after2 to change, their values will always be "Stop" and "Message" respectively
no matter which one you click on, unless you have some javascript to change them... so far as I can see, if the code gets that far
it will end up in a form, which is part nested in another one but has no closure </FORM>

I don't understand very well what you mean - can you please give me the / your correct code? - to resolve the issue with the blank variable ActId?

And, as I said before, your SQL statement has an error in it, you need a space before the and membernr :

Sorry, added to this post at the last minute and copied from an earlier post - my working code is correct ;-)

Thanks.

 

[GriffMG]

You have two buttons (submit ones) one with a value of Stop the other Message, you can click on which ever you like
in your browser, but the values sent to the server will not change.

You best bet, in my opinion, would be to use buttons (not submits) and then give them an onclick javascript snippet

<script LANGUAGE="JavaScript">
function cmdButton_onclick(Form,strValue){
	Form.After.value = strValue;
	Form.submit();
}
</script>

Then you make your form look like this:

<FORM name=MyForm Id=MyForm ACTION="updA.asp" method="post">


<table border="0" width="49%">
	<tr><td>
<INPUT TYPE="button" Name="button1" VALUE="Stop" onclick="cmdButton_onclick(MyForm,'Stop')"></td></tr>
	
	<tr><td>
      <input type="hidden" name="After" value="">
      <input type="hidden" name="Rec" value="<% = rs("AnwId") %>">
      <input type="hidden" name="NrAct" value="<% = rs("ActId") %>">
      (the same hidden input copied from the working one)
<INPUT TYPE="button" Name="button2" VALUE="Message" onclick="cmdButton_onclick(MyForm,'Message')"></td></tr>

</table>
</FORM>

Lastly updA.asp needs to look a bit like this:

lngRecordNo = Request.Form("NrAct")

'Server connection code

StrSQL = "SELECT * FROM table_H INNER JOIN activity21 ON activity21.actID = table_H.actAnId WHERE ActId =" & lngRecordNo & " and membernr =" & Session ("uid") 
rs.Open strSQL, adoCon

<%
Endtime = rs("ActEndDT")
LTtime = DateAdd("n",15,Endtime)
%>

<%if LTtijd<=now() then%>

	<%if Request.Form("After") = "Stop" then%>
    	<FORM METHOD="post" ACTION="Strt.asp">

	<%elseif Request.Form("After") = "Message" then%>
   		<FORM METHOD="post" ACTION="updB.asp"> 
  	<%end if%>

...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[leifoet]

Your suggested codes entered but error message remains identical (always referring to the Select query).
Other testing capabilities?

 

[GriffMG]

get the code to show the contents of strSQL

StrSQL = "SELECT * FROM table_H INNER JOIN activity21 ON activity21.actID = table_H.actAnId WHERE ActId =" & lngRecordNo & " and membernr =" & Session ("uid") 
response.write "[" & strSQL & "]<br>"
rs.Open strSQL, adoCon

And show the result to us

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[leifoet]

[SELECT * FROM FROM table_H INNER JOIN activity21 ON activity21.actID=table_H.actAnId WHERE ActId=5 and membernr=13]

ActId receives value but loses it on the way. Why ?

 

[GriffMG]

Is table_H.actAnId correct?
Should it not be table_H.ActID

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[leifoet]

Is table_H.actAnId correct?

=> OK

 

[GriffMG]

Are you getting the same error message or has it changed?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​