?
Solved

Problem Updating Access Database

Posted on 2013-11-09
6
Medium Priority
?
473 Views
Last Modified: 2013-11-10
Apologies if this has been asked before. I've combed through previous posts but could not find the answer. I'm attempting to update a record in an Access 2007 database in an ASP/VBScript page subroutine with a value passed from another page. I've tried a couple of different approaches (based on my research), and I can't get anything to work.

First approach:
sub allPayments()  ' begin sub ###########################################################
   sql = "UPDATE members SET paypalStatus = '" & payment_status & "' Where (memID = '" & memberID & "')"
   
   whichdir=Server.Mappath ("..\membership\joinRenew")
   whichname="\paypalreturn.html"
   whichFN=whichdir & whichname
   set filesys = Server.CreateObject("Scripting.FileSystemObject")
   set textfile = filesys.OpenTextFile(whichFN, 8, 0)
   textfile.WriteLine Now
   textfile.WriteLine "<BR><B>SQL:   </B>" & (sql)
   textfile.WriteLine "<BR><B>Status:   </B>" & (payment_status)
   textfile.WriteLine "<BR><B>ID:   </B>" & (memberID)      
   textfile.WriteLine "<br>===============================<p>"
   textfile.Close  
 
   set conn=Server.CreateObject("ADODB.Connection")
   conn.Provider="Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("..\..\DB\ILCAEVENTCALENDAR.MDB") & ";"   conn.Open
   set rs = Server.CreateObject("ADODB.recordset")
   Set rs=conn.Execute(sql)
   rs.close
end sub  'end sub ###########################################################################

I've verified the values in the variables from the values in textfile:
11/9/2013 4:09:07 PM
SQL: UPDATE members SET paypalStatus = 'verified' Where (memID = 'KENER000')
Status: verified
ID: KENER000

The following error is generated:
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
/scripts/paypalVerify.asp, line 35
My research hasn't turned up any insights as to why.

Second Approach:
sub allPayments()  ' begin sub ###########################################################

   'sql = "UPDATE members SET paypalStatus = '" & payment_status & "' Where (memID = '" & memberID & "')"
   
   whichdir=Server.Mappath ("..\membership\joinRenew")
   whichname="\paypalreturn.html"
   whichFN=whichdir & whichname
   set filesys = Server.CreateObject("Scripting.FileSystemObject")
   set textfile = filesys.OpenTextFile(whichFN, 8, 0)
   textfile.WriteLine Now
   textfile.WriteLine "<BR><B>SQL:   </B>" & (sql)
   textfile.WriteLine "<BR><B>Status:   </B>" & (payment_status)
   textfile.WriteLine "<BR><B>ID:   </B>" & (memberID)      
   textfile.WriteLine "<br>===============================<p>"
   textfile.Close  

   set conn=Server.CreateObject("ADODB.Connection")
   conn.Provider="Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("..\..\DB\ILCAEVENTCALENDAR.MDB") & ";"
   Dim cmd As New OleDbCommand(Nothing, conn)
   conn.Open()
   cmd.CommandText = "Update members SET paypalStatus = @paystat WHERE memID = @ID"

   cmd.Parameters.AddWithValue("@paystat", payment_status)
   cmd.Parameters.AddWithValue("@ID", memberID)
   conn.Open()
   cmd.ExecuteNonQuery()
conn.Close

end sub  'end sub ###########################################################################
 This generates the following error:
Microsoft VBScript compilation  error '800a0401'

Expected end of statement

/scripts/paypalVerify.asp, line 31
Dim cmd As New OleDbCommand(Nothing, conn)
--------^
I get the feeling that this may be the preferred approach, but I'm getting nowhere. Can anyone see what's wrong?
0
Comment
Question by:slegy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 525 total points
ID: 39636275
This code came with Windows 2000 with Office Pro 2000 for the 'Authors.mdb' demo database.
<%
Dim oConn		
Dim oRs			
Dim filePath		
		
' Map authors database to physical path
filePath = Server.MapPath("authors2.mdb")

' Create ADO Connection Component to connect with sample database

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath

' To add, delete and update  recordset, it is recommended to use 
' direct SQL statement instead of ADO methods.
			
oConn.Execute "insert into authors (author, YearBorn) values ('Paul Enfield', 1967)"
			
' Output Result
Set oRs = oConn.Execute (" select * from authors where Author= 'Paul Enfield' and YearBorn =1967 " )

Response.Write("<p>Inserted Author: " & oRs("Author") & "," & oRs("YearBorn"))
' Close Recordset
oRs.Close
Set oRs= Nothing
			
' Delete the inserted record
oConn.Execute "Delete From authors where  author='Paul Enfield' and YearBorn = 1967 "
			
' Output Status Result
Response.Write("<p>Deleted Author: Paul Enfield, 1967")
%>

Open in new window

0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 675 total points
ID: 39636782
You don't need to open a Recordset to run action queries, so try this for the first example:

set conn=Server.CreateObject("ADODB.Connection")
   conn.Provider="Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("..\..\DB\ILCAEVENTCALENDAR.MDB") & ";"  
   conn.Open
   conn.Execute sql

However, your error "No value given for one or more required parameters. " suggests that you have something misspelled. Check the column and table names and be sure you're using the exact spelling.

If you take the SQL from your txt file and try to "run" that directly in Access. does it succeed? To do that, download the db from the server and open it with your local Access program. Create a new Query, and switch to SQL view and paste in the UPDATE syntax from your text file.
0
 

Author Comment

by:slegy
ID: 39637369
Thank you very much. The "INSERT" query suggested by Dave produced the same results -
Yes, it does suggest that something is misspelled, but there are only two fields, and I am absolutely sure that they are referenced correctly. I've also gone back and researched to make sure that the query is being constructed correctly. I've reviewed at least a dozen examples and they are all done just like mine.

Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
/scripts/paypalVerify.asp, line 35


I followed your suggestion to run it directly in Access. Here is the SQL that was generated:

UPDATE members SET members.paypalStatus = "verified" WHERE (((members.memID)="EGYSH000"));

Here is the query as it is constructed in the VBScript:

UPDATE members SET members.paypalStatus = 'verified' Where (((members.memID) = 'EGYSH000'));

I even added all the parentheses so they matched. The only difference is the single and double quotes. All the examples I've reviewed use single quotes. But I replaced the singles with doubles:

UPDATE members SET members.paypalStatus = "verified" Where (((members.memID) = "EGYSH000")); (exactly the same as Access local). Same results:

Log file:

<BR><B>SQL:   </B>UPDATE members SET members.paypalStatus = "verified" Where (((members.memID) = "EGYSH000"));
<BR><B>StatusL:   </B>verified
<BR><B>ID:   </B>EGYSH000


Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
/scripts/paypalVerify.asp, line 35


So I'm mystified. Have tried again to use parameterized updates with no luck. Any additional suggestions most welcome.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39637410
Show us line 35.
0
 

Author Closing Comment

by:slegy
ID: 39637456
Well, guys, I feel like a bit of an idiot. Sometimes one gets in so deep and gets so lost they can't find the forest for the trees. If only error messages were a little more specific. Because the testing is related to Paypal, I've been doing all my testing online. I believe the table I was trying to write to got overlayed and the field I was trying to update was not there. Now that I have that straightened out, it is finally working. The good thing that comes out of this is that one learns a tremendous amount along the way. Thank you again for your time.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39637765
You're welcome, glad you got it sorted out.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question