• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2068
  • Last Modified:

Save the Data in msflexgrid

How to save the data in msflexgrid?
When i click the save button in my code below the data that saved is the last columns. there are four (4) columns supposed to be saved but unfortunately its not.
The code can saved already, but why is it that not all data in msflexgrid is going to be saved.
Please help...
Thanks!
Public Sub OPEN_CON1(cn As ADODB.Connection, db As String, ServerName As String)
Set cn = New ADODB.Connection
cn.Provider = "sqloledb"
cn.ConnectionTimeout = 25
cn.Properties("Data source").Value = Trim(ServerName)
cn.Properties("Initial Catalog").Value = Trim(db)
cn.Properties("User ID").Value = "sa"
cn.Properties("Password").Value = ""
cn.CursorLocation = adUseClient
cn.Open , "sa", ""
End Sub
Public Sub save_Table(cn As ADODB.Connection, lpTable_No As String, The_Quantity As String, The_Description As String, The_Disc As String)
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "select * from The_Table where Table_No='" & lpTable_No & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
        If .BOF = True And _
            .EOF = True Then
            .AddNew
            !Table_No = lpTable_No
        End If
        !The_Quantity = Trim(grid.TextMatrix(grid.Row, 0))
        !The_Description = Trim(grid.TextMatrix(grid.Row, 1))
        !The_Disc = Trim(grid.TextMatrix(grid.Row, 3))
        !The_Total = IIf(IsNull(!The_Total), 0, !The_Total) + CDbl(Trim(lblDueAmount.Caption))
        .Update
        .Close
    End With
    Set rs = Nothing
End Sub
Private Sub CmdSave()
Dim cn As ADODB.Connection
Call OPEN_CON1(cn, "MyTable", "Winpos")
    Call save_Table(cn, txtTable.text, grid.TextMatrix(grid.Row, 0), grid.TextMatrix(grid.Row, 1), grid.TextMatrix(grid.Row, 3))
 
Set cn = Nothing
End Sub

Open in new window

grid.bmp
0
Whing Dela Cruz
Asked:
Whing Dela Cruz
  • 4
  • 4
1 Solution
 
Antagony1960Commented:
I don't see how this question is much different to your previous one, "Code Lacking." As it stands your CmdSave procedure only tries to save the currently selected row. If you want it to save every row you need to use a for next loop, like this:

Private Sub CmdSave()
Dim cn As ADODB.Connection
Call OPEN_CON1(cn, "MyTable", "Winpos")
    For i = 1 to grid.Rows -1
        Call save_Table(cn, txtTable.text, grid.TextMatrix(i, 0), grid.TextMatrix(i, 1), grid.TextMatrix(i, 2))
    Next
Set cn = Nothing
End Sub

And you need to change the save_Table procedure to set the field values according to the passed arguments rather than trying to refer directly to the grid, like this:


Public Sub OPEN_CON1(cn As ADODB.Connection, db As String, ServerName As String)
Set cn = New ADODB.Connection
cn.Provider = "sqloledb"
cn.ConnectionTimeout = 25
cn.Properties("Data source").Value = Trim(ServerName)
cn.Properties("Initial Catalog").Value = Trim(db)
cn.Properties("User ID").Value = "sa"
cn.Properties("Password").Value = ""
cn.CursorLocation = adUseClient
cn.Open , "sa", ""
End Sub
Public Sub save_Table(cn As ADODB.Connection, lpTable_No As String, The_Quantity As String, The_Description As String, The_Disc As String)
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "select * from The_Table where Table_No='" & lpTable_No & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
        If .BOF = True And _
            .EOF = True Then
            .AddNew
            !Table_No = lpTable_No
        End If
        !The_Quantity = Trim(The_Quantity)
        !The_Description = Trim(The_Description)
        !The_Disc = Trim(The_Disc)
        !The_Total = IIf(IsNull(!The_Total), 0, !The_Total) + CDbl(Trim(lblDueAmount.Caption))
        .Update
        .Close
    End With
    Set rs = Nothing
End Sub

Open in new window

0
 
Antagony1960Commented:
Sorry, you will also have to add this line to the top of the CmdSave sub:

Dim i As Integer
0
 
Whing Dela CruzAuthor Commented:
Hi!

I tried the given code and no errors appeared during the execution but the result is still the same.. only the last row had saved.


What shall should i do next?

Thanks!




 if exists (select * from sysobjects where id = object_id(N'[dbo].[The_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[The_Table]
GO
 
CREATE TABLE [dbo].[The_Table] (
    [Table_No] [varchar] (50) NULL ,
    [The_Quantity] [float] NULL ,
    [The_Description] [varchar] (50) NULL ,
    [The_Disc] [float] NULL ,
    [The_Total] [float] NULL
) ON [PRIMARY]
GO

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Antagony1960Commented:
Evidently the Table_No column is the ID field and you aren't changing that argument each time you call the save routine (you're just using the value from txtTable.text every time). How are you populating the flex grid in the first place? If the value in txtTable changes when you click on different rows in the grid, then the ID field (Table_No) must be stored somewhere to correspond with each row--either in a hidden column of the grid or in a companion array. Essentially, you need to pass the correct Table_No value for each row, otherwise it will only ever update the row with the value in the text box.
0
 
Whing Dela CruzAuthor Commented:
I'm so sorry but  i have no idea on how to proceed it.. Can you give me some example?
Thanks!

i've been trying to omit the (table_No) and (the_total) as will but the result is the same...

0
 
Antagony1960Commented:
It's difficult to tell you how to proceed without knowing a bit more about how you're populating the grid. Are the rows of the grid being read in from existing records in The_Table or are they being created by user actions prior to being added to The_Table, or a combination of both?

0
 
Whing Dela CruzAuthor Commented:
It is being created by user actions prior to being added to the_table.
0
 
Whing Dela CruzAuthor Commented:
Okey thanks,
I got your idea and now,  i can save the file but still i have a problem on how to save the file not to replace the existing "The_code" i post other question regarding on this problem. i hope you will follow to monitor..
Thanks!  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now