vb/sql communication problem

[Pinan]

I have a vb/sql quesry that sort of works, and I cant tell where the error is. Here is the code.

strPullSQL = "SELECT * from bw5833.test_table WHERE (username LIKE '%" & txtSearch & "%') AND (Date_Run LIKE '%" & txtDate & "%') AND (PI_Value LIKE '%" & txtPI.Text & "%')"

When I enter info into the txtSearch variable (all letters), it searches the db great. However, whenever I enter info into the txtDate or the txtPI variable, it returns ALL records. Why would it work with one and not the others?

Here is a hint....When I run the program against anything other than txtDate, I get a "Partial String Matching requires charachter operands" which I can find no information on in the Black Book.

The line of code that it points to is

EDWInfpen strPullSQL, Cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText

Any help would be greatly appreciated

 

[IFELSETHEN]

If you can, change your LIKE DATE to BETWEEN Date would be better. John

 

[Malchik]

hi

the problem is your % in the criterias. If you provide the first criteria it will be ok, but if you omit it, SQL will receive this as statement:

SELECT * from bw5833.test_table WHERE (username LIKE '%%') AND (Date_Run LIKE '%2002-07-15%') AND (PI_Value LIKE '%%')

so it will return all the records because of %% (wildcards like * in Access or FoxPro). To avoid this add your criteria only if something is provided:

if len(txtSearch)>0 then
sCriteria = sCriteria & "username LIKE '%" & txtsearch & "%'"
end if

if len(Date_txtDate )>0 then
if scriteria<>&quot;&quot; then scriteria=scriteria & &quot; AND &quot;
sCriteria = sCriteria & &quot;Date_Run LIKE '%&quot; & txtDate & &quot;%'&quot;
end if

.... and so on...


Mal'chik

 

[Pinan]

I'll update the problem. I now have only this for code.

strPullSQL = &quot;SELECT * from bw5833.test_table WHERE (PI_Value = '&quot; & txtSearch & &quot;')&quot;

I still get the exact same error code. txtPI is just a five digit number.

 

[Pinan]

Malchik,
would

if txtSearch <> &quot;&quot; then
strPullSQL = &quot;SELECT * from bw5833.test_table WHERE (PI_Value = '&quot; & txtSearch & &quot;')&quot;

work in place of

if len(txtSearch)>0 then
sCriteria = sCriteria & &quot;username LIKE '%&quot; & txtsearch & &quot;%'&quot;
end if

 

[Malchik]

Hi,
its supose to work with what you put. But you will have only one criteria and it must be exact Mal'chik

 

[johnwm]

Is PI_Value a numeric field?
If so try WHERE(PI_Value = '&quot; & cint(txtSearch) & &quot;')&quot;
Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[jcalcoa]

If PI is a numeric value remove the single quotes.
WHERE(PI_Value = &quot; & cint(txtSearch) & &quot&quot;