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

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>
rabbits2Asked:
Who is Participating?
 
John_LennonCommented:
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
 
gyraCommented:
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
 
rabbits2Author Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rabbits2Author Commented:
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
 
rabbits2Author Commented:
Error:

Subscript out of range: '[number: 0]'
0
 
John_LennonCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.