Solved

Save the Data in msflexgrid

Posted on 2009-04-04
8
1,879 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 11

Expert Comment

by:Antagony1960
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Whing Dela Cruz
Comment Utility
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
Comment Utility
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
Comment Utility
It is being created by user actions prior to being added to the_table.
0
 

Author Comment

by:Whing Dela Cruz
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now