Contents of transc.asp:
<html> <head> <script language="VBScript"> <!--- ---> </script> </head> <body> Demonstrate transaction control. <p> Populate the <b>weekly_timecard</b> table: <% dim err_count sub pass1 %> <br>(i.e., drop the table, create the table, then insert several rows) <p> Click <a href="transc.asp?token=a"> here</a> for a version that works <p> Click <a href="transc.asp?token=b"> here</a> for a version where transaction control terminates the inserts <br>Note the data error on the last insert: the string 'rrr' is attempted to insert into the integer mon_hrs field<p> <% end sub sub createdrop set cn = Server.CreateObject("ADODB.COnnection") fdsn="gl001" fuid="gl001" fpwd="11111" cn.open fdsn,fuid,fpwd response.write "<p>**** opened cn ok" drop_string="DROP TABLE weekly_timecard" cn.execute drop_string response.write "<p>**** drop table ok" create_string="CREATE TABLE weekly_timecard (" create_string=create_string+"ssn int NOT NULL," create_string=create_string+"last_name varchar(30)," create_string=create_string+" first_name varchar(15)," create_string=create_string+" middle_name varchar(15)," create_string=create_string+" mon_hrs int," create_string=create_string+" tue_hrs int," create_string=create_string+" wed_hrs int," create_string=create_string+" thu_hrs int," create_string=create_string+" fri_hrs int" create_string=create_string+" PRIMARY KEY (ssn))" response.write "<P>create_string="+create_string cn.execute create_string cn.close response.write "<p>**** created table ok" end sub sub insert_row(cn,istring) on error resume next cn.execute istring,numa response.write "<p>*** insert_string="+cstr(istring) if Err = 0 and numa = 1 then response.write "<br>*** Inserted row OK." else error_count=error_count+1 If cn.Errors.Count > 0 Then for i = 0 to cn.Errors.Count - 1 response.write "<p>" etext=ucase(cn.errors(i)) k=instr(etext,"DUPLICATE") if k > 0 then response.write "<p>DUPLICATE ssn IN THE DATABASE!!<br>" response.write "SSN="+cstr(request.form("ssn")) + " already entered." exit for else response.write "<br><b>"+cn.errors(i)+"</b>" end if next end if end if end sub sub pass2 (token) on error resume next call createdrop set cn = Server.CreateObject("ADODB.Connection") fdsn="gl001" fuid="gl001" fpwd="11111" cn.open fdsn,fuid,fpwd response.write "<p>**** cn opened ok" cn. begintrans response.write "<p>**** cn begintrans ok" Insert_string_begin="INSERT INTO weekly_timecard (ssn,last_name,first_name,middle_name,mon_hrs,tue_hrs,wed_hrs,thu_hrs,fri_hrs) VALUES (" Insert_string=Insert_string_begin+"110234567,'jones','fred','andrew',11,22,13,14,15)" call insert_row(cn,Insert_string) Insert_string=Insert_string_begin+"229846744,'johnson','mary','alice',1,2,3,4,5)" call insert_row(cn,Insert_string) Insert_string=Insert_string_begin+"332451234,'james','thomas','percy',15,25,15,15,15)" call insert_row(cn,Insert_string) Insert_string=Insert_string_begin+"447125432,'smith','henry','harold',13,13,3,13,13)" call insert_row(cn,Insert_string) Insert_string=Insert_string_begin+"469127464,'wilson','toby','andrew',11,12,13,14,15)" call insert_row(cn,Insert_string) Insert_string=Insert_string_begin+"654161616,'clark','lewis','and',11,8,8,8,8)" call insert_row(cn,Insert_string) Insert_string=Insert_string_begin+"440009090,'marx','groucho','a',11,2,3,4,5)" call insert_row(cn,Insert_string) Insert_string=Insert_string_begin+"448762323,'brown','ted','t',14,2,5,8,5)" call insert_row(cn,Insert_string) Insert_string=Insert_string_begin+"111441919,'miller','john','b',11,22,33,44,55)" call insert_row(cn,Insert_string) Insert_string=Insert_string_begin+"112767878,'blackwell','minnie','ann',11,22,33,44,55)" call insert_row(cn,Insert_string) if token ="a" then Insert_string=Insert_string_begin+"111998888,'this','row','OK',11,22,33,44,55)" call insert_row(cn,Insert_string) else Insert_string=Insert_string_begin+"112767878,'this','row','FAILS -- char data in mon_hrs','rrr',22,33,44,55)" call insert_row(cn,Insert_string) end if if error_count=0 then response.write "<p><b>*** No Errors, transactions committed" cn.commitTrans else response.write "<p><b>*** Errors detected, transactions rolled back" cn.rollbacktrans end if cn.close set cn=nothing set rs=server.createobject("ADODB.Recordset") ls="Select * from weekly_timecard Order by ssn ASC" rs.open ls,"DSN=gl001;UID=gl001;PWD=11111" response.write "<p>*** Opened weekly_timecard OK" response.write "<p>Here is the data just inserted" k=rs.fields.count response.write "<br>Column count="+Cstr(k)+"<p>" response.write "<TABLE BORDER='1'><tr>" for i = 0 to rs.fields.count - 1 response.write "<TD><center><b>" response.write cstr(rs(i).Name) response.write "</b></center></td>" next response.write "</tr>" row_count=0 while not rs.EOF response.write "<tr>" for i = 0 to rs.fields.count - 1 response.write "<td align='right'>" response.write cstr(rs(i)) response.write "</td>" next response.write "</tr>" row_count=row_count+1 rs.MoveNext wend response.write "</table><p>Row count="+cstr(row_count) rs.close set rs=nothing end sub ' '*** top of main ' error_count=0 select case request.querystring("token") case "" call pass1 case "a" call pass2(request.querystring("token")) case "b" call pass2(request.querystring("token")) end select %> </form> </body> </html>
***** end of file *** printed: 185 lines on: 11/23/2009 6:03:48 AM
