ADO help

[marnitzg]

I have someone's code which I have to fix. Its a pain in the butt! Anyway, the problem is that when you add a record to the ADO control and then try to delete it straight afterwards in returns this error:

Run-time error '-217217864 (80040e38)':

Row cannot be located for updating. Some values may have been changed since it was last read.

The user clicks on a row of a dbgrid and then clicks delete which executs this code:

    Adodc1.Recordset.Delete
    Adodc1.Recordset.UpdateBatch adAffectAllChapters

Please help me. I am not in the mood to learn ADO database control!!

[vbuser1976]

From the information you have given, it seems to me that you would have to open the recordset before you do a delete method. Now regarding the dbgrid, you might need to pass the parameter(highlighted column) to the recordset before you can delete it, here's some sample code (you are going to need to adjust it to your needs):

Dim response As Integer
    Dim BYTER As Integer
    Dim rs As ADODB.Recordset
    Dim cm As ADODB.Command
    Dim p As ADODB.Parameter
    
    BYTER = DbGrid1.RowSel
    
    Set rs = New ADODB.Recordset
    Set cm = New ADODB.Command
    Set cm.ActiveConnection = cn
    cm.CommandType = adCmdStoredProc
    cm.CommandText = "sp_get_all_data_from_tblReport"
    Set p = cm.CreateParameter("@RepId", adInteger, adParamInput, Len(DbGrid1.TextMatrix(BYTER, 2)), DbGrid1.TextMatrix(BYTER, 2))
    cm.Parameters.Append p
    Set rs = cm.Execute

With rs
    .delete
    .UpdateBatch adAffectAllChapters
End With

End Sub

I hope this helps. Good Luck!

[marnitzg]

Heres the thing. i've never programmed anything to do with ADO before. The adodc1 is the ADO control stuck on the form. This is why I rarely offer to fix other people's code!

Another thing, its a datagrid, not a dbgrid (Sorry, my mistake!)

[vbuser1976]

Well, regarding datagrid and dbGrid, you just need to change dbGrid to whatever name you assigned to your datagrid. For the code above, you would still need the ADO data control connected to the form, but you don't need to use ADODC in code (no other changes are really needed). ADODB is part of the control so there is no need to add more components or references. I would suggest you get a book on ADO if you are going to use it. A good book that can start you off is titled:

"Database Access with Visual Basic 6" by Jeffrey McManus (i have this one)
ISBN: 0672314223

It has tons of sample code.

[marnitzg]

Thing is, I'm not going to use ADO ... EVER! This is a friend of mines prog who needs it for a varsity project.

Anyway, I just get errors with you code!

First 1
BYTER = DataGrid1.RowSel
There is no rowsel??? So I changed rowsel to row

Second
Set cm.ActiveConnection = cn
Object required. What is cn?

Is there a way I can do this with the adodc1 control?
The delete only fails if the record was just created. If you exit and run the prog again, it deletes it without complaining. Do I need to close the records first or something? I call adodc1.recordset.update.

Thanks for your help

[vbuser1976]

Okay, you can work with the control, but I am not comfortable with it. I like to use the db part of the control because it doesn't change the settings of the control.

Now to solve your problem, it looks like you would have to close the connection and reopen it for the deletion to take place. Even though .Update does put the data needed in the database, the connection is referring only to that recordset. So, what you would have to do is create another sub that reopens the connection, requeries the recordset, and deletes the requested data. By you closing the program, it closes the connection, so the only solution is what I stated before.

BTW, cn is the name of the connection. You can change it to the name of your connection.

Therefore, yes, you can use the ADODC for what you want to do, and yes, you do have to close the records first before you can delete.

I hope this helps you and Good Luck!

[marnitzg]

I'd prefer to use your code... But I have another problem:

Set p = cm.CreateParameter("@RepId", adInteger, adParamInput, Len(DbGrid1.TextMatrix(BYTER, 2)), DbGrid1.TextMatrix(BYTER, 2))

TextMatrix doesn't exist! Is there anything else I need to change in this line?

[vbuser1976]

I must apologize for that. I adapted the source code I gave you from code that used a MSHFlexGrid instead of a dbgrid. I have had many problems with dbgrid in my projects so I started using MSHFlexGrid instead. MSHFlexGrid is a lot easier to use.

Anyway, I don't know of something in dbgrid that is similar to .TextMatrix in MSHFlexGrid. Maybe another forum buddy will see this and add their input.

Sorry I couldn't be better help to you. Good Luck!