SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    adding columns to a table, can ASP tell? ( Edit: DTS+ActiveX question now )

    I have a question about SQL and ASP.

    We have a table of products that goes verticle.

    Company_____Apples__Berries__Penuts__Nuts
    1_____________X_______________X_____X
    2_____________________X_______X______
    3_____________X_______________X______

    Now there will be new food being added all the time (new columns). Is there anyway for ASP to know there is a new column, and to display it accordingly. Instead of recoding the asp page everytime there is a new food addded?
    Last edited by slboytoy; Nov 18, 2004 at 16:37.

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    That database design isn't the most sound in the world. I would do something like this:

    Code:
    CompanyProduct
    ______________
    CompanyID
    ProductID
    
    Product
    ______________
    ProductID
    ProductName
    Then INNER JOIN the results to get what you want. That way, you just make a new record in the Product table when you need to add new products rather than altering the entire structure of your database.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is what I think as well. Have CompanyID and ProductID for columns, instead of CompanyID, and then Product1, Prodcut2, Product3... etc.. I'll try and get the database people to change their tables.

    They want me to scan the column headings, to get the new products..
    I might have to write a VB script that will read the TXT file, find out how many columns are in there, and create a table, and then upload it to the new table.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the database people might not be able to change the design because of application code dependency

    however, they should be aware that the design is horrendous

    you could mention this to them, but that's not going to help firm up your relationship with them

    new products in new columns is terrible, just terrible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DTS + Active X question

    I did some DTS design for the first time, and want to see if anyone can tell me I'm doing it wrong, or how i could make it faster, make it better..

    Here is the background, the table off the main frame, has the Company ID, then 500, 503, 403, 600, 543, (etc...) for column names. Those numbers are products. Now if the company has that product, there is an 'X' in the field. So a record would look like...

    Company_500_503_403_600_543_
    111111___X___X_______X______
    222222___X___X___X__________
    555555___________________X__

    Now that is a horrible way to design a table, since product codes will come and go (thus changing column names)...

    So I've created a table like this...
    Company_Product
    111111______500
    111111______503
    111111______600
    222222______500
    222222______503
    222222______403
    555555______543


    Now am I doing this right? I have a DTS package, with an Active X script instead that looks like this..

    Code:
    '**********************************************************************
    '  Visual Basic ActiveX Script
    '************************************************************************
    
    Function Main()
    
    ' These values were copied from the ADOVBS.INC file.
    '---- CursorTypeEnum Values ----
    Const adOpenForwardOnly = 0
    Const adOpenKeyset = 1
    Const adOpenDynamic = 2
    Const adOpenStatic = 3
    
    '---- CommandTypeEnum Values ----
    Const adCmdUnknown = &H0008
    Const adCmdText = &H0001
    Const adCmdTable = &H0002
    Const adCmdStoredProc = &H0004
    
    Dim oFSO		'Text File Source
    Dim oFile		'Text File
    Dim i, y, x
    Dim ColumnName	'Array of Column Names
    Dim DataLines()		'Array of each line of data
    
    ' Instantiate the ADO objects.
    set myConn = CreateObject("ADODB.Connection")
    set myRecordset = CreateObject("ADODB.Recordset")
    
          myConn.Open "Provider=SQLOLEDB.1;Data Source=PCMCSQL;" & _
          "Initial Catalog=DataWarehouse;User Id=dwintranet;Password=molsoncanadian;"
    
    '  instantiate the Scripting Object
        set oFSO = CreateObject("Scripting.FileSystemObject")
    
    '   Open the file
        set oFile = oFSO.OpenTextFile("\\Hydlna01\downloads\SQLDWN\FPMMATXU.TXT")
    
    '  store the first line, which is the ProductCode Names, in a global variable
    '    DTSGlobalVariables("StartDate").value = x.Readline
    	ColumnName = Split(oFile.Readline, "\")
    
    '    MsgBox "The Start Date is: " & DTSGlobalVariables("StartDate").value
    '	MsgBox ColumnName(0)
    
    	i = 0  
    	Do While not oFile.AtEndOfStream
    		ReDim Preserve DataLines(i)
    		DataLines(i) = oFile.Readline                  'Each line of data put into array
            		i = i + 1  
    	Loop
    
    	oFile.Close
    '  Good practice to setobject variables to nothing
    	Set oFSO = Nothing
    	Set x = Nothing
    
    
    '  store the second line, which is the End Date, in a global variable
    '    DTSGlobalVariables("EndDate").value = x.Readline
    '	DataLine = Split(x.Readline, "\") 
    '   MsgBox "The End Date is: " & DTSGlobalVariables("EndDate").value
    '	MsgBox DataLines(2)
    
    
     strSQL = "Delete DataWarehouse.dbo.MattMatrix"
        myRecordset.Open strSQL, myConn, adOpenKeyset 
        
        For x = 0 To UBound(DataLines) Step 1
            Data = Split(Trim(DataLines(x)), "\")
            For i = 3 To UBound(Data) Step 1
                If Data(i) = "X" Then
                    Data(i) = ColumnNames(i)
                End If
            Next 
        
            For y = 3 To UBound(Data) Step 1
            If Data(y) > " " Then
                strSQL = "INSERT INTO DataWarehouse.dbo.MattMatrix (SoldToCustomerNumber, ShipTo, CustomerType, ProductCode)  Values  ('" _
                        & Data(0) & "', '" & Data(1) & "', '" & Data(2) & "', '" & Data(y) & "') "
                 myRecordset.Open strSQL, myConn, adOpenKeyset 
            End If
            Next 
        Next 
    
    	Main = DTSTaskExecResult_Success
    End Function
    So I am going from 800 records, to oer 40000 records. When running the active X, it takes over 5 minutes to do this. Is that alright?


Bookmarks

Posting Permissions

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