469,951 Members | 2,379 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,951 developers. It's quick & easy.

Problem saving to database

4
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
  1. <%@ Language=VBScript LCID=2057%>
  2. <% Option Explicit %>
  3. <!-- #include file="adovbs.inc" -->
  4.  
  5. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "(URL address blocked: See forum rules)">
  6.  
  7. <html>
  8. <head>
  9.     <title></title>
  10. </head>
  11.  
  12.   <h1>Questionnaire</h1>
  13.  
  14. <% 
  15.     ' -- Declare Variables
  16.     Dim Conn, strConn
  17.     Dim strSQL1, strSQL2, strSQL3
  18.     Dim strNTUsername, strComplete, ValidatedForm, ValidationErrorMsg, i, Q
  19.  
  20.     strComplete = "yes"
  21.  
  22.     ' open connection
  23.     Set Conn = Server.CreateObject("ADODB.Connection")
  24.     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=i:\inetpub\assessment.mdb"
  25.     Conn.Open strConn
  26.  
  27.     ' -- Get the users NTUsername
  28.     strNTUsername = Request.ServerVariables("REMOTE_USER")
  29.     strNTUsername = Right(strNTUsername,len(strNTUsername)-4)
  30.  
  31.    '-- validate form results
  32.   ValidatedForm = true
  33.   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>"
  34.  
  35.   For i = 1 to 41
  36.     Session("Q" & i) = Request.Form("fmQ" & i)
  37.     Session("fmQ" & i & "Comment") = Request.Form("fmQ" & i & "Comment")     
  38.     Q = Session("Q" & i)
  39.  
  40.     If Q = "" Then
  41.       ValidatedForm = false
  42.       ValidationErrorMsg = ValidationErrorMsg & i & ",*"
  43.     End If  
  44.   Next
  45.  
  46.   ValidationErrorMsg = Left(ValidationErrorMsg,len(ValidationErrorMsg)-7)
  47.  
  48.   If ValidatedForm = false Then
  49.     Response.Write(ValidationErrorMsg & "</p>")
  50.       Else
  51.  
  52.     ' create and open first Recordset using Connection - execute
  53.     strSQL1 = "INSERT INTO tblQuestionnaireResults (NTUsername, DateOfAssessment) VALUES ('" & strNTUsername & "', Now());"
  54.     Conn.Execute(strSQL1)
  55.  
  56.     ' -- Check if an action is required
  57.     For i = 1 to 41      
  58.       Q=Request.Form("fmQ" & i)
  59.       If Q = 0 AND i <> 39 OR Q = 1 AND i = 39 Then
  60.         strComplete = "action"
  61.         Exit For
  62.           Else
  63.         strComplete = "yes"
  64.       End If    
  65.     Next
  66.  
  67.      ' -- Write answers to database 
  68.     For i = 1 to 41
  69.       Q=Request.Form("fmQ" & i)
  70.       If i = 38 AND Q <> 1 Then
  71.         strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "', Q38Comment = '" & Request.Form("fmQ38Comment") & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"
  72.           ElseIf i = 39 AND Q = 1 Then
  73.         strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "', Q39Comment = '" & Request.Form("fmQ39Comment") & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"
  74.           Else
  75.         strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"
  76.       End If
  77.       Conn.Execute (strSQL2)
  78.     Next
  79.  
  80.       strSQL3 = "UPDATE tblUsers SET Complete = '" & strComplete & "' WHERE NTUsername ='" & strNTUsername & "';"
  81. %>
  82.       <p>Thank you for completing the assessment.</p>
  83.  
  84.  
  85. <% Conn.Execute (strSQL3)
  86.  
  87.    End If
  88.  
  89.    Conn.Close
  90.    Set Conn = Nothing %>
  91.  
  92. </body>
  93. </html>
Dec 15 '06 #1
4 1279
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
  1. <%@ Language=VBScript LCID=2057%>
  2. <% Option Explicit %>
  3. <!-- #include file="adovbs.inc" -->
  4.  
  5. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "(URL address blocked: See forum rules)">
  6.  
  7. <html>
  8. <head>
  9.     <title></title>
  10. </head>
  11.  
  12.   <h1>Questionnaire</h1>
  13.  
  14. <% 
  15.     ' -- Declare Variables
  16.     Dim Conn, strConn
  17.     Dim strSQL1, strSQL2, strSQL3
  18.     Dim strNTUsername, strComplete, ValidatedForm, ValidationErrorMsg, i, Q
  19.  
  20.     strComplete = "yes"
  21.  
  22.     ' open connection
  23.     Set Conn = Server.CreateObject("ADODB.Connection")
  24.     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=i:\inetpub\assessment.mdb"
  25.     Conn.Open strConn
  26.  
  27.     ' -- Get the users NTUsername
  28.     strNTUsername = Request.ServerVariables("REMOTE_USER")
  29.     strNTUsername = Right(strNTUsername,len(strNTUsername)-4)
  30.  
  31.    '-- validate form results
  32.   ValidatedForm = true
  33.   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>"
  34.  
  35.   For i = 1 to 41
  36.     Session("Q" & i) = Request.Form("fmQ" & i)
  37.     Session("fmQ" & i & "Comment") = Request.Form("fmQ" & i & "Comment")     
  38.     Q = Session("Q" & i)
  39.  
  40.     If Q = "" Then
  41.       ValidatedForm = false
  42.       ValidationErrorMsg = ValidationErrorMsg & i & ",*"
  43.     End If  
  44.   Next
  45.  
  46.   ValidationErrorMsg = Left(ValidationErrorMsg,len(ValidationErrorMsg)-7)
  47.  
  48.   If ValidatedForm = false Then
  49.     Response.Write(ValidationErrorMsg & "</p>")
  50.       Else
  51.  
  52.     ' create and open first Recordset using Connection - execute
  53.     strSQL1 = "INSERT INTO tblQuestionnaireResults (NTUsername, DateOfAssessment) VALUES ('" & strNTUsername & "', Now());"
  54.     Conn.Execute(strSQL1)
  55.  
  56.     ' -- Check if an action is required
  57.     For i = 1 to 41      
  58.       Q=Request.Form("fmQ" & i)
  59.       If Q = 0 AND i <> 39 OR Q = 1 AND i = 39 Then
  60.         strComplete = "action"
  61.         Exit For
  62.           Else
  63.         strComplete = "yes"
  64.       End If    
  65.     Next
  66.  
  67.      ' -- Write answers to database 
  68.     For i = 1 to 41
  69.       Q=Request.Form("fmQ" & i)
  70.       If i = 38 AND Q <> 1 Then
  71.         strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "', Q38Comment = '" & Request.Form("fmQ38Comment") & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"
  72.           ElseIf i = 39 AND Q = 1 Then
  73.         strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "', Q39Comment = '" & Request.Form("fmQ39Comment") & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"
  74.           Else
  75.         strSQL2 = "UPDATE tblQuestionnaireResults SET Q" & i & " = '" & Q & "' WHERE NTUsername ='" & strNTUsername & "' AND DateOfAssessment = Now();"
  76.       End If
  77.       Conn.Execute (strSQL2)
  78.     Next
  79.  
  80.       strSQL3 = "UPDATE tblUsers SET Complete = '" & strComplete & "' WHERE NTUsername ='" & strNTUsername & "';"
  81. %>
  82.       <p>Thank you for completing the assessment.</p>
  83.  
  84.  
  85. <% Conn.Execute (strSQL3)
  86.  
  87.    End If
  88.  
  89.    Conn.Close
  90.    Set Conn = Nothing %>
  91.  
  92. </body>
  93. </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
4
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
4
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by Bob Darlington | last post: by
5 posts views Thread by Jeff | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.