?
Solved

Connecting to a MS Access database with ASP

Posted on 2006-04-26
11
Medium Priority
?
375 Views
Last Modified: 2013-12-03
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.





0
Comment
Question by:nikora1
11 Comments
 
LVL 20

Expert Comment

by:Gawai
ID: 16541820
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
 
LVL 20

Expert Comment

by:Gawai
ID: 16541834
registration.mdb Is your db so change is accordingly
0
 
LVL 20

Accepted Solution

by:
Gawai earned 1500 total points
ID: 16541866
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Expert Comment

by:Gawai
ID: 16541873
let me know the result.
0
 
LVL 20

Expert Comment

by:Gawai
ID: 16541915
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
 
LVL 20

Expert Comment

by:Gawai
ID: 16541919
it sould be
strsuburb = request.form("postalsuburb")

is n't it ?

0
 
LVL 7

Expert Comment

by:Cherukuri30
ID: 16542254
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
 
LVL 8

Expert Comment

by:Leo Eikelman
ID: 16546017
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
 

Author Comment

by:nikora1
ID: 16548618
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
 
LVL 20

Expert Comment

by:Gawai
ID: 16550147
did u read my post or not ? there is error in your coding read above
0
 

Author Comment

by:nikora1
ID: 16550484
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month15 days, 5 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question