473,706 Members | 1,988 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem saving to database

4 New Member
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 1480
iam_clint
1,208 Recognized Expert Top Contributor
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 Recognized Expert Top Contributor
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 New Member
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 tblQuestionnair eResults SET Q1 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q2 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q3 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q4 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q5 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q6 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q7 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q8 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q9 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q10 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q11 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q12 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q13 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q14 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q15 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q16 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q17 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q18 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q19 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q20 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q21 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q22 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q23 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q24 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q25 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q26 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q27 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q28 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q29 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q30 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q31 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q32 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q33 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q34 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q35 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q36 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q37 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q38 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q39 = '1', Q39Comment = '' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q40 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = Now();
UPDATE tblQuestionnair eResults SET Q41 = '1' WHERE NTUsername ='ntusername' AND DateOfAssessmen t = 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 New Member
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

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

Similar topics

13
9532
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 Dir(strLDB) <> "" Then Kill (strLDB) where strLDB is the path to the ldb file. The client advises that the ldb doesn't lurk after the program closes. Any ideas?
5
2594
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 date, name, address etc) about the selected row in the datagrid... My problem is when I enter a new record in the details tabpage (saves data to database), and go back to the datagrid. Only the data from the PM-table
3
2755
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 icons are added to two ImageList objects which are bound to a ListView control, and everything looks great. I am saving the icons to a SQL Server table by using Icon.Save to a stream and assigning the byte array to the field, then loading them...
6
3072
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 appropriate value into the PasswordHash column OR make SQL Server Management Studio allow NULL in the PasswordHash column. I discovered I could do the latter in SQL Server Management Studio via: expand table | expand Columns | right-click PasswordHash...
2
3878
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 stated. Also please note that this is unrelated to another of my previous threads, “dataAdapter.Update problem”, which incidentally has been resolved. I’m learning SQL. I’m accessing database SQL2008 AdventureWorks, table Person.Contact, which has a...
0
9287
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9157
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9056
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8999
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6615
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5939
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4454
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3150
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2503
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.