Web Based Database - Problem in inserting Autonumber field

Hi!
I would like to create a form, which can also be updateable by users later on.
In the test form, I just want to capture 'Name' & 'Email ID' and create a Table in MS Access namely 'auto.mdb'.
 
However, I am facing some problem while inserting values in the table. I believe it is because of the AutoNumber field [RecID] which I have in the MS Access DB. The reason I kept RecID as Autonumber, so that I can use the same while updating the records later on by the users.
My table structure is as mentioned below:
RecID [Autonumber - PK]
tdate [Date/Time]
RecName [Text]
email [Text]

On submit, it is returning 'Syntax error in INSERT INTO statement.
/intranet/auto/submit.asp, line 18"
However, when I am removing Autonumber field, the same code is working fine.
TWO FILES (1. auto.asp & 2. submit.asp) - copied
 
1.
*******auto.asp********
<HTML>
<head>
<script>
<!--
function validation(){
 
	var RecName=document.myform.RecName.value;	
	var email=document.myform.email.value;
 
	if(RecName==""){
	alert ("Please mention your name !");
	document.myform.RecName.focus();
	return false;}
 
	if(email == ""){
	alert("You have not mentioned your email ID!");
	document.myform.email.focus();
	return false;}
	if((email.indexOf('@')>0)== false){
		alert("Please mention your email ID");
		document.myform.email.focus();
		return false;}
	if((email.indexOf('.com')>0)== false){
		alert("Please mention your email ID");
		document.myform.email.focus();
		return false;}
 
	document.myform.submit();
	return true;
}
// -->
</script>
</head>
<BODY BGCOLOR=FFFFFF>
<H3>Autonumber Example</H3>
<form name="myform" method="post" action="submit.asp" onsubmit="return validation()">
<!--BR><B>RecID: </B><INPUT SIZE=5 MAXLENGTH=5 NAME=RecID tabindex="1"-->
<BR><B>Name: </B><INPUT SIZE=30 MAXLENGTH=50 NAME=RecName tabindex="2">
<BR><B>Email: </B><INPUT SIZE=30 MAXLENGTH=50 NAME=email tabindex="3">
<BR><BR>
<input type="image" border="0" name="imageField" src="Submit.gif" width="100" height="18">
</FORM>
</BODY>
</HTML>
 
****END****
2. 
*******submit.asp********
<%
dim tdate, Recname, email
 
tdate=date()
RecName=trim(Replace(request.form("RecName"), "'","''"))
email=trim(Replace(request.form("email"), "'","''"))
 
	ISDataPath = Server.MapPath("auto.mdb")
	Set conn = Server.CreateObject("adodb.connection")
	conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ISDataPath 
 
	set rs=server.createObject("adodb.recordset")
	sql = "select * from auto where email='"&email&"' "
	rs.open sql,conn
 
	if rs.eof then
		SQL1 = "Insert into auto values('" &tdate& "','" &RecName& "','" &email& "')"
		conn.execute (SQL1)
 
end if
 
rs.close
set rs=nothing
conn.close
set conn=nothing
 
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Thank You, for your participation!</title>
</head>
<BODY bgcolor="#006699" MARGINHEIGHT="0" TOPMARGIN="0" RIGHTMARGIN="0" LEFTMARGIN="0" BOTTOMMARGIN="0" MARGINWIDTH="0">
<div align="center">
  <center>
  <table border="0" width="780" height="450">
    <tr>
      <td width="100%">
        <div align="center">
          <center>
          <table border="0" width="760">
            <tr>
              <td width="760" height="119">&nbsp;</td>
            </tr>
            <tr>
              <td width="760" height="300">
                <p align="center"><font face="Arial" size="2" color="#FFFFFF">
				Thanks</font></p>
				<p align="center"><font face="Arial" size="2" color="#FFFFFF">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
				Amitava Chatterjee</font></td>
            </tr>
          </center>
  </center>
            <tr>
              <td width="760" height="30">
                <p align="right"><font class="label" size="2" face="Arial">
				<a orgurl="javascript:history.go(-1)" href="javascript:history.go(-1)">
				<font color="#FFFFFF">Back</font></a></font></td>
            </tr>
          </table>
        </div>
      </td>
    </tr>
  </table>
</div>
 
</body>
 
****END****

Open in new window

Auto.zip
AmitavaChAsked:
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.

yousaftahirCommented:
change this
SQL1 = "Insert into auto values('" &tdate& "','" &RecName& "','" &email& "')"
to

SQL1 = "Insert into auto (tdate,recName,email) values('" &tdate& "','" &RecName& "','" &email& "')"
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
Wayne BarronAuthor, Web DeveloperCommented:
Better yet.
That is a Date/Time field.
Do this

SQL1 = "Insert into auto (tdate,recName,email) values('" &tdate& "',#" &RecName& "#,'" &email& "')"
0
Wayne BarronAuthor, Web DeveloperCommented:
And better then that goof up...:~)

SQL1 = "Insert into auto (tdate,recName,email) values(#" &tdate& "#,'" &RecName& "','" &email& "')"

This should do it.

Carrzkiss
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

AmitavaChAuthor Commented:
Thank you.
This will be very useful for my work, as a fresher in this field.
I spent almost a few hours trying to find out the solution.
0
Wayne BarronAuthor, Web DeveloperCommented:
would you like to explain to me how you got his code to work, when you are inserting time/date field?
To prove my code is accurate
http://www.codefixer.com/codesnippets/insert_date_access_sql.asp

Access database you have to have    #"tdate#"
In SQL Server it is the opposite:  '"&tdate&"'

So. please explain to me how you got    '" &tdate& "'
Which is a SQL Server Insert for a [datetime]
0
Wayne BarronAuthor, Web DeveloperCommented:
I stand corrected.
It works either way.

'" &tdate& "'  06-Jul-09
or
#" &tdate& "#    06-Jul-09
or
" &tdate& "        30-Dec-1899 (Also add thes time to it)

Oppologies to you both.
Have a good one.
Carrzkiss
0
AmitavaChAuthor Commented:
Yesterday, my autonumber problem has been solved.
Now, I faced a different problem this time, while adding few more fields in the form (e.g. I have added one more column i.e. Message [msg] - Text.  The form is working fine, however, facing difficulty, while inserting data for any name (which is already exist).
My main purpose of this form is to have only 'Autonumber' and the form should allow me to insert same name or email ID multiple times, along with their message. Now, this form allows me to insert the name or email ID for the first time. Though, it is not throwing any error message and submit page is working, but without updating the Table.

I think the problem is in the select statement, but, unable to figure it out. Help please.

      set rs=server.createObject("adodb.recordset")
      sql = " select * from autoTable where recName='"&recName&"' "

I am attaching the file once again.

How do I overcome this problem?  
********FILE NO.1 auto.asp **********
 
<HTML>
<head>
<script>
<!--
function validation(){
 
	var RecName=document.myform.RecName.value;	
	var email=document.myform.email.value;
	var msg=document.myform.msg.value;
 
	if(RecName==""){
	alert ("Please mention your name !");
	document.myform.RecName.focus();
	return false;}
 
	if(email == ""){
	alert("You have not mentioned your email ID!");
	document.myform.email.focus();
	return false;}
	if((email.indexOf('@')>0)== false){
		alert("Please mention your email ID");
		document.myform.email.focus();
		return false;}
	if((email.indexOf('.com')>0)== false){
		alert("Please mention your email ID");
		document.myform.email.focus();
		return false;}
 
	if(msg==""){
	alert("Remarks please!");
	document.myform.msg.focus();
	return false;}
 
	document.myform.submit();
	return true;
}
// -->
</script>
</head>
<BODY BGCOLOR=FFFFFF>
<H3>Autonumber Example</H3>
<form name="myform" method="post" action="submit.asp" onsubmit="return validation()">
<!--BR><B>RecID: </B><INPUT SIZE=5 MAXLENGTH=5 NAME=RecID tabindex="1"-->
<BR><B>Name: </B><INPUT SIZE=30 MAXLENGTH=50 NAME=RecName tabindex="2">
<BR><B>Email: </B><INPUT SIZE=30 MAXLENGTH=50 NAME=email tabindex="3"><b><br>
Message:</b><font size="1" face="Verdana"><textarea name="msg" cols="25" rows="6" style="background-color: #EAEBB1; color: #800000" tabindex="3"></textarea></font>
<p>
<BR><BR>
<input type="image" border="0" name="imageField" src="Submit.gif" width="100" height="18">
</p>
</FORM>
</BODY>
</HTML>
 
********FILE NO.2 submit.asp **********
 
<%
dim RecID, tdate, RecName, email, msg
 
tdate=date()
RecID=trim(Replace(request.form("RecID"), "'","''"))
RecName=trim(Replace(request.form("RecName"), "'","''"))
email=trim(Replace(request.form("email"), "'","''"))
msg=trim(Replace(request.form("msg"), "'","''"))
 
	ISDataPath = Server.MapPath("auto.mdb")
	Set conn = Server.CreateObject("adodb.connection")
	conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ISDataPath 
 
	set rs=server.createObject("adodb.recordset")
	sql = " select * from autoTable where recName='"&recName&"' "
 
	rs.open sql,conn
 
	if rs.eof then
		SQL1 = "Insert into autoTable (tdate,recName,email,msg) values('" &tdate& "','" &RecName& "','" &email& "','" &msg& "')"
		conn.execute (SQL1)
 
end if
 
rs.close
set rs=nothing
conn.close
set conn=nothing
 
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Thank You, for your participation!</title>
</head>
<BODY bgcolor="#006699" MARGINHEIGHT="0" TOPMARGIN="0" RIGHTMARGIN="0" LEFTMARGIN="0" BOTTOMMARGIN="0" MARGINWIDTH="0">
<div align="center">
  <center>
  <table border="0" width="780" height="450">
    <tr>
      <td width="100%">
        <div align="center">
          <center>
          <table border="0" width="760">
            <tr>
              <td width="760" height="119">&nbsp;</td>
            </tr>
            <tr>
              <td width="760" height="300">
                <p align="center"><font face="Arial" size="2" color="#FFFFFF">
				Thanks</font></p>
				<p align="center"><font face="Arial" size="2" color="#FFFFFF">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
				Amitava Chatterjee</font></td>
            </tr>
          </center>
  </center>
            <tr>
              <td width="760" height="30">
                <p align="right"><font class="label" size="2" face="Arial">
				<a orgurl="javascript:history.go(-1)" href="javascript:history.go(-1)">
				<font color="#FFFFFF">Back</font></a></font></td>
            </tr>
          </table>
        </div>
      </td>
    </tr>
  </table>
</div>
 
</body>

Open in new window

Auto.zip
0
Wayne BarronAuthor, Web DeveloperCommented:
you will need to open another question for this one.
As this post has already been resolved.

Carrzkiss
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
Databases

From novice to tech pro — start learning today.