Need help writing to MS Access DB

**1newbie** · Apr 20, 2011 06:32

I'm still learning ASP so please bare with me on this.
I'm trying to write a customer satifaction survey so I need to pass the survey results to an MS Database. The problem is that no user data is being written into the db. But blank records are being written. I'm not sure what went wrong here. Can someone provide some assistance with this? I assume it's an easy fix.

This is the HTML page with the form data:

HTML Code:

<html>


   <head>


      <title>New Customer Survey </title>


<script language="javascript">
<!--
function ClearForm() {
  document.surveyForm.q2.value= "";

}

function ClearQ3() {
  document.surveyForm.q3.value= "";

}


function ClearQ4() {
  document.surveyForm.q4.value= "";

}


//-->
</script>

   </head>

     <p align=center> <img src=images/banner.jpg></p>

     <form name=surveyForm meathod=POST action=getsurvey.asp>

         <table border=1 align=center>

            <tr>

               <td>1. Do you remember the Tech that you spoke to regarding your problem?</td>

                   <td>
                        <select>
                           <option value="select">--Select Answer--</option>
                           <option value="Yes">Yes</option>
                           <option value="No">No</option>
                        </select>

                   </td>

             <tr>
                       <td>2. Overall, how do you feel the Tech Support Representative handled your issue?</td>
                       <td><input type=text name=q2 value="Enter a number 1-5" onfocus="ClearForm();">
                  </td>

             <tr>
                       <td>3. Do you feel that your issue was acknowledged within timely manner?</td>
                       <td><input type=text name=q3 value="Enter a number 1-5" onfocus="ClearQ3();"</td>

             <tr>
                         <td>4. Do you feel that your issue was acknowledged within timely manner?</td>
                         <td><input type=text name=q4 value="Enter a number 1-5" onfocus="ClearQ4();"</td>

             </tr>

           </table>

                       <br>

                       <p align=center><input type=Submit value="Submit Survey"></p>
         </form>

Here is the ASP page that processes the form:

Code ASP:

<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Form to database</title>
</head>
<body>
<%
'declare your variables
Dim ques2, ques3, ques4
Dim sConnString, connection, sSQL
 
 
'Receiving values from Form, assign the values entered to variables
ques2 = Request.Form("q2")
ques3 = Request.Form("q3")
ques4 = Request.Form("q4")
 
'declare SQL statement that will query the database
sSQL = "INSERT into survey (Question2, Question3, Question4) values ('" & _
ques2 & "', '" & ques3 & "', '" & ques4 & "')"
 
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("data/survey.mdb")
 
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")
 
'Open the connection to the database
connection.Open(sConnString)
 
'execute the SQL
connection.execute(sSQL)
 
response.write "The form information was inserted successfully."
'Done. Close the connection object
connection.Close
Set connection = Nothing

**itHighway** · Apr 22, 2011 06:27

Hi,

Everything seems to be fine. So debug your script step by step so first check if form values are successfully posted to next page i.e:

Code ASP:

 
ques2 = Request.Form("q2")
ques3 = Request.Form("q3")
ques4 = Request.Form("q4")
 
response.write(ques2 &", "&ques3 &", "&ques4)
response.end

If above works fine then check your database path and see if you are POSTING and CHECKING same database.

**1newbie** · Apr 22, 2011 10:40

I figured out the issue. It problem occurred when using the "POST" and "Request.Form" methods. I changed it to "GET" and "Request.QueryString" and it started populating the DB.

However, I now have a new problem.
I'm using MS Access 2007. (I know this isn't the ideal db)
but what happens now is this:

If I type in a couple lines into the newly added "comments" field, everything is ok. But if I add around 20 - 30 lines or maybe more the application bombs.

don't see where to tell Access to accept so many characters or if this is a limitation of MS Access. Previous versions allowed you to specify it.

**ulthane** · Apr 22, 2011 14:16

Set ur comments field to Memo type

**itHighway** · Apr 23, 2011 08:41

Change data type from 'Text' to 'Memo'

**1newbie** · Apr 25, 2011 05:37

That fixed it. Thanks for the replies!

User Tag List

Thread: Need help writing to MS Access DB

Thread Tools

Display

Need help writing to MS Access DB

Bookmarks

Bookmarks

Posting Permissions