Sqlite Data grid program Header Issue

Discussion in 'Questions (Windows Mobile)' started by jotis1, Oct 15, 2009.

  1. jotis1

    jotis1 Member

    Dear Friend,

    I have a customer table in SQLite DB. I need to show data from the table in my application.

    Table definition is like

    Table Name: Customer
    Field Name1 : CustomerCode
    Field Name2 : CustomerName

    In my application the header of the data grid should be like

    CUSTOMER CODE and CUSTOMER NAME instead of CustomerCode and CustomerName respectively.

    that means I can define the header as I wish.

    Here I used a form called CustView. and I used a controller Table called CustGrid

    I used below code for this
    CustGrid.AddCol(cString, "CUSTOMER CODE", 50)
    CustGrid.AddCol(cString, "CUSTOMER NAME", 150)

    But When I retrieve the data from the database table I got the header as in the table definition ie CustomerCode and CustomerName .

    My Question is How I can define the header as I wish ?

    My program is attached herewith as well as I am copying my code here.


    Sub Globals
    'Declare the global variables here.

    End Sub

    Sub App_Start

    CreateConnection
    CreateTableIfNotExists
    Designer
    CustView.Show

    Connection.BeginTransaction
    Command.CommandText = "SELECT * FROM customer"
    Command.ExecuteTable("CustGrid", 1000)
    Connection.EndTransaction

    End Sub
    Sub CreateConnection
    Connection.New1
    Command.New1("", Connection.Value)
    Connection.Open("Data Source = " & AppPath & "\customer.sl3")

    End Sub

    Sub CreateTableIfNotExists
    Command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' AND name='customer'"
    'instantiate reader
    Reader.New1
    'Fill reader
    Reader.Value = Command.ExecuteReader
    If Reader.ReadNextRow = False Then
    ' No table with this name in the database.
    ' Create one.
    Reader.Close
    Command.CommandText = "CREATE TABLE customer (CustomerCode TEXT PRIMARY KEY, CustomerName TEXT)"
    Command.ExecuteNonQuery
    Else
    'Close reader anyhow
    Reader.Close
    End If
    End Sub

    Sub Designer

    AddForm("CustView", "View Customer")
    AddTable("CustView","CustGrid",30,50,200,150)
    CustGrid.AddCol(cString, "CUSTOMER CODE", 50)
    CustGrid.AddCol(cString, "CUSTOMER NAME", 150)
    End Sub




    Please see the attachment and run it


    Expecting your help.

    Thanking you

    Jotis
     

    Attached Files:

    Last edited: Oct 15, 2009
  2. jeterry

    jeterry Member Licensed User

    Headers

    Give this a try.
    cmd.CommandText = "Select CustCode 'Customer Code',CustName 'Customer Name',Caddress 'Address',Ccity 'City',cphone 'Phone' from Customer Order by CustCode"

    Using this method you can change your header to any thing want.

    Hope this helps
     
  3. JOTHA

    JOTHA Well-Known Member Licensed User

    Hello jeterry,

    I tried to find out a solution for jotis 1, but you are faster!
    Your code works:
    Another question:

    Do you know how to show a table in another order of appearance? (not only the header)

    Example: You have a table with 4 columns (Name, Date, Street, Item)

    Now you want to show it in another appearance: (Item, Date, Street, Name)

    Is there a easy way to do that?
     
  4. jeterry

    jeterry Member Licensed User

    Header

    How you make your select statement, I believe is how the data will appear in the data grid. As per your example, select item,date,street,name from table. The data should be displayed as follows: col0=item,col1=date,col2=street,col3=name

    Correct me if I am wrong, the selection statement is how the grid will display the data.

    Hope that helps
     
  5. JOTHA

    JOTHA Well-Known Member Licensed User

    Hello jeterry,

    thank you for your fast answer.
    YOU ARE RIGHT!

    :icon_clap:
     
  6. jotis1

    jotis1 Member

    Dear Jeterry/Jotha/Friends

    Thanks for your support.
    I forg0t to say one thing. My program support Multiple Language.
    I keep a text_translation table where I can select languages for correspnding variable.

    For eg: I keep two variable, 1. Customer 2. CustomerName

    In Text Translation table, there will be corresponding text for each variable.

    I need to bring the header in multilanguage support.

    I don't think I can mix with a variable in SQL Query. If possible just help me.
    Or do you know another way ?

    Anyway with Table.AddCol() function ?
    I used it but didn't get the value if I run an sql query. May be my coding mistake.


    For eg: Just see the code

    Sub App_Start

    CreateConnection
    CreateTableIfNotExists
    Designer
    CustView.Show

    CustCodeText= "CUSTOMER CODE"
    CustNameText= "CUSTOMER NAME"
    CustGrid.AddCol(cString, CustCodeText, 50)
    CustGrid.AddCol(cString, CustNameText, 150)

    Connection.BeginTransaction
    Command.CommandText = "SELECT * FROM customer"
    Command.ExecuteTable("CustGrid", 1000)
    Connection.EndTransaction



    End Sub
    Sub Designer

    AddForm("CustView", "View Customer")
    AddTable("CustView","CustGrid",30,50,200,150)
    End Sub

    HERE IF I comment the SQL Fetching Program, I get the header as per my requirement or as I defnined.
    But If I call the SQL program I didnt get the header as per my requirement and instead I got the coloumn name of table definition.

    I don't need the coloumn name, I need to define as I wish and it should be change along with How I select a language.
    If I select Spanish as my default language It should be spanish text.
    If I select English it will be in English

    Please help :sign0085::sign0085:
     
    Last edited: Oct 16, 2009
  7. jotis1

    jotis1 Member

    Dear Friends,

    Thank you for support.

    I findout another way. I think its ok now.
    If you know another way just inform me

    Please see my solution

    Sub App_Start

    CreateConnection
    CreateTableIfNotExists
    Designer
    CustView.Show
    CustCodeText= "CUSTOMER CODE"
    CustNameText= "CUSTOMER NAME"
    CustGrid.AddCol(cString, CustCodeText, 50)
    CustGrid.AddCol(cString, CustNameText, 150)
    Connection.BeginTransaction
    Command.CommandText = "SELECT * FROM customer"
    'Command.ExecuteTable("CustGrid", 1000)
    Reader.Value = Command.ExecuteReader
    Do While Reader.ReadNextRow = True
    CustGrid.AddRow (Reader.GetValue(0), Reader.GetValue(1))

    Loop


    Connection.EndTransaction



    End Sub

    Thanks
    Jotis
     
  8. Ariel_Z

    Ariel_Z Active Member Licensed User

    As Jeterry said, you can use this syntax to create the SELECT statement:
    Code:
    cmd.CommandText = "Select CustCode 'Customer Code',CustName 'Customer Name',Caddress 'Address',Ccity 'City',cphone 'Phone' from Customer Order by CustCode"
    But if you wish, you can use variables in the statement. Suppose strCusNameCol is a variable holding the name of your column:
    Code:
    cmd.CommandText = "Select CustName '" & strCusNameCol  & "'"
    (note the last ' between the two "...)

    Now put the column name in strCusNameCol and executeTable... Should be faster, I believe, than populating the table manually.
     
  9. jotis1

    jotis1 Member

    Thank you Ariel

    rgds
    Jotis
     
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