StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Exact searching

Exact searching

Thomas Holste — Tue, 31 Aug 2010 14:07:41 GMT

Hi there,

in one of my BOs ("LieferBO") I added a get-method I took from the help-file

and modified it to myBO-name and field-name.

[code]

Dim loCommand As New SqlCommand()

loCommand.CommandText = "SELECT COUNT(*) FROM liefer WHERE adresse = @pAdresse"

loCommand.Parameters.Add("@pAdresse", SqlDbType.VarChar)

loCommand.Parameters("@pAdresse").Value = cAdresse

Return CType(Me.ExecuteScalar(loCommand), Integer)

[/code

But now I have the following problem which I can't solve by myself:

There are, for example the following records with the field "adresse" which is a char(6)-field:

B-M

BALM

I want to search, if there is a record with "BAL", which would be a valid new value, but the method returns 1 because it finds the "BALM"-record. I tried changing the var cAdresse to cAdresse.padr(6," ") which always worked with VFP-Views, but not here.

Can somebody advise me how this can be done?

Thanks in advance

Thomas

RE: Exact searching

Thomas Holste — Tue, 31 Aug 2010 14:07:41 GMT

Hi Greg,

thank you for explaining the advantage of a varchar-type.

I guess, one of my next tasks will be to write conversion-apps (from VFP) and

compare the speed with some bigger tables (> 100000 records) .

Best regards

Thomas

RE: Exact searching

Greg McGuffey — Tue, 31 Aug 2010 09:19:33 GMT

I tend to almost always use varchar (or nvarchar if I expect to support multiple languages). If I did use a char is would be in cases were all data would be close to or exactly the length of the field. It is my understanding that if the values are variable, then a Varchar is faster (because exactly the data that is needed is sent over the wire). Usually if I want to encode data, I use an enum and store an int in the database, which is way faster than a char.

RE: Exact searching

Thomas Holste — Tue, 31 Aug 2010 02:36:34 GMT

Hi Greg,

thanks a lot for your help. After playing around a bit i found the reason why the function does not work as I expected it. I call it the "Cut and Paste Disease" because I copied the example but did not change

loCommand.Parameters.Add("@pAdresse", SqlDbType.VarChar)

loCommand.Parameters.Add("@pAdresse", SqlDbType.Char)

Because my datatype is char.

Nowe everything works fine. But after all I have a more generic question and that is what I should do when my data-conversion has to become more precise (By now I used Les Pinters conversion tool to ha a starting point)? Shoul I use char or varchar types. Space is today no problem but speed will always be, so after my reading by now I decided to use the char-type. What is your suggestion?

Thanks a lot

Thomas

RE: Exact searching

Greg McGuffey — Mon, 30 Aug 2010 14:57:33 GMT

Thomas, I think the problem is that the data type of the field is char(6). This means that the field [b]always[/b] has 6 characters (see last paragraph). However, when you are setting things up in .NET, it is a string, which won't have the extra characters. I.e.

If you provide 'B-M' from .NET, it won't match the value in the database, which is 'B-M ' (note extra spaces).

If the values are unique enough, you can just switch to a like statement:

WHERE adresse Like(@pAdresse + '%')

This won't work if you could have values like 'BAL' and 'BALM', because 'BAL' would incorrectly match 'BALM' also.

The next option would be to manipulate the SQL field by trimming it:

WHERE adresse = RTRIM(@pAdresse)

You could also try padding the .NET strings, but that would be harder than just trimming the db value.

When I did a test in SQL Server 2005, you original query worked. I.e. SQL Server 2005 doesn't include the extra spaces to ensure that the string is 6 characters. However, I think SQL Server 2000 does include the extra spaces, because I know I'd had to deal with this before.

RE: Exact searching

Edhy Rijo — Mon, 30 Aug 2010 13:37:22 GMT

Hi Thomas,
Your query looks OK and it should be returning a full match. The behavior you are describing is what you get when using the LIKE % operator. I am not sure, but probably there is some switch somewhere in MS-SQL to change the behavior, try looking in Google since this is basically plain SQL.

By the way, have you tested your query in MS Management Studio?