Connecting to a MS Access database with ASP

I am receiving the following error

"Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator to inform of the time the error occurred and of anything you might have done that may have caused the error."

I have an HTML form called "reg.html" that submits to a MS Access database called "registration.mdb" the code for the HTML is as follows.

<form name="registrationform" method="post" action="insert.asp">
            <p><font color="333399" face="Arial, Helvetica, sans-serif">First
              Name</font>:
              <input name="firstname" type="text" size="50">
            </p>
            <p> <font color="333399" face="Arial, Helvetica, sans-serif">Surname</font>:
              <input name="surname" type="text" size="50">
            </p>
            <font color="333399" face="Arial, Helvetica, sans-serif">Maiden Name:
            (If Applicable)</font>
            <input name="maidenname" type="text" size="50">
            <p align="left"><font color="333399" face="Arial, Helvetica, sans-serif">Date
              of Birth</font>: </p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">Day</font>
              <select name="day" size="1">
                <option>1</option>
                <option>2</option>
                <option>3</option>
                <option>4</option>
                <option>5</option>
                <option>6</option>
                <option>7</option>
                <option>8</option>
                <option>9</option>
                <option>10</option>
                <option>11</option>
                <option>12</option>
                <option>13</option>
                <option>14</option>
                <option>15</option>
                <option>16</option>
                <option>17</option>
                <option>18</option>
                <option>19</option>
                <option>20</option>
                <option>21</option>
                <option>22</option>
                <option>23</option>
                <option>24</option>
                <option>25</option>
                <option>26</option>
                <option>27</option>
                <option>28</option>
                <option>29</option>
                <option>30</option>
                <option>31</option>
              </select>
              <font color="333399" face="Arial, Helvetica, sans-serif">Month</font>
              <select name="month" size="1">
                <option>1</option>
                <option>2</option>
                <option>3</option>
                <option>4</option>
                <option>5</option>
                <option>6</option>
                <option>7</option>
                <option>8</option>
                <option>9</option>
                <option>10</option>
                <option>11</option>
                <option>12</option>
              </select>
              <font color="333399" face="Arial, Helvetica, sans-serif">Year</font>
              <select name="year" size="1">
                <option>1916</option>
                <option>1917</option>
                <option>1918</option>
                <option>1919</option>
                <option>1920</option>
                <option>1921</option>
                <option>1922</option>
                <option>1923</option>
                <option>1924</option>
                <option>1925</option>
                <option>1926</option>
                <option>1927</option>
                <option>1928</option>
                <option>1929</option>
                <option>1930</option>
                <option>1931</option>
                <option>1932</option>
                <option>1933</option>
                <option>1934</option>
                <option>1935</option>
                <option>1936</option>
                <option>1937</option>
                <option>1938</option>
                <option>1939</option>
                <option>1940</option>
                <option>1941</option>
                <option>1942</option>
                <option>1943</option>
                <option>1944</option>
                <option>1945</option>
                <option>1946</option>
                <option>1947</option>
                <option>1948</option>
                <option>1949</option>
                <option>1950</option>
                <option>1951</option>
                <option>1952</option>
                <option>1953</option>
                <option>1954</option>
                <option>1955</option>
                <option>1956</option>
                <option>1957</option>
                <option>1958</option>
                <option>1959</option>
                <option>1960</option>
                <option>1961</option>
                <option>1962</option>
                <option>1963</option>
                <option>1964</option>
                <option>1965</option>
                <option>1966</option>
                <option>1967</option>
                <option>1968</option>
                <option>1969</option>
                <option>1970</option>
                <option>1971</option>
                <option>1972</option>
                <option>1973</option>
                <option>1974</option>
                <option>1975</option>
                <option>1976</option>
                <option>1977</option>
                <option>1978</option>
                <option>1979</option>
                <option>1980</option>
                <option>1981</option>
                <option>1982</option>
                <option>1983</option>
                <option>1984</option>
                <option>1985</option>
                <option>1986</option>
                <option>1987</option>
                <option>1988</option>
                <option>1989</option>
                <option>1990</option>
                <option>1991</option>
                <option>1992</option>
                <option>1993</option>
                <option>1994</option>
                <option>1995</option>
                <option>1996</option>
                <option>1997</option>
                <option>1998</option>
                <option>1999</option>
                <option>2000</option>
                <option>2001</option>
                <option>2002</option>
                <option>2003</option>
                <option>2004</option>
                <option>2005</option>
                <option>2006</option>
              </select>
            </p>
            <p> <font color="333399" face="Arial, Helvetica, sans-serif">Street
              Number &amp; Name</font>:
              <input name="streetnumberandname" type="text" size="65">
            </p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">Suburb</font>:
              <input name="suburb" type="text" size="45">
            </p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">City</font>:
              <input name="city" type="text" size="40">
            </p>
            <p align="left"><font color="333399" face="Arial, Helvetica, sans-serif">Postal
              Address: (If different from above)</font></p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">Street
              Number &amp; Name:</font>
              <input name="postalstreetnumberandname" type="text" size="65">
            </p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">Suburb:</font>
              <input name="postalsuburb" type="text" size="45">
            </p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">City:</font>
              <input name="postalcity" type="text" size="40">
            </p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">Email
              Address</font><font color="333399">:</font>
              <input name="emailaddress" type="text" size="50">
            </p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">Home Phone</font><font color="333399">:</font>
              <input name="homephone" type="text" size="30">
            </p>
            <p> <font color="333399" face="Arial, Helvetica, sans-serif">Work
              Phone</font><font color="333399">:</font>
              <input name="workphone" type="text" size="30">
            </p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">Mobile
              Phone</font><font color="333399">:</font>
              <input name="mobilephone" type="text" size="30">
              <font color="333399" face="Arial, Helvetica, sans-serif">Fax</font><font color="333399">:</font>
              <input name="fax" type="text" size="30">
            </p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">Occupation:</font>
              <input name="occupation" type="text" size="65">
            </p>
            <p align="left"><font color="333399" face="Arial, Helvetica, sans-serif">Other
              Skills: [Please note any other occupational skills you may wish
              the board to know about]</font></p>
            <textarea name="otherskills" cols="60" rows="5"></textarea>
            <p align="left"><font color="333399" face="Arial, Helvetica, sans-serif">Hapu
              Affiliation[s]:please tick the appropriate box[es]</font></p>
            <p align="left"><font color="333399" face="Arial, Helvetica, sans-serif">In
              the event that you are not sure as to which Hapu you affiliatete
              to, please tick &quot;UNKNOWN&quot;. Every effort will be taken
              by the board to support you in your search to acquire this information.</font></p>
            <p>
              <input type="checkbox" name="kanihi" value="checkbox">
              <font color="333399" face="Arial, Helvetica, sans-serif">Kanihi-Umutahi
              [me etahi] Hapu</font>
              <input type="checkbox" name="okahu" value="checkbox">
              <font color="333399" face="Arial, Helvetica, sans-serif">Okahu-Inuawai
              Hapu</font></p>
            <p>
              <input type="checkbox" name="ngatimanu" value="checkbox">
              <font color="333399" face="Arial, Helvetica, sans-serif">Ngati Manuhiakai
              Hapu</font>
              <input type="checkbox" name="ngatitu" value="checkbox">
              <font color="333399" face="Arial, Helvetica, sans-serif">Ngati Tu
              Hapu</font></p>
            <p>
              <input type="checkbox" name="ngatihaua" value="checkbox">
              <font color="333399" face="Arial, Helvetica, sans-serif">Ngati Haua
              Hapu</font>
              <input type="checkbox" name="ngatitama" value="checkbox">
              <font color="333399" face="Arial, Helvetica, sans-serif">Ngati Tamaahuroa-Titahi
              Hapu</font></p>
            <p>
              <input type="checkbox" name="unknown" value="checkbox">
              <font color="333399" face="Arial, Helvetica, sans-serif">UNKNOWN</font>
            </p>
            <p><font color="333399" face="Arial, Helvetica, sans-serif">Please
              indicate how you would like future information to be distributed
              to you:</font></p>
            <p>
              <input type="checkbox" name="post" value="checkbox">
              <font color="333399" face="Arial, Helvetica, sans-serif">Post</font>
              <input type="checkbox" name="email" value="checkbox">
              <font color="333399" face="Arial, Helvetica, sans-serif">Email</font>
            </p>
            <p align="center"><font color="333399" face="Arial, Helvetica, sans-serif">The
              information I have given is true and correct:
              <input type="checkbox" name="trueandcorrect" value="checkbox">
              </font></p>
            <p align="center"><font color="333399" face="Arial, Helvetica, sans-serif">To
              start again click this button:</font></p>
            <p align="center">
              <input type="reset" name="Reset" value="Reset">
            </p>
            <p align="center"><font color="333399" face="Arial, Helvetica, sans-serif">If
              all of the information entered is true and correct click on the
              submit button:</font></p>
            <p align="center">
              <input type="submit" name="Submit" value="Submit">
            </p>
            <p align="center">
              <label></label>
              &nbsp;<em> <font color="333399" face="Arial, Helvetica, sans-serif">This
              information will be kept and maintained by the Nga Ruahine Iwi Board
              and will be held in accordance with the Privacy Act requirements.
              It will not be used for any other purpose without your express consent.
              You have the right to access and correct this information at any
              time.</font></em> </p>
            <p align="center">&nbsp;</p>
          </form>

The form uses the "insert.asp" page to insert all of the required data into the database. Code from my insert.asp page is as follows

<%@ Language="VBScript" %>

<!-- #include file="connectionstring.asp" -->

<%
'variables
dim Conn, Connstring, Sql
dim strfirstname, strsurname, strmaidenname, strday, strmonth, stryear, strstreetnumberandname, strsuburb, strcity, strpostalstreetnumberandname, strpostalsuburb, strpostalcity, stremailaddress, strhomephone, strworkphone, strmobilephone, strfax, stroccupation, strotherskills, strkanihi, strokahu, strngatimanu, strngatitu, strngatihaua, strngatitama, strunknown, strpost, stremail, strtrueandcorrect

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

' assign variables to items in the forms collection.
strfirstname = request.form("firstname")
strsurname = request.form("surname")
strmaidenname = request.form("maidenname")
strday = request.form("day")
strmonth = request.form("month")
stryear = request.form("year")
strstreetnumberandname = request.form("streetnumberandname")
strsuburb = request.form("suburb")
strcity = request.form("city")
strpostalstreetnumberandname = request.form("postalstreetnumberandname")
strpostalsuburb = request.form("postalsuburb")
strpostalcity = request.form("postalcity")
stremailaddress = request.form("emailaddress")
strhomephone = request.form("homephone")
strworkphone = request.form("workphone")
strmobilephone = request.form("mobilephone")
strfax = request.form("fax")
stroccupation = request.form("occupation")
strotherskills = request.form("otherskills")
strkanihi = request.form("kanihi")
strokahu = request.form("okahu")
strngatimanu = request.form("ngatimanu")
strngatitu = request.form("ngatitu")
strngatihaua = request.form("ngatihaua")
strngatitama = request.form("ngatitama")
strunknown = request.form("unknown")
strpost = request.form("post")
stremail = request.form("email")
strtrueandcorrect = request.form("trueandcorrect")
 
'database connection
Conn.provider = provider
Conn.ConnectionString = dbasepath
Conn.Open

'Sql statement to insert the data
Sql="INSERT INTO registration form (firstname, surname, maidenname, day, month, year, streetnumberandname, suburb, city, postalstreetnumberandname, postalsuburb, postalcity, emailaddress, homephone, workphone, mobilephone, fax, occupation, otherskills, kanihi, okahu, ngatimanu, ngatitu, ngatihaua, ngatitama, unknown, post, email, trueandcorrect) VALUES ('" _
        & strfirstname & "','" & strsurname & "','" & strmaidenname & "','" & strday & "','" & strmonth & "','" & stryear & "','" & strstreetnumberandname & "','" & strsuburb & "','" & strcity & "','" & strpostalstreetnumberandname & "','" & strpostalsuburb & "','" & strpostalcity & "','" & stremailaddress & "','" & strhomephone & "','" & strworkphone & "','" & strmobilephone & "','" & strfax & "','" & stroccupation & "','" & strotherskills & "','" & strkanihi & "','" & strokahu & "','" & strngatimanu & "','" & strngatitu & "','" & strngatihaua & "','" & strngatitama & "','" & strunknown & "','" & strpost & "','" & stremail & "', '" & strtrueandcorrect & "')"

' execute it
Conn.Execute(Sql)

Response.Write "Data successfully inserted!"
%>

I Have a second ASP page as you saw in my include statement called "connectionstring.asp" which makes the connection to the database. The code for this page is as follows.

<%
' connection string using server.mappath

dim dbasepath
dim provider

dbasepath = Server.MapPath("registration.mdb")
provider = "Microsoft.Jet.OLEDB.4.0"

%>

I have already allowed read/write permissions to the database on my web host. My Field Names in the database have no spaces between them so this can not be a problem. My database matches the form exactly.

Firstly is their an error in my ASP coding.
Secondly is their a problem with my connection or lack of a connection to the database.
Thirdly is their a problem with ny HTML form or/and database.

Or any other error that I have not thought of.





nikora1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GawaiCommented:
change to this

connection.asp

      strDB4Access = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=;"
      strDB4Access = strDB4Access & "DBQ=C:/Inetpub/wwwroot/ar-overseas/db/overseas.mdb"

-----------------
in asp page call :
<!--#include file="connection.asp" -->
Dim strDB4Access

Set objCon = Server.CreateObject ("ADODB.Connection")
objCon.Open strDB4Access

Set objRs = Server.CreateObject ("ADODB.Recordset")
objRs.Open strSQL, objCon,1,3

-------------------------

let me know your result now
0
GawaiCommented:
registration.mdb Is your db so change is accordingly
0
GawaiCommented:
if that fails then
change the name of db fields. some times long name causes the problem.

firstname, surname, maidenname, day, month, year, streetnumberandname, suburb, city, postalstreetnumberandname, postalsuburb, postalcity, emailaddress, homephone, workphone, mobilephone, fax, occupation, otherskills, kanihi, okahu, ngatimanu, ngatitu, ngatihaua, ngatitama, unknown, post, email, trueandcorrect

u have assigned long name for fields. so make it short and try again.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

GawaiCommented:
let me know the result.
0
GawaiCommented:
there is some mismatch in your html code and asp, see below

erro 1

<p><font color="333399" face="Arial, Helvetica, sans-serif">Suburb:</font>
              <input name="postalsuburb" type="text" size="45">
            </p>
           
           
postalsuburb

strsuburb = request.form("suburb")
0
GawaiCommented:
it sould be
strsuburb = request.form("postalsuburb")

is n't it ?

0
Cherukuri30Service EngineerCommented:
If you want to know the exact error where you are getting then goto internet explorer properties

Tools -> Internet Options -> click on Advanced Tab -> Uncheck Show friendly http errors.

And then execute your page now it will display where the error is occured, post the error here then experts can easily help you
0
Leo EikelmanDirector, IT and Business DevelopmentCommented:
Your SQL statement

>>Sql="INSERT INTO registration form (firstname, surname........etc...


As far as I know you can't have a space between registration and form and if your database table is actually called registration form then you need to do the following

>>Sql="INSERT INTO [registration form] (firstname, surname...........etc....


Leo

0
nikora1Author Commented:
I Have tried to disable "Show friendly http errors" via

Tools -> Internet Options -> click on Advanced Tab -> Uncheck Show friendly http errors.

With I.E 7.0 but it still displays the "Internal Server Error 500"

I have even tried Mozilla Firefox 1.06 &  Netscape Browser 8.1 and they do the same thing.

What do you recommend
0
GawaiCommented:
did u read my post or not ? there is error in your coding read above
0
nikora1Author Commented:
Below is the server error log related to this problem

2006-04-26 22:13:25 W3SVC377 VPS10009 203.89.178.32 POST /insert.asp |9|80004005|[Microsoft][ODBC_Driver_Manager]_Data_source_name_not_found_and_no_default_driver_specified 80 - 203.109.235.157 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+InfoPath.1) - - www.ngaruahineiwi.co.nz 500 0 0 1625 676 109
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.