[RESOLVED] Update database records like in MSFLEXGRID...

[ginothegodfather]

Hello everybody. I need some help. I have my project and im having trouble in msflexgrid. I will describe wat i wont.

Option Explicit
Public cnn As ADODB.Connection
Public mrs As ADODB.Recordset
Public cmd As ADODB.Command
Public Sub dbConnection()
Set cnn = New ADODB.Connection
With cnn    
.Provider = "Microsoft.jet.oledb.4.0"    
.ConnectionString = "data source = " & App.Path & "\Data\MPMAIN.mdb"    
.CursorLocation = adUseClient    
.Open
End With
End Sub

Private Sub cmdOK_Click()
Dim rrr As Integer
Dim ccc As Integer
Call dbConnection
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "update MAIN set QUANTITY=QUANTITY- val(" & MSFLEXGRID.TextMatrix(rrr + 1, ccc + 2) & ")where  NAME='" & MSFLEXGRID.TextMatrix(rrr + 1, ccc) & "' "
cmd.Execute
End Sub

But with this code i can only update only one record in the table MAIN of my database, that which is the first on the MSFLEXGRID list and I want to update all records that are in MSFLEXGRID list. My table MAIN structure is KEY|NAME|DESCIPTION|CATEGORY|QUANTITY|VALUE1|VALUE2 and my MSFLEXGRID structure is NAME|DESCRIPTION|QUANTITY|VALUE

I hope to have made my self clear and I hope that you will be able to help me.

[brucevde]

Create a Command with Parameters.
Loop thru the grid and for every row set the parameter value to the proper cell value of the grid.
Execute the command

Private Sub cmdOK_Click()
   Dim lngRow as Long 
   Dim cmd As ADODB.Command

   Call dbConnection
   Set cmd = New ADODB.Command
   With cmd
      Set .ActiveConnection = cnn
     .CommandText = "update main set quantity = quantity - ? Where [name]=?"
     .Parameters.Append .CreateParameter("@QTY", adInteger, adParamInput)
     .Parameters.Append .CreateParameter("@Name", advarchar, adParamInput, 30)
   End With 

   With MSFlexGrid
       For lngRow = .FixedRows to .Rows - 1
          cmd.Parameters("@Name").Value = .TextMatrix(lngRow, 0)
          cmd.Parameters("@Qty").Value = .TextMatrix(lngRow, 2)
          cmd.Execute ,,adExecuteNoRecords
       Next 
   End With

End Sub

[ginothegodfather]

YEAH! That is wat i wonted
Thank you very much brucevde. Have a nice day.
GinoTheGodFather.