Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3992
  • Last Modified:

Insert Record into Access DB using VBScript

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
slegy
Asked:
slegy
  • 3
1 Solution
 
chaauCommented:
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
 
slegyAuthor Commented:
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
 
slegyAuthor Commented:
Found the problem - bad code!
0
 
slegyAuthor Commented:
It was my coding error, but being sure about the insert code saved a lot of time. Thank you.
0

Featured Post

Technology Partners: 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!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now