Sqlite Data grid program Header Issue

[jotis1]

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

[jeterry]

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

[JOTHA]

Hello jeterry,

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

Originally Posted by jeterry

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

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?

[jeterry]

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

[JOTHA]

Hello jeterry,

thank you for your fast answer.

Originally Posted by jeterry

... Correct me if I am wrong ...

YOU ARE RIGHT!

[jotis1]

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

[jotis1]

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

[Ariel_Z]

As Jeterry said, you can use this syntax to create the SELECT statement:

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:

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.

[jotis1]

Thank you Ariel

rgds
Jotis