|
| |
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
 |
sending data from an update.asp to 2 different tables. - 2/10/2004 16:13:45
Hi..again :) Here is my latest issue. I have 2 identical tables: STA and HistoricalSTA. When users enter data via a form i have 2 sql stmts sending the data to each table. I allow users to go into the STA table and delete/update the data here as they need, but they will never be able to go into the historicalSTA table to delete. I wanted this kinda permanent. My issue is this: When a user goes into my Updateform.asp, i present them with the data out of the STA table for the record selected (using "ID-autonum" to get the record. They make their changes and then the changed data is sent back to the STA table. my problem is i am not altering the same record in the HistoricalSTA table, so now that table contains incorrect info.
My coding skills are not upto snuff so this is where i need your help. How can i alter the code below to send the data back into BOTH the STA table AND the HistoricalSTA table. Each record in each of the tables have matching "ID" (autonumber) numbers, so i am thinking that i can use this. Sorry for the length, but this is the entire code.
<%
Dim adoCon
Dim rsUpdateEntry
Dim strSQL
Dim lngRecordNo
lngRecordNo = CLng(Request.Form("ID"))
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DSN=attendance2004"
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT STA.* FROM STA WHERE ID=" & lngRecordNo
rsUpdateEntry.CursorType = 2
rsUpdateEntry.LockType = 3
rsUpdateEntry.Open strSQL, adoCon
rsUpdateEntry.Fields("employeeno") = Request.Form("employeeno")
rsUpdateEntry.Fields("stadate") = Request.Form("STAdate")
rsUpdateEntry.Fields("hourstaken") = Request.Form("hourstaken")
rsUpdateEntry.Fields("hourstakendeduct") = Request.Form("hourstakendeduct")
rsUpdateEntry.Fields("code") = Request.Form("code")
rsUpdateEntry.Fields("comments") = Request.Form("comments")
rsUpdateEntry.Update
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
Set adoCon = Nothing
Response.Redirect "reportschoosegroup.asp"
%>
Can i simply add one line like strSQL1 = "SELECT HistoricalSTA.* FROM HistoricalSTA WHERE ID=" & lngRecordNo
or will i need to add a whole bunch of "rs1UpdateEntry" as well and have to repeat every line above for the second table.
thanks
_____________________________
ethos anthropou daimon
still in the baby stages of learning.
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/11/2004 9:06:02
quote:
strSQL1 = "SELECT HistoricalSTA.* FROM HistoricalSTA WHERE ID=" & lngRecordNo
I'm not sure why you're updating your record this way, but I would try doing it this way:
strSQL1 = "UPDATE HistoricalSTA SET employeeno = " & Request.Form("employeeno") & ", stadate = #" & Request.Form("STAdate") & "hourstaken = " & Request.Form("hourstaken") & ", hourstakendeduct = " & Request.Form("hourstakendeduct") & ", code = " & Request.Form("code") & ", comments = " & Request.Form("comments") & "' WHERE ID=" & lngRecordNo
IMO if you can do the UPDATE in 1 line of SQL, that is more efficient. At least that's how I'd do it. I think the way you're trying, you'll have to duplicate all the RS code for the second SELECT SQL.
Any help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/11/2004 13:07:51
Hi, thanks for the info. Just so that i am clear are you saying that if i use the method your showing: strSQL1 = "UPDATE HistoricalSTA SET employeeno = " & Request.Form("employeeno") & ", stadate = #" & Request.Form("STAdate") & ...., then i can delete all the rsUpdateEntry.Fields("employeeno") = Request.Form("employeeno") , etc etc lines i have and do it all in the strSQL line? I guess i would then have to have both a strSQL and strSQL1 line (one to reference and update each table). If i use your method, so i still need lines:
rsUpdateEntry.CursorType = 2
rsUpdateEntry.LockType = 3
rsUpdateEntry.Open strSQL, adoCon
and
rsUpdateEntry.Update
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
Set adoCon = Nothing
You said you are not sure why i am updating my records, the way i am..it is only because i found an example on the web of an 'update.asp' code and i tried it out and it worked for me...so i have just been adapting this code i found to my various asp pages.
thanks for your assistance
still a little green in the coding part
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/11/2004 13:36:39
Personally, I don't open recordsets that way if I can help it. I try to just pass a SQL string and let it do the work. It reduces the DB overhead as well. I'd try something like this:
<%
Dim adoCon, rstemp, myDSN
Dim strSQL, strSQL1, fieldData
Dim lngRecordNo
lngRecordNo = CLng(Request.Form("ID"))
fieldData = "employeeno = " & Request.Form("employeeno") & ", stadate = #" & Request.Form("STAdate") & "#, hourstaken = " & Request.Form("hourstaken") & ", hourstakendeduct = " & Request.Form("hourstakendeduct") & ", code = " & Request.Form("code") & ", comments = '" & Request.Form("comments") & "' WHERE ID=" & lngRecordNo
strSQL = "UPDATE STA SET " & fieldData
strSQL1 = "UPDATE HistoricalSTA SET " & fieldData
myDSN = "DSN=attendance2004"
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.open myDSN
set rstemp=adoCon.execute(strSQL)
set rstemp=adoCon.execute(strSQL1)
adoCon.close
%>
Notice what I'm doing? Instead of passing an UPDATE to each individual element of the record, I'm sending 1 SQL command to do it all at once. (Actually two; one for each table).
Haven't troubleshot this code, but give it a try. In the code I'm assuming stadate is a Date type, comments is text, all other fields are numeric, and that the DB is Access. You'll have to probably change delimiters in the string 'fieldData' if not. Hope it helps...
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/11/2004 15:17:58
hey Roger, just to complicate things (LOL) let me ask you this. Is there a command or code piece similar to strSQL = "UPDATE 825STA SET " & fieldData that will allow me to MOVE a record from one table to another (given all the field names etc are the same). I am taking a stab in the dark but how bout something like:
strSQL = "MOVE NONSTA SET " & fieldData
strSQL1 = "MOVE HistoricalNONSTA SET " & fieldData
Essentially i need to give the user the ability to move a record from the STA and Historical tables to a matching NONSTA and HistoricalNONSTA tables. (the table are virtually identical, but are used for different purposes which is why they exist)
What i would do is present the user with updateform.asp, populate the form with data from my STA table; let them make any changes; then take the changes and instead of updating the STA and HistoricalSTA, it would actually MOVE the data to NONSTA and HistoricalNONSTA
just thinking outloud thanks
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/11/2004 15:29:14
If they're in the same DB, I suspect you'd have to use first an INSERT (into new table) then a DELETE (from old table) statement for each record. I'm not familiar with any MOVE SQL command but I'm not positive.
If there's an easier way, it may be somewhere in here:
http://www.learnasp.com/learn/
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/11/2004 16:09:00
thanks. After further thought i am just going to revamp my DB so that i am only dealing with 2 tables and not 4. All i need to make it work is an additional field in my 2 tables.
thanks for your help!
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/11/2004 17:55:25
Roger, if i may tap your brain 1 more time. i am adapting the Update script you gave me above for another database, hoever this database has several fields that are checkboxes (Datatype=Yes/No, set to Format=True/False). Do i need to worry about any special delimiters or anything for the check boxes to write back properly?
If i use:
fieldData = "firstname = '" & Request.Form("firstname") & "', lastname = '" & Request.Form("lastname") & "', dateofexception = #" & Request.Form("dateofexception") & "#, cancelled = " & Request.Form("cancelled") & ", noshow = " & Request.Form("noshow") & ", other = '" & Request.Form("other") & "', exceptionreason = '" & Request.Form("exceptionreason") & "' WHERE ID=" & lngRecordNo
where cancelled and 'noshow' are both checkboxes, should this work?
Using my previous method of Updateing i had the following lines, and i dont know if i would still require something liek this here...checkboxes can be a funny thing!
if len(Request.Form("cancelled"))=0 then svalue4=false else svalue4=true
if len(Request.Form("noshow"))=0 then svalue5=false else svalue5=true
thanks for your input
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/11/2004 18:19:22
Update to my previous entry: below is my full code i am using to update a diff db:
<%@ Language=VBScript %>
<%
Dim adoCon, rstemp, myDSN
Dim strSQL, strSQL1, fieldData
Dim lngRecordNo
lngRecordNo = CLng(Request.Form("ID"))
fieldData = "firstname = '" & Request.Form("firstname") & "', lastname = '" & Request.Form("lastname") & "', dateofexception = #" & Request.Form("dateofexception") & "#, cancelled = " & Request.Form("cancelled") & ", noshow = " & Request.Form("noshow") & ", late = " & Request.Form("late") & ", sick = " & Request.Form("sick") & ", exceptionreason = '" & Request.Form("exceptionreason") & "', schedule = " & Request.Form("schedule") & " WHERE ID=" & lngRecordNo
strSQL = "UPDATE exceptions SET " & fieldData
strSQL1 = "UPDATE exceptionsHistory SET " & fieldData
myDSN = "DSN=shiftchanges"
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.open myDSN
set rstemp=adoCon.execute(strSQL)
set rstemp=adoCon.execute(strSQL1)
adoCon.close
Response.Redirect "resultsexceptions.asp"
%>
This the same idea of what i was asking above (to update 2 tables), but these table have checkboxes. The 'cancelled', 'noshow', 'sick', 'late' and 'schedule' parts are checkboxes.
When i run this now, i am getting Syntax error in UPDATE statement.
in Line19 which is: set rstemp=adoCon.execute(strSQL) line.
Is the problem with my fielddata= line and the delimitors i am using around my checkboxes?
thanks
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/12/2004 8:41:28
quote:
fieldData = "firstname = '" & Request.Form("firstname") & "', lastname = '" & Request.Form("lastname") & "', dateofexception = #" & Request.Form("dateofexception") & "#, cancelled = " & Request.Form("cancelled") & ", noshow = " & Request.Form("noshow") & ", late = " & Request.Form("late") & ", sick = " & Request.Form("sick") & ", exceptionreason = '" & Request.Form("exceptionreason") & "', schedule = " & Request.Form("schedule") & " WHERE ID=" & lngRecordNo
I would attempt to do it something like this. Assuming all your checkboxes have a value of "ON", I'd put your checkbox names into an array and build part of the SQL recursively. (Can also show you array - one of my favorite tools )
...
fieldData = "firstname = '" & Request.Form("firstname") & "', lastname = '" & Request.Form("lastname") & "', dateofexception = #" & Request.Form("dateofexception") & "#"
myArray = split("cancelled,noshow,late,sick,schedule",",")
FOR i = 0 to uBound(myArray)
fieldData = fieldData & ", " & myArray(i) & " = "
IF Request.Form(myArray(i)) = "ON" THEN
fieldData = fieldData & "true"
ELSE
fieldData - fieldData & "false"
END IF
NEXT
fieldData = fieldData & ", exceptionreason = '" & Request.Form("exceptionreason") & "' WHERE ID=" & lngRecordNo
...
At least that's the direction I'd be going in. IIRC an Access db wants true or false for UPDATE actions; I just can't remember which and/or whether they need text delimiters or not. If it doesnt work this way, try using:
...
fieldData = fieldData & "'true'"
ELSE
fieldData = fieldData & "'false'"
...
If this gives you problems, try putting a response.write(strSQL) right after you finish building the SQL. Seeing what the SQL we're trying to pass will help a lot.]
Hope it helps...
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/12/2004 12:44:09
Roger...thanks :), i have a couple questions though:
Is there any reason why you put the 'exceptionsreason' on a separate fieldData line below and not along with the 'firstname','lastname' etc line? Here is my full code now (with all my checkboxes on the 'split' line-and yes there are lots :) Notice i added 'other' to the first fieldData line ).
<%@ Language=VBScript %>
<%
Dim adoCon, rstemp, myDSN
Dim strSQL, strSQL1, fieldData
Dim lngRecordNo
lngRecordNo = CLng(Request.Form("ID"))
fieldData = "firstname = '" & Request.Form("firstname") & "', lastname = '" & Request.Form("lastname") & "', dateofexception = #" & Request.Form("dateofexception") & "#, other = '" & Request.Form("other") & "', exceptionreason = '" & Request.Form("exceptionreason") & "' & " WHERE ID=" & lngRecordNo
myArray = split("cancelled,noshow,earlydep,late,lateandearly,latehomesick,homesick,sick,sta,schedule,calabrio,hris",",")
FOR i = 0 to uBound(myArray)
fieldData = fieldData & ", " & myArray(i) & " = "
IF Request.Form(myArray(i)) = "ON" THEN
fieldData = fieldData & "true"
ELSE
fieldData - fieldData & "false"
END IF
NEXT
fieldData = fieldData & ", exceptionreason = '" & Request.Form("exceptionreason") & "' WHERE ID=" & lngRecordNo
strSQL = "UPDATE exceptions SET " & fieldData
strSQL1 = "UPDATE exceptionsHistory SET " & fieldData
myDSN = "DSN=shiftchanges"
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.open myDSN
set rstemp=adoCon.execute(strSQL)
set rstemp=adoCon.execute(strSQL1)
adoCon.close
Response.Redirect "resultsexceptions.asp"
%>
I know as it is, the 2 'fieldData' lines are incorrect, but do i need to separate them as you did with 'exceptionreason' in your example? All the values in the first 'fieldData' line are either date or text format and everything in myArray=split is a checkbox. Also where and what do i do with the "& " WHERE ID=" & lngRecordNo " part...in the example you gave me you put it on the second fieldata line and not the first...any reason why?
thanks....
_____________________________
ethos anthropou daimon
still in the baby stages of learning.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/12/2004 13:01:38
quote:
I know as it is, the 2 'fieldData' lines are incorrect, but do i need to separate them as you did with 'exceptionreason' in your example?
I put exceptionreason on bottom just guessing that it was a memo field. Memo fields seem to work better if they're at the end.
quote:
Also where and what do i do with the "& " WHERE ID=" & lngRecordNo " part...in the example you gave me you put it on the second fieldata line and not the first...any reason why?
The WHERE clause MUST come at the end after all your field values. What I'm doing with all those "fieldData = fieldData &..." is building your SQL on the fly. I built the array loop 'cause you had more than 3 checkboxes. You could do individual IF...THEN clauses, but it seems more efficient this way IMO.
Try using the code that I'm posting below. See if that works w/ dummy data before you alter any. (Let's walk before we run... )
I commented out your redirect and added a response.write for ease of troubleshooting.
...........
<%@ Language=VBScript %>
<%
Dim adoCon, rstemp, myDSN
Dim strSQL, strSQL1, fieldData
Dim lngRecordNo
lngRecordNo = CLng(Request.Form("ID"))
fieldData = "firstname = '" & Request.Form("firstname") & "', lastname = '" & Request.Form("lastname") & "', dateofexception = #" & Request.Form("dateofexception") & "#, other = '" & Request.Form("other") & "', exceptionreason = '" & Request.Form("exceptionreason") & "'"
myArray = split("cancelled,noshow,earlydep,late,lateandearly,latehomesick,homesick,sick,sta,schedule,calabrio,hris",",")
FOR i = 0 to uBound(myArray)
fieldData = fieldData & ", " & myArray(i) & " = "
IF Request.Form(myArray(i)) = "ON" THEN
fieldData = fieldData & "true"
ELSE
fieldData - fieldData & "false"
END IF
NEXT
fieldData = fieldData & ", exceptionreason = '" & Request.Form("exceptionreason") & "' WHERE ID=" & lngRecordNo
strSQL = "UPDATE exceptions SET " & fieldData
strSQL1 = "UPDATE exceptionsHistory SET " & fieldData
Response.write(strSQL)
myDSN = "DSN=shiftchanges"
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.open myDSN
set rstemp=adoCon.execute(strSQL)
set rstemp=adoCon.execute(strSQL1)
adoCon.close
'Response.Redirect "resultsexceptions.asp"
%>
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/12/2004 13:22:50
hmm i am getting the following error:
Type mismatch: '[string: "firstname = 'test2',"]' on line 20. Line 20 is fieldData - fieldData & "false"
'test2' is simply the name i have in my "firstname" field as a test value and that field in the db is a text dataype.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/12/2004 14:04:36
Another note - the text fields 'other' and memo field 'exceptions entry' (Comments) may not always have a value in it, and generally only 1 of the checkboxes will ever be selected. Usually the user will just be updating the bottom 3 checkboxes (filling in a blank check box till all 3 are checked off), but the upper checkboxes will only ever have only 1 checked off.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/12/2004 14:10:18
quote:
fieldData = "firstname = '" & Request.Form("firstname") & "', lastname ....
Can you doublecheck if there is an apostrophe (in your code) where I've bolded?
fieldData = "firstname = '" & Request.Form("firstname") & "', lastname ....
It looks like it may be missing and it is needed.
Yeah, we can deal with the empty fields quite easily by using spaces. Personally I'm concerned with the logic and field types at the moment.
Let me know if we have any sucess. Also, you don't have a URL where we can look at this, do you?
EDIT: Look closely, there's a bolded apostrophe there...
< Message edited by rdouglass -- 2/12/2004 14:11:16 >
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/12/2004 14:15:26
yes there is already an apostrophe there
fieldData = "firstname = '" & Request.Form("firstname") & "', lastname = '" & Request.Form("lastname") & "'
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/12/2004 14:17:29
unfortunately this is on an intranet and i have no way of getting you to view it through the internet
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/12/2004 15:19:41
Any chance you can email me your .asp file and let me take a look? AFAIK that code should not be returning any errors if you have the appropriate data types.
Before you do that, try using this block of code instead of what's there:
IF Request.Form(myArray(i)) = "ON" THEN
fieldData = fieldData & "'true'"
ELSE
fieldData - fieldData & "'false'"
END IF
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: sending data from an update.asp to 2 different tables. - 2/12/2004 15:26:16
ok..i tried replacing the code with what you just sent and i am still getting the error. How do you want me to email you the code...thru email link here (i cant do attachments there) or will you just give me ur email addy
thanks
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
