StrataFrame Forum

Problem with searching on a varchar(2) field...

http://forum.strataframe.net/Topic13193.aspx

By Robin J Giltner - 12/28/2007

I have a table with a varchar(2) field in it.  While checking out a problem that exhibited when using the Contains on a BrowseDialog of the table, I noticed that the query was putting in '%I' instead of '%I%' when searching for Contains with a 'I'.  I figured this was due to the varchar(2) field.

Do you think the easiest way to handle this would be to simply switch that field to a varchar(4), so that it could properly add '%' to the search parameter value ?

Thanks,

Robin Giltner

By Kenneth Langley - 1/17/2008

This occurs for varchar(7) fields also if you search with contains.
By StrataFrame Team - 1/17/2008

Are you using the SqlDataSourceItem?
By StrataFrame Team - 1/17/2008

The SqlDataSourceItem is configured to do this:

loParam.Value = "%" & CType(loWhere.Values(0), String) & "%"

when you use Contains... something else is amiss...

By Kenneth Langley - 1/17/2008

test
By Kenneth Langley - 1/17/2008

When I use a field that is a a varchar(44) in the database. If I include say only 2 or three letters in the search, the query looks like

exec sp_executesql N'SELECT TOP 1000 [countryPK], [ccode], [ccountry] FROM [dbo].[countrycode] WHERE [ccountry] LIKE @PARAM0;

',N'@PARAM0 varchar(44)',@PARAM0='%Andorra%'

 


But, lets say I use 44 characters in my search (same field) the query looks like

exec sp_executesql N'SELECT TOP 1000 [countryPK], [ccode], [ccountry] FROM [dbo].[countrycode] WHERE [ccountry] LIKE @PARAM0;

',N'@PARAM0 varchar(44)',@PARAM0='%Andfghjyuilopoiujhgfrtyuioplkijuhytredswqas'


These are both using contains on the form.

This is the case on another table with a varchar(7) field.  If you use all 7 characters for a search (Z155326) it puts in the query @PARAM0=’%Z15532’

And leaves off the last character, and thus no results are returned, because our table doesn’t have any items ending in Z15532, it has an item starting with Z15532 (Z155326).

I hope this makes sense.

Thanks.

Also Robin Giltner's forum account is unable to post to the forum. Any help would be appreciated.
By Kenneth Langley - 1/17/2008

After making the changes you mentioned in the SqlDataSourceItem Class, method CreateDBParameter.   I changed line 1006 from

'-- Set the size on the parameter

loReturn.Size = QueryInfo.FieldLengths(FieldName)

to

If QueryInfo.FieldLengths(FieldName) > 0 Then

'-- Set the size on the parameter + 2

loReturn.Size = QueryInfo.FieldLengths(FieldName) + 2

Else

'-- Set the size on the parameter

loReturn.Size = QueryInfo.FieldLengths(FieldName)

End If

And this seems to have fixed our problem of using all possible characters on a Contains search in the BrowseDialog.

Thank you guys.

By Trent L. Taylor - 1/17/2008

Good. I thought that this might fix it.  I will go ahead and make the change to the framework source so it will be included in the future.  Sorry I was is such a rush...but things are crazy around here today Smile
By Kenneth Langley - 1/17/2008

You guys are the greatest. Thanks for the very prompt reply and fix. Just another reason supporting our decision for using this framework.
By Trent L. Taylor - 1/17/2008

Thank you very much, sir Smile
By Robin J Giltner - 1/17/2008

Thanks from me too guys.
By Trent L. Taylor - 1/17/2008

You're very welcome...thanks for all of your help as well!