Problem saving to database

I have a form with a list of yes/no questions which records to an Access database. Occasionally a string of 'yes' answers are being recorded as 'no' in the database. For example, the first 3 may record as 'yes' then the remaining questions all record as 'no'.

I have set up tests to check the SQL statements which confirm the data being sent is correct.

The code is posted below.

Any help is appreciated.
Thanks
Graham

Expand|Select|Wrap|Line Numbers

 <%@ Language=VBScript LCID=2057%>

<% Option Explicit %>

<!-- #include file="adovbs.inc" -->
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "(URL address blocked: See forum rules)">
 
<html>

<head>

    <title></title>

</head>
 
  <h1>Questionnaire</h1>
 
<% 

    ' -- Declare Variables

    Dim Conn, strConn

    Dim strSQL1, strSQL2, strSQL3

    Dim strNTUsername, strComplete, ValidatedForm, ValidationErrorMsg, i, Q
 
    strComplete = "yes"
 
    ' open connection

    Set Conn = Server.CreateObject("ADODB.Connection")

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=i:\inetpub\assessment.mdb"

    Conn.Open strConn
 
    ' -- Get the users NTUsername

    strNTUsername = Request.ServerVariables("REMOTE_USER")

    strNTUsername = Right(strNTUsername,len(strNTUsername)-4)
 
   '-- validate form results

  ValidatedForm = true

  ValidationErrorMsg = "<p>You have not completed one or more questions. Please return to the <a href='test_form.asp'>form</a> and answer the following questions:</p><p>"
 
  For i = 1 to 41

    Session("Q" & i) = Request.Form("fmQ" & i)

    Session("fmQ" & i & "Comment") = Request.Form("fmQ" & i & "Comment")     

    Q = Session("Q" & i)
 
    If Q = "" Then

      ValidatedForm = false

      ValidationErrorMsg = ValidationErrorMsg & i & ",*"

    End If  

  Next
 
  ValidationErrorMsg = Left(ValidationErrorMsg,len(ValidationErrorMsg)-7)
 
  If ValidatedForm = false Then

    Response.Write(ValidationErrorMsg & "</p>")

      Else
 
    ' create and open first Recordset using Connection - execute

    strSQL1 = "INSERT INTO tblQuestionnaireResults (NTUsername, DateOfAssessment) VALUES ('" & strNTUsername & "', Now());"

    Conn.Execute(strSQL1)
 
    ' -- Check if an action is required

    For i = 1 to 41      

      Q=Request.Form("fmQ" & i)

      If Q = 0 AND i <> 39 OR Q = 1 AND i = 39 Then

        strComplete = "action"

        Exit For

          Else

        strComplete = "yes"

      End If    

    Next
 
     ' -- Write answers to database 

    For i = 1 to 41

      Q=Request.Form("fmQ" & i)

      If i = 38 AND Q <> 1 Then

        strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "', Q38Comment = '" & Request.Form("fmQ38Comment") & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"

          ElseIf i = 39 AND Q = 1 Then

        strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "', Q39Comment = '" & Request.Form("fmQ39Comment") & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"

          Else

        strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"

      End If

      Conn.Execute (strSQL2)

    Next
 
      strSQL3 = "UPDATE tblUsers SET Complete = '" & strComplete & "' WHERE NTUsername ='" & strNTUsername & "';"

%>

      <p>Thank you for completing the assessment.</p>
 
<% Conn.Execute (strSQL3)
 
   End If
 
   Conn.Close

   Set Conn = Nothing %>
 
</body>

</html>

Dec 15 '06 #1

Subscribe Post Reply

1396

iam_clint

1,208

Expert 1GB

I have a form with a list of yes/no questions which records to an Access database. Occasionally a string of 'yes' answers are being recorded as 'no' in the database. For example, the first 3 may record as 'yes' then the remaining questions all record as 'no'.

I have set up tests to check the SQL statements which confirm the data being sent is correct.

The code is posted below.

Any help is appreciated.
Thanks
Graham

Expand|Select|Wrap|Line Numbers

<%@ Language=VBScript LCID=2057%>

<% Option Explicit %>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "(URL address blocked: See forum rules)">

<html>

<head>

    <title></title>

</head>

  <h1>Questionnaire</h1>

<%

    ' -- Declare Variables

    Dim Conn, strConn

    Dim strSQL1, strSQL2, strSQL3

    Dim strNTUsername, strComplete, ValidatedForm, ValidationErrorMsg, i, Q

    strComplete = "yes"

    ' open connection

    Set Conn = Server.CreateObject("ADODB.Connection")

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=i:\inetpub\assessment.mdb"

    Conn.Open strConn

    ' -- Get the users NTUsername

    strNTUsername = Request.ServerVariables("REMOTE_USER")

    strNTUsername = Right(strNTUsername,len(strNTUsername)-4)

   '-- validate form results

  ValidatedForm = true

  ValidationErrorMsg = "<p>You have not completed one or more questions. Please return to the <a href='test_form.asp'>form</a> and answer the following questions:</p><p>"

  For i = 1 to 41

    Session("Q" & i) = Request.Form("fmQ" & i)

    Session("fmQ" & i & "Comment") = Request.Form("fmQ" & i & "Comment")

    Q = Session("Q" & i)

    If Q = "" Then

      ValidatedForm = false

      ValidationErrorMsg = ValidationErrorMsg & i & ",*"

    End If

  Next

  ValidationErrorMsg = Left(ValidationErrorMsg,len(ValidationErrorMsg)-7)

  If ValidatedForm = false Then

    Response.Write(ValidationErrorMsg & "</p>")

      Else

    ' create and open first Recordset using Connection - execute

    strSQL1 = "INSERT INTO tblQuestionnaireResults (NTUsername, DateOfAssessment) VALUES ('" & strNTUsername & "', Now());"

    Conn.Execute(strSQL1)

    ' -- Check if an action is required

    For i = 1 to 41

      Q=Request.Form("fmQ" & i)

      If Q = 0 AND i <> 39 OR Q = 1 AND i = 39 Then

        strComplete = "action"

        Exit For

          Else

        strComplete = "yes"

      End If

    Next

     ' -- Write answers to database

    For i = 1 to 41

      Q=Request.Form("fmQ" & i)

      If i = 38 AND Q <> 1 Then

        strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "', Q38Comment = '" & Request.Form("fmQ38Comment") & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"

          ElseIf i = 39 AND Q = 1 Then

        strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "', Q39Comment = '" & Request.Form("fmQ39Comment") & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"

          Else

        strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"

      End If

      Conn.Execute (strSQL2)

    Next

      strSQL3 = "UPDATE tblUsers SET Complete = '" & strComplete & "' WHERE NTUsername ='" & strNTUsername & "';"

%>

      <p>Thank you for completing the assessment.</p>

<% Conn.Execute (strSQL3)

   End If

   Conn.Close

   Set Conn = Nothing %>

</body>

</html>

whats the error?

Dec 15 '06 #2

sashi

1,754

Expert 1GB

Hi there,

Please provide clear & simple problem / error statement as it would ease in providing solution in return. Good luck & Take care.

Dec 16 '06 #3

twave

There is no error message. I am recording the SQL output to a text file to check the results. If I answer yes to all questions on the form, this is the output in the text file:

UPDATE tblQuestionnaireResults SET Q1 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q2 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q3 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q4 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q5 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q6 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q7 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q8 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q9 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q10 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q11 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q12 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q13 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q14 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q15 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q16 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q17 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q18 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q19 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q20 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q21 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q22 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q23 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q24 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q25 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q26 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q27 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q28 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q29 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q30 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q31 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q32 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q33 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q34 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q35 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q36 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q37 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q38 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q39 = '1', Q39Comment = '' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q40 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();
UPDATE tblQuestionnaireResults SET Q41 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessment = Now();

In this instance the database recorded Q1 - Q41 as:

Yes Yes Yes Yes Yes Yes Yes
Yes Yes Yes Yes Yes Yes Yes
Yes Yes Yes Yes Yes Yes Yes
Yes Yes No No No No No
No No No No No No No
No No No No No No

It is not a consistant pattern. The same input has recorded differently in the database. It is always a number of positive answers followed by negative answers. i.e:

Yes Yes Yes No No No No
No No No No No No No
No No No No No No No
No No No No No No No
No No No No No No No
No No No No No No

Most of the time, the same output records correctly in the database.

Thanks for your help.

Dec 18 '06 #4

twave

Problem was in SQL statement. 'Now()' changed as it ran through a loop updating fields in the record. If the update completed before the next second started it all got written to the database. Otherwise the WHERE clause didn't match and it didn't write.

I added another SQL statement to get the date from the original record and compare this in the SQL statement instead of 'Now()'.

Dec 20 '06 #5

Similar topics