Solved

Save the Data in msflexgrid

Posted on 2009-04-04
8
1,946 Views
Last Modified: 2012-06-22
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
Comment
Question by:Whing Dela Cruz
  • 4
  • 4
8 Comments
 
LVL 11

Expert Comment

by:Antagony1960
ID: 24067372
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
 
LVL 11

Expert Comment

by:Antagony1960
ID: 24067395
Sorry, you will also have to add this line to the top of the CmdSave sub:

Dim i As Integer
0
 

Author Comment

by:Whing Dela Cruz
ID: 24067489
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 11

Expert Comment

by:Antagony1960
ID: 24068083
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
 

Author Comment

by:Whing Dela Cruz
ID: 24068520
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
 
LVL 11

Accepted Solution

by:
Antagony1960 earned 500 total points
ID: 24069127
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
 

Author Comment

by:Whing Dela Cruz
ID: 24069593
It is being created by user actions prior to being added to the_table.
0
 

Author Comment

by:Whing Dela Cruz
ID: 24070391
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question