Create DB table as a subset table; Email DB Result

Results 1 to 2 of 2

Thread: Create DB table as a subset table; Email DB Result

  1. #1
    Russ Secrest Guest

    Default Create DB table as a subset table; Email DB Result

    I&#039m working on a new web application which involves querying joined database tables withing a SQL Anywhere db. The base table is sizable and the query takes time to generate. To resolve this issue, I thought I would create a new db table (possibly in MS Access to keep things simple) that would be updated periodically, containing approximately 400 records. The end user would generally receive results of 20 records. <BR><BR>As I said, this is a new application and web design is also new for me. Getting the initial query is not a problem -- I&#039ve been very successfull in using stored procedures -- and I can display the data in the format the users are use to seeing on printed paper. The problem is generating a routine which will take the data which comes from the initial query and putting it in a new or defined empty table.<BR><BR>Currently, I&#039m forced to use PWS running on &#039 95. I&#039ve attempted to use the ADO AddNew command and get various errors. I know that the data does have quotes and doublequotes, so I was hoping to move the data by some means other that SQL and brute force.<BR><BR>Another problem I&#039m having is emailing the results of a query. SMTP is installed, and using Frontpage I can email a html page or confirmation page. If I attempt to email an ASP page, I cannot send the data by means of confirmation and if I send a copy of the displayed page, the query is re-ran and indicates No Records Found in the e-mail. I&#039ve attempted CDONT -- then found that it was for WinNT (duh!!) and I&#039ve not attempted CDO yet.<BR><BR>Any help would be greatly appreciated.<BR><BR>Thanks<BR>Russ

  2. #2
    Join Date
    Dec 1969

    Default RE: Create DB table as a subset table; Email DB Re

    &#062;I know that the data does have quotes and doublequotes, so I <BR>&#062;was hoping to move the data by some means other that SQL and <BR>&#062;brute force.<BR><BR>We have found that "brute force" is actually the fastest (and most reliable and must usable across multiple DB systems).<BR><BR>We just generate a CREATE TABLE statement (if appropriate, of course) and then a whole bunch of INSERT statements and run the whole thing as a "batch file" against whatever DB we need to move/copy the data to.<BR><BR>The quotes thing is pretty easy using VBS: Just do the Replace correctly. Tougher is if you have embedded control-characters in some text field. But it&#039s all doable.<BR><BR>And if you need to use ASP on the other end to load up the DB (hopefully you can find another way), you just read the text file one line at a time and send the line, unmodified, as a SQL query (via Connection.Execute, for example). It works. I&#039ve even moved data from an MS Access DB to a DB2 DB running on an IBM mainframe (390) this way. And, yes, with ASP, because in batch mode the stupid mainframe wanted punched card images (no more than 72 characters per line with a mark in column 73 to indicate continuation...yuck). <BR><BR>*****************<BR><BR>Regarding emailing results:<BR><BR>Why not simply write the results to a text file instead of to an HTML page and then attach the text file to the email?<BR><BR>It&#039s not hard to convert a page if you *exclusively* use Response.Write to put the info out (even doing Response.Write "&#060;HTML&#062;&#060;BODY&#062;" for example).<BR><BR>You just change to using the FSO to CreateTextFile and then use MyOutputFile.Write in place of Response.Write. A global edit will fix it right up.<BR><BR>Heck, if you code the ASP correctly, one page can do both!<BR><BR>Just do:<BR><BR>&#060;%<BR>Sub PRINT( msg )<BR>&nbsp;&nbsp;&nbsp;&nbsp;If goingToFile Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;theFile.WriteLine msg<BR>&nbsp;&nbsp;&nbsp;&nbsp;Else<BR>&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Response.Writ e msg & vbNewLine<BR>&nbsp;&nbsp;&nbsp;&nbsp;End If<BR>End Sub<BR>%&#062;<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts