Compare user-input text with db entry 1

[adrianjohnson]

I'm trying to compare what a user types in a text-area on a html form, with Code entries in the database.

The codes in the database could be:
AC100
A8300 ... and so on

The user can type anything in the text-area, but it might contain some of the codes.

E.G.

text-area: "Person ordered AC100, A8300"

I've got a recordset (rsCodes) to grab the codes from the database, and retrieved the data from the form. But, what I want to do is see if the user typed in any of the codes and flag it up.

How I've tried to do it (in simple English) is:
1. Create recordset.
2. Loop through each code.
3. Compare the code to the text-area entry
4. If 3 is the same, do something.
5. Move onto the next code.

Or, in ASP

rsCodes.MoveFirst
Do While Not rsCodes.EOF
	lsCode = rsCodes("CodeID")
	lsTreat1 = Ucase(lsTreat) ' From text-area
	'If strComp(lsCode, lsTreat1, 1) = 0 Then
	If Instr(lsCode, lsTreat1) >=0 Then
            ' Do something
	End If
	rsCodes.MoveNext
Loop

As you can see, I've tried Instr and strcomp, but neither give me success.

How can I do this?

Many thanks,

Adrian Johnson

 

[Lothario]

You can use...

SELECT blah, blah1 FROM yourtable WHERE CODEID LIKE '%"&usertext&"%'

-L

 

[adrianjohnson]

But then that would be multiple reads from the db, and splitting the user text into codes.

Remember the user can type anything, and intersperse codes in there.

 

[Lothario]

if suppose the user enters...

"Person ordered AC100, A8300"

then you will get records for both the AC100 CodeId and A8300 CodeId if you use...

SELECT CodeId FROM yourtable WHERE CODEID LIKE '%"&usertext&"%'

because the text entered by the user can both be looked as

"%AC100%"
or
"%A8300%"

then after getting this recordset which also contains CodeID...you can easily do the comparison...

May be i am missing something...

-L

 

[adrianjohnson]

No, I think I'm missing something.

Is this instead of the string comparison bit, e.g. loop through the recordset, then do the SQL.

Sorry, it's been a long day!

 

[Lothario]

Yeah...i was referring that your rsCodes recordset can be modified with the SQL query i suggested to bring only the codes that the user entered text contains...

does that make sense?

-L

 

[adrianjohnson]

Er no.

Sorry for being dim, but how would the code look?

(Thanks for the help, btw).

Adrian

 

[Lothario]

ok...

show me your code for rsCodes recordset...

i mean u have something like...

rsCodes.Open "SELECT....."

just post that part of code...

-L

 

[adrianjohnson]

Here:

SQLCmdc = "Select * FROM tblCodes"
Set rsCodes = conAPPS2.Execute(SQLCmdc)

rsCodes.MoveFirst
Do While Not rsCodes.EOF
  ' Compare code with usertext.
  rsCodes.MoveNext
Loop

 

[Lothario]

ok create another query...

SQLCmdc1= " SELECT CODEID FROM tblCodes WHERE CODEID LIKE '%"&usertext&"%' "
Set rsCodes1 = conAPPSnew.Execute(SQLCmdc1)

Now compare...

rsCodes("CodeID") with rsCodes1("CodeID")

-L

 

[adrianjohnson]

On the first line after the new Execute line (for SQLCmdc1), I get:

ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

 

[Lothario]

Sorry...i got the other way around...try this..

I think you should use array to break up the text entered by the user and then do a comparison...

something like this:

Dim utext,uarray
utext="TEXT ENTERED BY THE USER"
uarray=split(utext," ")

For i=0 to Ubound(uarray)
splittext=Response.Write uarray(i)&","
Next

splittextnew=Response.Write "(" & splittext & Response.Write ")"

Then you can use the following query

SQLCmdc1= " SELECT CODEID FROM tblCodes WHERE CODEID IN '"&splittextnew&"' "
Set rsCodes1 = conAPPSnew.Execute(SQLCmdc1)


-L

 

[adrianjohnson]

Ok, thanks.

I'll try that when I get a minute, sometime this evening.

Thanks for the help.

 

[adrianjohnson]

Thanks for the code, Lothario, but it still only returns one code (when the user enters 2)

I had to fiddle with the code to stop some error messages, but this is what I got:

arTreat = Split(lsTreat, " ")

For i=0 To UBound(arTreat)
 lsTreatTxt = "'" & UCase(arTreat(i)) & "',"
Next

lsTreatText = "(" & lsTreatTxt & ")"

'SQLCmdc = "Select CodeID FROM tblCodes WHERE CodeID IN '" & lsTreatText & "'"
SQLCmdc = "Select CodeID FROM tblCodes WHERE CodeID IN " & lsTreatText
Set rsCodes = conAPPS2.Execute(SQLCmdc)

If rsCodes.BOF And rsCodes.EOF Then
	Response.Write rsCodes("CodeID")
Else
	rsCodes.MoveFirst
	Do While Not rsCodes.EOF
		Response.Write rsCodes("CodeID") & ": "
		rsCodes.MoveNext
	Loop
End If

Even when I print the SQLCmd, it still only shows 1 code. Have I missed something?

Thanks,

Adrian Johnson

 

[Lothario]

ok do some debugging with inserting response.write statements


Response.Write lsTreatText

Response.Write SQLCmdc

let me know how they look like...

-L

 

[adrianjohnson]

Already did that (for myself).

As the user, I enter:

ac100 & a8300

... both of which are on the system.

lsTreatText:

('A8300',)

SQLCmd

Select CodeID FROM tblCodes WHERE CodeID IN ('A8300',)

Thanks,

Adrian Johnson

 

[Lothario]

1. We can consider only array elements which are of length 6...i guess all your CODEID's are length 6...

This is eliminate the errors caused by entering "&" or any other special characters...

2. The trailing "," should be deleted from lsTreatText...

thats top off my head...i will try to come up with code...

in the mean time...you can think of something

-L

 

[adrianjohnson]

Sorted it.

The line:

lsTreatTxt = "'" & UCase(arTreat(i)) & "',"

should read:

lsTreatTxt = lsTreatTxt & "'" & UCase(arTreat(i)) & "',"

I always forget to do something simple!!

Thanks for your help.

Regards,

Adrian Johnson

 

[Lothario]

Yep...i missed that one too

I am glad i couldbe of help

-L