home *** CD-ROM | disk | FTP | other *** search
/ Shareware Overload / ShartewareOverload.cdr / windows / sqllink.zip / VAUTH.PRG < prev   
Text File  |  1990-08-09  |  9KB  |  326 lines
  1. SET PROCEDURE TO vauth
  2. SET PROCEDURE TO ssldemo
  3.  
  4. DECLARE fld[8]
  5.  
  6. xBatch=.F.
  7. USE Auth
  8. ZAP
  9. INDEX ON UPPER(au_lname+au_fname) TO Co1
  10.  
  11. DO WHILE .T.
  12.   IF xBatch
  13.     xau_lname=yau_lname
  14.     xau_fname=yau_fname
  15.   ELSE
  16.     CREATE WINDOW "Input Author" FROM 10,25 TO 14,55
  17.     xAu_lname=SPACE(40)
  18.     xAu_fname=SPACE(20)
  19.     @ 1, 0 SAY " Enter Last Name -" GET xAu_lname
  20.     @ 2, 0 SAY "Enter First Name -" GET xAu_fname
  21.     READ
  22.     CLOSE WINDOW "Input Author"
  23.   ENDIF
  24.  
  25.   
  26.   DO say_msg1 WITH "Getting primary key from SQL Server..."
  27.  
  28.   xs="select au_id,au_lname,au_fname from authors where "
  29.   xs=xs+"Substring(au_lname+space(40),1,40)+"
  30.   xs=xs+"Substring(au_fname+space(20),1,20) >= "+q+xAu_lname+xAu_fname+q
  31.   xs=xs+" order by au_lname,au_fname"
  32.  
  33.   IF .NOT. fdbcmd(xs)
  34.     DO say_msg2
  35.     RETURN
  36.   ENDIF
  37.  
  38.   IF fresults("")
  39.     ZAP
  40.  
  41.     DO WHILE .T.
  42.       xline=REQUEST("Row")
  43.       IF xline=CHR(26)
  44.         EXIT
  45.       ENDIF
  46.  
  47.       parse_row(3)
  48.       
  49.       APPEND BLANK
  50.       REPLACE Au_id WITH fld[1]
  51.       REPLACE Au_lname WITH fld[2]
  52.       REPLACE Au_fname WITH fld[3]
  53.     ENDDO
  54.   ELSE
  55.     DO say_msg2
  56.     RETURN
  57.   ENDIF
  58.  
  59.   IF EOF()
  60.     WARNING("","No Author records available.",1)
  61.     DO say_msg2
  62.     RETURN
  63.   ELSE
  64.     yau_lname=au_lname
  65.     yau_fname=au_fname
  66.   ENDIF
  67.  
  68.   GOTO TOP
  69.  
  70.   DO say_msg2
  71.  
  72.   xwin="Edit Authors"
  73.  
  74.   DO WHILE .T.
  75.  
  76.     CLEAR
  77.                  
  78.     BROWSE FIELDS Au_id,Au_lname,Au_fname WIDTH 30 ;
  79.       TITLE "Select Author;ID #;Last Name;First Name" 
  80.  
  81.     CREATE WINDOW xwin FROM 5,0 TO 18,79
  82.  
  83.     DO sh_text
  84.     
  85.     sh_but("Next Rec;Prev Rec;Browse;Next Batch;New Batch;Delete;Add;Exit;",11,0)
  86.  
  87.     DO WHILE .T.
  88.       xAu_lname=Au_lname
  89.       xAu_fname=Au_fname
  90.       xAu_id=Au_id
  91.     
  92.       xs="select au_id,au_lname,au_fname,phone,address,city,state,zip "
  93.       xs=xs+"from authors where au_id="+q+xAu_id+q
  94.       IF .NOT. fdbcmd(xs)
  95.         CLOSE WINDOW xwin
  96.         RETURN
  97.       ENDIF
  98.  
  99.       IF fresults("")
  100.         DO WHILE .T.
  101.           xline=REQUEST("Row")
  102.           IF xline=CHR(26)
  103.             EXIT
  104.           ENDIF
  105.  
  106.           parse_row(8)
  107.         ENDDO
  108.       ELSE
  109.         CLOSE WINDOW xwin
  110.         RETURN
  111.       ENDIF
  112.  
  113.       @ 1,24 CLEAR TO 3,79
  114.       @ 1,24 SAY TRANSFORM(fld[1],"99999999999")
  115.  
  116.       fld[2]=SUBSTR(fld[2]+SPACE(40),1,40)
  117.       @ 2,24 GET fld[2] VALID .NOT. CHR(34) $ fld[2] ERROR e
  118.       
  119.       fld[3]=SUBSTR(fld[3]+SPACE(20),1,20)
  120.       @ 3,24 GET fld[3] VALID .NOT. CHR(34) $ fld[3] ERROR e
  121.       
  122.       fld[4]=SUBSTR(fld[4]+SPACE(12),1,12)
  123.       @ 5,24 GET fld[4] VALID .NOT. CHR(34) $ fld[4] ERROR e
  124.       
  125.       fld[5]=SUBSTR(fld[5]+SPACE(40),1,40)
  126.       @ 6,24 GET fld[5] VALID .NOT. CHR(34) $ fld[5] ERROR e
  127.       
  128.       fld[6]=SUBSTR(fld[6]+SPACE(20),1,20)
  129.       @ 7,24 GET fld[6] VALID .NOT. CHR(34) $ fld[6] ERROR e
  130.       
  131.       fld[7]=SUBSTR(fld[7]+SPACE(2),1,2)
  132.       @ 8,24 GET fld[7] VALID .NOT. CHR(34) $ fld[7] ERROR e
  133.       
  134.       fld[8]=SUBSTR(fld[8]+SPACE(5),1,5)
  135.       @ 9,24 GET fld[8] VALID .NOT. CHR(34) $ fld[8] ERROR e
  136.       
  137.       READ
  138.       CLEAR GETS
  139.  
  140.       IF UPDATED()
  141.         error=.F.
  142.         fdbcmd("update authors ")
  143.         fdbcmd("set Au_lname="+ALLTRIM(STR(fld[1]))+",")
  144.         fdbcmd("set Au_id="+q+fld[1]))+q+",")
  145.         fdbcmd("    Au_lname="+q+fld[2]+q+",")
  146.         fdbcmd("    Au_fname="+q+fld[3]+q+",")
  147.         fdbcmd("    Phone="+q+fld[4]+q+",")
  148.         fdbcmd("    Address="+q+fld[5]+q+",")
  149.         fdbcmd("    City="+q+fld[6]+q+",")
  150.         fdbcmd("    State="+q+fld[7]+q+",")
  151.         fdbcmd("    Zip="+q+fld[8]+q+" ")
  152.         fdbcmd(" where Au_id="+q+xAu_id+q)
  153.         IF .NOT. error
  154.           IF REQUEST("Dbsqlexec")="T"
  155.             REPLACE Au_lname WITH fld[2]
  156.             REPLACE Au_fname WITH fld[3]
  157.             REPLACE Phone WITH fld[4]
  158.             REPLACE Address WITH fld[5]
  159.             REPLACE City WITH fld[6]
  160.             REPLACE State WITH fld[7]
  161.             REPLACE Zip WITH fld[8]
  162.           ELSE
  163.             WARNING("","Cannot update with new data.",1)
  164.             disp_msgs()
  165.             SELECT WINDOW xwin
  166.           ENDIF
  167.         ENDIF
  168.       ENDIF
  169.  
  170.       IF EVENT()=6
  171.         xbutton=BUTTON()
  172.         DO CASE
  173.           CASE xbutton="Next Rec"
  174.             SKIP
  175.             IF EOF()
  176.               DO say_msg1 WITH "Last record in batch..."
  177.               INKEY(2)
  178.               DO say_msg2
  179.               SELECT WINDOW xwin
  180.               SKIP -1
  181.             ENDIF
  182.           CASE xbutton="Prev Rec"
  183.             SKIP -1
  184.             IF BOF()
  185.               DO say_msg1 WITH "First record in batch..."
  186.               INKEY(2)
  187.               DO say_msg2
  188.               SELECT WINDOW xwin
  189.               SKIP
  190.             ENDIF
  191.           CASE xbutton="Browse"
  192.             CLEAR
  193.             EXIT
  194.           CASE xbutton="New Batch"
  195.             xBatch=.F.
  196.             CLEAR
  197.             EXIT
  198.           CASE xbutton="Next Batch"
  199.             xBatch=.T.
  200.             CLEAR
  201.            EXIT
  202.           CASE xbutton="Delete"
  203.             IF CONFIRM("Delete current record?")
  204.               REQUEST("MsgMsg")
  205.               fdbcmd("delete from authors ")
  206.               fdbcmd("where Au_id="+q+xAu_id+q)
  207.               IF fresults("")
  208.                 xmsg=REQUEST("MsgMsg")
  209.                 IF LEN(TRIM(xmsg))>0
  210.                   WARNING("",TRIM(xmsg),1)
  211.                 ELSE
  212.                   xrec=RECNO()
  213.  
  214.                   GOTO TOP
  215.                   xtop=RECNO()
  216.                   GOTO BOTTOM
  217.                   xbot=RECNO()
  218.                      
  219.                   GOTO xrec
  220.                   DELETE
  221.  
  222.                   IF xtop=xbot
  223.                     xbutton="New Batch"
  224.                     xBatch=.F.
  225.                     EXIT
  226.                   ELSE
  227.                     SKIP -1
  228.                     IF BOF()
  229.                       SKIP
  230.                     ENDIF
  231.                   ENDIF
  232.  
  233.                   LOOP
  234.                 ENDIF
  235.               ENDIF
  236.             ENDIF
  237.             WARNING("","Record not deleted",1)
  238.           CASE xbutton="Add"
  239.             IF CONFIRM("Add another Author?")
  240.               xAu_id=SPACE(11)
  241.               xAu_lname=SPACE(40)
  242.               xAu_fname=SPACE(20)
  243.               xPhone=SPACE(12)
  244.               xAddress=SPACE(40)
  245.               xCity=SPACE(20)
  246.               xState=SPACE(2)
  247.               xZip="99999"
  248.  
  249.               CLEAR
  250.               DO sh_text
  251.     
  252.               @ 1,24 CLEAR TO 3,79
  253.               @ 1,24 GET xAu_id PICTURE "999-99-9999"
  254.               @ 2,24 GET xAu_lname VALID .NOT. CHR(34) $ xAu_lname ERROR e
  255.               @ 3,24 GET xAu_fname VALID .NOT. CHR(34) $ xAu_fname ERROR e
  256.               @ 5,24 GET xPhone VALID .NOT. CHR(34) $ xPhone ERROR e
  257.               @ 6,24 GET xAddress VALID .NOT. CHR(34) $ xAddress ERROR e
  258.               @ 7,24 GET xCity VALID .NOT. CHR(34) $ xCity ERROR e
  259.               @ 8,24 GET xState VALID .NOT. CHR(34) $ xState ERROR e
  260.               @ 9,24 GET xZip VALID .NOT. CHR(34) $ xZip ERROR e PICTURE "99999"
  261.  
  262.               READ
  263.  
  264.               error=.F.
  265.               fdbcmd("insert authors values")
  266.               fdbcmd(" ("+q+xAu_id+q+","+q+xAu_lname+q+","+q+xAu_fname+q+",")
  267.               fdbcmd("  "+q+xPhone+q+","+q+xAddress+q+","+q+xCity+q+",")
  268.               fdbcmd("  "+q+xState+q+","+q+xZip+q+",1)")
  269.                 
  270.               IF .NOT. error
  271.                 IF REQUEST("Dbsqlexec")="T"
  272.                   APPEND BLANK
  273.                   REPLACE Au_id WITH xAu_id
  274.                   REPLACE Au_lname WITH xAu_lname
  275.                   REPLACE Au_fname WITH xAu_fname
  276.                   REPLACE Phone WITH xPhone
  277.                   REPLACE Address WITH xAddress
  278.                   REPLACE City WITH xCity
  279.                   REPLACE State WITH xState
  280.                   REPLACE Zip WITH xZip
  281.                 ELSE
  282.                   WARNING("","Cannot add record.",1)
  283.                   disp_msgs()
  284.                   SELECT WINDOW xwin
  285.                 ENDIF
  286.               ENDIF
  287.               sh_but("Next Rec;Prev Rec;Browse;Next Batch;New Batch;Delete;Add;Exit;",11,0)
  288.             ENDIF
  289.           CASE xbutton="Exit"
  290.             CLOSE WINDOW xwin
  291.             RETURN
  292.         ENDCASE
  293.       ENDIF
  294.     ENDDO
  295.     CLOSE WINDOW xwin
  296.     IF xbutton="New Batch" .OR. xbutton="Next Batch" .OR. xbutton="Exit"
  297.       EXIT
  298.     ENDIF
  299.   ENDDO
  300.   IF xbutton="Exit"
  301.     EXIT
  302.   ENDIF
  303. ENDDO
  304. RETURN
  305. *----------------------------------------
  306. PROCEDURE sh_text
  307.  
  308. CLEAR
  309.   
  310. *******************************************************************************
  311. TEXT                                                                     
  312.              Author ID: -----------
  313.       Author Last Name: ----------------------------------------
  314.      Author First Name: --------------------
  315.  
  316.                  Phone: ------------
  317.                Address: ----------------------------------------
  318.                   City: --------------------
  319.                  State: --
  320.                    Zip: -----
  321. ENDTEXT
  322. RETURN
  323.  
  324.  
  325.  
  326.