Solved

Save the Data in msflexgrid

Posted on 2009-04-04
8
1,923 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

813 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

17 Experts available now in Live!

Get 1:1 Help Now