Solved

Insert Record into Access DB using VBScript

Posted on 2014-01-02
5
3,417 Views
Last Modified: 2014-01-03
This should be simple and straightforward, but I've spent all day and had no success. I'm updating one table and, based on the results, attempting to insert a record in another table. The first update (to the members table) is working.  But I have had no success with the insert to ilcaMembers:

'define subroutine to handle "all" payments ##
sub allPayments()  ' begin sub ###########################################################
   
   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")

   sql = "UPDATE members SET members.paypalStatus = '" & payment_status & "' Where (((members.memID) = '" & memberID & "'));"
   rs=conn.Execute(sql)
      
   sql = "SELECT * FROM members WHERE (memID = '" & memberID & "');"
   rs=conn.Execute(sql)
   
   districtCode = rs.district   
   If (rs.appType = "M" or rs.appType = "R") And (Trim(payment_status) = "Pending") then

      conn.close  
	
	  set conn=Server.CreateObject("ADODB.Connection")	  conn.Provider="Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("../../DB/ILCADATA.MDB") & ";"
	  conn.Open	 
	  set rs = Server.CreateObject("ADODB.recordset")
   
      sql = "SELECT districtName FROM districts WHERE (districtCode = '" & districtCode & "');"
      rs=conn.Execute(sql)
      districtName = rs.DistrictName
	  
	  sql = "INSERT INTO ilcaMembers (memberID, districtName) VALUES ([" & memberID & "], [" & districtName &"])"

      rs=conn.Execute(sql)

      conn.close

   End If
end sub  'end sub 

Open in new window


There are no error messages.
0
Comment
Question by:slegy
  • 3
5 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39752809
Modify sql to this (if the memberID column in ilcaMembers table is numeric):
 sql = "INSERT INTO ilcaMembers (memberID, districtName) VALUES (" & memberID & ", '" & districtName &"')"

Open in new window

Modify sql to this (if the memberID column in ilcaMembers table is TEXT):
 sql = "INSERT INTO ilcaMembers (memberID, districtName) VALUES ('" & memberID & "', '" & districtName &"')"

Open in new window

Most likely the former is correct. I just added the latter in case the memberID column in ilcaMembers table is TEXT
0
 

Author Comment

by:slegy
ID: 39754199
Well, I just determined that the insert query is not the problem - at least at the moment. What I'm trying to do is update a specific record (lines 8,9 - which complete successfully), then retrieve that same record and use information from it to append a record in a table in another db. Lines 11 and 12 execute, then nothing happens beyond that. I tried closing and reopening the db, but I got the same results. Is this the wrong way of going about it?

Thank you for the tip about the code.
0
 

Author Comment

by:slegy
ID: 39754353
Found the problem - bad code!
0
 

Author Closing Comment

by:slegy
ID: 39754827
It was my coding error, but being sure about the insert code saved a lot of time. Thank you.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

813 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

11 Experts available now in Live!

Get 1:1 Help Now