Changing data between tables

[Scarecrow]

I am in the process of creating an online 'auction'. I have two tables in my access dbase labled 'Item' and 'Bid'. Within the 'Item' table are field names ItemID, ItemName, ExpirationDate, and ItemStatus. Within the 'Bid' table are field names ItemID, and BidClose.

My script in addbid.asp (where the Bid table is filled) is:

<%
Dim objCmd, rsHighBid, varHighBid

Set objCmd = Server.CreateObject(&quot;ADODB.Command&quot
Set objCmd.ActiveConnection = objConn
strSQL = &quot;SELECT Max(AdjBid) AS MaxBidAmount FROM Bid &quot; & _
&quot;WHERE ItemID = &quot; & Request(&quot;ItemID&quot & &quot;;&quot;
objCmd.CommandType = adCmdText
objCmd.CommandText = strSQL
Set rsHighBid = objCmd.Execute

If IsNull( rsHighBid(&quot;MaxBidAmount&quot ) Then
varHighBid = 0
Else
varHighBid = rsHighBid(&quot;MaxBidAmount&quot
End If
rsHighBid.Close
Set rsHighBid = Nothing

Dim rsBid
Set rsBid = Server.CreateObject(&quot;ADODB.Recordset&quot
rsBid.Open &quot;Bid&quot;, objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable
rsBid.AddNew
rsBid(&quot;ItemID&quot = Request.Form(&quot;ItemID&quot
rsBid(&quot;BidderID&quot = Session(&quot;PersonID&quot
rsBid(&quot;BidLength&quot = CCur(Request.Form(&quot;Sel&quot)
rsBid(&quot;BidAmount&quot = CCur(Request.Form(&quot;Bid&quot)
rsBid(&quot;BidChange&quot = CCur(Request.Form(&quot;Bid&quot) * CCur(Request.Form(&quot;Sel&quot)
rsBid(&quot;AdjBid&quot = CCur(Request.Form(&quot;Bid&quot) * ((3 - ABS(3-CCur(Request.Form(&quot;Sel&quot)))+5.47)
rsBid(&quot;BidClose&quot = CDate(now) + 1
rsBid(&quot;BidWord&quot = FormatNumber(Request.Form(&quot;Sel&quot,0) & &quot; yrs/ &quot; & FormatCurrency(Request.Form(&quot;Bid&quot,0) & &quot; per yr&quot;
rsBid(&quot;Team&quot = Request.Form(&quot;BidTeam&quot
rsBid.Update
Response.Redirect &quot;BrowseListings.asp&quot;
%>

My question is: How do I get it so that the ExpirationDate value in the item table is changed to the BidClose value in the Bid Table. I don't care how its done, either through the script or through the dbase - I just need it done!

And, as always, thanks in advance.

 

[Tarwn]

Why not just execute an SQL Update like your select query above?

sql_update = "UPDATE Item SET ExpirationDate = '"&(CDate(now) + 1)&"' WHERE ItemID = "&Request.Form("ItemID")

Depending on the database your using you may need to change the quotes around the date (MS Access uses # instead of ')

-Tarwn &quot;If you eat a live toad first thing in the morning, nothing worse will happen all day long.&quot; - California saying
&quot;To you or the toad&quot; - Niven's restatement of California saying
&quot;-well most of the time anyway...&quot; - programmers caveat to Niven's restatement of California saying
(The Wiz Biz - Ri

 

[Scarecrow]

That's what I'm looking for (I think). Two questions, though:

1. It is and MS Access dBase. Which ' should be changed to #?
2. Exactly where in the script do I add that line?

I apologize for being so stupid; this was done by someone else prior and, in addition to me never using asp before, my 'bosses' are making changes.

 

[Tarwn]

Ah, ok.
Here is the basis for what you will be inserting:

<%
  Dim objCmd, rsHighBid, varHighBid

  Set objCmd = Server.CreateObject(&quot;ADODB.Command&quot;)
  Set objCmd.ActiveConnection = objConn
  strSQL = &quot;SELECT Max(AdjBid) AS MaxBidAmount FROM Bid &quot; & _
           &quot;WHERE ItemID = &quot; & Request(&quot;ItemID&quot;) & &quot;;&quot;
  objCmd.CommandType = adCmdText
  objCmd.CommandText = strSQL
  Set rsHighBid = objCmd.Execute

  If IsNull( rsHighBid(&quot;MaxBidAmount&quot;) ) Then
    varHighBid = 0
  Else
    varHighBid = rsHighBid(&quot;MaxBidAmount&quot;)
  End If
  rsHighBid.Close
  Set rsHighBid = Nothing
    
  Dim rsBid
  Set rsBid = Server.CreateObject(&quot;ADODB.Recordset&quot;)
  rsBid.Open &quot;Bid&quot;, objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable
  rsBid.AddNew
  rsBid(&quot;ItemID&quot;) = Request.Form(&quot;ItemID&quot;)
  rsBid(&quot;BidderID&quot;) = Session(&quot;PersonID&quot;)
  rsBid(&quot;BidLength&quot;) = CCur(Request.Form(&quot;Sel&quot;))
  rsBid(&quot;BidAmount&quot;) = CCur(Request.Form(&quot;Bid&quot;))
  rsBid(&quot;BidChange&quot;) = CCur(Request.Form(&quot;Bid&quot;)) * CCur(Request.Form(&quot;Sel&quot;))
  rsBid(&quot;AdjBid&quot;) = CCur(Request.Form(&quot;Bid&quot;)) * ((3 - ABS(3-CCur(Request.Form(&quot;Sel&quot;))))+5.47)
  rsBid(&quot;BidClose&quot;) = CDate(now) + 1
  rsBid(&quot;BidWord&quot;) = FormatNumber(Request.Form(&quot;Sel&quot;),0) & &quot; yrs/ &quot; &  FormatCurrency(Request.Form(&quot;Bid&quot;),0) & &quot; per yr&quot; 
  rsBid(&quot;Team&quot;) = Request.Form(&quot;BidTeam&quot;)
  rsBid.Update
  

  '***** Tarwn's Additions *************************
	Dim sql_update
	sql_update = &quot;UPDATE Item SET ExpirationDate = #&quot;&(CDate(now) + 1)&&quot;# WHERE ItemID = &quot;&Request.Form(&quot;ItemID&quot;)	'Notice the #'s around the date for an MS Access query

	objCmd.CommandText = sql_update		'Set the text of the query to our SQL above
	objCmd.Execute							'Execute the sql, we are not asking it to return anything
											' so it doesn't need a variable set = to it

	'The following two lines are just good programming practice
	Set rsBid = Nothing		'This is set to nothing to release the memory that was in use
	Set objCmd = Nothing	'Ditto :)
  '*************************************************

  Response.Redirect &quot;BrowseListings.asp&quot;
%>

One warning, if you check in the database and find that ItemID is a text field (I assumed above that it was numeric) than you will need to change the sql_update string to be:

sql_update = sql_update = &quot;UPDATE Item SET ExpirationDate = #&quot;&(CDate(now) + 1)&&quot;# WHERE ItemID = '&quot;&Request.Form(&quot;ItemID&quot&&quot;'&quot;

Notice with this one that it has single quotes around the string we are comparing with ItemID. The single quotes tell the db to treat it as a string, where the lack of quotes would make it treat it as a numeric entry.

If you can't see the differance right away, write in an extra line under the definition of the above code:
Response.Write sql_update
and it will be printed to the browser page for you in the same exact format as it will be passed to the database. If your curious you can substitue in the second sql_update string I gave and the differance should be more apparent.

Hope that helps,

-Tarwn &quot;If you eat a live toad first thing in the morning, nothing worse will happen all day long.&quot; - California saying
&quot;To you or the toad&quot; - Niven's restatement of California saying
&quot;-well most of the time anyway...&quot; - programmers caveat to Niven's restatement of California saying
(The Wiz Biz - Ri

 

[Scarecrow]

It worked!!!!

[bows down]Thank you.[/Bows]