Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Unanswered: table to table copy error

    Hello everyone, I am new to this forum, and I have been having a lot of problems with this one error. I am trying to display the results from a database query that basically goes through a bunch of number fields and checks to see which number shows up the most with a user selected value.

    I want it to look like this:

    "Num1" the input number variable from previous form "-" & "(lottery number with highest frequency)"

    "Num1" the input number variable from previous form "-" & "(lottery number with 2nd highest frequency)"

    ...
    Here is the code i have at the moment:

    Code:
    <%
    
    Set dbaseConn = Server.CreateObject("ADODB.Connection")
    dbaseConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("\WednesdayNumbers2008Trial.mdb") & ";"
    
    ' Now, create the SQL statement
    SQLQuery = "SELECT * FROM winningNumbers WHERE (number1='" & Num1 & "') OR (number2='" & Num1 & "') OR (number3='" & Num1 & "') OR (number4='" & Num1 & "') OR (number5='" & Num1 & "') OR (number6='" & Num1 & "');"
    
    Set RsIndividual = dbaseConn.Execute(SQLQuery)
    
    Do while not RsIndividual.EOF
       SQLQuery2 = "INSERT INTO tempTable  VALUES (" & RsIndividual("number1") & ", " & RsIndividual("number2") & ", " & RsIndividual("number3") & ", " & RsIndividual("number3") & ", " & RsIndividual("number4") & ", " & RsIndividual("number5") & ", " & RsIndividual("number6") & " );"
    set RsIndividual2 = dbaseConn.Execute(SQLQuery2)
    RsIndividual.MoveNext
    Loop
    
    ' Close the Recordset object and destroy it
    RsIndividual.Close
    Set RsIndividual = Nothing
    
    RsIndividual2.Close
    Set RsIndividual2 = Nothing
    
    QryFrequency = "SELECT QryUnionLottery.LotNum, Count(QryUnionLottery.LotNum) AS ItemCount FROM QryUnionLottery GROUP BY QryUnionLottery.LotNum ORDER BY Count(QryUnionLottery.LotNum) DESC;"
    
    ' Execute the SQL statement, and set the recordset object
    ' to the result of this execution. We obtain the resulting
    ' records in Rs object
    Set RsFrequency = dbaseConn.Execute(QryFrequency)
    
    ' Use this RecordSet object to populate your HTML output stream
    ' In this example, we will just write out the last name field
    Do While NOT RsFrequency.EOF
      Response.Write(Num1)
      Response.Write("-")
      Response.Write(RsFrequency.Fields("LotNum").value)
    Response.Write("<br/>")
      ' Move to the next record in the resultset
      RsFrequency.MoveNext
    Loop
    
    ' Close the Recordset object and destroy it
    RsFrequency.Close
    Set RsFrequency = Nothing
    
    ' You might want to release the resources for connection object,
    ' unless you want to use the same connection again in the later code
    dbaseConn.Close
    Set dbaseConn = Nothing
    						%>
    here is the internal database query code:

    Code:
    SELECT number1 AS LotNum
    FROM tempTable
    UNION ALL SELECT number2 as LotNum
    FROM tempTable
    UNION ALL SELECT number3 as LotNum
    FROM tempTable
    UNION ALL SELECT number4 as LotNum
    FROM tempTable
    UNION ALL SELECT number5 as LotNum
    FROM tempTable
    UNION ALL SELECT number6 as LotNum
    FROM tempTable;
    I am getting the following error when this is run:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

    /2NumberResults.asp, line 236

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you considered changing your query to use aggregate functions for you?
    Code:
    SELECT number
         , Count(*) As [frequency]
    FROM   datable
    GROUP
        BY number
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2008
    Posts
    3
    yeh i tried that last night, but no luck. But thank you for the suggestion, the same error came up unfortunately.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, the duplicate key error? You'd only get that if you were inserting or updating a table; not through a simple select.

    Post your table DDL, some sample data and expected output.
    George
    Home | Blog

Posting Permissions

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