View page 1

[GoldPearl]

i have a search page. the user can input the search criteria in a text box and click the view button. the problem is that i want it to check the criteria which is in fact a "department name" in the db and see if it exists before displaying the data. if it is not there, it displays an error message.
anyone can suggest how to achieve this?

here is my code for the view button:

Sub ViewDept

dim rs
dim cn
dim sql

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open(Conn)
set rspos = cn.execute ("select positionName from [position]")

Set rs = Server.CreateObject("ADODB.Recordset")
sql= "SELECT * FROM department WHERE deptName='" & Request.Form("txtdeptname") & "'"

rs.Open sql, cn

cn.execute(sql)

do while not rs.eof

%>
<font size="2" face="Arial">

<div align="center">
<table width="30%" border="2" cellspacing="20" bordercolor="#FFFFFF" bgcolor="#CCCCCC">
<tr>
<td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial">Department
ID:&nbsp;</font></td>
<td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial">
<input name="txtDeptId" type="text" id="txtDeptId" size="20" value=<%= rs.fields("deptId") %>>
</font></td>
</tr>
<tr>
<td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial">Department
Name:</font></td>
<td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial">
<input name="txtDeptname" type="text" id="txtDeptname2" size="20" value=<%= rs.fields("deptName") %>>
</font></td>
</tr>
<tr>
<td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial">Position:&nbsp;&nbsp;&nbsp;</font></td>
<td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial">
<select name="lstpos" size="1" id="lstpos">
<option selected>Select a Position</option>
<%
do while not rspos.eof
%>
<% if rspos(0) = rs.fields("positionId") then %>
<option selected><%=rs.fields("positionId")%></option>
<% else %>
<option ><%=rspos(0)%></option>
<%
end if
rspos.movenext
loop
rspos.close
set rspos = nothing
%>
</select>
</font></td>
</tr>
</table>
<p><font size="2" face="Arial">
<input name="btndel" type="submit" id="btndel" value="Delete">
<input name="btnmod" type="submit" id="btnmod" value="Modify">
</font></p>
</div>
<p>

<%
rs.movenext
loop

cn.close
set rs=nothing
set cn=nothing

End Sub


Cheers,
Gold Pearl

 

[DotNetGnat]

something like this:

.....
.....
.....
 cn.execute(sql)
    
[red]if rs.EOF AND rs.BOF then
response.write "Sorry, no records found."
else [/red]  
 do while not rs.eof

%>
  <font size="2" face="Arial">
      
  <div align="center"> 
    <table width="30%" border="2" cellspacing="20" bordercolor="#FFFFFF" bgcolor="#CCCCCC">
    <tr> 
      <td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial">Department 
        ID:&nbsp;</font></td>
      <td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial"> 
        <input name="txtDeptId" type="text
.....
.....
<%
        rs.movenext
        loop
[red]end if[/red]
.....
.....

-DNG

 

[GoldPearl]

ok i've done the changes but am getting this error.


Microsoft VBScript compilation (0x800A0400)
Expected statement
/ppat/dept.asp, line 149
end if

 

[DotNetGnat]

which line is line 149..i think you are messing up with some quotes...

also change your

do while not rs.eof

to

Do until rs.eof

how are you calling this sub??

-DNG

 

[GoldPearl]

<%
rs.movenext
loop
end if
.....

this is the problematic "end if

 

[DotNetGnat]

ok let reverse the thing and let me know what error you get:

Sub ViewDept

    dim rs
    dim cn
    dim sql

    Set cn = Server.CreateObject("ADODB.Connection")
    cn.Open(Conn)
    set rspos = cn.execute ("select positionName from [position]")
        
    Set rs = Server.CreateObject("ADODB.Recordset")
    sql= "SELECT * FROM department WHERE deptName='" & Request.Form("txtdeptname") & "'"

    rs.Open sql, cn

    cn.execute(sql)
    
[red] if not(rs.EOF AND rs.BOF) then

Do until rs.EOF[/red]
%>
  <font size="2" face="Arial">
      
  <div align="center"> 
    <table width="30%" border="2" cellspacing="20" bordercolor="#FFFFFF" bgcolor="#CCCCCC">
    <tr> 
      <td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial">Department 
        ID:&nbsp;</font></td>
      <td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial"> 
        <input name="txtDeptId" type="text" id="txtDeptId" size="20" value=<%= rs.fields("deptId") %>>
        </font></td>
    </tr>
    <tr> 
      <td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial">Department 
        Name:</font></td>
      <td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial"> 
        <input name="txtDeptname" type="text" id="txtDeptname2" size="20" value=<%= rs.fields("deptName") %>>
        </font></td>
    </tr>
    <tr> 
      <td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial">Position:&nbsp;&nbsp;&nbsp;</font></td>
      <td bordercolor="#CCCCCC"><font color="#000000" size="2" face="Arial"> 
        <select name="lstpos" size="1" id="lstpos">
          <option selected>Select a Position</option>
          <%
    do while not rspos.eof
    %>
          <% if rspos(0) = rs.fields("positionId") then %>
          <option selected><%=rs.fields("positionId")%></option>
          <% else %>
          <option ><%=rspos(0)%></option>
          <%
           end if
    rspos.movenext
    loop
    rspos.close
    set rspos = nothing
    %>
        </select>
        </font></td>
    </tr>
  </table>
    <p><font size="2" face="Arial"> 
      <input name="btndel" type="submit" id="btndel" value="Delete">
      <input name="btnmod" type="submit" id="btnmod" value="Modify">
      </font></p>
    </div>
  <p> 
    
<%
        rs.movenext
        loop

        cn.close
        set rs=nothing
        set cn=nothing
[red]
else
response.write "sorry, no records found"
end if
%>[/red]
End Sub

-DNG

 

[GoldPearl]

thks it worked!

 

[DotNetGnat]

glad to be of help...

-DNG