Add new Row to datagrid from textbox

[kathyms]

Hi, I'm trying to add new rows to a datagrid from values of textboxes, it works fine but when the rows of the grid are bigger than the visible size of it, it gives an error of "incorrect number of line", i think it has to do something with the bookmark but i'm not very sure, can someone help plz?

' Set new recordset to add the new row
Set rsArray = New ADODB.Recordset
            With rsArray
                
               .Fields.Append "ID_MONTO_ADICIONAL", adInteger
               .Fields.Append "CONCEPTO_MONTO", adVarChar, 50
               .Fields.Append "MONTO_ADICIONAL", adDouble
               .CursorType = adOpenStatic
               .LockType = adLockOptimistic
               .Open
 
               '--load all the records that were before
               Dim i As Integer
               For i = 0 To can_filas - 1 ' can_filas has already the number of rows before adding a new one
                   Me.dgMontosAdic.RowBookmark (i) [B]'--Get a row of the grid, gives error here when i insert record 11, and the visible size of the grid is 10[/B]
 
' Add all rows that were before
                   .AddNew
                   ![ID_MONTO_ADICIONAL] = i + 1
                   ![CONCEPTO_MONTO] = Me.dgMontosAdic.Columns(1).Text
                   ![MONTO_ADICIONAL] = Me.dgMontosAdic.Columns(2).Text
               Next
               
               '--Add the new record
               .AddNew
               ![ID_MONTO_ADICIONAL] = i + 1
               ![CONCEPTO_MONTO] = txtConcepto.Text
               ![MONTO_ADICIONAL] = txtMonto.Text
               
               .Update
' update the number of rows
               can_filas = can_filas + 1
            End With
            
' update the grid with the new recordset and the new row.
            Set Me.dgMontosAdic.DataSource = rsArray

i hope u understand me, and thanks in advance for help

[brucevde]

You can only call RowBookmark for visible rows. Try GetBookMark instead.

[kathyms]

i try using getbookmark like this:

Me.dgMontosAdic.GetBookmark(i)

but i get the same error but in the second row and not after the number of rows are bigger than the visible size like before.

any ideas?

[brucevde]

The argument to GetBookmark is the row relative to the current row, not a specific row number.

Also, use the Column's CellValue property instead of the Text property to access the data. CellValue requires the Bookmark of the Row from where you want to retrieve data.

Me.dgMontosAdic.Row = 0 'start the first row in the grid 
For i = 0 To can_filas - 1
   'Me.dgMontosAdic.RowBookmark (i) 
   .AddNew
   ![ID_MONTO_ADICIONAL] = i + 1
   ![CONCEPTO_MONTO] = Me.dgMontosAdic.Columns(1).CellValue(Me.dgMontosAdic.GetBookmark(i))
   ![MONTO_ADICIONAL] = Me.dgMontosAdic.Columns(2).CellValue(Me.dgMontosAdic.GetBookmark(i))
Next

[kathyms]

i try that and i got the same error, the first row inserts just fine, so does the second row, but the third row gives me that error of "incorrect number of line", why it doesn't do it with the first two? and the error was once again in the bookmark when i use the CellValue Property

[brucevde]

Does the Can_Filas variable contain the number of Rows in the grid? Post your updated code.

[kathyms]

yes, can_filas has the initial number of rows of the datagrid, in the load event i have this line:

can_filas = rSetMontosAdic.RecordCount

because i make an initial search to my database for previews values, and when i add a new row i increment this value

' new recordset for the old values and the new value to add
Set rsArray = New ADODB.Recordset
With rsArray
 
       .Fields.Append "ID_MONTO_ADICIONAL", adInteger
       .Fields.Append "CONCEPTO_MONTO", adVarChar, 50
       .Fields.Append "MONTO_ADICIONAL", adDouble
       .CursorType = adOpenStatic
       .LockType = adLockOptimistic
       .Open
               
       '-- we add all the previews values
       Dim i As Integer
       For i = 0 To can_filas - 1
            .AddNew
            ![ID_MONTO_ADICIONAL] = i + 1
            ![CONCEPTO_MONTO] = Me.dgMontosAdic.Columns(1).CellValue(Me.dgMontosAdic.GetBookmark(i)) ' Now i get the error here because of the bookmark
            ![MONTO_ADICIONAL] = Me.dgMontosAdic.Columns(2).CellValue(Me.dgMontosAdic.GetBookmark(i)) ' and here too
       Next
               
       '--add the new value
       .AddNew
       ![ID_MONTO_ADICIONAL] = i + 1
       ![CONCEPTO_MONTO] = strConcepto
       ![MONTO_ADICIONAL] = strMonto
               
       .Update
       can_filas = can_filas + 1 ' here i increment the number of rows that was already set in the load event
End With
 
' update the datagrid with the modified recordset
Set Me.dgMontosAdic.DataSource = rsArray

[brucevde]

You did not include this line of code.

Me.dgMontosAdic.Row = 0 'start the first row in the grid

GetBookMark is relative to the current row so ensure the first row in the grid is the current row before starting the For Loop.

[kathyms]

i got the same error but this time it was like the first time, i got the error after the number of rows is bigger than the visible size of the grid any ideas?

[kathyms]

now i've tried this:

' New recordset for old values and new one
Set rsArray = New ADODB.Recordset
With rsArray
    .Fields.Append "ID_MONTO_ADICIONAL", adInteger
    .Fields.Append "CONCEPTO_MONTO", adVarChar, 50
    .Fields.Append "MONTO_ADICIONAL", adDouble
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Open
            
    ' i give the recordset the old data that exist in database
    dgMontosAdic.AllowAddNew = True
    Set .DataSource = Me.dgMontosAdic.DataSource
       
    ' i'm not using this now
    ' Me.dgMontosAdic.Row = 0
    '--Bucle para recorrer el contenido de la grilla
    Dim i As Integer
    For i = 0 To can_filas - 1
       
'       .AddNew
'       ![ID_MONTO_ADICIONAL] = i + 1
'       ![CONCEPTO_MONTO] = Me.dgMontosAdic.Columns(1).CellValue(Me.dgMontosAdic.GetBookmark(i))
'       ![MONTO_ADICIONAL] = Me.dgMontosAdic.Columns(2).CellValue(Me.dgMontosAdic.GetBookmark(i))
    Next
   
    '--Add the new record
    
    .AddNew
    ![ID_MONTO_ADICIONAL] = i + 1
    ![CONCEPTO_MONTO] = strConcepto
    ![MONTO_ADICIONAL] = strMonto
    
    .Update
    can_filas = can_filas + 1
End With
 
' update the grid with the old rows and the new one.
Set Me.dgMontosAdic.DataSource = rsArray

it works fine, but the problem is now that if the grid has old values, when i try to add a new one, gives me an error "The actual Recordset does not allow actualizations. This can be a limitation of the provider or the type of lock selected", something like that, i'm not very good with translations

thanks for helping