Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Need to create a form that will pass input fields to sql query results page

We have a query already that adds up totals from a series of tables and returns only results from the current week. What I need is to have a webpage that passes input fields to a results page that will display results based on the input.For example:

Our current query returns time logged for the current week with each time entry listed:
Date      User             Description           Total Hours
4/11/05 John Doe       Emailed Jane         00:30
and also returns total time logged per day:
Date      Total Hours
4/10/05   8.5
4/11/05   6
4/12/05   10

What we want is to be able to input the username (John Doe) and the week ending date into a form and get the same format of results as above, but with the criteria the user has input.

I'm not sure how to accomplish this
0
GMJ29
Asked:
GMJ29
  • 3
  • 2
  • 2
2 Solutions
 
ajaikumarrCommented:
Hai,

Try this,

'
'Name this asp page as myPage.asp
'
<%
Dim bvlSearched
bvlSearched = False
If uCase(Trim(Request.Querystring("ACT"))) = "SAVE" Then
      Dim svlUserName, svlEndingDate, svlQuery
      svlUserName = Request.Form("txtUserName")
      svlEndingDate = Request.Form("txtEndingDate")

      svlQuery = "Select * From myTable Where myField1 <> '' "
      If Trim(svlUserName) <> "" Then
            svlQuery = svlQuery & " And UserName = '" & Trim(svlUserName) & "' "
      End If

      If Trim(svlEndingDate) <> "" Then
            svlQuery = svlQuery & " And EndingDate = " & svlEndingDate
      End If

      'Do your query functions here
      bvlSearched = True
End If
%>

<form name="frmTest" id="frmTest" method="post">
      <table width="98%" cellspacing="0" cellpadding="0" border="0">
            <tr>
                  <td colspan="2" align="center">Enter your search text here</td>
            </tr>

            <tr><td colspan="2">&nbsp;</td></tr>

            <tr>
                  <td width="25%">User Name</td>
                  <td><input type="text" name="txtUserName" id="txtUserName" value=""></td>
            </tr>

            <tr><td colspan="2">&nbsp;</td></tr>

            <tr>
                  <td>Ending Date</td>
                  <td><input type="text" name="txtEndingDate" id="txtEndingDate" value=""></td>
            </tr>

            <tr><td colspan="2">&nbsp;</td></tr>

            <tr>
                  <td colspan="2" align="center">
                        <input type="button" name="cmdSearch" id="cmdSearch" onclick="javascript:SearchRecords()">
                  </td>
            </tr>
      </table>

      <%If bvlSearched Then %>
      <table width="98%" cellspacing="0" cellpadding="0" border="0">
            <tr>
                  <td>Search Result</td>
            </tr>

            <tr>
                  <td>
                        Write your search results here
                  </td>
            </tr>
      </table>
      <%End If%>
</form>

<script language="javascript">
      function SearchRecords() {
            //If you dont need to validate user name field comment below 4 lines
            if (document.frmTest.txtUserName.value == "") {
                  alert("Please enter user name for search.");
                  return false;
            }

            //If you dont need to validate date field comment below 4 lines
            if (document.frmTest.txtEndingDate.value == "") {
                  alert("Please enter ending date for search.");
                  return false;
            }

            document.frmTest.action = "./myPage.asp?Act=SAVE"
            document.frmTest.submit();
      }
</script>

Bye
Ajai
0
 
shadieCommented:
if I'm understanding correctly, you can just type the username into a form and submit. The results page could either grab the form variable request.form("username") or pass the variable in a queryString and grab it from there.

<form action="results.asp?user=Jane%20Doe&endDate=4/11/2005">

the results page would be

dim varUser, varDate
varUser = request.QueryString("user")
varDate = request.QueryString("endDate")
then do your rs select

select * from table where user = '" & varUser & "' and endDate < '" & varDate & "'"

or ...endDate between '" & (varDate-7) & "' and '" varDate & "'" to select a date range between the week end date and 7 days prior

ditto for the date. The person entering the data could select a date from a list or enter. I always use some javaScript calendar that forces the user to pick a date from, this way it forces the user to adhere to a common date standard. So you don't get things like 4/11/05 vs. 04/11/2005 which could cause headaches later.
0
 
GMJ29Author Commented:
up to this point, i've been using the frontpage database results wizard to build my asp pages, so i have to admit i'm a little in the dark about writing my own asp code - my strength is really the sql queries behind the asp. Is it possible to put a sql script into the page as either of you described? the script contains joins and multiple criteria.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ajaikumarrCommented:
Hai GMJ29,

Have you tried the code which i posted?

Bye
Ajai
0
 
GMJ29Author Commented:
i'm sorry, but i'm not really sure where to plug in my info.
0
 
ajaikumarrCommented:
Hai,

>>>>>'Do your query functions here
Write your database functions here. like connection open, recordset open....

>>>>>Write your search results here
Write the code to display the results. something like.

if your recordset is rsMyRecordSet
<%
Do While Not rsMyRecordSet.EOF
    Response.write rsMyRecordSet.Fields(0)...
    rsMyRecordSet.MoveNext
Wend
%>

Bye
Ajai
0
 
shadieCommented:
you can absolutely do joins and whatever sql queries you need to on an asp page. Just make sure you get your syntax correct or you will either throw a bunch of errors or just not display the correct results. If you're using variables you need to "break out" of the sql select by ending the select statement with quotes" then putting in your asp variable, like:

select * from table where field1 = '" & variable & "' and field2 = '" variable2 & "'"

and so on

or select * from table1 inner join table2 where table1.field1 = table2.field 2 where table1.field1 = '" & varaible & "'"
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now