Solved

Referencing Records Returned from a Select Query

Posted on 2014-02-13
15
299 Views
Last Modified: 2016-03-24
I'm in way over my head - AGAIN! I have an Access table that gets updated with 1-5 records. After I determine that a payment was successfully made to PayPal, I need to go back and create new records in a different table based on the original 1-5. How is this done?
0
Comment
Question by:slegy
  • 7
  • 5
  • 3
15 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39856870
If I understand correctly, then query the table that just got 1-5 records inserted to it and then insert into your new table.


INSERT INTO newTable (field1, field2, field3, field4, field5)
SELECT  o.field1, o.field2, o.field3, o.field4, o.field5 from theOtherTable o

WHERE newTable.Id NOT EXIST (select id from theOtherTable)
order by o.ID DESC

Open in new window


I think something like that.
0
 

Author Comment

by:slegy
ID: 39856898
The problem is I can't do a straight insert. There needs top be some data manipulation in between.
0
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 250 total points
ID: 39856918
If you insert data, then retrieve the last id, then use the id to insert the rest of the records (you would use this a foreign key and need to add a field in the db if you don't have one now).  You could store the id in a session or cookie ( but encrypt it).   Then look for the cookie or session variable to use for the other tables.   I would use both the cookie and session.  Sessions can get lost and cookies can be removed or not set.

Another option is you can grab the asp session and use that as your look up.  Every time somebody comes to your site, there is a new session.   It is safe to say the session will be unique for that day.

Try <%=Session.SessionID%> and you will see a number out put.  You could create a key like

<%
key=Session.SessionID&date
%>
Then use that key throughout your insert and if you need to call up other tables, you can use the "key" as the look up.
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39856964
It will probably help to show data structure for the two tables involved.

Your original post indicated you are UPDATING an original table.

If that's the case, then you have to ensure that the records you just updated do not already exist on the table you are trying to insert the just updated records into.
0
 

Author Comment

by:slegy
ID: 39857613
I should have said "to which records are added."

I may have been too vague about the process. I have created a transaction ID that identifies the record groups. I've been researching and wondering is something like this would work:              
        
sql = "SELECT * FROM members WHERE transID=" & transID
	  rs=conn.Execute(sql)
          Do While Not rs.EOF              
		 districtCode = rs("district")
		 fleetNo = rs("fleet")
                 rs.MoveNext
          Loop

Open in new window

Data is extracted and reformatted as needed, then inserted in the other table. The few tests I've run are not working so I'm guessing this is not the right approach.
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39857679
I *think* you need to start all over explaining what you are trying to do.

The code you just posted, syntactically is fine but I am not sure I understand how that figures into what you said you are trying to do.

Just me.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39857721
I agree.  Maybe start out with some bullet points of the order of operations you are trying to do.

When you are selling something, in my mind the first thing is to show the products and have some type of cart that people can add items.

When they checkout, you generate a sales order ID.  Then you generate a sales transaction table that contains the sales order ID as a foreign key.  The sales order would have name, phone number, date etc.  The sales transaction would have item_id, qty, price_sold, discount, tax_code.  

If they buy 5 items, you now have 1 sales_order and 5 related sales_transaction.  The sales transaction has the item_id and that can be linked to your products for a friendly description.

When the payment goes through, you will generate a payment transaction with the sales_order_id, payment_type, amount, date.

That would be a good base anyway.  If we can see your schema and bullet points of what you are doing, we can help in more detail
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:slegy
ID: 39857743
When an individual fills out a membership application or renewal form, all of that information is saved to a table which looks like this:
(ID AUTOINCREMENT(1, 1), appType Text(1), memID Text(8), memType Text(1), memYear Text(4), memUpdt Text(1), memDues Currency, title Text(4), firstName Text(18), midInit Text(4), lastName Text(24), suffix Text(5), nickname Text(12), birthyear Text(4), sex Text(1), address Text(50), address2 Text(50), city Text(28), stateProvince Text(2), zipCode Text(10), country Text(2), homePhone Text(18), workPhone Text(18), mobilePhone Text(18), homeEmail Text(80),loginEmail Text(80), workEmail Text(80), bgRecipient Text(4), bgMentor Text(4), boat1 Text(5), boatName1 Text(24), boat2 Text(5), boatName2 Text(24), boat3 Text(5), boatName3 Text(24), district Text(2), districtOffice Text(24), fleet Text(3), fleetOffice Text(24), fleetOffice2 Text(24), ilcaFund currency, limbFund currency, huntFund currency, boatFund currency, flashes double, yearbooks double, undesigCrew double, comments memo, payorLast Text(24), payorFirst Text(24), fbtoken Text(48), ggtoken Text(48), litoken Text(48), yhtoken Text(48), dateStamp date, time stamp date)

Open in new window

All memberships are paid for through PayPal. When notification is received from PayP)al that payment was successful, this table is updated and a corresponding record(s) need to be added to a current members table in another database:
memberID - districtName - fleet - memberName - suffix - address - state - country - location - loginEmail - memType - fbToken - ggToken - liToken - yhToken - boat - boatName - memberLast - memberFirst - homeEmail.

Open in new window

I've run several tests, adjusting some of the code, but the "Do While Not rs.EOF" is apparently not working:
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")
 	  rs.open "SELECT * FROM members WHERE transID=" & transID
	   
	  Do While Not rs.EOF 
		 districtCode = rs("district")
		 fleetNo = rs("fleet")		   
		 textfile.WriteLine "<BR><B>District:   </B>  " & (districtCode)   
		 textfile.WriteLine "<BR><B>Fleet:   </B>  " & (fleetNo)	
		 textfile.WriteLine "<br>===============================<p>"

Open in new window

If I move the Writelines above the Do While, the values print. After the Do While, nothing happens.
0
 

Author Comment

by:slegy
ID: 39857753
Sorry, forgot when I added the original table layout. The field "transID" has since been added.
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39857962
Ok, here is *how* I would approach it.

Perform your updates like you indicated.

While doing so, wherever the Id on your WHERE clause, remember:

update table set field=value WHERE id=???

Put that id in hidden form or as Padas suggested, in session.

Then before performing an insert, do a check:

    If currentId <> CLng("0" & Request("hiddenId")) Then
         Response.Write "INCONSISTENT ID!"
         Response.End
   End If

Open in new window


Then I will do my insert statement.

I would hope that the table you are inserting into has a primary (identityseed) for accurate history tracking.

So, if that's the case, check to see if Id already exists on the table you are inserting into.

If no, create one automatically.

If yes, increment it programmatically.

        SQL = "SELECT Max(childid) FROM ThisTableYouareInsertingInto" _
			 & " where Id=" & currentId 
		Set RS = conn.Execute( SQL )
        newChildid = 1 'just in case none already exists
        If Not RS.EOF Then
            If Not Isnull(rs(0)) Then newChildid = RS(0)+1 'one exists, increment it
        End If
        RS.Close

Open in new window


Then perform your insert statement here with all those fields you listed above, including the Id (in this example currentId) that you updated your records with.

Both Update and Insert statements can be one.

Remember, I am coding blindly here and this could all be wrong.

We indicated that you show some code to help us help YOU.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39858473
Is this the form you are working on? https://www.lightningclass.org/membership/joinRenew/membershipApplication.asp?1

Sometimes issues can be fixed and created with the way your data is stored. I don't know if there is any real perfect way to do this.  I try and normalize where it makes sense.

When I look at that form, I see a tables perhaps outlined like below that would be linked by either by the sales transaction, contact or membership.  There are valid reasons that this many tables may be too complex for what you are doing.  However, this could allow somebody in year 2 and beyond to simply log in and most of this info would be the same and all they need to do is add a new membership.

contacts - name...address....email....,date_created, date_updated
product_category - id, name, status,date_created, date_updated
product_sub_category - id, prod_category_id, name, status,date_created, date_updated
products - id, item, price, category, sub_category, status,date_created, date_updated  {Membership, printed flashes/yearbook/crew}
sales_orders - id, date, contact_id,date_created, date_updated
sales_transactions - id, sales_order_id, item, qty, price, discount,tax,date_created, date_updated
Memberships - id, date_start,date_end, type, contact id, status,linked_sales_trans,date_created, date_updated
Location - I would either make these fields part of the membership or boat table (do people have multiple boats in multiple locations?)
boats - id, boat_no,boat_name,linked_membership_id,date_created, date_updated
donations - part of products using category "donations"
0
 

Author Comment

by:slegy
ID: 39859085
Yes, that is one of the membership forms that we are using, There are actually four - two for new memberships and two for renewals. Know that may sound like an overkill, but there are good reasons.

I'm sorry I haven't done a good job of explaining this. You seem to be looking at it as being much more complicated than it is. All of the information needed for the second table exists in the first. The transaction ID groups the records that need to be processed - based on what is returned from PayPal.

I could add some information to the original table that could then be directly selected and inserted in the second table. However, the kink in the works is that PayPal performs multiple notifications.

Essentially what I need to do is select a recordset from the first table, based on transaction ID, loop through that recordset, check to see if the member ID already exists in the active members table, if not, do some minimal reformatting and then insert the record(s) in the second table.

So what I really need help with is correctly selecting the recordset and then looping through it.
0
 
LVL 28

Assisted Solution

by:sammySeltzer
sammySeltzer earned 250 total points
ID: 39859220
When I write classic asp code, I like to perform all the necessary tasks on ONE page.

So, I am not sure how your page is organized but I usually start with the form.

But in your case, let's start with your code:

<HTML>
<HEAD>
<title>Transactions</title>
</HEAD>

' ***********************************************************************
' ***********************************************************************
'
' Process postback requests from this page. All code is in one page'
' ***********************************************************************
' ***********************************************************************

If Trim("" & Request.Form("POSTBACK")) = "YES" Then
	' get all the expected form fields
	'
	' make sure info is consistent:
    If TransId <> CLng("0" & Request("TransactionID")) Then
         Response.Write "INCONSISTENT TransactionID!"
         Response.End
  End If

   ' Get form fields
     districtCode 	= Request.Form("districtCode ")
     fleetNo 	= Request.Form("fleetNo ")

	' process form submittal back to ourselves, if any
	If Trim(Request.Form("EDIT")) = "Save!" Then
	  ' save changes to existing transaction
	If currentChildTransId <= 0 Then
                   Response.Write "INVALID ChildTransId"
                   Response.End
	End If

'Now your update statement after transaction is made
	SQL= "UPDATE table Set.....
         ' Response.Write "DEBUG: " & SQL & "<HR>"
         conn.Execute SQL

'Now perform the INSERT

	Elseif Trim(Request.Form("ADD")) = "Save!" Then
		' add a new a transaction from current updates

        SQL = "SELECT Max(childTransID) FROM ThisTableYouareInsertingInto" _
			 & " where ChildTransId=" & currentId 
		Set RS = conn.Execute( SQL )
        newChildTransId = 1 'just in case none already exists
        If Not RS.EOF Then
            If Not Isnull(rs(0)) Then newChildTransId = RS(0)+1 'one exists, increment it
        End If
        RS.Close

'Finally insert the recently updated records into your new table:

          SQL	= "INSERT INTO Table(TransId, rest of the fields) Values(" & TransId & ",rest of the stuff)

     'Response.Write "DEBUG: " & SQL & "<HR>"
    'Response.end
    conn.Execute SQL
  End If
End If ' end of handling postback=yes
' ***********************************************************************
' ***********************************************************************
' End Of Handling Postbacks
' ***********************************************************************
' ***********************************************************************

This is the main query to find existing relevant Transaction records in the members table:
sql = "SELECT * FROM members WHERE transID=" & transID
	  rs=conn.Execute(sql)

If RS.EOF Then
%>
	<span  class=middletitle style="font-size: small; font-weight: bold;">
	&nbsp;View - Transaction Results: NO Transactions ON RECORD
	</span>
<%
Else
%>

<!--data info -->
<table ID="TOPTABLE" width="100%" style="font-size: x-small;">
<tr>
</TR>
<TR class="INVERSEBOLD">
        <TD>District Code</TD>
        <TD>Fleet Number</TD>
</TR>
<%
	Do Until RS.EOF
                        transID   = RS("transID")
		districtCode =   RS("district")
		fleetNo =   RS("fleet")

%>
<form method=post>
  <input type=hidden name="POSTBACK" value="YES">
 <input type="hidden" name="transactId" value="<%=transID%>">

<TR>
    <TD><INPUT size=11 name="districtCode " value="<%=districtCode %>"></TD>
    <TD><INPUT size=7 name=fleetNo value="<%=fleetNo %>"></TD>
</TR>
<TR>
	<TD colspan=9 valign=top ><hr size=1></hr></TD>
</TR>
<%
   RS.MoveNext
LOOP
%>
</table>
</div>
<%
end if
RS.Close
%>
</form>
</body>
</html>

Open in new window


First, notice how your loop is slightly rearranged.

Then organized in my view in logical sequence.

Not sure if this can work with your current layout and set up but you can see where I am going with this.

You will need to add Name="Save" to your submit button and name="Edit" to your update button.

As stated before, I could be going about it all wrong just based on what I have read here.
0
 

Accepted Solution

by:
slegy earned 0 total points
ID: 39879399
I again apologize if the wording of my question was misleading. I was attempting to keep it simple, as I explained initially. In a VBScript IPN handler, I needed to retrieve a recordset based on a transaction ID and create a corresponding set of records in a table in a different database. I couldn't quite get a grip on looping through the recordset and outputting to a different database.

I have solved the problem as follows:
*  Retrieve recordset based on transaction code
*  "GetRows" into an array
*  Close recordset
*  Open other database
*  Loop through array and create records for other table: For iRow = 0 to UBound(arrMember, 2)

This approach accomplished exactly what I needed. When opening the question, I was not aware of GetRows and initially had a problem with defining the array properly. But it is now doing what I need.

Thank you for you time. I appreciate your examples and will save and refer to them in the future.
0
 

Author Closing Comment

by:slegy
ID: 39891394
The solution I described directly solved the problem.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

708 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

12 Experts available now in Live!

Get 1:1 Help Now