MSHFlexgrid column heading

[silversurf]

Hello everyone. I am trying to build a inventory and pos software with vb 6.0 and MS-Access as database. In one of my form I have the following code to call my inventory of a certain invoice number to print tag for my products. I am using a MSHFlexgrid with 4 columns. Everything seems ok but when I run the code the column heading of the grid changed to the heading of my database column, I just want the data of those column not the heading. Here is the code :

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
        Set cn = New ADODB.Connection
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\main.mdb;Persist Security Info=False"
        cn.Open
        Set rs = New ADODB.Recordset
       rs.Open ("Select code,tprice,quantity from tblstock where inv_no like '" & cmbinvno.Text & "' and inv_date like '" & cmbinvdate.Text & "'"), cn, adOpenStatic, adLockOptimistic
With Grid
    Set Grid.DataSource = rs
If .Rows = 2 And .TextMatrix(1, 3) = "" Then
     .TextMatrix(1, 1) = rs!code
     .TextMatrix(1, 2) = rs!tprice
     .TextMatrix(1, 3) = rs!Quantity
    Else
    .Rows = .Rows + 1
    .TextMatrix(.Rows - 1, 1) = rs!code
    .TextMatrix(.Rows - 1, 2) = rs!tprice
    .TextMatrix(.Rows - 1, 3) = rs!Quantity
    .Rows = .Rows - 1
End If
End With

I don't have any idea what I am doing wrong or how can I overcome this situation. Please help.

[AndreRet]

Grid.TextMatrix(0, 1) = "This is caption for first coloumn"

[silversurf]

Thanks sir for replying. By saying column heading I mean the first row of the MSHFlexgrid is collecting the first row of my database, although I initiate the grid with Code, Tag Price, No. of Pcs, after loading the data the first row in converting to code, tprice and quantity and from the second row the data starts. What I want is the first row will remain Code, Tag Price and No. of Pcs and from the second row the data starts. I have avoided grid.textmatrix (0,1) from gaining any code, i started from (1,1).

[silversurf]

for your further assistance I have the following code in Initgrid

Private Sub InitGrid()
   With Grid
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 4
        .ColSel = 3
        'Initialize the column size
        .Colwidth(0) = 500
        .Colwidth(1) = 1000
        .Colwidth(2) = 1000
        .Colwidth(3) = 1000
        .Colwidth(4) = 1000
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Code"
        .TextMatrix(0, 2) = "Tag Price"
        .TextMatrix(0, 3) = "No. of Pcs"
    End With
End Sub

[AndreRet]

Try the following...

With Grid
''First call initgrid here to set headers, then link to data...
Call InitGrid

    Set Grid.DataSource = rs
If .Rows = 2 And .TextMatrix(1, 3) = "" Then

[silversurf]

Thanks sir for replying. Although I have already call InitGrid under Form Load (), I have againg tried it as per your suggession, but the result remains the same. Column headers are changing to the database column header, not reamining as I coded to Initgrid. Is it possible to remove

Set Grid.DataSource = rs

and load all the required data from the databse directly to grid ?

[AndreRet]

Try...

Private Sub InitGrid(rsInit As ADODB.RecordSet)

With Grid
    .Clear
    .ClearStructure
    .Rows = 2
    .FixedRows = 1
    .FixedCols = 1
    .Cols = 4
    .ColSel = 3
    'Initialize the column size
    .Colwidth(0) = 500
    .Colwidth(1) = 1000
    .Colwidth(2) = 1000
    .Colwidth(3) = 1000
    .Colwidth(4) = 1000
    'Initialize the column name
    .TextMatrix(0, 0) = ""
    .TextMatrix(0, 1) = "Code"
    .TextMatrix(0, 2) = "Tag Price"
    .TextMatrix(0, 3) = "No. of Pcs"
    Set .Datasource = rsInit
End With
End Sub

Now, when you call InitGrid, do it as follow...

Call InitGrid(rs)

That should solve your problem...

[silversurf]

i have changed as you said and call initgrid (rs) but still have the same problem. the first row is still changing to database field names not as per my initgrid heading
i am giving a view of the form here. look at the first row where the column heading is showing. problem is there.

[silversurf]

No solution yet from anybody.

[Jx_Man]

Try to using Alias in your SQL Statement. It's working for me in vb.net version, but never test it in vb6 with flex grid.
Change alias with your desire column name.

[silversurf]

Jx Man, sir can u give me an example code of making the alias, please.

[Jx_Man]

Select code as Code,tprice as Price,quantity as Quantity from tblstock

[silversurf]

Thanks Jx Man, In the mean time I have reconstructed the code as per following :

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim irowcounter As Integer 'used to keep place of the current row
        Set cn = New ADODB.Connection
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\main.mdb;Persist Security Info=False"
        cn.Open
        Set rs = New ADODB.Recordset
       rs.Open ("Select code,tprice,quantity from tblstock where inv_no like '" & cmbinvno.Text & "' and inv_date like '" & cmbinvdate.Text & "'"), cn, adOpenStatic, adLockOptimistic
If rs.RecordCount > 0 Then ' check to see if any records were found
With Grid
irowcounter = 1 'set the starting row in the grid
Do While Not rs.EOF
If irowcounter = .Rows Then .Rows = .Rows + 1
                  .TextMatrix(irowcounter, 1) = rs!code
                  .TextMatrix(irowcounter, 2) = rs!tprice
                   .TextMatrix(irowcounter, 3) = rs!Quantity
                  irowcounter = irowcounter + 1 ' increment the row counter
                   rs.MoveNext ' move to the next record
          Loop
       End With
       Else
    MsgBox "No records" ' feedback if the database returns nothing
     End If

        ' house keeping
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
        'end with
        End Sub

It is giving me what I want. But I still having one problem, its adding an extra column in the grid. I mean after the No. of Pcs column I am having another blank column in the grid. Why ?

[silversurf]

it looks like i m all alone now, as nobody is answering my questions.

[Jx_Man]

MSHFlexgrid can use recordset as datasource. Why you write it manually?
Just used Grid.DataSource = rs then it will loaded automaticaly to your MSHFlexgrid.
I think andre was tell it before.
See this example :

Private Sub GetData()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

    Set cn = New ADODB.Connection

    cn.Provider = "microsoft.jet.oledb.4.0"
    cn.CursorLocation = adUseClient
    cn.Open App.Path & "\Authors.mdb"

    Set rs = New ADODB.Recordset
    rs.Open "Select au_id as Id,author as Name,yearborn as Birthday from Authors", cn

     Set grid2.DataSource = rs

End Sub

Private Sub Form_Load()
    GetData
End Sub

[silversurf]

sorry, I didn't get you Jx_man sir. What you r telling me ? r u giving suggestion to remove the line

`Grid.DataSource = rs`

from the code ? if not, then if you plz look at my first post, there I have the code in line no. 9. and if also u are talking about removal of the line I have already asked andre about it in one of my post :

Thanks sir for replying. Although I have already call InitGrid under Form Load (), I have againg tried it as per your suggession, but the result remains the same. Column headers are changing to the database column header, not reamining as I coded to Initgrid. Is it possible to remove

    Set Grid.DataSource = rs

and load all the required data from the databse directly to grid ?

I am confused here totally. Plz help.

[Jx_Man]

Yes. I understand.
I mean at the first post, you use "Set Grid.DataSource = rs"
but in the last post you remove it and use 'looping' to read data from database then write manually it to flexgrid.

The logic is data loaded from database and it accomodated in recordset.
Mshflexgrid can use recordset as datasource. So after you get data in recordset with sql statement just set datasourece with current recordset. it will make data automaticaly loaded into flexgrid and i don't get any problems (no more column added)

You can use listview if you want to write data manually with looping.

See an attachment.

[silversurf]

Thanks sir for replying. I have tried out two set of codes here.
NO.1 is in the first post in which I set grid datasource to rs, which give me the problem that the column heading changed to database column heading although I init different types of column heading for the grid.
No. 2 is without the set grid.datasource = rs, through data loop from the database, it solve my no.1 problem but create a a new empty colum. As I init 4 column (0, 1, 2, 3) with Code (1), Tag Price (2), No. of Pcs (3), after loading data from database it adds in extra empty column in the end, after the No. of pcs column without any heading or data.
I think I make it clear to you.

[silversurf]

As per your example I now have the follwing code to load data in my mshflexgrid (grid)

 Dim cn As New ADODB.Connection
 Dim rs As New ADODB.Recordset
    Set cn = New ADODB.Connection
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\main.mdb;Persist Security Info=False"
        cn.Open
        Set rs = New ADODB.Recordset
    rs.Open "Select code as Product Code,tprice as Tag Price,quantity as No. of Pcs from tblstock where inv_no like '" & cmbinvno.Text & "' and inv_date like '" & cmbinvdate.Text & "'", cn, adOpenStatic, adLockOptimistic
    Set Grid.DataSource = rs
    With Grid
    Do While Not rs.EOF
    If .Rows = 2 And .TextMatrix(1, 3) = "" Then
     .TextMatrix(1, 1) = rs!code
     .TextMatrix(1, 2) = rs!tprice
     .TextMatrix(1, 3) = rs!Quantity
    Else
    .Rows = .Rows + 1
    .TextMatrix(.Rows - 1, 1) = rs!code
    .TextMatrix(.Rows - 1, 2) = rs!tprice
    .TextMatrix(.Rows - 1, 3) = rs!Quantity
    .Rows = .Rows - 1
End If
Loop
End With
    rs.Close
    cn.Close
End Sub

Now I have the error : Method 'open' of object 'recordset' failed in the following line

        rs.Open "Select code as Product Code,tprice as Tag Price,quantity as No. of Pcs from tblstock where inv_no like '" & cmbinvno.Text & "' and inv_date like '" & cmbinvdate.Text & "'", cn, adOpenStatic, adLockOptimistic

I don't know whats going wrong here again. plz help.

[AndreRet]

Dim cn As ADODB.Connection
 Dim rs As ADODB.Recordset ''Remove the New part

 Set rs = New ADODB.Recordset

This is already off topic as well. Please open a new thread with your error. The original question was answered. Please mark as solved, thanks.

[silversurf]

I have removed the new part but still the same error in the same line.
By the way, is it already off topic ?? I was searching help how I can load data in my MSHFlexgrid without changing the column header that is given, that is where I am getting all this codes and error, if everybody thinks it is offtopic already I am ready to open another thread, but I didn't get my question solved yet. I am personally sorry if i hurt someone.