?
Solved

Select WHERE firstname equals part of value and surname equals part.

Posted on 2006-04-29
6
Medium Priority
?
274 Views
Last Modified: 2006-11-18
What I am trying to do is combine two variables to make one value - the value found in a colum in the db called 'firstname' and the value found in a colum in the db called 'surname'.  
Firstly I select the two values from the db and then combine them to populate a value in a select list here named 'appname'.  Once this has happened I wish to populate the second select list here named 'friend' with values found in the db in colums 'friend1, friedn2 and friend3' WHERE the firstname matches the first name of the value selected in 'appname' and the surname matches the surname part of the value selected in 'appname'

See code below, I think I am making it more complicated than it is, any guidance appreiciated.  thanks.


<table align="left">
<tr>
<td valign="top" class="text_2" style="height:14 ">Search by Event:</td>
</tr>

<tr>
<%
SQLQuerymodel = "Select firstname,surname FROM tbl_application"
set rs_appname=server.CreateObject("ADODB.Recordset")
set rs_appname=objconn.execute(SQLQuerymodel)
%>
<tr>
<td valign="top" style="height:30 "><select name="appname" class="form">
<%while not rs_appname.eof%>
<option value = "<%Response.Write rs_appname ("firstname")%>" &nbsp; "<%Response.Write rs_appname ("surname")%>">
<%Response.Write rs_appname ("firstname")%> &nbsp; <%Response.Write rs_appname  ("surname")%>
</option><br>
<%
               rs_appname.movenext
          wend %>
</select></td>
</tr>

</table>
 <table>
 <tr>
 <td valign="top" class="text_2" style="height:14 ">Friend Option: </td>
</tr>
<% If Request.Form("searchtype") ="Applicant Name" Then
SQLQuerymodel = "Select friend1,friend2,frined3 FROM tbl_application WHERE firstname='" & Request.Form("appname") & "' surname='" & Request.Form("appname") & "'"
set rs_friend=server.CreateObject("ADODB.Recordset")
 set rs_friend=objconn.execute(SQLQuerymodel)
          %>
 <tr>
<td valign="top" style="height:30 "><select name="friend" class="form">
 <%while not rs_friend.eof%>
  <option value = "<%Response.Write rs_friend ("friend1")%>">
 <%Response.Write rs_friend ("friend1")%>
 </option><br>
<option value = "<%Response.Write rs_friend ("friend2")%>">
<%Response.Write rs_friend ("friend2")%>
</option><br>
<option value = "<%Response.Write rs_friend ("friend3")%>">
<%Response.Write rs_friend ("friend3")%>
</option>
<%
               rs_friend.movenext
          wend %>
      </select></td>
      </tr>
</table>
0
Comment
Question by:rabbits2
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:gyra
ID: 16568962
This should be with an AND

<% If Request.Form("searchtype") ="Applicant Name" Then
SQLQuerymodel = "Select friend1,friend2,frined3 FROM tbl_application WHERE firstname='" & Request.Form("appname") & "' ************************  surname='" & Request.Form("appname") & "'"
set rs_friend=server.CreateObject("ADODB.Recordset")
 set rs_friend=objconn.execute(SQLQuerymodel)
          %>


so it should look like this

<%
If Request.Form("searchtype") ="Applicant Name" Then

sql = "Select friend1,friend2,frined3 FROM tbl_application WHERE firstname='" & Request.Form("appname") & "' AND surname='" & Request.Form("appname") & "'"
   
   set rs_friend= objConn.execute(sql)
%>

hope this helps.
0
 

Author Comment

by:rabbits2
ID: 16615296
That is not working, Error BOF or EOF.

Because the value of appname is a combination of two variables would you not need to split the Select statement up too as you do whenyou split a file name from the .extension like this:
strFile = objUpload.Fields("File1").FileName
strFileName1 = Left(str, InStrRev(str, ".") - 1) & "_" & ad & "." & Right(str, Len(str) - InStrRev(str, "."))

So that when you do WHERE firstname=   you specify the first part of the value of appname and WHERE surname =    you specify the second part.  Or would this get complicated if the surname consisited of more than one part in which case would you advise me to set the firstname and surname into two seperate inout boxes so that I could then read then seperately?

0
 

Author Comment

by:rabbits2
ID: 16615529
I'm not thinking that suggestion of mine to place the firstname and surname in seperate boxes would not help as I am wanting the names to populate a list box from which the user can select the desired applicant name.  Sorry.
So still trying to sort out the line:
sql = "Select friend1,friend2,frined3 FROM tbl_application WHERE firstname='" & Request.Form("appname") & "' AND surname='" & Request.Form("appname") & "'"
0
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!

 
LVL 6

Accepted Solution

by:
John_Lennon earned 2000 total points
ID: 16615869
try this changes
this
<option value = "<%Response.Write rs_appname ("firstname")%>|<%Response.Write rs_appname ("surname")%>">
instead of this
<option value = "<%Response.Write rs_appname ("firstname")%>" &nbsp; "<%Response.Write rs_appname ("surname")%>">

and this
<% If Request.Form("searchtype") ="Applicant Name" Then
  dim arrNames
  arrNames = split(Request.Form("appname"), "|")
  SQLQuerymodel = "Select friend1,friend2,frined3 FROM tbl_application WHERE firstname='" & arrNames(0) & "' surname='" & arrNames(1) & "'"
set rs_friend=server.CreateObject("ADODB.Recordset")
 set rs_friend=objconn.execute(SQLQuerymodel)
          %>

instead of this
<% If Request.Form("searchtype") ="Applicant Name" Then
SQLQuerymodel = "Select friend1,friend2,frined3 FROM tbl_application WHERE firstname='" & Request.Form("appname") & "' surname='" & Request.Form("appname") & "'"
set rs_friend=server.CreateObject("ADODB.Recordset")
 set rs_friend=objconn.execute(SQLQuerymodel)
%>
0
 

Author Comment

by:rabbits2
ID: 16616166
Error:

Subscript out of range: '[number: 0]'
0
 
LVL 6

Expert Comment

by:John_Lennon
ID: 16616658
check the source code and make sure that this line
<option value = "<%Response.Write rs_appname ("firstname")%>|<%Response.Write rs_appname ("surname")%>">

get's the value correct

also, you can change that line for this one
<option value = "<%=rs_appname("firstname")%>|<%=rs_appname("surname")%>">
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question