Solved

Problem Updating Access Database

Posted on 2013-11-09
6
465 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 85

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
Independent Software Vendors: 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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