Solved

Save the Data in msflexgrid

Posted on 2009-04-04
8
1,986 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

696 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