Problem saving to database - ASP / Active Server Pages

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

1463

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

Problem deleting ldb file

by: Bob Darlington | last post by:

I have a repair and backup database routine which runs when a user closes down my application. It works fine in my development machine, but breaks on a client's at the following line: If...

Microsoft Access / VBA

problem with refreshing a datagrid

by: Jeff | last post by:

IDE: VS 2003 :NET OS: XP Pro My app have a form with a tab-control on it. The tab-control have 2 tabpages. One of the tabpages displays a datagrid, and the other tabpage displays details (order...

C# / C Sharp

Problem reading small icon from database (repeat post PLEASE HELP)

by: Tony Lugg | last post by:

I have an application with a document management form. When users add documents to the form, I call the API function SHGetFileInfo to get the associated large and small icons for the file. These...

Visual Basic .NET

PasswordHash NULL problem

by: BobLewiston | last post by:

When I try to save a new (inserted) record in an SQL database, I get the following System.Runtime.InteropServices.ExternalException message: I have to either find out how to insert an...

Microsoft SQL Server

dataAdapter.Update / SQL PasswordHash NULL problem

by: BobLewiston | last post by:

Some of you may have seen my earlier thread “PasswordHash NULL problem”. I’ve started a new thread because investigation has shown that the problem is actually quite different than I previously...

Microsoft SQL Server

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing