Errors updating Access database and uploading PDF file on update

I am trying to update a record in an Access database as well as upload a PDF file.  The submission page contains the File Field Name where the user can select the PDF to upload. The database will update the "proc_file" field with the name of the PDF that is generated, using a fixed location path as well as time, data, and random numbers serving as the name.

When I attempt to Update, I get the following error:

Request object error 'ASP 0208 : 80004005'

Cannot use generic Request collection

/procedures/submission_review_thankyou.asp, line 37

Cannot use the generic Request collection after calling BinaryRead.


I realize I am behind the times using ASP but for time constraints, I need to get thisup and running as quickly as possible. I will take the time in the future to familiarize my self with ASPX. Below is the code for review.

Any help or assistance is greatly appreciated with the code. On the surface, this should be very quick and easy to do, but I am stuck and of need of expert help.  Thank you.

<%
Set Upload = Server.CreateObject("Persits.Upload") 

Upload.OverwriteFiles = False 
Upload.SetMaxSize 5000000, true 
Upload.Save 

Date_Long = Upload.Form("date_long")
Extension = Upload.Form("extension")

For Each File in Upload.Files 
File.SaveAs "c:\inetpub\wwwroot\procedures\uploads\manual\" & Date_Long & "_" & extension &".pdf" 
Next 
%>

<%
Dim Recordset1
Dim Recordset1_numRows

pID=Request.Querystring("ID")
pIDX=Upload.Form("IDX")

Proc_File = "http://flmb.circ11.dcn/procedures/uploads/manual/" & Date_Long & "_" & extension &".pdf"

Set Recordset1 = Server.CreateObject("ADODB.recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\manual.mdb"

Recordset1.Source = "SELECT * FROM manual" & pID

Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0

if request("Update") = "Update" Then

Set Recordset1 = Server.CreateObject("ADODB.connection")
Recordset1.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\manual.mdb"

sql=""

If Upload.Form("proc_file_new") <>"" Then
  sql=sql & ",proc_file='" & Proc_File & "'"
End IF 

sql="UPDATE manual SET " & Mid(sql, 2, 10000) 
sql=sql & " WHERE ID = " & pIDX& "" 
'response.write sql 
Recordset1.Execute sql
 
response.end      
 
end if
%>

<html>
<head>
<title>Submission Complete</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="styles/flub.css" rel="stylesheet" type="text/css">
</head>

<body leftmargin="0" topmargin="0" rightmargin="0">
<table border="0" cellspacing="0" cellpadding="0" width="100%">
  <tr>
    <td height="65" bgcolor="#FF0000"> 
      <div align="center"></div></td>
  </tr>
  <tr>
    <td><div align="center">Thank you.</div></td>
  </tr>
</table>
</body>
</html>

Open in new window

arendt73Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
change all

Request.Forms("

to

Upload.Form("
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
more specifically, change

if request("Update") = "Update" Then

to

if Upload("Update") = "Update" Then

The reason being is that you're using the file upload component, you now need to use that components method of requesting data from the previous page
0
arendt73Author Commented:
I applied your recommendation to: if Upload("Update") = "Update" Then

on line 38 and now I am receiving the following error:

Microsoft VBScript runtime  error '800a01b6'

Object doesn't support this property or method: 'Upload'

/procedures/submission_review_thankyou.asp, line 38
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
looks like you'll have to specify if its a Request.Form or Request.QueryString action...

if Request("Update") = "Update"

is suppose to be

 if Request.Form("Update") = "Update"

then you should use

 if Upload.Form("Update") = "Update"

if it's for a querystring, just replace form with querystring
0
arendt73Author Commented:
I applied the change and it seems to have resolved that issue. But now I get the following error when attempting to update the Access database:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

/procedures/submission_review_thankyou.asp, line 51

For your review, below is my updated code for your review:

<%
Set Upload = Server.CreateObject("Persits.Upload") 

Upload.OverwriteFiles = False 
Upload.SetMaxSize 5000000, true 
Upload.Save 

Date_Long = Upload.Form("date_long")
Extension = Upload.Form("extension")

For Each File in Upload.Files 
File.SaveAs "c:\inetpub\wwwroot\procedures\uploads\manual\" & Date_Long & "_" & extension &".pdf" 
Next 
%>

<%
Dim Recordset1
Dim Recordset1_numRows

pID=Request.Querystring("ID")
pIDX=Upload.Form("IDX")

Proc_File = "http://flmb.circ11.dcn/procedures/uploads/manual/" & Date_Long & "_" & extension &".pdf"

Set Recordset1 = Server.CreateObject("ADODB.recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\manual.mdb"

Recordset1.Source = "SELECT * FROM manual" & pID

Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0

If Upload.Form("Update") = "Update" Then

Set Recordset1 = Server.CreateObject("ADODB.connection")
Recordset1.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\manual.mdb"

sql=""

If Upload.Form("proc_file_new") <>"" Then
  sql=sql & ",proc_file='" & Proc_File & "'"
End IF 

sql="UPDATE manual SET " & Mid(sql, 2, 10000) 
sql=sql & " WHERE ID = " & pIDX & "" 
'response.write sql 
Recordset1.Execute sql
 
response.end      
 
End If
%>

<html>
<head>
<title>Submission Complete</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="styles/flub.css" rel="stylesheet" type="text/css">
</head>

<body leftmargin="0" topmargin="0" rightmargin="0">
<table border="0" cellspacing="0" cellpadding="0" width="100%">
  <tr>
    <td height="65" bgcolor="#FF0000"> 
      <div align="center"></div></td>
  </tr>
  <tr>
    <td><div align="center">Thank you.</div></td>
  </tr>
</table>
</body>
</html>

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
uncomment line 50 and right below it, put in

Response.End

This will write out your sql statement to the screen, which should help you troubleshoot the issue
0
arendt73Author Commented:
I uncommented line 50 and received the following:

UPDATE manual SET WHERE ID = 4

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

/procedures/submission_review_thankyou.asp, line 51


The update is not occurring and does not display the "Proc_File" (line 23)
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
this whole portion of your code looks incorrect:

sql=""

If Upload.Form("proc_file_new") <>"" Then
  sql=sql & ",proc_file='" & Proc_File & "'"
End IF 

sql="UPDATE manual SET " & Mid(sql, 2, 10000) 
sql=sql & " WHERE ID = " & pIDX & "" 

Open in new window


if think what you way want is:

sql=""

If Upload.Form("proc_file_new") <>"" Then
  sqlTmp=",proc_file='" & Proc_File & "'"
End IF 

sql="UPDATE manual SET " & Mid(sqlTmp, 2, 10000) 
sql=sqlTmp & " WHERE ID = " & pIDX & "" 

Open in new window


if that deosnt work, let me know what the response.write statement yields
0
arendt73Author Commented:
After applying the recommendation, I receive the following:

WHERE ID = 7

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

/procedures/submission_review_thankyou.asp, line 52
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok it looks like the if statement is failing, look for a field on the posting page called proc_file_new. If its not there, thats your problem. if it isnt, then its not getting a value to it.

a few other things...

- remove the comma in sqlTmp=",proc_file='" & Proc_File & "'"
- change sql=sqlTmp & " WHERE ID = " & pIDX & ""  

to

sql=sql & sqlTmp & " WHERE ID = " & pIDX & ""
0
arendt73Author Commented:
I believe the issue is that Line 45 cannot or does not recognize the name string from Line 23 (Proc_File = "http://flmb.circ11.dcn/procedures/uploads/manual/" & Date_Long & "_" & extension &".pdf").  Thus it cannot insert into Access because it is empty. I am not sure to add "" or leave it as is on line 45 ( sqlTmp="proc_file='" & Proc_File & "'").

The posting page contains a proc_file_new field. This field is used to upload the new PDF document.

After applying your suggestion, I get the following:

UPDATE manual SET WHERE ID = 5

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

/procedures/submission_review_thankyou.asp, line 52
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Maybe I missed that you did this, but all of your variables you are setting from the form have to be done inside of the for each statement.

For Each File in Upload.Files 
File.SaveAs "c:\inetpub\wwwroot\procedures\uploads\manual\" & Date_Long & "_" & extension &".pdf" 

' collect all form variables inside the for each

Date_Long = Upload.Form("date_long")
Extension = Upload.Form("extension")

x1=upload.form("x1")
x2=upload.form("x2")
x3=upload.form("x3")


Next

' Now you can use the variables such as Date_Long, Extension and any other form variables  in the rest of your code.

 

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
at the top of the page:

Upload.OverwriteFiles = False
Upload.SetMaxSize 5000000, true
Upload.Save

put in right after

Response.Write "Filename = " & Upload.FileName
Response.End

do you get the file name of the file you're trying to upload? if so, remove the newly added code and replace it with

fileName = Upload.FileName

and then change your sql to:

If fileName <>"" Then
  sqlTmp="proc_file='" & Proc_File & "'"
End IF

sql="UPDATE manual SET " & sqlTmp & " WHERE ID = " & pIDX & ""

if that doesnt work, then you may have to loop through the files collection as padas suggestion, although i thought if you only had one file you could access it as above or maybe fileName = Upload.Files(0).FileName
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
This might help  understand how aspupload works assuming since I see Server.CreateObject("Persits.Upload")  


http://www.aspupload.com/manual_simple.html#2_2
0
arendt73Author Commented:
After applying the recommendations, it appears the form submits and updates correctly. I greatly appreciate the help, assistance, and time working through my issue.

There is one more update I need to do. I need an update date. Below is the full code and I believe the issue is with Line 67 (updating the database).  I am not sure to have single or double quotes around the ampersands.

Again, any assistance is greatly appreciated. This will be the last modification request on the question.

Thank you again.

<%
Set Upload = Server.CreateObject("Persits.Upload") 

Upload.OverwriteFiles = False 
Upload.SetMaxSize 5000000, true 
Upload.Save 

Date_Long = Upload.Form("date_long")
Extension = Upload.Form("extension")

Proc_File = "http://flmb.circ11.dcn/procedures/uploads/manual/" & Date_Long & "_" & extension &".pdf"

fileName = Proc_File

'Response.Write Proc_File
'Response.End

For Each File in Upload.Files 
File.SaveAs "c:\inetpub\wwwroot\procedures\uploads\manual\" & Date_Long & "_" & extension &".pdf" 

x1=upload.form("x1")
x2=upload.form("x2")
x3=upload.form("x3")

Next 
%>

<%
Dim Recordset1
Dim Recordset1_numRows

pID=Request.Querystring("ID")
pIDX=Upload.Form("IDX")

'Proc_File = "http://flmb.circ11.dcn/procedures/uploads/manual/" & Date_Long & "_" & extension &".pdf"

Set Recordset1 = Server.CreateObject("ADODB.recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\manual.mdb"

Recordset1.Source = "SELECT * FROM manual" & pID

Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0

If Upload.Form("Update") = "Update" Then

Set Recordset1 = Server.CreateObject("ADODB.connection")
Recordset1.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\manual.mdb"

'Response.Write Proc_File
'Response.End

sql=""

If fileName <>"" Then
  sqlTmp="proc_file='" & Proc_File & "'"
End IF 

If fileName <>"" Then
  sqlDate="up_date='" & Upload.Form("date_sub") & "'"
End IF

sql="UPDATE manual SET " & sqlTmp & sqlDate &" WHERE ID = " & pIDX & "" 

'If Upload.Form("proc_file_new") <>"" Then
'  sqlTmp="proc_file='" & Proc_File & ""
'End IF 

'sql="UPDATE manual SET " & Mid(sqlTmp, 2, 10000) 
'sql=sql & sqlTmp & " WHERE ID = " & pIDX & ""

'Response.Write sql 
Recordset1.Execute sql
 
'response.end      
 
End If
%>

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
since sqlDate is the 2nd field in your query, you need to separate it with a comma:

sqlDate=", up_date='" & Upload.Form("date_sub") & "'"

if that fails, please do a response.write of the final sql statement and the error you're getting
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.