Using ADO, all the records update instead of just 1. What am i doing wrong?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mike - 24 Sep 2007 03:39 GMT

Heres the code, I am doing it with excel. I dont normally use
recordsets and I don't understand why all the records are updating at
once. Any ideas?

    Set cnnExcel = Server.CreateObject("ADODB.Connection")

    'known as a DSN-less connection
    cnnExcel.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" &
Server.MapPath("calc4.xls") & ";ReadOnly=0;"

    Set rstExcel = Server.CreateObject("ADODB.Recordset")

    'http://www.w3schools.com/ado/prop_rs_cursortype.asp
    'http://www.w3schools.com/ado/prop_rs_locktype.asp

    rstExcel.Open "SELECT * FROM TestData WHERE id=1;", cnnExcel, 3, 3

    rstExcel.MoveFirst
        rstExcel("symbol") = myStock1.symbol
        rstExcel("historical_price")= myStock1.historical_price
        rstExcel("opening_price") = myStock1.opening_price
        rstExcel("pe_ratio")= myStock1.pe_ratio
        rstExcel("market_cap")= myStock1.market_cap
    rstExcel.Update

Reply to this Message

mike - 24 Sep 2007 05:35 GMT

some more information: it seems tied to the actual excel file. I
tried using a different file that had no formulas etc in it, just
numbers typed into cells, and only the first row of data was changed.
I think excel has some built in "intelligence" for copy down that is
interfering with this. For example, sometimes when you insert a row,
excel will automatically fill in the formula for the inserted cells.
Anyone know a workaround?

Reply to this Message

mike - 24 Sep 2007 06:22 GMT

OK I got it working by creating a new xls file. Apparently excel has
some kind of hidden attributes applied when you do "copy down" or
something. Hopefully someone will find this useful.

Reply to this Message

Webmaster Forum / ASP / Database Access / September 2007

Using ADO, all the records update instead of just 1. What am i doing wrong?