Update a RS using a flexgrid

[holly]

Hi

I have a form that contains a flexgrid and a cmd button which the
user presses to update any changes...but I have a problem
everytime I press the update cmd I receive an error (see JPG
attached)

The code I use is
[Highlight=VB]
Private Sub CmdUpdate_Click()
UpdateFlexreview

End Sub
Private Sub UpdateFlexreview()
Dim i As Integer
Dim j As Integer

With ObjRsFlex
For i = 1 To MSFlexReview.Rows - 1
.Fields("Event_Datetime").Value = i
For j = 1 To MSFlexReview.Cols - 1
.Fields("Column" & j).Value = MSFlexReview.TextMatrix(i, j)
Next j
.Update
Next i
End With

End Sub
[/vbode]

Has anyone any suggestions... the error is displayed on the
bolded line!!!!

[mendhak]

Show the code you're using to open the connection and recordset

[holly]

OK here goes! here is the procedure I use to populate the flexgrid and open the RS...

Dim strDatabase As String
    Dim strUsername As String
    Dim strPassword As String
    Dim strServer As String
    Dim objINIFile As cInifile
    Dim strMfgDate As Date
    'inititate new object
    Set objINIFile = New cInifile
   
   On Error GoTo Form_Load_Error
       
   'Locate ini file and receive databasename/Username and Pwd
   objINIFile.Path = App.Path & "\GOPALS.ini"
   objINIFile.Section = "DataBase Parameters"
   objINIFile.Key = "SERVER"
   strServer = objINIFile.Value
   objINIFile.Key = "DatabaseName"
   strDatabase = objINIFile.Value
   objINIFile.Key = "USERNAME"
   strUsername = objINIFile.Value
   objINIFile.Key = "PASSWORD"
   strPassword = objINIFile.Value
     
    txtEdit.ZOrder
    txtEdit = ""
    
    set_title_caption ' set title caption for MSFLex
    enlarge_grid      ' Enlarge the grid to fit detail
        If Time > Format(CDate("06:30:00"), "HH:MM:SS") Then
           lbldate = (Format(Now - 1, "MMM DD, YYYY"))
            Else
         lbldate = (Format(Now, "MMM DD, YYYY"))
    End If
         strMfgDate = lbldate ' assign the label date to a variable
         lbltime = Time 'assign time to label
         Tmrclock.Interval = 1000
         Tmrclock.Enabled = True
    MSFlexReview.FillStyle = 0 'format individual cells
    txtEdit.Visible = False
        DTStartDate = Now - 3 'Display start date
        DTEnddate = DTStartDate + 7
        'Opens Subroutine - connection to SQL with the values received from the ini file
       Call OpenConnection(strServer, strUsername, strPassword, strDatabase)
   Exit Sub
   
Private Sub DisplayResults()
     'Declare variable
      On Error GoTo Display_Result_Error
    Dim IntCounter As Integer
    Dim objCmd As ADODB.Command
    Dim PrmBeginDate As ADODB.Parameter
    Dim PrmEndDate As ADODB.Parameter
    Dim startDate As Date
    Dim endDate As Date
  
    startDate = DTStartDate.Value
    endDate = DTEnddate.Value
   
    'Set counter to zero
    IntCounter = 0
    Set ObjRsFlex = New Recordset
    Set objCmd = New ADODB.Command
 
   objOcn.CursorLocation = adUseClient
   ObjRsFlex.LockType = adLockBatchOptimistic
       
        With objCmd
            .ActiveConnection = objOcn
            .CommandType = adCmdStoredProc
            .CommandText = "SP_ReviewScale_Transactions"
            .Parameters.Append .CreateParameter("@beginningDate", adDBDate, adParamInput, , Format(DTStartDate.Value, "yyyy-mmm-dd"))
            .Parameters.Append .CreateParameter("@EndingDate", adDBDate, adParamInput, , Format(DTEnddate.Value, "yyyy-mmm-dd"))
        End With
 
  'Populate grid with search results and set # rows to
    'equal the # records returned by the search
      Set ObjRsFlex = objCmd.Execute
    
    If ObjRsFlex.RecordCount > 0 Then
          IntCounter = IntCounter + 1           'Reserves first row for column headers
         MSFlexReview.Rows = IntCounter + 1       'Adds a row for next record
 
        While Not ObjRsFlex.EOF
            With MSFlexReview
                .Row = 0
                .AddItem ObjRsFlex.Fields("Event_DateTime") & vbTab & ObjRsFlex.Fields("Operator_Badge") & vbTab & ObjRsFlex.Fields("Operator_Lastname") & vbTab & ObjRsFlex.Fields("Pallet_ID_Number") & vbTab & ObjRsFlex.Fields("Product_Code") & vbTab & ObjRsFlex.Fields("Product_description") & vbTab & ObjRsFlex.Fields("Product_Gross_Weight") & vbTab & ObjRsFlex.Fields("NET_WEIGHT")
             End With
            ObjRsFlex.MoveNext
        Wend
    Else
        MsgBox " There is no records to populate the Grid."
    End If
    'clean objects
    Set objCmd = Nothing
    Set PrmBeginDate = Nothing
    Set PrmEndDate = Nothing
 
   Exit Sub
   
Display_Result_Error:
     Call frmscan.ShowError("Error -  in procedure Form_Load of Form frmscan")
End Sub

Thanks

[Killazzz]

Are you updating the whole recordset or just one row.

In the latter case you'll have to capture the row or some sort of ID that you can use to populate a recordset.

For example :

flexgrid.col=1
id=flexgrid.text

with this id you can create a recordset wth only that one record and update it with data from the flexgrid or textboxes.

[holly]

Hi

I wanted to update the RS the reason being..a user selects a
specific date range which can display multiple records...

second if you were to read my first post I wanted to update
based upon the event_dattime the reason..every transaction has
a different time...

Fields("Event_Datetime").Value = i

Thanks

[Killazzz]

I'm not sure if it's clear to me.

the user selects a range of records based on time.

You then update this time field with a row number ?

The error message you get might be the locktype. Or the stored procedure. The recordset might not be update-able. THen you'll have to loop the result set and update each record separately using some sort of ID as I mentioned earlier.

set ObjRSFlex=new recordset

shouldn't this be

set objrsflex=new ADODB.recordset ?

[holly]

To answer your questions:

The user selects a range of records based on date...see attached
*.jpg
I dont update anything because it does not work....

Thanks

Hope it is a bit clearer

What I want to do is to be able to change any of the records
and then update it back to SQL using the first post procedure..

[Killazzz]

The selection is clear but what do you want to update ?

With what dou you want to update the selected data ?

1. User selects a range
2. User updates this range ? with what ?


It's hard to see what you want the cmdupdate button to do.

The only thing you're doing now is looping the rows and columns and that's it ? With a flexgrid there's no link between data and display. A data grid might be more appropriate but is less flexible (according to me).

[holly]

I want to update with anything I wish to change....so for
example if I want to change the Operators_lastname from
'harrison' to 'harris'.

I'm sorry if I am not making myself clear enough

Thanks for you help!!!

[Killazzz]

No problem.

It's always better to state the full idea than only a small part.

The only way to change data in this range is by looping the range and editing record by record.
You can have a combo which contains the different fields that are displayed. Next to this you can put a textbox.

When the user selects "lastname" the user enters the new value into the textbox and clicks update.

With the textbox data you should perform an update for each record that is displayed in the flexgrid.

therefore you'll be using the for.. next loop. And as you know that the user selected lastname, you know what field to update.

You can perform a similar action on the click event of the flexgrid. If the user clicks the lastname column you can pre-select lastname in the combo and set the text from the flexgrid into the textbox. Then edit the textbox and click update and that's it.

The recordset has to be opened in such a way so you can update it.

[holly]

I understand what youre saying is just that I'm stuck with
the code...Do i just add a locktype in the displayresults procedure
or do I need to do a bit more??

Thanks

[Killazzz]

objOcn.CursorLocation = adUseClient
   ObjRsFlex.LockType = adLockBatchOptimistic

You can try to change the adlockBatchOptimistic into adLockOptimistic ?

I presume you're using SQL Server ? I'm not sure if adlockoptimistic exists for this MS SQL. Have you changed the new recordset into new adodb.recordset ?

I think without adodb you're creating a DAO recordset.

The only way to change the items in the flexgrid is doing it line by line. using the date_time stamp in combination with the field that needs to be updated.

with flexgrid
.col=1
DatetimeStamp=.text
.col=2
lastname=.text
end with

set adors=new adodb.recordset
adors.open "select * from sales where timestamp=" datetimestamp & " and employee.lastname= ' " & lastname &"'"

if not adors.eof and not adors.bof then
adors!lastname=txtEditedLastname.text
end if

You could also create a stored procedure that receives an sql string. But I'm not familiar with stored procedures.

[brucevde]

The Command.Execute method can only create a ReadOnly recordset. Use the Recordset.Open method instead.

Change
Set ObjRsFlex = objCmd.Execute

To
ObjRsFlex.Open objCmd, , adOpenStatic, adLockOptimistic