arendt73
asked on
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_rev
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.
When I attempt to Update, I get the following error:
Request object error 'ASP 0208 : 80004005'
Cannot use generic Request collection
/procedures/submission_rev
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>
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
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
ASKER
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_rev
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_rev
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
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
ASKER
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_rev
For your review, below is my updated code for your review:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/procedures/submission_rev
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>
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
Response.End
This will write out your sql statement to the screen, which should help you troubleshoot the issue
ASKER
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_rev
The update is not occurring and does not display the "Proc_File" (line 23)
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_rev
The update is not occurring and does not display the "Proc_File" (line 23)
this whole portion of your code looks incorrect:
if think what you way want is:
if that deosnt work, let me know what the response.write statement yields
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 & "" 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 & "" if that deosnt work, let me know what the response.write statement yields
ASKER
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_rev
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_rev
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 & ""
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 & ""
ASKER
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_rev
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_rev
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This might help understand how aspupload works assuming since I see Server.CreateObject("Persi
http://www.aspupload.com/manual_simple.html#2_2
http://www.aspupload.com/manual_simple.html#2_2
ASKER
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.
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
%>SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Request.Forms("
to
Upload.Form("