multiple wildcards in WHERE statement

[mfenn]

Hello,

I'm using classic asp with a text (flat file) database that doesnt like searches using "like" or left(field,N) so I'm trying wildcards.

I'm looking to see if a person exists in our database already and trying to account for typo's. The search is looking for partials on first and last name and year part of dob.

Here is my code for the search:

Dim strTempLast
Dim strTempFirst
Dim strTempDOB

strTempLast = left(strEmpLastName,5) + "%"
strTempFirst = left(strEmpFirstName,3)+ "%"
strTempDOB = left(strEDOB,4)+ "%"
		
dbconn3.open ImpactCS
GetImpMemName = "0"
strsql = "select mem_id from mem where RELATION = '01' and (DATE_OF_BIRTH like '" & strTempDOB & "')  and (LAST_NAME like '" & strTempLast & "') and (FIRST_NAME like '" & strTempFirst & "')   "
set rst10 = dbConn3.execute(strsql)
if not rst10.EOF then
strMyMEMID    = trim(rst10.Fields("MEM_ID"))
End if
GetImpMemName = strMyMEMID
dbConn3.close

It seems to work only when its a single wildcard search (i.e. last name only) but that does not usually give me the correct person.

strsql = select mem_id from mem where RELATION = '01' and (DATE_OF_BIRTH like '1973%') and (LAST_NAME like 'Strou%') and (FIRST_NAME like 'Mel%')

If I do a manual search of the database using Strou, Mel, 1973 i get the correct person. I'm not getting an error, just not getting any data either.

What am i doing wrong? Any help is appreciated.

mfenn

 

[xwb]

Try

MsgBox strsql

before the execute. Are you getting what you expect?

 

[mfenn]

xwb,
Thanks for replying. I get a permissions error when using MsgBox so I'm not sure what I should expect to see.

response.write strsql gives me: select mem_id from mem where RELATION = '01' and (DATE_OF_BIRTH like '1973%') and (LAST_NAME like 'Strou%') and (FIRST_NAME like 'Mel%')

It should return: 00572573 as mem_id

 

[Turkbear]

Can you confirm that the DATE_OF_BIRTH starts with 1973 for mem_id 00572573 and RELATION = "01"?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mfenn]

Date of birth is YYYYMMDD with YYYY being 1973 and Relation is 01.

 

[xwb]

Sorry - shouldn't have been Msgbox - forgot it was asp.

Are you using the same database in both cases?

Is DATE_OF_BIRTH stored as a string or as a number?

 

[mfenn]

Good question xwb. The database in question is really a text file so I'm guessing everything is a string.

We have a 3rd party software application that stores our membership as a text file (.dbf or data dictionary format if that means anything). We need the data in realtime so we have to use these text files instead of SQL Server, which is only updated at night via DTS.

It's basically telling me that its not finding any matches in the database because its returning strMyMEMID = 0.

 

[xwb]

Try this. Create a file called query.vbs on the server. In the file insert

' Set up the query
strEmpLastName = "Stroud"
strEmpFirstName = "Melon"
strEDOB = "19730101"

' Connect to your DB and open it
...
' Insert your posted code here up to execute
...
if rst10.EOF then
   wscript.echo "nothing found"
else
   wscript.echo "[" & rst10.Fields("MEM_ID") & "]"
End if
dbConn3.close

Execute from the command line using cscript query.vbs.
I don't know if it is case sensitive - you might like to try mem_id instead of "MEM_ID" as the Fields parameter.

 

[mfenn]

Sorry, I'm not familiar with vbscripting. I'm getting an error that says Object Required: 'Server'

' Set up the query
strEmpLastName = "Stroud"
strEmpFirstName = "Mel"
strEDOB = "1973"

' Connect to your DB and open it...
Set dbConn3 = Server.CreateObject("ADODB.Connection")
Set rst10 = Server.CreateObject("ADODB.RecordSet")
dbconn3.open  = "DSN=fstest.dbd; Min Pool Size=2;Max Pool Size=10;Connection Reset=True;"

' Insert your posted code here up to execute...
strsql = "select mem_id from mem where RELATION = '01' and DATE_OF_BIRTH like 'strEDOB' and FIRST_NAME like '" & strEmpFirstName & "'  and LAST_NAME like '" & strEmpLastName & "' "
set rst10 = dbConn3.execute(strsql)

if rst10.EOF then   
	wscript.echo "nothing found"
else   
	wscript.echo "[" & rst10.Fields("MEM_ID") & "]"
End if
dbConn3.close
set dbConn3 = nothing

 

[guitarzan]

mfenn: You can run that code in a vbs by removing the "Server." before the two "CreateObject" lines. But there is no need for that, you can test in ASP, just use "Response.write" instead of "WScript.echo".

And you had a typo with strEDOB. The code below should run just fine in ASP, and will either return "nothing found", or will give you a mem_id in brackets.

' Set up the query
strEmpLastName = "Stroud"
strEmpFirstName = "Mel"
strEDOB = "1973"

' Connect to your DB and open it...
Set dbConn3 = Server.CreateObject("ADODB.Connection")
Set rst10 = Server.CreateObject("ADODB.RecordSet")
dbconn3.open  = "DSN=fstest.dbd; Min Pool Size=2;Max Pool Size=10;Connection Reset=True;"

' Insert your posted code here up to execute...
strsql = "select mem_id from mem " & _
   " where RELATION = '01' " & _
   " and DATE_OF_BIRTH like '" & strEDOB & "' " & _
   " and FIRST_NAME like '" & strEmpFirstName & "' " & _
   " and LAST_NAME like '" & strEmpLastName & "' "
set rst10 = dbConn3.execute(strsql)

if rst10.EOF then   
   response.write "nothing found"
else   
   response.write "[" & rst10.Fields("MEM_ID") & "]"
End if
dbConn3.close
set dbConn3 = nothing

 

[mfenn]

guitarzan - that did work and returned "nothing found"

I played around with it a bit more and found the problem. The problem appears to have been that I didn't specify the first and last names have to be UPPER. It's never mattered in any other query, but apparently having the text file as the database requires this...?

Thanks everyone for your help!