Help with sql (very basic) code.

[kyto]

Hello.
I began experimenting with databases (sql), and I have a doubt that I cannot solve: I write a very basic code (based in examples from manual), in which a list appears with information (TextBox) and images (Image). With the "Next" menu,advance to the following cell of information and image, but if I use a filter, the correct images do not appear.

Some advice or help to solve this?

Mi code:

Sub Globals
cur_row=0
End Sub

Sub App_Start
   Form1.Show
   Con.New1
   Reader.New1
   Cmd.New1("",con.Value)
   Con.Open("Data Source = " & AppPath & "\DB.sl3") 'Opens a connection with the database.
   cmd.CommandText = txtCommand.Text 'Executes the SQL command
   'Executes the user query and fills the table with the result.
   cmd.ExecuteTable("table1",500) 'Limits the number of rows to 500 (change to 0 for unlimited)
   Table1.SelectCell ("ID", 0)
   Load
   Showrow
End Sub

Sub Load 'Load image from database
cmd.CommandText = "SELECT image FROM Datos"
   reader.Value = cmd.ExecuteReader
   For i = 0 To table1.SelectedRow 'Find the right record.
      reader.ReadNextRow
   Next
   Image1.Image = reader.GetImage(0)
   Image1.Visible = True
   reader.Close
   Return
End Sub   

Sub Table1_SelectionChanged (ColName, Row) event
   Table1.SelectCell(Table1.SelectedCol, cur_row)
    cur_row = Row
   load
   ShowRow
End Sub

Sub ShowRow
    TextBox1.Text = Table1.Cell("Info" , cur_row)
   Table1.SelectCell(Table1.SelectedCol, cur_row)
End Sub

Sub Nxt_Click
   cur_row = (cur_row + 1) Mod table1.RowCount
   Showrow
End Sub

Sub Previous_Click
    cur_row = (cur_row + table1.RowCount-1) Mod table1.RowCount
   Showrow
End Sub

Sub MemoryFilter_Click
Table1.Filter ("Category = 'Memory'")
Load
End Sub

Sub All_Click
Table1.Filter ("")
Table1.SelectCell ("ID", 0)
End Sub

My Database is attached.

Thanks for read this.

Regards.

 

[klaus]

Hi kyto,

In your code you must change line 22

[FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] i = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0 [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] table1.SelectedRow [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000]'Find the right record[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

to

[FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] i = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0 [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] Table1.Cell([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"ID"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],Table1.SelectedRow)-[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]1[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000]'Find the right record[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

to select the right image.
The current row in the filter is not the current row in the database.

I have seen that in your program in the ShowRow routine you use

[FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]Table1.SelectCell(Table1.SelectedCol, cur_row)[/SIZE][/FONT][/SIZE][/FONT]

This calls Table1_SelectionChanged once more which calls ShowRow once more.
I have added a version with some changes.

I am not a SQL specialist, but I think there must be a direct call to get the right record in the database, without the For / Next loop.

Best regards.

 

[kyto]

Thanks!

Works excellent.
Thanks for your help klaus.