Solved

Problem Updating Access Database

Posted on 2013-11-09
6
460 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 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 175 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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 225 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 83

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 83

Expert Comment

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

697 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