Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unhappy Unanswered: Violation of PRIMARY KEY

    I am having a a problem with a very simple quiz form. For some reason after inserting 2 answers the following error is received:

    Microsoft VBScript runtime error '800a000d'
    Type mismatch: 'LBound'

    After receiving this error, if you try to go back and enter answers you get the following:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_responsec'. Cannot insert duplicate key in object 'responsec'.

    It seems that the response database table can not hold more than 2 records at a time. The DB is MS SQl and the table data is the following:

    username varchar(50) not null,
    answerID int not null,
    questionID int not null,
    primary key (answerID,questionID)

    The insert record code is as follow:
    <select name="dropAnswerc" id="dropAnswerc">

    <%While (NOT rsAnswerc.EOF)
    %>
    <option value="<%=(rsAnswerc.Fields.Item("answerID").Value )%>"><%=(rsAnswerc.Fields.Item("answerText").Value )%></option>

    <%rsAnswerc.MoveNext()
    Wend
    If (rsAnswerc.CursorType > 0) Then
    rsAnswerc.MoveFirst
    Else
    rsAnswerc.Requery
    End If
    %>

    Anybody have any suggestions? This is driving me crazy!

  2. #2
    Join Date
    Dec 2003
    Posts
    17
    What is the actual data you are trying to enter?

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    what it sounds like to me is that your second record is going in fine but your ASP code has a fault in it somewhere around your LBound statement, or rather regarding the variable you are performing LBound against.

    I assume you have a primary key on your answer table that says a user can only answer a question once.

    So wheny ou "go back" and try to enter the answers again then the primary key violation is correct....

    if you can supply more of the code for the ASP page perhaps I can help more.

  4. #4
    Join Date
    Dec 2003
    Posts
    4

    entering answers thru a dropdown

    There are 5 questions to the quiz but after entering 2 questions you get the runtime error. It is really frustrating. Here is the insert code:

    Dim MM_tableValues
    Dim MM_dbValues

    If (CStr(Request("MM_insert")) <> "") Then

    ' create the sql insert statement
    MM_tableValues = ""
    MM_dbValues = ""
    For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
    MM_formVal = MM_emptyVal
    Else
    If (MM_altVal <> "") Then
    MM_formVal = MM_altVal
    ElseIf (MM_delim = "'") Then ' escape quotes
    MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
    Else
    MM_formVal = MM_delim + MM_formVal + MM_delim
    End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
    MM_tableValues = MM_tableValues & ","
    MM_dbValues = MM_dbValues & ","
    End If
    MM_tableValues = MM_tableValues & MM_columns(MM_i)
    MM_dbValues = MM_dbValues & MM_formVal
    Next
    MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

    If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
    Response.Redirect(MM_editRedirectUrl)
    End If
    End If
    End If


    The error message is mismatch runtime error is pointing specifically to the line For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2

    I hope you have more insight!

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Where are you defining what the value of MM_fields??

  6. #6
    Join Date
    Dec 2003
    Posts
    4

    Unhappy MM fields is for the dynamic drop box

    Originally posted by rokslide
    Where are you defining what the value of MM_fields??
    variables are set here:

    If (CStr(Request("MM_insert")) = "form1") Then

    MM_editConnection = MM_connPetition_STRING
    MM_editTable = "admintia.responsec"
    MM_editRedirectUrl = ""
    MM_fieldsStr = "dropAnswerc|value|questionID|value|email|valu e"
    MM_columnsStr = "answerID|none,none,NULL|questionID|none,none,NULL |email|',none,''"

    ' create the MM_fields and MM_columns arrays
    MM_fields = Split(MM_fieldsStr, "|")
    MM_columns = Split(MM_columnsStr, "|")

    ' set the form values
    For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
    Next

    ' append the query string to the redirect URL
    If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
    MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
    MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
    End If

    End If


    MM fields is for the dynamic drop box.

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie, so these are two different files?? all one file, just seperate sections??

  8. #8
    Join Date
    Dec 2003
    Posts
    4

    Unhappy different sections.

    Originally posted by rokslide
    okie, so these are two different files?? all one file, just seperate sections??
    different sections.

  9. #9
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    looking at what you have sent through so far.... you don't seem to be re-defining MM_formVal the code you have shown will have it return the same values at all times.
    Last edited by rokslide; 12-08-03 at 01:41.

Posting Permissions

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