Microsoft.AspNetCore.Mvc.Localization.LocalizedHtmlStringUpdate SELECT statement with form field?

Author : Lon2
Subject : Update SELECT statement with form field?
Posted : 06 December 2008, 18:24
Message :

Anyone know how to make an SQL SELECT statement updateable by a form field entry? For example:

Code:

rsTopics.Source = "SELECT * FROM Topics WHERE TopicID = 365"

I would like make the TopicID equals part (365) updateable by just typing the number in a form on an ASP page and clicking submit to update the page with the new TopicID number.<

Reply Author: Etymon
Replied on: 06 December 2008, 23:27
Message:

Originally posted by Lon2
Anyone know how to make an SQL SELECT statement updateable by a form field entry? For example:

Code:
rsTopics.Source = "SELECT * FROM Topics WHERE TopicID = 365"
I would like make the TopicID equals part (365) updateable by just typing the number in a form on an ASP page and clicking submit to update the page with the new TopicID number.

Take a look at post.asp and post_info.asp to see how they work together. What you are appearing to want done is to have your 365 become the catcher's mit for the form value like this TopicID = Request.Form("Topic_ID") ... so on post.asp look for a form value like name=""TOPIC_ID"" and then look on post_info.asp to see how that value is caught and processed (look for TopicID = " & cLng(Request.Form("Topic_ID")) & ").
Note, for a new topic, you will not see the topic_id value on the post.asp side because it has not been created yet, so look for name=""TOPIC_ID"" type=""hidden"" value=""" & strRqTopicID & """ where you find the name=""Method_Type"" type=""hidden"" value=""Reply"" or name=""Method_Type"" type=""hidden"" value=""EditReply"" or name=""Method_Type"" type=""hidden"" value=""EditTopic"" parts of the form on post.asp. That is where you will find the beginning of your question. How it is processed in post_info.asp is where you will find the answer to your question.

<

Reply Author: Carefree
Replied on: 08 December 2008, 09:40
Message:

You could also do it as a variable passed in a query expression. At the beginning of your "topic.asp", add this:

Code:


dim intTPID
intTPID=server.htmlencode(request.QueryString)

or, to use a simple variable from a form:

Code:


dim intTPID
intTPID=request.form("-----")

Note: You'll need to change the five hyphen "-" characters to the name of your form field representing the topic ID.
For both/either of the cases above, change your line of code to say:

Code:


rsTopics.Source = "SELECT * FROM " & strActivePrefix & "Topics WHERE Topic_ID = " & intTPID

Thanks to Etymon for the table/variable correction - don't know where my mind was.<

Reply Author: ruirib
Replied on: 08 December 2008, 09:44
Message:

Carefree,

Better sanitize it too...<

Reply Author: Lon2
Replied on: 08 December 2008, 14:51
Message:

Thanks for the answers!
I did what Carefree suggested:

Code:

rsTopics.Source = "SELECT * FROM Topics WHERE TopicID =" & intTPID

and

dim intTPID
intTPID=request.form("ManualTopicID")

and

<form action="pend_topics.asp" method="post" name="ManualTopicID"><input type="text" size="10">
<input type="submit" name="Submit2" value="Submit">
</form>

And get the following error:

Syntax error (missing operator) in query expression 'TopicID ='.<

Reply Author: Etymon
Replied on: 08 December 2008, 17:07
Message:

The field you are querying should be topic_id<

Reply Author: Lon2
Replied on: 08 December 2008, 18:10
Message:

I'm sorry I'm not following you, Etymon...
When I manually write this:

Code:

rsTopics.Source = "SELECT * FROM Topics WHERE TopicID = 365

and upload it to the server, the page works correctly and shows all responses to the topic. When I write this:

Code:

rsTopics.Source = "SELECT * FROM Topics WHERE TopicID =" & intTPID

With the seperate dim statement and form, I get the error listed aboved. What am I doing wrong?

<

Reply Author: Etymon
Replied on: 08 December 2008, 19:02
Message:

Unless you have a table structure that is different from a standard Snitz FORUM_TOPICS table ...

rsTopics.Source = "SELECT * FROM Topics WHERE TopicID =" & intTPID

should be:

rsTopics.Source = "SELECT * FROM " & strActivePrefix & "Topics WHERE Topic_ID = " & intTPID

or:

rsTopics.Source = "SELECT * FROM FORUM_Topics WHERE Topic_ID = " & intTPID<

Reply Author: Etymon
Replied on: 08 December 2008, 19:11
Message:

Are you trying to make it so that folks can search for a Topic ID from a form?<

Reply Author: Lon2
Replied on: 09 December 2008, 08:30
Message:

Sorry but this doesn't have anything to do with Snitz forums or Snitz code, hence the reason I started this topic in the "Code Support: ASP (Non-Forum Related)" forum. I was just using examples similar to Snitz in hopes it would be easier understood. Sorry for the confusion!

I should also clarify that the form, where the TopicID (365) is entered, is not accessible to anyone on the site. It is on an ASP page with a login for my access only.
Hope this doesn't discourage anyone from helping me further. I'm still stumped.<

Reply Author: Carefree
Replied on: 09 December 2008, 08:49
Message:

Then we're back to ALMOST where we started.
Using a variable from a form:

Code:


dim intTPID
intTPID=request.form("-----")

Note: You'll need to change the five hyphen "-" characters to the name of your form field representing the topic ID.
Change your line of code to say:

Code:


rsTopics.Source = "SELECT * FROM Topics WHERE TopicID = " & intTPID

This should work. Your error is in using the FORM's name instead of the FIELD's name.<

Reply Author: Lon2
Replied on: 09 December 2008, 10:27
Message:

I still can't get it to work with the form. In my example above, what name should go in where the hyphens are?
By the way, when I use:

Code:

dim intTPID
intTPID=server.htmlencode(request.QueryString)

It works great! I type "?365" after the page URL and it comes up. <

Reply Author: cripto9t
Replied on: 09 December 2008, 13:27
Message:

http://www.w3schools.com/asp/asp_inputforms.asp

Save this as "test.asp". The key to passing the value from one side of the form to the other is the input name.

Code:

<html>
<body>
<form action="test.asp" method="post">
Enter Topic_ID Here: <input type="text" name="topicid" size="10">
<input type="submit" value="Submit!">
</form>

<%
Dim TopicID

TopicID = Request.Form("topicid")

if trim(TopicID) <> "" and isNumeric(TopicID) = true then   'make sure TopicID is not empty and is a number
    Response.Write "TopicID = " & TopicID & " - use it as you want"
else
    if trim(TopicID) = "" then 
        Response.Write "TopicID is empty"
    else
        Response.Write TopicID & " is not numeric"
    end if
end if 
%>

</body>
</html>

<

Reply Author: Lon2
Replied on: 09 December 2008, 13:58
Message:

Thanks! I got that, tried it, and that worked fine. However, I still get the same error.
Here's was I got now:

Code:

rsTopics.Source = "SELECT * FROM Topics WHERE TopicID = " & intTPID

and

Code:

dim intTPID
intTPID = Request.Form("ManTopicID")

and

Code:

<form action="pend_topics.asp" method="post" name="form5">
Enter Topic ID Here: <input name="ManTopicID" type="text">
<input type="submit" value="Go">
</form>

And I still get the following error:

Syntax error (missing operator) in query expression 'TopicID ='.
Looks like a problem with the connection string not the form... doesn't it?<

Reply Author: Carefree
Replied on: 09 December 2008, 14:30
Message:

The problem is definitely NOT the connection string.
Although it shouldn't result in that error, try eliminating the spaces at the end of your rsTopics.Source line as follows:

rsTopics.Source = "SELECT * FROM Topics WHERE TopicID="&intTPID

The pend_topics.asp page is the one we're working from? Can you post a link to a .txt version of it?
Otherwise, immediately after your rsTopics.Source line, add the following:

Response.Write "<br>rsTopics.Source=" & rsTopics.Source
Response.End

Give us the result and maybe we can identify the problem. As long as the dimensioned & assigned values of intTPID precede the rsTopics line, that error shouldn't occur.<

Reply Author: cripto9t
Replied on: 09 December 2008, 14:31
Message:

Yea, I would say intTPID has no value. Put db query inside an if statement like I did in my example.

Code:

if trim(intTPID) <> "" and isNumeric(intTPID) = true then
     'Query code here
else
   Response.Write "Error - unable to query db intTPID has an invalid value"
end if

<

Reply Author: Etymon
Replied on: 09 December 2008, 14:39
Message:

Above this:

dim intTPID
intTPID = Request.Form("ManTopicID")

Place this:

Response.Write Request.Form()
Response.End

Look for ManTopicID= in what is displayed. If there is not a value after the = sign, then no value is being collected from the form.
<

Reply Author: Lon2
Replied on: 09 December 2008, 15:53
Message:

Hope I got this right:

Code:

<%@LANGUAGE="VBSCRIPT"%>

<!--#include virtual="top.asp"-->
<!--#include file="connTopics.asp" -->

<% 
dim intTPID
intTPID = Request.Form("ManTopicID")
%>

<%
set rsCommentsAppr = Server.CreateObject("ADODB.Recordset")
rsCommentsAppr.ActiveConnection = connTopics_STRING
rsCommentsAppr.Source = "SELECT * FROM Comments WHERE TopicID="& intTPID
rsCommentsAppr.CursorType = 0
rsCommentsAppr.CursorLocation = 2
rsCommentsAppr.LockType = 3
rsCommentsAppr.Open()
rsCommentsAppr_numRows = 0
%>

<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsCommentsAppr_numRows = rsCommentsAppr_numRows + Repeat1__numRows
%>

<%
Dim HLooper1__numRows
HLooper1__numRows = 30
Dim HLooper1__index
HLooper1__index = 0
rsCommentsAppr_numRows = rsCommentsAppr_numRows + HLooper1__numRows
%>

<% 
If Not 
rsCommentsAppr.EOF 
Or Not 
rsCommentsAppr.BOF 
Then 
%>

<% 
While ((Repeat1__numRows <> 0) 
AND (NOT rsCommentsAppr.EOF)) 
%>

<form action="pend_topics.asp" method="post" name="form5">
Enter Topic ID Here: <input name="ManTopicID" type="text">
<input type="submit" value="Go">
</form>

<% 
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCommentsAppr.MoveNext()
Wend
%>

<% 
End If 
%>

<% 
If rsCommentsAppr.EOF 
And rsCommentsAppr.BOF 
Then 
%>

There are no Comments for this topic

<% 
End If 
%>

<!--#include virtual="/footer.asp"-->

<% 
rsCommentsAppr.Close() 
%>

<% 
Set rsCommentsAppr = Nothing 
%>

Go easy on me, I'm a newbie!

<

Reply Author: Carefree
Replied on: 09 December 2008, 17:40
Message:

What is "ManImgID"? Your variable is "ManTopicID".... Change line 8 accordingly. <

Reply Author: Lon2
Replied on: 09 December 2008, 17:52
Message:

I had the correct variable on the web page, and it's still not working. I copied and pasted the code wrong here. I'm scrounging code from another one of my projects.

<

Reply Author: cripto9t
Replied on: 10 December 2008, 08:49
Message:

I tested this with 1 of my tables and it works alright. I commented out alot of the code because I didn't know what it was for and wasn't necessary for the test. At the least, it should get rid of the sql syntax error ;)

Code:

<%@LANGUAGE="VBSCRIPT"%>

<!--#include virtual="top.asp"-->
<!--#include file="connTopics.asp" -->

<% 
dim intTPID
intTPID = Request.Form("ManTopicID")

if trim(intTPID) <> "" and isNumeric(intTPID) = true then
    set rsCommentsAppr = Server.CreateObject("ADODB.Recordset")
    rsCommentsAppr.ActiveConnection = connTopics_STRING
    rsCommentsAppr.Source = "SELECT * FROM Comments WHERE TopicID="& intTPID
    rsCommentsAppr.CursorType = 0
    rsCommentsAppr.CursorLocation = 2
    rsCommentsAppr.LockType = 3
    rsCommentsAppr.Open()
    'rsCommentsAppr_numRows = 0

    'Dim Repeat1__numRows
    'Repeat1__numRows = -1
    'Dim Repeat1__index
    'Repeat1__index = 0
    'rsCommentsAppr_numRows = rsCommentsAppr_numRows + Repeat1__numRows

    'Dim HLooper1__numRows
    'HLooper1__numRows = 30
    'Dim HLooper1__index
    'HLooper1__index = 0
    'rsCommentsAppr_numRows = rsCommentsAppr_numRows + HLooper1__numRows
 
    If Not rsCommentsAppr.EOF Or Not rsCommentsAppr.BOF Then 
        'While ((Repeat1__numRows <> 0) AND (NOT rsCommentsAppr.EOF))
        Response.Write "<table border=""1px"">"
        While rsCommentsAppr.EOF = false
            'Repeat1__index=Repeat1__index+1
            'Repeat1__numRows=Repeat1__numRows-1
            Response.Write "<tr>"
            for each x in rsCommentsAppr.Fields
                Response.Write "<td>" & x.value & "</td>"
            next
            Response.Write "</tr>"

            rsCommentsAppr.MoveNext()
        Wend
        Response.Write "</table>"
    ELSe
         Response.Write "There are no Comments for this topic" 
    End If

    rsCommentsAppr.Close
    Set rsCommentsAppr = Nothing
End If
%>

<form action="pend_topics.asp" method="post" name="form5">
Enter Topic ID Here: <input name="ManTopicID" type="text">
<input type="submit" value="Go">
</form> 

<!--#include virtual="/footer.asp"-->

<

Reply Author: Lon2
Replied on: 10 December 2008, 11:29
Message:

Thanks a lot for your time on that, cripto, it's very much appreciated! [^]
Is there an easy way to toggle between 2 connection strings on one page? For example; clicking a buttin to set the following connection string to one or the other:

Code:

rsComments.Source = "SELECT * FROM Comments WHERE Approved = False"

rsComments.Source = "SELECT * FROM Comments WHERE TopicID="& intTPID

I think I may be doing this the hard way...
Or, run 2 queries on the same page? For example:

Code:

rsComments.Source = "SELECT * FROM Comments WHERE "& Option1" OR "& Option2"

Option1 = Approved = False"
Option2 = TopicID="& intTPID

I guess I'm asking how to run 2 different queries on the same connection (on the same page) so I don't have to duplicate everything... and will they work simultaniously?<

Reply Author: Etymon
Replied on: 10 December 2008, 12:35
Message:

Take a look throughout the Snitz forum base code, and you will find many examples of what you want.

<

Reply Author: Lon2
Replied on: 10 December 2008, 12:38
Message:

Ok, I got it working somewhat the way I need, and I think I saved a ton of work. Here's what I did:

Code:

<% 
dim intTPID
intTPID = Request.Form("ManImgID")

set rsComments = Server.CreateObject("ADODB.Recordset")
rsComments.ActiveConnection = MM_connImageGallery_STRING

if trim(intTPID) <> "" and isNumeric(intTPID) = true then
rsComments.Source = "SELECT * FROM Comments WHERE TopicID="& intTPID

else
rsComments.Source = "SELECT * FROM Comments WHERE Approved = False"
end if

rsComments.CursorType = 0
rsComments.CursorLocation = 2
rsComments.LockType = 3
rsComments.Open()
rsComments_numRows = 0
%>

On the page I have a form field. When it's blank it shows Comments Not Approved (Approved = False) and when I enter a TopicID and submit it shows Topic Comments (TopicID="& intTPID). I just hit the submit button with a blank field to get back to Comments Not Approved.
Does anyone see any problems with the way I'm doing this?<

Reply Author: Lon2
Replied on: 12 December 2008, 20:19
Message:

I guess not.
Thanks anyway!<

Update SELECT statement with form field?

Topic

Replies ...