StrataFrame Forum » StrataFrame Application Framework - V1 » Issues » Browse

Browse

Nikki Chase — Thu, 20 Apr 2006 16:19:41 GMT

Hi all,

I am having a problem with the browse object not seeming to work properly.

It will return ALL records just fine, however I never seem to get any records returned when I try to search by any of the search fields.

For instance if I launch the browse dialog and hit Enter it returns all rows however if I try to filter by name starts with A it returns no results. Any ideas on what i mat be missing?

Thanks

RE: Browse

Trent L. Taylor — Thu, 20 Apr 2006 16:19:41 GMT

I never thought for a minute that you were "crazy" :). I know exactly where you are coming from though. It is always nice to find out that it's not you :D.

Nikki, I am truly excited about having you as a new user. Thank you for your kind words. We too are excited about the framework and its future. It is users like you that makes it all worth while!

StrataFrame is a great tool to migrate forward for VFP users. Let me know if you need any help on that front. Thanks again.

RE: Browse

Nikki Chase — Thu, 20 Apr 2006 14:53:39 GMT

Well at least I know I'm not crazy :) Well at least not about this.

After looking at the trace i thought the issue was because of the Field type so changed it to see what would happen and bingo it worked as advertised. Glad it is resolved in the new build.

By the way great framework glad to see something like this in .Net. I have been showing it off like a new car. I plan on re-writing / converting a fox 2.6 app with this so talk about bells and whistles to the current users.

RE: Browse

Trent L. Taylor — Thu, 20 Apr 2006 14:38:49 GMT

Nikki,

I see the problem. This issue has been fixed and can be downloaded in the pre-release or if you wait another day we will have the "official" 1.4 released. The problem that you are having is that your field is a fixed length string instead of a variable length field (i.e. VarChar). When this happens, the field contents actually has spaces at the end, which produces a search that doesn't match.

We have already fixed this issue to take into account fixed length character fields. If you want to fix to day, you can download the Pre-Release. But it has been resolved and this is why we have been chasing our tails :D

RE: Browse

Nikki Chase — Thu, 20 Apr 2006 14:33:28 GMT

Trent I can manualy retireve data if I change the @param to char(2) and remove the trailing spaces. It seems the parameter length is throwing it off

exec sp_executesql N'SELECT TOP 1000 [cu_pk_id], [cu_fk_sm_id], [cu_id], [cucode], [cutype], [cuname], [cuaddr],

[cuadl2], [cucity], [custat], [cuzipc], [cubaddr], [cubadl2], [cubcity], [cubstat], [cubzipc], [cucraprvd],

[cuterms], [cutdays], [cubuconame], [cubucophon], [cubucoextn], [cuapconame], [cuapcophon], [cuapcoextn], [cuauthot],

[cuauthfone], [cufaxfone], [cu800fone], [cunextjo], [cufrstused], [culastused], [cucurbalnc], [cuo30balnc],

[cuytdbilng], [cu90dbilng], [cucredlim], [cucredopn], [cucomment], [custateid], [sm_id], [cudispcnt], [cudisdays],

[cuactid], [culastupdt], [cu_Use_Main] FROM [dbo].[customer] WHERE [cuname] LIKE @PARAM0;

',N'@PARAM0 char(2)',@PARAM0='A%'

declare @PARAM0 char(2)

SET @PARAM0 = 'A%'

SELECT TOP 1000 [cu_pk_id], [cu_fk_sm_id], [cu_id], [cucode], [cutype], [cuname], [cuaddr], [cuadl2], [cucity], [custat], [cuzipc], [cubaddr], [cubadl2],

[cubcity], [cubstat], [cubzipc], [cucraprvd], [cuterms], [cutdays], [cubuconame], [cubucophon], [cubucoextn], [cuapconame], [cuapcophon],

[cuapcoextn], [cuauthot], [cuauthfone], [cufaxfone], [cu800fone], [cunextjo], [cufrstused], [culastused], [cucurbalnc], [cuo30balnc],

[cuytdbilng], [cu90dbilng], [cucredlim], [cucredopn], [cucomment], [custateid], [sm_id], [cudispcnt], [cudisdays], [cuactid], [culastupdt],

[cu_Use_Main]

FROM [dbo].[customer]

WHERE [cuname] LIKE @PARAM0

RE: Browse

Nikki Chase — Thu, 20 Apr 2006 14:28:05 GMT

Here what i get in Sql Profiler.

exec sp_executesql N'SELECT TOP 1000 [cu_pk_id], [cu_fk_sm_id], [cu_id], [cucode], [cutype], [cuname], [cuaddr],
[cuadl2], [cucity], [custat], [cuzipc], [cubaddr], [cubadl2], [cubcity], [cubstat], [cubzipc], [cucraprvd],
[cuterms], [cutdays], [cubuconame], [cubucophon], [cubucoextn], [cuapconame], [cuapcophon], [cuapcoextn], [cuauthot],
[cuauthfone], [cufaxfone], [cu800fone], [cunextjo], [cufrstused], [culastused], [cucurbalnc], [cuo30balnc],
[cuytdbilng], [cu90dbilng], [cucredlim], [cucredopn], [cucomment], [custateid], [sm_id], [cudispcnt], [cudisdays],
[cuactid], [culastupdt], [cu_Use_Main] FROM [dbo].[customer] WHERE [cuname] LIKE @PARAM0;
',N'@PARAM0 char(40)',@PARAM0='A% '

RE: Browse

Trent L. Taylor — Thu, 20 Apr 2006 14:23:44 GMT

Next question :)

If you create a query through the Enterprise Manager or SQL Management Studio, do you get any data back? Are there any records that begin with "A"? To prove this you can manually open your table and look or execute the query directly on the server.

To prove it you can copy the SELECT statement that is created and paste into the SQL Enterprise Manager or SQL Management Studio query window and replace the @PARAM0 with 'A%' and see if you get any results. There is nothing wrong with the SELECT statement, so we need to investigate the server and the data a little more closely.

Let me know what you get. Thanks :)

RE: Browse

Nikki Chase — Thu, 20 Apr 2006 14:12:25 GMT

Here is the result

General Command Settings
Command Type:	Text
Object Type:	System.Data.SqlClient.SqlCommand
Connection String:	Data Source=APOLLO\sql2005;Initial Catalog=payroll;Integrated Security=True;Persist Security Info=False;Asynchronous Processing=True
Transaction:	False
Command Settings
CommandText:	SELECT TOP 1000 [cu_pk_id], [cu_fk_sm_id], [cu_id], [cucode], [cutype], [cuname], [cuaddr], [cuadl2], [cucity], [custat], [cuzipc], [cubaddr], [cubadl2], [cubcity], [cubstat], [cubzipc], [cucraprvd], [cuterms], [cutdays], [cubuconame], [cubucophon], [cubucoextn], [cuapconame], [cuapcophon], [cuapcoextn], [cuauthot], [cuauthfone], [cufaxfone], [cu800fone], [cunextjo], [cufrstused], [culastused], [cucurbalnc], [cuo30balnc], [cuytdbilng], [cu90dbilng], [cucredlim], [cucredopn], [cucomment], [custateid], [sm_id], [cudispcnt], [cudisdays], [cuactid], [culastupdt], [cu_Use_Main] FROM [dbo].[customer] WHERE [cuname] LIKE @PARAM0;
Command Parameters
@PARAM0:	A% (AnsiStringFixedLength)

RE: Browse

Trent L. Taylor — Thu, 20 Apr 2006 12:52:03 GMT

You can turn on SQL debugging to see what the actual SQL statement is when executing the browse. Why don't we do that and see what is going on behind the scenes. To turn on SQL debugging add the following code:

AppMain.vb or program.cs

MicroFour.StrataFrame.Data.DataLayer.DataSources(0).DebugMode = True

This will turn on the debugging and will bring up a window each time an SQL query is executed. So be patient as you get to the browse dialog. The window will look something like this:

RE: Browse

Nikki Chase — Thu, 20 Apr 2006 10:58:44 GMT

That is exactly how it is set. Just to make sure here is a shot of that window. If I run the following Query in Sql Server it return the expected results.

SELECT    *
                 FROM         customer
WHERE     (cuname LIKE 'A%')

RE: Browse

Trent L. Taylor — Thu, 20 Apr 2006 10:27:17 GMT

Actually you need to go a little deeper to get to the BeginsWith option. Selection your SearchFields property and then clik the "..." next to it. This will bring up an editor. Select your search field, and then click the edit button, this will bring up the window in question :) It will look something like this:

Make sure that the BeginsWith is set and that it is not set to equals or something of that nature. Hope this helps.

RE: Browse

Nikki Chase — Thu, 20 Apr 2006 09:33:07 GMT

I believe that is how i have it set up. Here are a few screen shots that hopefully illustrate the settings I have.

RE: Browse

Trent L. Taylor — Thu, 20 Apr 2006 08:42:18 GMT

Gary is correct, the first place to look is the BeginsWith that is set within the SearchFields property of the BrowseDialog control. SQL commands are dynamically created based on the Advanced options that are active for each search field. If no value is entered within a search field, no WHERE clause is added for the field. So if you enter something in the field, it will use the Advanced option (even if you do not allow the end-user to change the advanced option) to build that segment of the WHERE clause.

RE: Browse

Gary Wynne — Thu, 20 Apr 2006 08:28:15 GMT

Hi Nikki

Ensure that the field selection is set to "Beginswith". This will ensure that the query uses a LIKE "A%" or similar as opposed to an = "A". Checkout the BrowseDialog help in the guide which makes reference to this.

HTH

-=Gary

RE: Browse

Gary Wynne — Thu, 20 Apr 2006 08:25:56 GMT

Hi Nikki

(Third Time lucky) :)

Check that advanced options are made available and ensure that the search field is set to "Beginswith" - see the browse dialog help.

This will effectively ensure that a LIKE comparison is performed instead of an equals. If you don't, it will look for a value that is exactly equal to "A" instead of LIKE "A%".

HTH

-=Gary

RE: Browse

Gary Wynne — Thu, 20 Apr 2006 08:23:17 GMT

Hi Nikki

RE: Browse

Gary C Wynne — Thu, 20 Apr 2006 08:22:01 GMT

Hi Nikki