ComboBox SelectionChanged Headache

[RandomCoder]

I have a table with a list of files whose name comprises a machine number and other values such as the date etc.
At the top of my table are ComboBoxes which are populated with unique values for each of the columns... so far so good.

My problem is that I use SelectionChanged events to filter the values displayed in the table (which I have working fine), but if I then update the ComboBox lists to reflect only the items that are in the table, thus narrowing the search, this inadvertently triggers the SelectionChanged event again and the program gets stuck in a nasty loop.

I really wanted the table to update as items are selected from the ComboBoxes but the only way round my problem as I see it is to add a Button that triggers the filter using the ComboBox values and then updates the ComboBoxes afterwards.

Can anyone suggest a better way?
Please don't post example code as I'd rather do it myself... just need a nudge in the right direction :sign0089:

Thanks,
RandomCoder

 

[Erel]

You can use a global variable (IgnoreEvent) which will function as a flag.
In sub Combo_SelectionChanged first check the value of IgnoreEvent and return if it is true.
When you update the combobox set IgnoreEvent to true at the beginning and false at the end.

 

[mjcoon]

You can use a global variable (IgnoreEvent) which will function as a flag.
In sub Combo_SelectionChanged first check the value of IgnoreEvent and return if it is true.
When you update the combobox set IgnoreEvent to true at the beginning and false at the end.

That is exactly how I solved the problem of allowing a progress TrackBar to show the autonomous playing of an mp3 file and also to allow the user to dragg it forward or back (just like normal players).

I was a bit surprised that the event was triggered by programmatic action, making it a bit recursive, as RandomCoder said. But having realised this behaviour is common amongst controls makes it easier to anticipate!

Mike.

 

[RandomCoder]

Thanks, I'll give it a go.

Regards,
RandomCoder.

 

[RandomCoder]

Got it working but now have a SQL glitch??

Thanks for the advice before, I've now resolved that problem but have stumbled across another glitch for which I can't see what I'm doing wrong....

[COLOR="Blue"]Private Sub[/COLOR] TableToSQLTable( Table , SQLTable )
 RemoveTableIfExists( SQLTable )
 SQLConnection.CreateSQLTable( Table , SQLTable )
[COLOR="blue"]End Sub[/COLOR]

[COLOR="blue"]Private Sub[/COLOR] GetFileList
 FileSearch( AlFiles , DirectoryPath , "*.rtf" )
 SQLConnection.BeginTransaction
 For i = 0 To AlFiles.Count-1
 ParsedFileName( ) = StrSplit( FileName( AlFiles.Item( i ) ) , "_" & " " )
 ParsedMagNumber( ) = StrSplit( ParsedFileName( 1 ) , "#" )
  For Num = 0 To 1
   Magazine( Num ) = ""
   If Num <= ArrayLen( ParsedMagNumber( ) )-1 Then Magazine( Num ) = ParsedMagNumber( Num )
  Next
  SQLCommand.CommandText = "INSERT INTO FileNames VALUES ( '" & i & "' , '" & AlFiles.Item( i ) & "'  , '" & _
                             ParsedFileName( 0 ) & "' , '" & Magazine( 0 ) & "' , '" & Magazine( 1 ) & "' , '" & _
                             ParsedFileName( 2 ) & "' , '" & ParsedFileName( 3 ) & "' , '" & _ 
                             ParsedFileName( 4 ) & "' , '" & ParsedFileName( 5 ) & "' )"
  SQLCommand.ExecuteNonQuery
 Next
 SQLConnection.EndTransaction

 SQLCommand.CommandText = "SELECT Machine , Magazine0 , Magazine1 , OrderNumber , BatchNumber , Date " & _
                           "FROM FileNames " & _
                           "ORDER BY Machine"
 SQLCommand.ExecuteTable( "TblFiles" , 0 )
 TableToSQLTable( "TblFiles" , "FilterList" )
[COLOR="blue"]End Sub[/COLOR]

[COLOR="blue"]Private Sub[/COLOR] RefreshFileList( Index , Value )
 If IgnoreEvent = False Then
  StrFilter = ""
  If CmbMachine.Item( CmbMachine.SelectedIndex ) <> "ALL" Then
   If StrFilter = "" Then StrFilter = "WHERE "
   StrFilter = StrFilter & "Machine = '" & CmbMachine.Item( CmbMachine.SelectedIndex ) & "'"
  End If

  If CmbMagazine.Item( CmbMagazine.SelectedIndex ) <> "ALL" Then
   If StrFilter = "" Then StrFilter = "WHERE " Else StrFilter = StrFilter & " AND "
   StrFilter = StrFilter & "(Magazine0 = '" & CmbMagazine.Item( CmbMagazine.SelectedIndex ) & _
                 "' OR Magazine1 = '" & CmbMagazine.Item( CmbMagazine.SelectedIndex ) & "')"
  End If
  SQLCommand.CommandText = "SELECT Machine , Magazine0 , Magazine1 , OrderNumber , BatchNumber , Date " & _
                            "FROM FileNames " & StrFilter & " " & _
                            "ORDER BY Machine"
  SQLCommand.ExecuteTable( "TblFiles" , 0 )
  [COLOR="Red"]TableToSQLTable( "TblFiles" , "FilterList" )[/COLOR] [COLOR="Red"]THIS LINE APPEARS TO BE DOING NOTHING!!!!!![/COLOR]
  IgnoreEvent = True
  UpdateOptions
 End If
[COLOR="blue"]End Sub[/COLOR]

To make sure that I had done nothing silly :signOops: I made the new sub called TableToSQLTable which both routines use. The first time I run it after getting a file list which populates the table with values. I then store the table in an SQL table called FileList. This works fine!! But when I filter the table and try to do the same thing again it doesn't work, I get no error messages it just appears to do nothing.

I've stepped the program through to see what is going wrong but its not obvious (not to me anyway). I've even changed the SQL Table name to FileList2 where I've marked the problem in red but alas when I check with a SQL viewer there is no new table created.

What am I doing wrong and why is there no error message

Regards,
RandomCoder

 

[Erel]

Can you upload your code (or a working part of it) which demonstrates the problem?
BTW, I recommend you to issue an INSERT query combined with the SELECT query instead of populating the Table control and then saving it back to the database.
See this post for a similar query: http://www.b4x.com/forum/questions-help-needed/4903-saving-table-data.html

 

[RandomCoder]

Hi Erel,

Please find attached my program.
It's the first time I've used SQL... even though I requested the tutorial many months ago, which I've read and referred to many times already.
I'm also attempting at using a module as then I can add it to my other program which is what created the .rtf's in the first place.

Now I'll continue looking into using the INSERT command as you suggested.

Thanks,
RandomCoder

PS I've attached some sample file names, you just need to update the path in Globals to reflect where they are saved. (also I've only programmed the machine and magazine combo's to filter the data so far).

 

[RandomCoder]

I don't think the INSERT INTO does quite what I need as I want to recreate the table with all new values, at least thats what I think needs to be done.
The INSERT INTO sounds like it's for adding new records onto the end of a table.

I have one SQL Table that holds all my filenames and the associated values.
I run a query on this table and the values it returns are displayed in a normal B4PPC table which also get stored in a different SQL table called FilterList.
The FilterList is then used to update values in the Combo boxes that allow me to run queries on the main SQL filenames table and thus return new values.

Is this the wrong way to do it?

Regards,
RandomCoder

 

[RandomCoder]

OK so now I've tried to implement the INSERT INTO by first deleting all the records in table FilterList.....

 SQLCommand.CommandText = "DELETE FROM FilterList"
 SQLCommand.ExecuteNonQuery
 SQLCommand.CommandText = "SELECT Machine , Magazine0 , Magazine1 , OrderNumber , BatchNumber , Date " & _
                          "INTO FilterList " & _
                          "FROM FileNames " & _
                          StrFilter & " " & _ 'This is the WHERE parameters
                          "ORDER BY Machine"
 [COLOR="Red"]SQLCommand.CommandText = "SELECT * FROM FilterList"[/COLOR]
 SQLCommand.ExecuteTable( "TblFiles" , 0 )

But if I put a breakpoint at the line highlighted red and view the database I find that the table named FilterList still contains all the values (not the filtered values) and now my normal form table displays nothing after selecting a machine to filter by.

I'm obviously doing something wrong but have no idea what :sign0163:
Attached is my attempt at using the INSERT instead of CreateSQLTable.

Regards,
RandomCoder

 

[RandomCoder]

BTW...
If I put SQLCommand.CommandText = "DELETE * FROM FilterList"
It throws an error, yet this would appear to be the correct syntax.

Regards,
RandomCoder

 

[Erel]

The correct syntax is "DELETE FROM FilterList" - SQLite Query Language: DELETE
However if you want to delete the table you should use "DROP TABLE FilterList" - SQLite Query Language: DROP TABLE

I've download the code from post #7. Which steps should I follow to reproduce the problem?

 

[RandomCoder]

Hi Erel,

Sorry for the slow response, only just finished work.

To see the problem save the rtf's to disk and adjust the globals directoy path to reflect where you have saved them.
Now run the program and select any of the available options from the Machine combobox. The table will be filtered but the new SQLTable called FilterList isn't updated with new values to reflect what is shown in the table.
Each time a value is selected from the comboboxes I aim to select the relavent records from the main filelist table and display them to the user. It was then intended to change the combobox values to reflect only the values that are being shown. This is why I have the SQLtable called filterlist as it is this that I use to select distinct values from for use in the comboboxes.

My problem is that the filterlist table is ignoring all my attempts at changing vaules stored within it.

Regards,
RandomCoder

 

[Erel]

Ok. This was not an easy one.
In your code there are several occasions where you call SQLConnection.BeginTransaction without calling SQLConnection.EndTransaction eventually.
After fixing it the table was created properly.
Note that you don't need to call BeginTransaction before each query.
It is only useful when you issue a large number of queries one after another. Like in Sub GetFileList.

 

[RandomCoder]

Many, many thanks :icon_clap:
I tried to debug it myself but just couldn't see where it was going wrong.
I'll check all the calls to begin transaction now.

Do you mind me asking how you found the problem... I always like to fix things myself if at all possible.

Regards,
RandomCoder

 

[Erel]

After playing with your application and not finding anything I created a small application which called Connection.CreateSQLTable several times and it worked fine. So I guessed that the problem is related to the way you use the SQL connection.
After several attempts I've found the Connection.Begin/EndTransaction issue.

 

[RandomCoder]

SQL SELECT INTO doesn't work....

First of all I'd like to thank you for spending time to help me.

Next I'd like to point out another problem that I have managed to resolve myself but which may help someone else in the future.

This doesn't work -

   SQLCommand.CommandText = "SELECT Machine , Magazine0 , Magazine1 , OrderNumber , BatchNumber , Date " & _
                                 "INTO FilterList " & _
                                 "FROM FileNames " & _
                                  StrFilter & " " & _ 'This is the WHERE parameters
                                 "ORDER BY Machine"

This does work -

   SQLCommand.CommandText = "INSERT INTO FilterList " & _
                                 "Select Machine , Magazine0 , Magazine1 , OrderNumber , BatchNumber , Date " & _
                                 "FROM Filenames " & _
                                  StrFilter 'This is the WHERE parameters

I've been using the tutorials at SQL Tutorial to learn SQL and so maybe SQL Lite doesn't support SELECT INTO new_table FROM old_table but I couldn't see this mentioned on the SQL Lite homepage, features not supported SQL Features That SQLite Does Not Implement.

Anyway, I've resolved all the problems now and its working as I hoped it would. Just a few more finishing touches to do.

Thanks,
RandomCoder.

 

[Erel]

I find this section very useful for finding the exact syntax supported by SQLite: Query Language Understood by SQLite