Advance help sql query

[jordan11]

I have tried do an advance search using the below code but it displays everying in the database it does not filter out the results based on the criteria the user wants, does anyone have any suggestions on where I have gone wrong in my sql statement. the sql statement is in a sub which is executed after validate.



strphYSpecialty = trim(Request("Specialty"))
strmedicalschool = trim(Request("medicalschool"))
strphylastname = trim(Request("lastname"))
strBoardStatus = trim(Request("BoardStatus"))
strphyemail = trim(Request("email"))

txtsql= "SELECT a.PhysicianId, a.PhySpecialty, a.phyemail, a.Phyhomephone,a.Phylastname, a.Phyfirstname, b.MedicalSchool, b.Internship, b.Residency, b.SalaryExpected,"_
& " b.BoardStatus,b.LicenseStates,b.SalaryExpected, b.SponsEmpVisaStatus"_
& " from Physiciancontact AS a inner JOIN physicianqualifications AS b ON a.physicianid = b.physicianid)"_
& " Where Specialty LIKE '" & strphYSpecialty & "%' " _
& " or MedicalSchool LIKE '" & strmedicalschool & "%' " _
& " or lastname LIKE '" & strphylastname& "%' " _
& " or BoardStatus LIKE '" & strphYSpecialty & "%' " _
& " or email LIKE '" & stremail & "%' " _
& " order by a.customerID;"

Thanks for help in advance

 

[gmmastros]

If your users are not filling in data for all the fields, then your query would return all data, every time.

For example, if strEmail is an empty string, the part of your where clause looks like "Or Email Like '%'" which will always return all the data.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jordan11]

Thanks for the quick reply,

Do you know what I need to do to allow this query to work correctly so that this problem does not happen.


Thanks for your help

 

[vongrunt]

Lemme interrupt...

First question is: when user specifies more than one input value - for example, Specialty and lastname - do you want AND or OR conditions?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[stevexff]

You could change the ORs to ANDs...

 

[jordan11]

I would like it to be based on everything they select so it should really be AND

 

[DotNetGnat]

Try something like this:

txtsql= "SELECT blah, blah from Physiciancontact AS a inner JOIN physicianqualifications AS b ON a.physicianid = b.physicianid) Where"
if (strphYSpecialty<>"") Then
tstsql=txtsql & "Specialty LIKE '" & strphYSpecialty & "%' AND"
End if

--continue this kind of if else condition for all the filter and in the end for the last if else condition...put this in the else condition...

Else
txtsql=left(txtsql,len(txtsql)-3)
End if

--remaining sql here...

-DNG

 

[jordan11]

I will try that.

Thanks

 

[jordan11]

Below is my revised code but I get a error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Whe'.

strphYSpecialty = trim(Request("phYSpecialty"))
strmedicalschool = trim(Request("medicalschool"))
strphylastname = trim(Request("phylastname"))
strBoardStatus = trim(Request("BoardStatus"))
strphyemail = trim(Request("phyemail"))
strSponsEmpVisaStatus = trim(Request("SponsEmpVisaStatus"))
strPreferredState = trim(Request("PreferredState"))
strHomeState = trim(Request("HomeState"))

txtsql= "SELECT a.PhysicianId, a.PhySpecialty, a.phyemail, a.Phyhomephone,a.Phylastname, a.Phyfirstname, b.MedicalSchool, b.Internship, b.Residency, b.SalaryExpected,"_
& " b.BoardStatus,b.LicenseStates,b.SalaryExpected, b.SponsEmpVisaStatus"_
& " from Physiciancontact AS a inner JOIN physicianqualifications AS b ON (a.physicianid = b.physicianid)"_
& " Where "
if (strphYSpecialty<>"") Then
txtsql=txtsql & "Specialty LIKE '" & strphYSpecialty & "%' AND"

elseif (strmedicalschool<>"") Then
txtsql=txtsql & "Specialty LIKE '" & strmedicalschool & "%' AND"

elseif (strphylastname<>"") Then
txtsql=txtsql & "Specialty LIKE '" & strphylastname & "%' AND"

elseif (strphyemail<>"") Then
txtsql=txtsql & "Specialty LIKE '" & strphyemail & "%' AND"

elseif (strSponsEmpVisaStatus<>"") Then
txtsql=txtsql & "Specialty LIKE '" & strSponsEmpVisaStatus & "%' AND"

else
txtsql=left(txtsql,len(txtsql)-3)
End if

 

[DotNetGnat]

You have "Specialty" repeated throughout your filter...

also check for spaces before and after closing the quotes from one line to the next...

if you still get any errors...post your whole sql query...

-DNG

 

[gmmastros]

You need to handle the case where no 'filters' are specified by the user. In this case there are no conditions in your where clause. The last part takes the last 3 characters off. So Before the last part, you query looks like 'select blah, blah from tables Where ' then you remove the last there character.

You should also change your code so that it is like DotNetGnat showed you. Do not use ElseIf. Change all to If's.

    txtsql= "SELECT a.PhysicianId, a.PhySpecialty, a.phyemail, a.Phyhomephone,a.Phylastname, a.Phyfirstname, b.MedicalSchool, b.Internship, b.Residency, b.SalaryExpected,"_
    & " b.BoardStatus,b.LicenseStates,b.SalaryExpected, b.SponsEmpVisaStatus"_
    & " from Physiciancontact AS a inner JOIN physicianqualifications AS b ON (a.physicianid = b.physicianid)"_
    & " Where "
    if (strphYSpecialty<>"") Then
    txtsql=txtsql & "Specialty LIKE '"  & strphYSpecialty & "%' AND"
    End If

    if (strmedicalschool<>"") Then
    txtsql=txtsql & "Specialty LIKE '"  & strmedicalschool & "%' AND"
    End If

    if (strphylastname<>"") Then
    txtsql=txtsql & "Specialty LIKE '"  & strphylastname & "%' AND"
    End If
 
    if (strphyemail<>"") Then
    txtsql=txtsql & "Specialty LIKE '"  & strphyemail & "%' AND"
    End If

    If (strSponsEmpVisaStatus<>"") Then
    txtsql=txtsql & "Specialty LIKE '"  & strSponsEmpVisaStatus & "%' AND"
    End If

    txtsql=left(txtsql,len(txtsql)-3)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DotNetGnat]

try this:

txtsql= "SELECT a.PhysicianId, a.PhySpecialty,"
txtsql= txtsql & " a.phyemail,a.Phyhomephone,a.Phylastname,"
txtsql= txtsql & " a.Phyfirstname, b.MedicalSchool,"
txtsql = txtsql & " b.Internship, b.Residency,"
txtsql= txtsql & " b.SalaryExpected,b.BoardStatus,b.LicenseStates,"
txtsql= txtsql & " b.SalaryExpected, b.SponsEmpVisaStatus"
txtsql = txtsql & " from Physiciancontact AS a inner JOIN" txtsql= txtsql & " physicianqualifications AS b ON "
txtsql = txtsql & " (a.physicianid = b.physicianid)"
txtsql= txtsql & " Where "
    
if (strphYSpecialty<>"") Then
txtsql=txtsql & "Specialty LIKE '"  & strphYSpecialty & "%' AND"
End if

if (strmedicalschool<>"") Then
txtsql=txtsql & "MedicalSchool LIKE '"  & strmedicalschool & "%' AND"
End if

if (strphylastname<>"") Then
txtsql=txtsql & "lastname LIKE '"  & strphylastname & "%' AND"
End if
     
if (strphyemail<>"") Then
txtsql=txtsql & "email LIKE '"  & strphyemail & "%' AND"
End if

if (strSponsEmpVisaStatus<>"") Then
txtsql=txtsql & "BoardStatus LIKE '"  & strSponsEmpVisaStatus & "%' AND"

else
txtsql=left(txtsql,len(txtsql)-3) 
End if 

txtsql = txtsql & " order by a.customerId;"

-DNG

 

[jordan11]

Thank you all for your help but I still get this error message
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Whe'.

This is my new revised code

<%
end sub

Sub Displayresults()

dim strerr


txtsql= "SELECT a.PhysicianId, a.PhySpecialty,"
txtsql= txtsql & " a.phyemail,a.Phyhomephone,a.Phylastname,"
txtsql= txtsql & " a.Phyfirstname, b.MedicalSchool,"
txtsql = txtsql & " b.Internship, b.Residency,"
txtsql= txtsql & " b.SalaryExpected,b.BoardStatus,b.LicenseStates,"
txtsql= txtsql & " b.SalaryExpected, b.SponsEmpVisaStatus"
txtsql = txtsql & " from Physiciancontact AS a inner JOIN"
txtsql= txtsql & " physicianqualifications AS b ON "
txtsql = txtsql & " (a.physicianid = b.physicianid)"
txtsql= txtsql & " Where "

if (strphYSpecialty<>"") Then
txtsql=txtsql & "phySpecialty LIKE '" & strphYSpecialty & "%' AND"
End if

if (strmedicalschool<>"") Then
txtsql=txtsql & "MedicalSchool LIKE '" & strmedicalschool & "%' AND"
End if

if (strphylastname<>"") Then
txtsql=txtsql & "phylastname LIKE '" & strphylastname & "%' AND"
End if

if (strphyemail<>"") Then
txtsql=txtsql & "phyemail LIKE '" & strphyemail & "%' AND"
End if

if (strSponsEmpVisaStatus<>"") Then
txtsql=txtsql & "BoardStatus LIKE '" & BoardStatus & "%' AND"

else
txtsql=left(txtsql,len(txtsql)-3)
End if

txtsql = txtsql & " order by a.physicianId;"



ID = Request("ID")

set rs=server.createobject("ADODB.recordset")
Set rs= conn.Execute(txtsql)
'Errhandlerr Conn




%>
<table border="1" align="center">
<% if rs.eof then
response.redirect "adminphysearch2.asp?err=No record was found for the above criteria"

elseif Request("formtype") = 1 then %>


<tr>
<td><strong>ID</strong></TD>
<td><strong>Specialty</strong></td>
<td><strong>Last Name</strong></td>
</tr>

<% do while not rs.eof %>

<tr>
<td VALIGN="TOP"><a href="adminphysiciansummary2.asp?ID=<%=rs("physicianid")%>"><%= rs("physicianid")%> </a></td>
<td><%=rs("physpecialty")%></td>
<td><%=rs("phylastname")%></td>
</tr>
<%
rs.movenext
loop

rs.Close
set rs = nothing
set conn = nothing

end if


%>
<tr><td colspan="4">&nbsp;</td></tr>

</table>
<%
end sub

Thanks again as I would be lost without the information you lot are giving me.

 

[vongrunt]

Don't ya people know that spaghetti aren't good for weight control?

aColumns = Array( "Specialty", "MedicalSchool", "lastname", "BoardStatus", "Email")
aWhere = Array()

For Each sCol in aColumns
	sValue = Request(sCol)
	If sValue <> "" Then
		ReDim Preserve aWhere(UBound(aWhere)+1)
		aWhere(UBound(aWhere)) = sCol & " LIKE '" & sValue & "%'"
	End If
Next		

txtsql= "SELECT a.PhysicianId, a.PhySpecialty, a.phyemail, a.Phyhomephone,a.Phylastname, a.Phyfirstname, b.MedicalSchool, b.Internship,   b.Residency, b.SalaryExpected,"_
     & " b.BoardStatus,b.LicenseStates,b.SalaryExpected, b.SponsEmpVisaStatus"_
     & " from Physiciancontact AS a inner JOIN physicianqualifications AS b ON       a.physicianid = b.physicianid"

If UBound(aWhere) >= 0 Then txtsql = txtsql & " WHERE " & Join(aWhere, " AND ")

-- proceed as usual

You may also need to DIM variables, escape sValue to allow quotes in search values etc.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLBill]

If you are getting this error:

Incorrect syntax near 'Whe'.

That would indicate to me that your script is being truncated. The only part of your script that matches that is the Where statement. But if the error was near the Where...it would be spelled out fully, not partially (Whe).

I think if you add this after the ENDIF, it will display the actual script being run:

PRINT txtsql

-SQLBill

Posting advice: FAQ481-4875

 

[TheJon]

Rather than taking the last 3 characters off your query to deal with an unsatisfied "AND", try building the query with a true statement and for each additional clause add it as "and blah = blah". e.g.

txtsql= "SELECT a.PhysicianId, a.PhySpecialty,"
txtsql= txtsql & " a.phyemail,a.Phyhomephone,a.Phylastname,"
txtsql= txtsql & " a.Phyfirstname, b.MedicalSchool,"
txtsql = txtsql & " b.Internship, b.Residency,"
txtsql= txtsql & " b.SalaryExpected,b.BoardStatus,b.LicenseStates,"
txtsql= txtsql & " b.SalaryExpected, b.SponsEmpVisaStatus"
txtsql = txtsql & " from Physiciancontact AS a inner JOIN" 
txtsql= txtsql & " physicianqualifications AS b ON "
txtsql = txtsql & " (a.physicianid = b.physicianid)"
txtsql= txtsql & " Where 1 = 1"
    
if (strphYSpecialty<>"") Then
txtsql=txtsql & "AND phySpecialty LIKE '"  & strphYSpecialty
End if

...

if (strSponsEmpVisaStatus<>"") Then
txtsql=txtsql & "AND BoardStatus LIKE '"  & BoardStatus
End if 

txtsql = txtsql & " order by a.physicianId;"

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[DotNetGnat]

oops..sorry my bad....change the last section of your sql code to something like this..

if (strSponsEmpVisaStatus<>"") Then
txtsql=txtsql & "BoardStatus LIKE '" & BoardStatus & "%' AND"
end if
If right(sSql,6)="where" then
txtsql=left(txtsql, len(txtsql) - 5)
else
txtsql=left(txtsql,len(txtsql)-3)
End if

txtsql = txtsql & " order by a.physicianId;"


that should work...

-DNG

 

[DotNetGnat]

sorry again..

this line...If right(sSql,6)="where" then
should be If right([red]txtsql[/red],6)="where" then

-DNG

 

[jordan11]

ok I feel I am getting nearer yhanks to all the help I received as I can I get results if I make one selection ie strphYSpecialty.
The first problem is if nothing is selected I still get the error message

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Whe'

and the second problem is if i try to make 2 selection such as strphYSpecialty and strmedicalschool I get the error message

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'ANDMedicalSchool'.

Which is a problem as I would like the user to be able to select as many fields as possible and then press submit so there results should be based on there selected criteria

Thanks

 

[DotNetGnat]

see that string 'ANDMedicalSchool'...you are not being careful in the spaces before and after the quotes..

i would suggest you to end the line with no space...

and the new line with a space...

something like this...

txtsql = select blahblah"
txtsql = txtsql & " FROM mytable"

observe the space in the second line before the word FROM...

-DNG