insert data into ms access database using asp

Hi i'm an asp newbie and am just trying to insert some data into a database table.

i can successfully retrieve data but it wont let me insert.

my code is below, if you have any questions, ask away.

thanks in advance.
'Creating connection Object
set Conn=server.createobject("ADODB.Connection")
'Creating Recordset Object
set rs = Server.CreateObject("ADODB.Recordset")
'Initialising Provider String
 
connStr="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Studentrecords.mdb")
 
'Opening Connection to Database
Conn.open  connStr
 
strSQL_Insert = "INSERT INTO tblStudent ( FName, SurName, Email, StudentNo, Password, Keystage, EntryTime)" & _
                        " VALUES ('" & strName & "','" & strLastName & "','" & strEmail & "','" & strUserName & "','" & strPassword & "','" & strKS & "','" & strDate & "');"
                              
On error resume next
rs.execute(strSQL_Insert)
 
Conn.Close
Set Conn = Nothing

Open in new window

khuz01Asked:
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.

Leigh PurvisDatabase DeveloperCommented:
You're close to it.  But you don't want
rs.execute
but instead
Conn.execute

There is no execute method of the recordset object - and indeed, no need for a recordset here (unless you're fetching data and filling the variables strName etc with one.
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
khuz01Author Commented:
OK, I tried that but then i get the following error:

-2147467259

i've searched google and it seems to be a permissions issue but how do i resolve it??
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The error you report is a generic error ... you should be able to get more information by iterating the ADO Errors collection. See this article for a description:

http://support.microsoft.com/default.aspx/kb/168336

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

khuz01Author Commented:
i returned err.description and got:

[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

i've looked at this article, and belive i've given write permissions to the mdb and the folder its in, but maybe i've dont something wrong.

Can you please explain how this is done?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You need root access to the machine hosting your database. Locate the folder, then right click and select Sharing and Security, then select the Security tab. You can review and edit permissions from there.

I'd say your query is definitely updateable, since it's a simple INSERT ...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, you may need to set properties in the IIS configuration manager (through Control Panel, or find the "iis.msc" file in your System32\inetsrv folder and doubleclick it). Locate the website in the leftside panel, then right-click and select Properties, then the Home Directory tab. You may need to set read/write in this area as well.
0
khuz01Author Commented:
unfortunately i'm still getting the same error.

i've attached the code for your very kind consideration.

please see if you can run the files and tell me if there's anything wrong with that?

the database is an access one called
Studentrecords.mdb with a table called
tblStudent and all the fields in the image attached.

the code for
signup.asp and
addstudent.asp is below.

please let me know what the problem is and provide a possible solution, it'd be much appreciated!
<!-- simple registration form  -->
 
<%@ Language=VBScript %>
 
<%Response.Expires = -1 %>
<%Response.ExpiresAbsolute = Now() - 1 %>
<%Response.AddHeader "pragma", "no-cache" %>
<%Response.AddHeader "cache-control", "private" %>
<%Response.CacheControl = "no-cache" %>
 
<% 
Response.Expires = -1000
Response.Buffer = True
Response.Clear 
%>
 
<head>
<title>Resigtration Form.</title>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<STYLE TYPE="text/css"> 
 
BODY 
{ 
scrollbar-base-color: #7782C3; 
scrollbar-arrow-color: #ffffff;
scrollbar-DarkShadow-Color: #000000; 
}
</STYLE>
</head>
 
<body bgcolor="#FFFFFF">
 
<div align="center">
  <center>
  <table border="0" width="550" cellspacing="0" cellpadding="0" height="427">
   <form action="addstudent.asp" method="POST" name="form1">
    <tr>
      <td width="544" valign="top" align="center" height="74" colspan="3">
        <div align="center">
          <center>
          <table border="0" width="550" cellspacing="0" cellpadding="0" height="62">
            <tr>
              <td width="100%" valign="middle" align="center" height="33" bgcolor="#E0E0E0"><b><font face="Arial" size="4" color="#FF0000">Student Registration
                Form</font></b></td>
            </tr>
            <tr>
              <td width="100%" valign="middle" align="center" height="29"><font face="Verdana" size="2" color="#FF0000">*
                </font><font face="Verdana" color="#0000FF" size="1">marked fields
                are mandatory.</font></td>
            </tr>
          </table>
          </center>
        </div>
      </td>
    </tr>
    <tr>
      <td width="256" valign="middle" align="left" height="30"><font face="Verdana" size="2" color="#FF0000">*
        </font><font face="Verdana" size="2" color="#000080">First
        Name</font></td>
      <td width="31" valign="middle" align="center" height="30"><font face="Verdana" size="2" color="#000080">-</font></td>
      <td width="257" valign="middle" align="left" height="30"><input type="text" name="txtName" size="20"></td>
    </tr>
    <tr>
      <td width="256" valign="middle" align="left" height="30"><font face="Verdana" size="2" color="#FF0000">*
        </font><font face="Verdana" size="2" color="#000080">Last
        Name</font></td>
      <td width="31" valign="middle" align="center" height="30"><font face="Verdana" size="2" color="#000080">-</font></td>
      <td width="257" valign="middle" align="left" height="30"><input type="text" name="txtLastName" size="20"></td>
    </tr>
    <tr>
      <td width="256" valign="middle" align="left" height="30"><font face="Verdana" size="2" color="#FF0000">*
        </font><font face="Verdana" size="2" color="#000080">E-Mail</font></td>
      <td width="31" valign="middle" align="center" height="30"><font face="Verdana" size="2" color="#000080">-</font></td>
      <td width="257" valign="middle" align="left" height="30"><input type="text" name="txtEmail" size="20"></td>
    </tr>
    <tr>
      <td width="256" valign="middle" align="left" height="30"><font face="Verdana" size="2" color="#FF0000">*
        </font><font face="Verdana" size="2" color="#000080">Key Stage</font></td>
      <td width="31" valign="middle" align="center" height="30">-</td>
      <td width="257" valign="middle" align="left" height="30">
      <select size="1" name="txtKS">
<option></option>
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
</select></font>
      </td>
    </tr>
    <tr>
      <td width="256" valign="middle" align="left" height="30"></td>
      <td width="31" valign="middle" align="center" height="30"><font face="Verdana" size="2" color="#000080"></font></td>
      <td width="257" valign="middle" align="left" height="30"></td>
    </tr>
    <tr>
      <td width="256" valign="middle" align="left" height="30"><font face="Verdana" size="2" color="#FF0000">*
        </font><font face="Verdana" size="2" color="#000080">Student Number</font></td>
      <td width="31" valign="middle" align="center" height="30"><font face="Verdana" size="2" color="#000080">-</font></td>
      <td width="257" valign="middle" align="left" height="30"><input type="text" name="txtUserName" size="20"></td>
    </tr>
    <tr>
      <td width="256" valign="middle" align="left" height="30"><font face="Verdana" size="2" color="#FF0000">*
        </font><font face="Verdana" size="2" color="#000080">Password</font></td>
      <td width="31" valign="middle" align="center" height="30"><font face="Verdana" size="2" color="#000080">-</font></td>
      <td width="257" valign="middle" align="left" height="30"><input type="password" name="txtPassword" size="20" maxlength="10"></td>
    </tr>
    <tr>
      <td width="544" valign="middle" align="right" height="45" colspan="3"><font face="Verdana" color="#0000FF" size="1">Password
        can be Alpha-Numeric but not more than 10 letters.</font></td>
    </tr>
      <td width="544" valign="middle" align="center" height="118" colspan="3"><input type="submit" value="Submit" name="submit">
        &nbsp;&nbsp;&nbsp;&nbsp; <input type="reset" value="Reset"></td>
    </tr>
  </table>
  </center>
</div>
</form>
 
</body>
</html>
 
<!-- form action page addstudent.asp -->
 
<%@ Language=VBScript %>
 
<%
'Connection String
Dim Conn
'Query to be executed
Dim strSQL_Insert
'Recordset
Dim rs
 
if Request.ServerVariables("REQUEST_METHOD") = "POST" then
 
    	strName = Request.Form("txtName")
    	strLastName = Request.Form("txtLastName")
    	strEmail = Request.Form("txtEmail")
    	strUserName = Request.Form("txtUserName")
   	strPassword = Request.Form("txtPassword")
    	strKS = Request.Form("txtKS")
   
 
 
    strDate = Now()
    blnValid = True
    strValid = "<p align=""left""><font face=""Verdana"" size=""4"" color=""#FF00000""><b>" & _
               "Registration Failed..!!</b></font></p>" & vbCrLf & _
               "<ul><font face=""Verdana"" size=""2"" color=""#000080"">" & vbCrLf
 
    if strName = "" then
        blnValid = False
        strValid = strValid & "<table border=""0"" width=""302"" cellspacing=""0"" cellpadding=""0"" height=""22"">"  & vbCrLf &_
		     "<tr>"  & vbCrLf &_
		     "<td width=""300"" height=""22""><p align=""left""><font face=""Verdana"" size=""2"" color=""#000080""># Please Enter Your First Name.</p></td>"  & vbCrLf &_
		     "</tr>" & vbCrLf &_
		     "</table>" 
   end if 
 
    if strLastName = "" then
        blnValid = False
        strValid = strValid & "<table border=""0"" width=""302"" cellspacing=""0"" cellpadding=""0"" height=""22"">"  & vbCrLf &_
		     "<tr>"  & vbCrLf &_
		     "<td width=""300"" height=""22""><p align=""left""><font face=""Verdana"" size=""2"" color=""#000080""># Please Enter Your Last Name.</p></td>"  & vbCrLf &_
		     "</tr>" & vbCrLf &_
		     "</table>" 
   end if 
 
    if strEmail = "" then
        blnValid = False
        strValid = strValid & "<table border=""0"" width=""302"" cellspacing=""0"" cellpadding=""0"" height=""22"">"  & vbCrLf &_
		     "<tr>"  & vbCrLf &_
		     "<td width=""300"" height=""22""><p align=""left""><font face=""Verdana"" size=""2"" color=""#000080""># Please Enter Your E-Mail ID.</p></td>"  & vbCrLf &_
		     "</tr>" & vbCrLf &_
		     "</table>"
   end if 
 
    if strUserName = "" then
        blnValid = False
        strValid = strValid & "<table border=""0"" width=""302"" cellspacing=""0"" cellpadding=""0"" height=""22"">"  & vbCrLf &_
		     "<tr>"  & vbCrLf &_
		     "<td width=""300"" height=""22""><p align=""left""><font face=""Verdana"" size=""2"" color=""#000080""># Please Enter Your Username.</p></td>"  & vbCrLf &_
		     "</tr>" & vbCrLf &_
		     "</table>"
   end if 
 
    if strPassword = "" then
        blnValid = False
        strValid = strValid & "<table border=""0"" width=""302"" cellspacing=""0"" cellpadding=""0"" height=""22"">"  & vbCrLf &_
		     "<tr>"  & vbCrLf &_
		     "<td width=""300"" height=""22""><p align=""left""><font face=""Verdana"" size=""2"" color=""#000080""># Please Enter Your Password.</p></td>"  & vbCrLf &_
		     "</tr>" & vbCrLf &_
		     "</table>"
   end if 
 
    if strKS = "" then
        blnValid = False
        strValid = strValid & "<table border=""0"" width=""302"" cellspacing=""0"" cellpadding=""0"" height=""22"">"  & vbCrLf &_
		     "<tr>"  & vbCrLf &_
		     "<td width=""300"" height=""22""><p align=""left""><font face=""Verdana"" size=""2"" color=""#000080""># Please Enter Your Key Stage.</p></td>"  & vbCrLf &_
		     "</tr>" & vbCrLf &_
		     "</table>"
   end if 
 
 
        	strValid =  strValid & "<p align=""center""><a href=""register.asp"" target=""_top"" title=""Click Here""><font face=""Verdana"" size=""2"" color=""#0000FF"">Click Here To Continue</font></a></p>"
else
	strValid =  strValid & Response.Redirect ("ssignup.asp")
   end if  
    
    if blnValid = True then
    
        'Creating connection Object
    set Conn=server.createobject("ADODB.Connection")
    'Creating Recordset Object
    set rs = Server.CreateObject("ADODB.Recordset")
    'Initialising Provider String
 
connStr="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Studentrecords.mdb")
 
    'Opening Connection to Database
    Conn.open  connStr
 
        strSQL_Insert = "INSERT INTO tblStudent ( FName, SurName, Email, StudentNo, Password, Keystage, EntryTime)" & _
                        " VALUES ('" & strName & "','" & strLastName & "','" & strEmail & "','" & strUserName & "','" & strPassword & "','" & strKS & "','" & strDate & "');"
                        
      
               On error resume next
    Conn.execute(strSQL_Insert)
 
            Conn.Close
        Set Conn = Nothing   
       
    if err.number <> 0 then
          
            strValid = "<p align=center><font face=""Verdana"" size=""2"" color=""#F0000""><b>" &_
                       "Sorry!</font></b><font face=""Verdana"" size=""2"" color=""#000080""> There has been an error with your registration.</font><br>" & _
                       "<br><font face=""Verdana"" size=""2"" color=""#000080"">Please email the message below to the webmaster.</br>" & _
                       "</font></p><p align=center><font face=""Verdana"" size=""2"" color=""#F0000""><b>" & err.number & err.description & _
                       "</b></font></p><p align=""center""><a href=""ssignup.asp"" target=""_self"" title=""Click Here""><font face=""Verdana"" size=""2"" color=""#0000FF"">Click Here To Continue</font></a></p>"
        else
        strValid = "<p align=""left""><b><font face=""Verdana"" size=""2"" color=""#008080"">Dear " & strName & " " & strLastName & ".</b></font>" & _
	"<p align=""center""><font face=""Verdana"" size=""2"" color=""#000080"">Your Name Has Been Registerd Successfully.</font></p><br>" & _
                  "<center><table border=""0"" width=""327"" height=""39"" cellspacing=""0"" cellpadding=""0"">" &_
	"<tr>" &_
	"<td width=""98"" valign=""middle"" align=""center"" height=""21""><font size=""2"" face=""Verdana"" color=""#000080""><b>Username</b></font></td>" &_
	"<td width=""20"" align=""left"" valign=""middle"" height=""21""><font size=""2"" face=""Verdana""><b>-</b></font></td>" &_
	"<td width=""187"" valign=""middle"" align=""left"" height=""21""><font size=""2"" face=""Verdana"" color=""#FF0000"">" & strUserName & "</font></td>" &_
	"</tr>" &_
	"<tr>" &_
	"<td width=""98"" valign=""middle"" align=""center"" height=""18""><font size=""2"" face=""Verdana"" color=""#000080""><b>Password</b></font></td>" &_
	"<td width=""20"" align=""left"" valign=""middle"" height=""18""><font size=""2"" face=""Verdana""><b>-</b></font></td>" &_
	"<td width=""187"" valign=""middle"" align=""left"" height=""18""><font size=""2"" face=""Verdana"" color=""#FF0000"">" & strPassword & "</font></td>" &_
	"</tr></table></center>" &_
	"<p align=""center""><a href=""login.asp"" target=""_top"" title=""Click Here""><font face=""Verdana"" size=""2"" color=""#0000FF"">Click Here To Continue</font></a></p>"
 
end if
 
end if
%>
 
<%Response.Expires = -1 %>
<%Response.ExpiresAbsolute = Now() - 1 %>
<%Response.AddHeader "pragma", "no-cache" %>
<%Response.AddHeader "cache-control", "private" %>
<%Response.CacheControl = "no-cache" %>
 
<% 
Response.Expires = -1000
Response.Buffer = True
Response.Clear 
%>
 
<html>
 
<head>
<title>Registration.</title>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<link rel="stylesheet" type="text/css" href="data/css.css">
<STYLE TYPE="text/css"> 
 
BODY 
{ 
scrollbar-base-color: #7782C3; 
scrollbar-arrow-color: #ffffff;
scrollbar-DarkShadow-Color: #000000; 
}
</STYLE>
</head>
 
<body bgcolor="#FFFFFF">
 
<p align="center">
<%
Response.Write strValid
 %>
</p>
 
 
</body></html>

Open in new window

table.JPG
0
khuz01Author Commented:
OK found a solution here: it was permissions: http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20840436.html

i think LSMConsulting was the closest with his answer.

Unfortunately, i had to give 'Everyone' write rights, once i've tested the application locally will my host (1&1) be able to give me the correct rights aswell?
0
Leigh PurvisDatabase DeveloperCommented:
Indeed Scott (LSMConsulting) was the *only* one who offered any answer at all on the subject of failed permissions.
My original post was on the subject of the incorrect syntax.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure about your host, but  I doubt your host will provide Everyone permissions. Normally the IUSR_machinename permission is the only one that's needed for your ASP to work ... many hosts provide some method to manipulate your user permissions, you might inquire at your host's tech support area.
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
Microsoft Access

From novice to tech pro — start learning today.