Error Type:(0x80020009)

[lince_tang]

I have this error when i trying to delete multiple data in DB that has been checked by the user. I have this error but when i opened my DB, the data is deleted(the code works but display this error)..

Error Type:
(0x80020009)
Exception occurred.
/acx/adminDeleteTPDetail.asp, line 39

The line 39 is: if cstr(msg(t)) = cstr(RS("tpID")) then
--->

<%
Dim total
Dim checkbox
Dim RS, strSQLQuery
Dim msg()

total = Request.Form("checkbox").count
checkbox = split(request.form("checkbox"),",")

Redim msg(total)
Response.write ubound(checkbox)
for t=0 to ubound(checkbox)
msg(t) = cstr(checkbox(t))
Response.write msg(t)
next

strSQLQuery = "select * from partner"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open strSQLQuery, conn

If rs.EOF or rs.bof then
response.redirect("deleteproduct.asp?requestprodid=" & valID)
else
RS.MoveFirst
While Not RS.EOF
for t=0 to ubound(msg)
(line 39) if cstr(msg(t)) = cstr(RS("tpID")) then
conn.Execute("DELETE from partner WHERE tpID = '"& cstr(msg(t)) & "';")
end if
next
RS.MoveNext
Wend
End if

%>

[lmf232s]

could be a couple of things.
First i would check to see what the values of those two are when it
errors out.

for t=0 to ubound(msg)
response.write cstr(msg(t)) & " = " & cstr(RS("tpID")) & "<BR>"
if cstr(msg(t)) = cstr(RS("tpID")) then
conn.Execute("DELETE from partner WHERE tpID = '"& cstr(msg(t)) & "';")
end if
next

[lince_tang]

no prob wif that..

I think the problem is this line

conn.Execute("DELETE from partner WHERE tpID = '"& cstr(msg(t)) & "';")

becoz i execute conn.execute many times in for loop.
Do you know if i can execute multiple lines in DB without execute conn.execute for many times?

Thanks.

[javaNoobie]

I've posted my reply here (http://www.webdeveloper.com/forum/showthread.php?s=&threadid=47001#post264072). Reply back in this thread if you have other enquiries or problems.

[javaNoobie]

Ok, I think I've identified the problem. If I am wrong, correct me.

total = Request.Form("checkbox").count 'this will return the number of checked checkboxes. For this example, take it as 4
checkbox = split(request.form("checkbox"),",")

Redim msg(total) 'For easier reading take this as "Redim msg(4)" which means you know have an array of 5 elements
Response.write ubound(checkbox) 'this will return 3
for t=0 to ubound(checkbox) 'this is interpreted as "for t=0 to 3"
msg(t) = cstr(checkbox(t))
Response.write msg(t)
next
'so far so good except that now your last element in your array "msg" is null

for t=0 to ubound(msg) 'take this as "for t=0 to 4"
if cstr(msg(t)) = cstr(RS("tpID")) then 'first 4 loops 0-3 will be cool. At the last loop, msg(4) is empty. Hence the error.
conn.Execute("DELETE from partner WHERE tpID = '"& cstr(msg(t)) & "';")
end if
next

[lince_tang]

erhm.. the problem wif the conn.execute..
it can't be executed for more than once...

my code can work already.. thanks a lot..;>

[lince_tang]

keke.. thanks yeah..
One more question...
How do I display a pop-up 'browse' window when i click on "browse" button? then write the file name in the textfield?

[javaNoobie]

<input type="file">

[lince_tang]

Hi javaNoobie.. I got an error again when running this code.. keke..
I would like to import this excel to the access..
the version of my ms.office is 2002..
I have close excel when i run this program. And i am the administrator of this comp. But this error keep come out...
Thanks for your time to find the solution =)

Error Type:
Microsoft JET Database Engine (0x80004005)
The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
/acx/sample.asp, line 33

<%

Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
ExcelConn.Open "C:\folder\sample.xls" --->line 33

'get data from sheet
set ExcelRS = ExcelConn.Execute ("SELECT * FROM Sheet1$")

'connect to access
set objConnAccess = Server.CreateObject("ADODB.Connection")
objConn.Open "SELECT * from mpn", conn, 1, 3

'loop through each record in Excel and write it to access
'might be slow, but will work
Dim countDB
countDB = cint(objConn.RecordCount) + 1
tpID = "item000" & countDB

While NOT ExcelRS.EOF
countDB=countDB+1
tpID = "item000" & countDB
objConnAccess.Execute("INSERT INTO mpn (itemID, mpnID, description, datecode, quantity, price, manufacturer) VALUES ('" & tpID & "','" & objRS("mpnID") & "', '" & objRS("description") & "', '" & objRS("datecode") & "', '" & objRS("quantity") & "', '" & objRS("price") & "','" & objRS("manufacturer") & "');")

ExcelRS.MoveNext
Wend

ExcelRS.Close
set ExcelRS = NOTHING
objExcelConn.Close
set ExcelConn = NOTHING
objConnAccess.close
set objConnAccess = NOTHING


%>

[javaNoobie]

http://support.microsoft.com/kb/195951/EN-US/

or

Google it (http://www.google.com.sg/search?hl=en&q=accessing+excel+files+with+ADO&btnG=Google+Search&meta=)

[lince_tang]

Hi.. thanks a lot! it works.. the problem is tat i have to give permission to iusr_[comp_name] for modifying the file.

Keke.. same again.. i have another question..

ok..

http://localhost/acx/confirmation.asp?requestType=Request%20for%20Quotation&fileName=C:\Documents%20and%20Settings\lince\Desktop\ADOtest.xls

I am using Request.QueryString("requestType")and("filename") would like to display it into 'textfield' but i only managed to get
requestType = request --> instead of (Request for Quotation)
filename = C:\Documents --> instead of (C:\Documents and Settings\lince\Desktop\ADOtest.xls)

It will stop executing after " "/(%20).
Cheers!

[javaNoobie]

You can try replacing those spaces with a '+' sign before you append the querystring. When retrieving, simply replace those '+' sign with a space..

[lince_tang]

Hi javaNoobie thanks..

ok.. i should put "" for the value of the input type
valid --> value="<%=fileName%>"
instead of value=<%=fileName%>

I have another question..

I have input value of:
quantity = "qty100" or "50000" or "8000/pieces"
But I would like to store the digits of "0123456789" to DB which have a 'number' DataType.
Results:
quantity = "100" or "50000" or "8000"

[javaNoobie]

Use the RegExp object to trim out whatever you do not want.(i.e non-digits)

Dim re
set re = new RegExp
re.global = true
re.Pattern = "\D"
Response.write(Re.Replace("D1A2D34FGH6",""))
To accomodate decimal places..

Dim re
set re = new RegExp
re.global = true
re.Pattern = "[^\d\.]"
Response.write(Re.Replace("D1.A2D34.FGH6",""))

[lince_tang]

Hello javaNoobie,
I have this error when I want to upload 'file' into the web server.
I have the 'upload' function inside 'upload.asp' file.
But inside this file, I also need to do a Request.form to obtain other fields value.
But it prompts me with this error.


Request object, ASP 0207 (0x80004005)
Cannot use Request.Form collection after calling BinaryRead.

[javaNoobie]

http://support.persits.com/show.asp?code=PS01041843

[lince_tang]

Hi javaNoobie, I would like to count and retrieve the 'requestID' from my Access DB using the following SQL Query:

"SELECT requestID, COUNT(*) AS requestCount FROM request WHERE date >='" &fromDate& "' AND date <='" & toDate & "' AND category='" & category & "' GROUP BY requestID"

But, It keeps returns RS("requestCount") = 1 which is wrong.
===============================================================
But when I write:(without query for the 'requestID' field)

"SELECT COUNT(*) AS requestCount FROM request WHERE date >='" &fromDate& "' AND date <='" & toDate & "' AND category='" & category & "'"

It returns the correct answer.
===============================================================

Thanks!

[javaNoobie]

Try:

SELECT requestID FROM request WHERE ...

Use the recordCount property of the recordset object to find out how many records were returned. Your cursortype must be 3.