Can I persist a custom recordset to a database?

Results 1 to 5 of 5

Thread: Can I persist a custom recordset to a database?

  1. #1
    Jim d. Guest

    Default Can I persist a custom recordset to a database?

    Hello all,<BR><BR>I&#039;ve read up on custom recordsets, and I guess I&#039;m comfortable with using them, but now I&#039;m interested in persisting them to a DB.<BR><BR>I&#039;m trying to import a series of tab-delimited text files, with the first row of the text file containing the column names. Each file has a different set of column names and a variable number of columns so there&#039;s no getting the around the fact that I have create the DB tables dynamically. However, I&#039;d really like to avoid generating a SQL "CREATE TABLE" statement on the fly (ugh).<BR><BR>Is something like this possible? <BR><BR>Thanks, Jim d.

  2. #2
    Jim d. Guest

    Default More info...

    The first row contains the column names, but all the other values are floating point numbers, so I don&#039;t need to guess the data type each time.

  3. #3
    Join Date
    Dec 1969

    Default Then why not CREATE TABLE?

    It will be a very, very simple format, after all.<BR><BR>You might even be able to use REPLACE on the first line of the text file to generate the list of fields and types!<BR><BR>&#060;%<BR>...<BR>fields = theFile.ReadLine<BR>fields = Replace( fields, ",", " DOUBLE," )<BR>SQL = "CREATE TABLE tablename ( " & fields & " DOUBLE )"<BR>...<BR>%&#062;<BR><BR>See it?<BR><BR>

  4. #4
    jim d. Guest

    Default good point. thanks!

    &nbsp;<BR>Not bad at all. Not sure why I had thought that a recordset way would be inherently simpler. My bad. --Jim d.

  5. #5
    Join Date
    Dec 1969

    Default Another IMPORTANT point!

    Once you have created the table, then the lines to do the INSERTs are *ALSO* dirt simple!<BR><BR>&#060;%<BR>Do Until theFile.atEndOfStream<BR>&nbsp; &nbsp; Conn.Execute( "INSERT INTO table Values(" & theFile.ReadLine & ")" )<BR>Loop<BR>%&#062;<BR><BR>See it? You do NOT need to give the NAMES of the fields in an INSERT INTO if you are guaranteeing that you are giving *ALL* the values and given them in the order of the fields. Given the nature of the data you are positing, this is clearly true!<BR><BR>So you can do the entire operation in what...a dozen lines of ASP? Even including opening and closing the connection?<BR><BR><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