SQL Server Performance

Delete Field in an ADO recordset?

Discussion in 'General Developer Questions' started by tmarko, Sep 15, 2004.

  1. tmarko New Member

    I tried to use the following syntax

    RS.Field.Delete("Name of Field")

    and

    RS.Field.Delete 1

    without success.

    Both give med following error in Excel2003 where I want to first use this field then delete it and use copyfromrecordset but without this field

    "Operation is not allowed in this context"

    what is wrong and how can I delete a field after using it?
  2. bradmcgehee New Member

    This is not the answer you want, but use Transact-SQL in a Stored Procedure, not ADO code, to delete the record. It is much more efficient. Generally speaking, you should always use Transact-SQL in a SP to access SQL Server. ADO and ADO.NET are generally easier to use, but not as efficient or scalable.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. Adriaan New Member

    Are you trying to erase the value stored in a field in a row in your recordset? To do that, you just change the value of the field to Null or a zero-length string. Since you are trying to copy data to a new row, you change that value on the target row, before committing the new record.

    There is no Delete method associated with the ADODB.Field object (there is one associated with the ADODB.Fields object: remove a field from a recordset). Error will only occur at runtime.
  4. tmarko New Member

    Hello Adrian!

    Well I want to erase the whole column becasue I then want to use CopyFrom Recordset and this column should not be put into the Excelsheet. So is it enough to update the RS field in this case with a NULL, but all the values for the field is still there.
  5. Adriaan New Member

    If you want to ignore a field in a recordset, then why is the field included in the recordset to begin with?

    We are going a bit too far off SQL Server here, perhaps you should ask around in one of the Office communities.

    Take care,
    Adriaan
  6. tmarko New Member

    Beacuse I want to set defined names first in the first column in Excel then set values in the same column. So first I want to use a column in the RS to set this defined names, by looping through the RS, then I want to use the CopyFromRecordset and when doing this everything in the RS will be pasted in Excel so I want to Exclude the used column in the RS , that is why I want to Exclude the first field.

    Well this is not an Office question but an ADO Recordset question.
  7. Adriaan New Member

    Not sure what you;re trying to accomplish ...

    Are you looking for a way to find out what field names are present in a recordset? In that case, before doing the CopyFromRecordset, you could just loop through the Fields collection that is attached to the Recordset object, and set the column names in your Excel sheet.

    For n = 0 To (RS.Fields.Count - 1)
    ........... = RS.Fields(n).Name
    Next n
    Just replace the ........... with the object to which you want to apply the field name.
  8. tmarko New Member

    I want to place both .Name and .Value in the same cell of an Excel2003 sheet. For this I need I separate column (Field) in my Recordset for these data. One for the .Name values and one for .Value values. So as I written before. To accomplish this in Excel I nedd to first loop through the first column with the .Name values and set these. Then I want to avoid placing these values again as values in column 1 in Excel whare I want the .Values to be placed using .CopyFromRecordset the the rest values is placed in the following columns. The problem seems to that when using .CopyFromrecordset I cannot decide from which column to start printing. If I could I would not bother with this delete thing. So that is why I'm curious about how to delete a column in a RS
  9. Adriaan New Member

    I guess you have to code around the CopyFromRecordset method. It's usually not too bad performance-wise, as long as you use arrays of the Field() type: this is much faster than referring to the fields by their names. So you set two Field() arrays to the fields in the source and target recordsets (skipping the fields that you don't need) using the order in which you want to copy the data, and use an index number to loop through the fields and just copy with a single line of code in a loop (rsTarget(n).Value = rsSource(n).Value).
  10. tmarko New Member

    Adriaan! sounds interesting what you writing but I do not get everything so if you could provide some more details I would be glad. My RS has fields that correspond to the columns in Excel

    Regards
  11. Adriaan New Member

    Okay, I was thinking too much in database structures, and was assuming you can access data on an Excel sheet with the ADO objects - which I guess you can't. You can probably use the DAO objects for the columns on the Excel sheet, but you need to work out how to move data between the DAO and ADO objects.

    But as I said, this is more an Office issue, and you need to have a basic understanding of arrays and objects. Best of luck.

Share This Page