Solved

Problem Updating Access Database

Posted on 2013-11-09
6
457 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

809 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