Solved

Web Based Database - Problem in inserting Autonumber field

Posted on 2009-07-06
8
403 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:AmitavaCh
  • 5
  • 2
8 Comments
 
LVL 2

Accepted Solution

by:
yousaftahir earned 500 total points
ID: 24783182
change this
SQL1 = "Insert into auto values('" &tdate& "','" &RecName& "','" &email& "')"
to

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

Expert Comment

by:Wayne Barron
ID: 24783248
Better yet.
That is a Date/Time field.
Do this

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

Expert Comment

by:Wayne Barron
ID: 24783252
And better then that goof up...:~)

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

This should do it.

Carrzkiss
0
 

Author Closing Comment

by:AmitavaCh
ID: 31600045
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24783397
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
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24789917
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
 

Author Comment

by:AmitavaCh
ID: 24791763
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
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24791851
you will need to open another question for this one.
As this post has already been resolved.

Carrzkiss
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

932 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now