Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    10

    Angry Unanswered: First Timer having an INSERT Problem

    Ok I am stumped.....

    I am currently writing a custom DTS task through the DTS designer and an ActiveX script. The goal of my Script is to access 3 tables which already exist in the DB, extract and numerically manipulate data from each table, and then insert this data into a new table.

    This is my first time writing an ActiveX script and my first time using the VB scripting language, so most of my script is based on examples I could find.

    To be more exact about my problem here is the description. I created six recordsets from the data I extracted from the 3 tables. When I attempt to insert all the data into my new table some of the data inserts in the wrong order. Three of my columns are time columns, so it is easy to see where the data falls out of its intended sequential order. It is also easy to see that this happens at the exact same time value, every time I run the code.

    I am confused because if I write a for loop and insert less data either through fewer columns or fewer rows, my code seems to work perfectly. I have approximately 4800 rows total to insert, but my code seems to mess up if I try and insert in excess of 4100 rows. Someone suggested that I may be exceeding my allowed buffer, but I don't really know what this means or how to correct it.

    Becasue my data inserts correctly if I only insert, say 2 columns, but all the rows, I think that the recordsets are being creatred correctly, and that the fault lies in my insert loop.

    I have included the script for my insert loop and an example of how the data looks when it is in error.

    I am very grateful for any help you might be able to provide and please let me thank you in advance for you time!

    -TRoche

    do until GPSxRecord.EOF
    GPSx = GPSxRecord.Fields ("GPS_x").value
    tx = GPSxRecord.Fields ("tx").value

    GPSy = GPSyRecord.Fields ("GPS_y").value
    ty = GPSyRecord.Fields ("ty").value

    GPSz = GPSzRecord.Fields ("GPS_z").value
    tz = GPSzRecord.Fields ("tz").value

    'Executing the Insert Command
    DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "

    DestCon.Execute DestCmd

    GPSxRecord.MoveNext
    GPSyRecord.MoveNext
    GPSzRecord.MoveNext

    Loop

    Time(x) GPSx Time(y) GPS(y)
    58.9447 383421.96 58.94497 1213470.912 58.94526 488
    58.99134 383421.959 58.99162 1213470.912 58.99191 489
    59.04329 383421.957 59.04356 1213470.912 59.04385 490
    59.0951 383421.956 59.09538 1213470.912 59.09566 490
    59.14204 383421.955 59.14231 1213470.912 59.1426 490
    221.19337 383447.07 221.19364 1213349.901 221.19393 479
    221.24045 383447.069 221.24072 1213349.895 221.241 479
    221.29253 383447.068 221.2928 1213349.889 221.29308 479
    221.34434 383447.067 221.34461 1213349.883 221.3449 478

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    try something like this

    '************************************************* **********
    ' Visual Basic ActiveX Script
    '************************************************* **********

    Function Main()

    dim ConnSQL1 ' SQL Server connection
    dim RSSQL ' SQL Server recordset
    dim strSQL ' SQL String
    dim rc

    ' SET DATA HANDLING OBJECTS
    set ConnSQL1 = CreateObject("ADODB.Connection")
    set RSSQL = CreateObject("ADODB.Recordset")

    'OPEN DATA CONNECTION
    ConnSQL1.Open = "Provider=SQLOLEDB;Data Source=server;Initial Catalog=database;UID=username;Password=asdf"


    'create a select statement and put into temp table in the
    'format that you are after from the three tables
    'I assume you can do that
    'Once you have all those records, then just insert them
    'from the temp table

    strSQL = "SELECT * into #temp from table " & _
    "insert into newtable(f1, f2, f3, f4)" & _
    "select f1, f2, f3, f4 from #temp"

    RSSQL.Open strSQL, ConnSQL1
    ConnSQL1.execute strSQL
    RSSQL.close
    Main = rc
    End Function

Posting Permissions

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