Solved

Problem Updating Access Database

Posted on 2013-11-09
6
444 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
  • 3
  • 2
6 Comments
 
LVL 82

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 82

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 82

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now