1. Black Friday Sale - 50% off

    B4A Standard for $29 instead of $59!
    Buy Now
    Dismiss Notice

Help with sql (very basic) code.

Discussion in 'Questions (Windows Mobile)' started by kyto, Jan 26, 2009.

  1. kyto

    kyto Member Licensed User

    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:

    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 + 
    1Mod table1.RowCount
       Showrow
    End Sub

    Sub Previous_Click
        cur_row = (cur_row + table1.RowCount-
    1Mod 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.
     
    Last edited: Jan 26, 2009
  2. klaus

    klaus Expert Licensed User

    Hi kyto,

    In your code you must change line 22
    Code:
    [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
    Code:
    [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
    Code:
    [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.
     

    Attached Files:

    Last edited: Jan 31, 2009
  3. kyto

    kyto Member Licensed User

    Thanks!

    Works excellent.
    Thanks for your help klaus.
    :)
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice