StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Problem with searching on a varchar(2) field...

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

Robin J Giltner — Thu, 17 Jan 2008 16:04:52 GMT

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

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

Trent L. Taylor — Thu, 17 Jan 2008 16:04:52 GMT

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

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

Robin J Giltner — Thu, 17 Jan 2008 14:56:34 GMT

Thanks from me too guys.

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

Trent L. Taylor — Thu, 17 Jan 2008 14:32:21 GMT

Thank you very much, sir :)

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

Kenneth Langley — Thu, 17 Jan 2008 14:18:02 GMT

You guys are the greatest. Thanks for the very prompt reply and fix. Just another reason supporting our decision for using this framework.

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

Trent L. Taylor — Thu, 17 Jan 2008 14:11:07 GMT

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 :)

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

Kenneth Langley — Thu, 17 Jan 2008 14:02:26 GMT

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

[codesnippet]

'-- Set the size on the parameter

loReturn.Size = QueryInfo.FieldLengths(FieldName)

[/codesnippet]

[codesnippet]

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

[/codesnippet]

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

Thank you guys.

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

Kenneth Langley — Thu, 17 Jan 2008 13:19:50 GMT

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

[codesnippet]

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

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

[/codesnippet]

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

[codesnippet]

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

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

[/codesnippet]

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.

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

Kenneth Langley — Thu, 17 Jan 2008 13:15:40 GMT

test

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

StrataFrame Team — Thu, 17 Jan 2008 09:10:14 GMT

The SqlDataSourceItem is configured to do this:

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

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

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

StrataFrame Team — Thu, 17 Jan 2008 09:07:58 GMT

Are you using the SqlDataSourceItem?

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

Kenneth Langley — Thu, 17 Jan 2008 08:09:37 GMT

This occurs for varchar(7) fields also if you search with contains.