Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access ASP Using ASP with Microsoft Access databases. For Access questions not specific to ASP, please use the Access forum. For more ASP forums, please see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 6th, 2004, 11:15 AM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to remnatch
Default Syntax error in INSERT INTO statement

Hey all,

After going through a search, I see that this is a popular problem. I tried a handful of solutions, and haven't been able to come up with a resolution to my problem.

I'm attempting to take an html form and use it to update an access database via asp.

Here's my code in its entirety.

Code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<script language="javascript">
function form_Validator(form)
{

  if (form.app_list.value =="")
  {
  alert("Please enter applications to be tested.");
  form.app_list.focus();
  return (false);
  }

  if (form.project_name.value =="")
  {
  alert ("Please enter your project name.");
  form.project_name.focus();
  return (false);
  }
  if (form.event_descript.value =="")
  {
  alert("Please enter your event description.");
  form.event_descript.focus();
  return (false);
  }
  if (form.event_date.value =="")
  {
  alert("Please enter event date.");
  form.event_date.focus();
  return(false);
  }
  if (form. event_start.value =="")
  {
  alert("Please enter start date.");
  form.event_start.focus();
  return(false);
 }
  if (form.event_end.value =="")
  {
  alert("Please enter end date.");
  form.event_end.focus();
  return(false); 
  }
  if (form.requested.value =="")
  {
  alert("Please enter email.");
  form.requested.focus();
  return(false); 
  }
   if (form.primary_contact.value == "")
  {
    alert("Please enter your name.");
    form.primary_contact.focus();
    return (false);
  }

  if (form.primary_info.value == "")
 {
    alert("Please enter your info.");
    form.primary_info.focus();
    return (false);
 }
 
 if (form.secondary_contact.value == "")
 {
    alert("Please enter secondary contact info.");
    form.second_contact.focus();
    return (false);
  }

 if (form.secondary_info.value == "")
  {
    alert("Please enter secondary contact info.");
    form.secondary_info.focus();
    return (false);
  }
  return (true);
  }
  //-->
  </script>
<title>IPS Test Support Team</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.style1 {font-family: Verdana, Arial, Helvetica, sans-serif}
.style2 {font-size: 9px}
.style3 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 9px; }
-->
</style>
</head>

<body>
 
<% 
Dim environment, app_list, project_name, event_descript, event_date, event_start, event_end, requested, primary_contact, primary_info, secondary_contact, secondary_info, additional_support, comments

Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''")
End Function


environment = ChkString(Request.Form("environ_pull"))
app_list = ChkString(Request.Form("app_list"))
project_name = ChkString(Request.Form("project_name"))
event_descript = ChkString(Request.Form("event_descript"))
event_date = ChkString(Request.Form("event_date"))
event_start = ChkString(Request.Form("event_start"))
event_end = ChkString(Request.Form("event_end"))
requested = ChkString(Request.Form("requested"))
primary_contact = ChkString(Request.Form("primary_contact"))
primary_info = ChkString(Request.Form("primary_info"))
secondary_contact = ChkString(Request.Form("secondary_contact"))
secondary_info = ChkString(Request.Form("secondary_info"))
additional_support = ChkString(Request.Form("additional_support"))
comments = ChkString(Request.Form("comments"))

data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ 
Server.MapPath("supportrequest.mdb")
sql_insert = "insert into Request Support (environment, app_list, project_name, event_descript, event_date, event_start, event_end, requested, primary_contact, primary_info, secondary_contact, secondary_info, additional_support, comments)" 
values = (environ_pull & " ', ' " &_
project_name & "', '" &_
event_descript & "', '" &_
event_date & "', '" &_
event_start & "', '" &_
event_end & "', '" &_
requested & "', '" &_
primary_contact & "', '" &_
primary_info & "', '" &_
secondary_contact & "', '" &_
secondary_info & "', '" &_
additional_support & "', '" &_
comments &")" )'

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

con.Open data_source
con.Execute sql_insert

con.Close
Set con = Nothing
%>


<form name="form" method="post" action="request_support.asp" onSubmit=" return form_Validator(this)">

  <table border="1" cellspacing="1" style="border-collapse: collapse; border-width: 0" bordercolor="#111111" width="100%" id="AutoNumber1">
    <tr>
      <td width="50%" style="border-style: none; border-width: medium">
  <table width="518" border="1" cellpadding="0" cellspacing="0" bordercolor="#999999" id="AutoNumber6" style="border-collapse: collapse; float:left" height="482">
    <tr>
      <td width="518" bgcolor="#F7F7F7" valign="bottom" bordercolor="#800000" height="473">      <blockquote>
        <p>
        <span class="style2"> <br>
      <span class="style3">            
        Environment:   
<select name="environ_pull" size="5"select" style="font-family: Verdana" multiple>
  <option>FAC</option>
  <option>FIN</option>
  <option>PAC</option>
  <option>PIN</option>
  <option>CustQA</option>
</select> Hold ctrl key for multiple selections</span></span></p>
        <p class="style1 style2">              
        Applications :
        <textarea name="app_list" cols="34" rows="3" id="app_list"></textarea>          
  <br>
          <br>
            Project Name: 
        <textarea name="project_name" cols="34" rows="3" id="project_name"></textarea></p>
      <p class="style1 style2">
         Testing Description:
        <textarea name="event_descript" cols="34" rows="3" id="event _descript"></textarea>
      </p>
      <p class="style1 style2">               Testing Date:  
        <input name="event_date" type="text" id="event_date" size="11"> 
        (MM/DD/YYYY)                    </p>
        <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="41%" id="AutoNumber5" align="right">
          <tr>
            <td width="10%">
            <img border="0" src="http://ips.fmr.com/IPSTest/images/image001.gif" align="left"></td>
          </tr>
        </table>
      <p class="style1 style2">        Event Start Time:
        <input name="event_start" type="text" id="event_start" size="11"> <br>
        (HH : MM) 24 hr, EST</p>
      <p class="style1 style2">          Event End Time:
        <input name="event_end" type="text" id="event_end" size="11"> 
        (HH : MM) 24 hr, EST</p>
      </blockquote>
      <p class="style2">
      <p class="style2">
      <p class="style2">
      <p class="style2"></td>
    </tr>
    </table>
  <div align="left" style="width: 518; height: 80; font-size: 9px; font-family: Verdana, Arial, Helvetica, sans-serif">

    <table width="518" height="74" border="1" align="left" cellpadding="0" cellspacing="0" bordercolor="#999999" id="AutoNumber2" style="border-collapse: collapse">
      <tr>
        <td width="518" bgcolor="#F7F7F7" height="0">        <span class="style2"> <br>
        <br>
                          Requested By:
<input name="requested" type="text" id="requested" size="21"> Enter your E - 
        mail.</span></td></tr>
    </table>
    <p><br>
    <br>
 </p>
    <p></div>
  <div align="left" style="width: 518; height: 377; font-size: 9px; font-family: Verdana, Arial, Helvetica, sans-serif">
    <table width="518" height="74" border="1" cellpadding="0" cellspacing="0" bordercolor="#999999" id="AutoNumber3" style="border-collapse: collapse">
      <tr>
        <td width="518" height="74" bgcolor="#F7F7F7">
        <blockquote>
          <p>
          <span class="style2">    </span></p>
          <p>
          <span class="style2">Contact Information 1: 
<input name="primary_contact" type="text" id="first_name" size="31"></span></p>
          <p><span class="style2"> Contact Information 2:
    <input name="primary_info" type="text" id="last_name" size="31"> </span></p>
          <div align="left" class="style2" style="width: 435; height: 79">
     Contact Information 3:

    <input name="secondary_contact" type="text" id="secondary_contact" size="31"><p> Contact Information 4:
    <input name="secondary_info" type"text" id= "secondary_info" size="31" "20">

    <br>
 </div>
  <div align="left">
    <span class="style2">    Do you require additional support 
    <select name="additional_support" size="1" id="additional_support" style="">
      <option>Yes</option>
      <option>No</option>
    </select>
    </span><br>
    <br>
                  <span class="style2">Comments: </span>
<textarea name="comments" cols="34" rows="6" id="comments"></textarea>
<br>
                                           </div>
        </blockquote>
        </td>
      </tr>
    </table>
  </div>
  <div align="center" style="width: 522; height: 90">
    <table width="518" border="1" align="left" cellpadding="0" cellspacing="0" bordercolor="#999999" id="AutoNumber4" style="border-collapse: collapse">
      <tr>
        <td width="516" align="left" bgcolor="#F7F7F7">
        <p align="center"><span class="style1"><br>
        Note: This posting will 
        be queued and responded to within 48 hours.<br>
        <br>
 
<input type="submit" name="Submit" value="Submit"> <br>
 </span></td>
      </tr>
    </table>
  </div>
      <p></td>
      <td width="50%" style="border-style: none; border-width: medium"> </td>
    </tr>
  </table>
</form>
</body>
</html>
And just the ASP
[code]<%
Dim environment, app_list, project_name, event_descript, event_date, event_start, event_end, requested, primary_contact, primary_info, secondary_contact, secondary_info, additional_support, comments

Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''")
End Function


environment = ChkString(Request.Form("environ_pull"))
app_list = ChkString(Request.Form("app_list"))
project_name = ChkString(Request.Form("project_name"))
event_descript = ChkString(Request.Form("event_descript"))
event_date = ChkString(Request.Form("event_date"))
event_start = ChkString(Request.Form("event_start"))
event_end = ChkString(Request.Form("event_end"))
requested = ChkString(Request.Form("requested"))
primary_contact = ChkString(Request.Form("primary_contact"))
primary_info = ChkString(Request.Form("primary_info"))
secondary_contact = ChkString(Request.Form("secondary_contact"))
secondary_info = ChkString(Request.Form("secondary_info"))
additional_support = ChkString(Request.Form("additional_support"))
comments = ChkString(Request.Form("comments"))

data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("supportrequest.mdb")
sql_insert = "insert into Request Support (environment, app_list, project_name, event_descript, event_date, event_start, event_end, requested, primary_contact, primary_info, secondary_contact, secondary_info, additional_support, comments)"
values = (environ_pull & " ', ' " &_
project_name & "', '" &_
event_descript & "', '" &_
event_date & "', '" &_
event_start & "', '" &_
event_end & "', '" &_
requested & "', '" &_
primary_contact & "', '" &_
primary_info & "', '" &_
secondary_contact & "', '" &_
secondary_info & "', '" &_
additional_support & "', '" &_
comments &")" )'

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

con.Open data_source
con.Execute sql_insert

con.Close
Set con = Nothing
%> [code]
Finally, the error message:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/ips/request_supportDB2.asp, line 141

Anyone who can shed light on my predicament will be my new best friend.

P.S.

When I do fix the problem, altering the ASP as such:

Code:
<% 
Dim environment, app_list, project_name, event_descript, event_date, event_start, event_end, requested, primary_contact, primary_info, secondary_contact, secondary_info, additional_support, comments

Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''")
End Function


environment = ChkString(Request.Form("environ_pull"))
app_list = ChkString(Request.Form("app_list"))
project_name = ChkString(Request.Form("project_name"))
event_descript = ChkString(Request.Form("event_descript"))
event_date = ChkString(Request.Form("event_date"))
event_start = ChkString(Request.Form("event_start"))
event_end = ChkString(Request.Form("event_end"))
requested = ChkString(Request.Form("requested"))
primary_contact = ChkString(Request.Form("primary_contact"))
primary_info = ChkString(Request.Form("primary_info"))
secondary_contact = ChkString(Request.Form("secondary_contact"))
secondary_info = ChkString(Request.Form("secondary_info"))
additional_support = ChkString(Request.Form("additional_support"))
comments = ChkString(Request.Form("comments"))

data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ 
Server.MapPath("supportrequest.mdb")
sql_insert = "insert into Request Support (environment, app_list, project_name, event_descript, event_date, event_start, event_end, requested, primary_contact, primary_info, secondary_contact, secondary_info, additional_support, comments)" 
values(environ_pull & " ', ' " &_
project_name & "', '" &_
event_descript & "', '" &_
event_date & "', '" &_
event_start & "', '" &_
event_end & "', '" &_
requested & "', '" &_
primary_contact & "', '" &_
primary_info & "', '" &_
secondary_contact & "', '" &_
secondary_info & "', '" &_
additional_support & "', '" &_
comments &"')'"
I get this error.

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/ips/request_supportDB2.asp, line 136

comments &"')'"
---------------^

Even if I add the expected ')', it still asks me to add two or three more.

Any ideas?
natch.
Reply With Quote
  #2 (permalink)  
Old July 6th, 2004, 11:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hello,

There should be no single quote after the ); that's why you are probably having the error.

Brian
Reply With Quote
  #3 (permalink)  
Old July 6th, 2004, 11:40 AM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to remnatch
Default

First, thanks for the help.

I've already tried that, as well as closing the parenthetical expression after Comments and closing the quotes after the parenthesis.

Either way, I'm still getting Syntax error messages.
Reply With Quote
  #4 (permalink)  
Old July 6th, 2004, 12:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi natch,

Why don't you do a response.write of "sql_insert" before "con.Execute sql_insert" and post its output here? That should be helpful to identify where you go wrong. It is hard to find from the code which concatenates all that using ASP.

Code:
con.Open data_source
Response.write sql_insert
Response.end
con.Execute sql_insert
Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #5 (permalink)  
Old July 6th, 2004, 02:21 PM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to remnatch
Default

First, thanks for the help.

Now to the problem.

I added

Response.write sql_insert
Response.end

after

Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source

but when I went to access the form, it merely wrote

Quote:
quote:insert into [Request Support] (environment, app_list, project_name, event_descript, event_date, event_start, event_end, requested, primary_contact, primary_info, secondary_contact, secondary_info, additional_support, comments) values (' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ')
Ignoring my html.

I removed these two lines, and took the advice of another programmer, changing my ASP to
Code:
sql_insert = "insert into [Request Support] (environment, app_list, project_name, event_descript, event_date, event_start, event_end, requested, primary_contact, primary_info, secondary_contact, secondary_info, additional_support, comments)" 

sql_insert = sql_insert & " values ('" & environ_pull & " ', ' " & project_name & "', '" & event_descript & "', '" & event_date & "', '" & event_start & "', '" & event_end & "', '" & requested & "', '" & primary_contact & "', '" & primary_info & "', '" & secondary_contact & "', '" & secondary_info & "', '" & additional_support & "', '" & comments &"')"
But as a result, I now recieve this error

Quote:
quote:Microsoft JET Database Engine error '80040e14'

Number of query values and destination fields are not the same.

/ips/request_supportDB2.asp, line 132
I have an autonumber field as my primary key, which I haven't added as it is not part of the form. Could this be the issue?

Any ideas?
natch.



Any ideas?
natch.
Reply With Quote
  #6 (permalink)  
Old July 6th, 2004, 02:32 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

You can also use this after the 2 SQL_Insert codes that you posted in your last mail here.
Code:
Response.write sql_insert
Response.end
Leaving out AUTONUMBER/Identity column in insert statement is the RIGHT thing, you got to exclude that from the insert statement. That doesn't give you this error.

But AFAICS, you are trying to insert only 13 values against 14 columns in the INSERT statement.;) APPLIST is missing in there at the second postion. That is what the ERROR too denotes. Can you check that and get back to us?

Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #7 (permalink)  
Old July 6th, 2004, 03:02 PM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to remnatch
Default

After fixing the typo, and adding app_list back to equalize the form and the database, I'm now recieving this error:

Quote:
quote:Microsoft JET Database Engine error '80004005'

Operation must use an updateable query.

/ips/request_supportDB2.asp, line 128
I have permission to write on this server, but I get the feeling that I'm not opening the database the correct way, something in this:

Quote:
quote:Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert
region seems wrong.

Any ideas?
natch.
Reply With Quote
  #8 (permalink)  
Old July 6th, 2004, 03:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Your IUSR_ComputerName account has not got enough permission to the run such statements onto the database.

This has been discussed many times in this forum, but I am unable to find the thread URL, instead I was able find this for you. Go through this article and do as mentioned in this page to get it resolved.

How Do I Fix ASP 80004005 errors?

Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #9 (permalink)  
Old July 8th, 2004, 09:43 AM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to remnatch
Default

I've rewritten most of my ASP code.

Code:
<%
Dim environment, app_list, project_name, event_descript, event_date, event_start, event_end, requested, primary_contact, primary_info, secondary_contact, secondary_info, additional_support, comments

Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''")
End Function


environment = ChkString(Request.Form("environ_pull"))
app_list = ChkString(Request.Form("app_list"))
project_name = ChkString(Request.Form("project_name"))
event_descript = ChkString(Request.Form("event_descript"))
event_date = ChkString(Request.Form("event_date"))
event_start = ChkString(Request.Form("event_start"))
event_end = ChkString(Request.Form("event_end"))
requested = ChkString(Request.Form("requested"))
primary_contact = ChkString(Request.Form("primary_contact"))
primary_info = ChkString(Request.Form("primary_info"))
secondary_contact = ChkString(Request.Form("secondary_contact"))
secondary_info = ChkString(Request.Form("secondary_info"))
additional_support = ChkString(Request.Form("additional_support"))
comments = ChkString(Request.Form("comments"))

    Dim MyConnection
    Dim MyConnectionString
    Dim MySQLStatement

    MyConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=IPSDatabase.mdb"

    MySQLStatement = "INSERT INTO [Request Support] (environment, app_list, project_name, event_descript, event_date, event_start, event_end, requested, primary_contact, primary_info, secondary_contact, secondary_info, additional_support, comments)"
     Values = "(" & environ_pull & " 'test, ' " & applist & "'test,'" & project_name & "'test, '" & event_descript & "'test, '" & event_date & "', '" & event_start & "', '" & event_end & "', '" & requested & "', '" & primary_contact & "', '" & primary_info & "', '" & secondary_contact & "', '" & secondary_info & "', '" & additional_support & "', '" & comments &"')"

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

    MyConnection.Open MyConnectionString
    MyConnection.Execute(MySQLStatement)
    MyConnection.Close()

    Set MyConnection = Nothing
%>
The issue I'm having now is such:

 
Quote:
quote:Microsoft JET Database Engine error '80004005'
Quote:

Could not find file 'C:\WINNT\system32\IPSDatabase.mdb'.

/ips/Request_SupportDB3.asp, line 133
I have permission to read, write, and do anything else to the database and the server, so I don't know what my issue is.

Any ideas?
natch.
Reply With Quote
  #10 (permalink)  
Old July 8th, 2004, 12:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

That is fine with the permission part of it. But where have you specified the location of your database in your connection string?

Code:
MyConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=IPSDatabase.mdb"
            You got to specify the path there.

Use this if you have your database under the same location where your ASP files are.
Code:
MyConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & server.mappath("./IPSDatabase.mdb")

Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Syntax error in INSERT INTO statement mega ASP.NET 1.0 and 1.1 Basics 3 January 12th, 2005 04:30 PM
Syntax error in INSERT INTO statement. askaggs Classic ASP Databases 5 June 10th, 2004 12:21 AM



All times are GMT -4. The time now is 04:44 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.