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

BZ

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

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?

Edhy Rijo

Thomas, I think the problem is that the data type of the field is char(6). This means that the field always 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.