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

 

[jordan11]

Thank you so much that so that solved that problem I can now make as many selection as I like. You are the best and thanks for being patient with me.


Do you have an idea whatI need to do if no selection is made so I can get rid of the other error message.


Thanks again

 

[DotNetGnat]

WE alreay took care of it...

see this piece of code again...

If right(txt,6)="where" then
[red]'this mean that no selection is made...
'and our query string look something like this...
'select blah from mytable where "
'and we dont want that dangling "Where"
'so we delete 5 characters from left...
[/red
txtsql=left(txtsql, len(txtsql) - 5)


else

[blue]'here we have some condition and if
'suppose our string ends with a dangling "AND"
'we are deleting it...
[/blue]

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

hope that helps

-DNG

 

[DotNetGnat]

WE alreay took care of it...

see this piece of code again...

If right(txt,6)="where" then
[red]'this mean that no selection is made...
'and our query string look something like this...
'select blah from mytable where "
'and we dont want that dangling "Where"
'so we delete 5 characters from left...
[/red]
txtsql=left(txtsql, len(txtsql) - 5)


else
[blue]
'here we have some condition and if
'suppose our string ends with a dangling "AND"
'we are deleting it...
[/blue]

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

hope that helps

-DNG

 

[TheJon]

Watch out for the hanging spaces:

txtsql= txtsql & " Where "

either use

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

or try my code from above (observing DotNetGnats fine comment about placement of spaces (about 4 posts up))

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

 

[jordan11]

Thanks you guys I will check my spacing again !