How do you get mother ######ing ADO to work properly on an xls file

[boylesg]

Can some one please point me in the direction of a working example that is not going to give me:"Item cannot be found in the collection corresponding to the requested name or ordinal.""query cannot be updated because it contains no searchable columns to use as a key"I have this: var objConn = Server.CreateObject("ADODB.Connection"); objConn.Mode = 3; // 3 = adModeReadWrite objConn.Open("Invoice"); //Setup in xp odbc adminstrator var objRS = Server.CreateObject("ADODB.Recordset"); objRS.ActiveConnection = objConn; objRS.CursorType = 2; // Open dynamic cursor. objRS.LockType = 2; // Pessimistic Lock.This works:function WriteInvDate(objRS){ var objDate = new Date(); var strDate = String(objDate.getDate()) + "/" + String(objDate.getMonth() + 1) + "/" + String(objDate.getFullYear()); // If open then close it. if (objRS.state == 1) objRS.Close(); objRS.Source = "Select * from invoice_date"; objRS.Open(); objRS.Fields.Item(1).Value = strDate; objRS.MoveNext(); objRS.Close();}Excel spread sheet[blank] [blank]DATE: 20/6/2010name=invoice_dateThis works:function WriteInvNum(objRS){ // If open then close it. if (objRS.state == 1) objRS.Close(); objRS.Source = "Select * from invoice_num"; objRS.Open(); Index = Number(objRS.Fields.Item(1).Value); Index++; objRS.Fields.Item(1).Value = Index; objRS.MoveNext(); objRS.Close();}Excel spread sheet[blank] [blank]INVOICE #: 330name=invoice_numThis wont work properlyfunction WriteInvBillingDetails(objRS){ // If open then close it. if (objRS.state == 1) objRS.Close(); objRS.Source = "Select * from billing_name_address"; objRS.Open(); objRS.Fields.Item(0).Value = String("Fred Smith"); objRS.Fields.Item(1).Value = String("Fred Smith"); //objRS.Fields.Item(2).Value = String("Fred Smith"); //objRS.Fields.Item(3).Value = String("Fred Smith"); //objRS.Fields.Item(4).Value = String("22 Claremont Street, Fawkner, VIC, 3076");Excel spread sheet[blank] [blank]Billing Details[blank] Name Greg BoylesAddres 56 Derby Drive, Epping, VIC, 3076objRS.Fields.Item(0).Value = String("Fred Smith"); worksobjRS.Fields.Item(1).Value = String("Fred Smith"); worksobjRS.Fields.Item(2).Value = String("Fred Smith"); "Item cannot be found in the collection corresponding to the requested name or ordinal."objRS.Fields.Item(3).Value = String("Fred Smith"); "Item cannot be found in the collection corresponding to the requested name or ordinal."objRS.Fields.Item(4).Value = String("Fred Smith"); "Item cannot be found in the collection corresponding to the requested name or ordinal."I don't understand!

[boylesg]

Can't use this method either beause it generates an error along the lines of no ISAM loaded. 'Open the ADO connection to the destination Excel workbook Set oConn = Server.CreateObject("ADODB.Connection") oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & sDestXLS & ";" & _ "Extended Properties=""Excel 8.0;HDR=NO;""" 'Add values to individual cells oConn.Execute "Insert into First_Name (F1) Values ('Nancy')" oConn.Execute "Insert into Last_Name (F1) Values ('Davolio')" oConn.Execute "Insert into Title (F1) Values ('Sales Manager')" oConn.Execute "Insert into Hire_Date (F1) Values (#11/1/00#)" oConn.Execute "Insert into Comments (F1) Values ('This is a line of long text " & _ "that will wrap in the cell.This is a line of long text that will " & _ "wrap in the cell.')"

[boylesg]

To be honest I would rather put text markers in the cells I want to edit, open the xls file and a simple binary file in asp javascript and then change the markers for the proper values.That way I could avoid all these pain in the arse methods with all their hurdles that have to be jumped.But I can't find any binary file support in asp.

[justsomeguy]

Those errors are because it is only returning 2 columns for each row. I'm not real sure how Excel works with ODBC, but you get an error when you try to update the third column because the recordset only contains 2 columns.

[boylesg]

Those errors are because it is only returning 2 columns for each row. I'm not real sure how Excel works with ODBC, but you get an error when you try to update the third column because the recordset only contains 2 columns.

Yeah well that was my first and last attempt at using ODBC, at least with Excel. It has to many hurdles and is to much hassle.I found that you can enter the data into a csv file and then merge that with my xls file. And I can treat the csv file as a text file inside asp.Much easier. Just a matter of putting field markers in the csv file to make data insertion easy.