Improve company productivity with a Business Account.Sign Up

x
?
Solved

INSERT INTO Syntax Error using ASP, VBScript, Advanced Server 2000

Posted on 2004-08-04
7
Medium Priority
?
696 Views
Last Modified: 2008-02-01
Hi,

I am having one hell of a day trying to extract some records from an existing table in an existing database.  The idea behind this application is for a Manufacturing Client of mine who wants to decide to build 5 types of unit in one day and only wants 1 kitting list to feature the whole of day's requirements.

I am extracting the ParentPart, ComponentPart and Qty per fields using an SQL query.  That works fine and I can display all the items meeting the criteria, but when I try to add it to another seperate existing table, it just throws up the INSERT INTO syntax Errox on the line containing

The code below takes the 5 Parts and their Quantities from an htm file and extracts all the records meeting the criteria

<% @language="vbscript" %>

<%
Part1=Request.Form("Part1")
Part2=Request.Form("Part2")
Part3=Request.Form("Part3")
Part4=Request.Form("Part4")
Part5=Request.Form("Part5")
Qty1=Request.Form("Quantity1")
Qty2=Request.Form("Quantity2")
Qty3=Request.Form("Quantity3")
Qty4=Request.Form("Quantity4")
Qty5=Request.Form("Quantity5")

' Dimension my arrays and variables

 Dim BOMDBConnection,BOMDBConnectionOut
 Dim GetParts,GetParts1,GetParts2,GetParts3,GetParts4
 Dim AddParts,AddParts1,AddParts2,AddParts3,AddParts4
 Dim BOMdbresults,BOMdbresults1,BOMdbresults2,BOMdbresults3,BOMdbresults4
 Dim BOMdbpath,BOMdbpath1,BOMdbpath2,BOMdbpath3,BOMdbpath4

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

'  This section opens the Database for the connection, this works fine

BOMdbpath = server.mappath("/bom/fpdb")
connection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & BOMdbpath & "\cbom.mdb"
BOMDBConnection.open (connection)      
                  
'  This section creates the Recordset to hold the information returned by the first query

set BOMdbresults = Server.CreateObject("ADODB.Recordset")
GetParts = "SELECT * FROM BOM WHERE ParentPart ='" & Part1 & "'"

'  This section runs the first query      

            set BOMdbresults = BOMDBConnection.execute(GetParts)
            if not BOMdbresults.EOF then
                  Response.Write(" Data for : " & Part1 & "<BR>")
                        while not BOMdbresults.EOF
                        Response.Write(" ID : " & BOMdbresults("ID") & " ")
                        Response.Write(" Component : " & BOMdbresults("Component")& " ")                        
                        Response.Write(" Quantity : " & BOMdbresults("Qty")& "<BR>")
                        set PartVar = BOMdbresults("Component")
                        set QtyVar = BOMdbresults("Qty")
                        set IDVar = BOMdbresults("ID")
                        response.write "THE QUERY IS : INSERT INTO Output (ID,PartOut,QtyOut)VALUES ('" & IDVar & "','" & PartVar & "','" & QtyVar& "')"

***** The above line generates the following output *********

THE QUERY IS : INSERT INTO Output (ID,PartOut,QtyOut)VALUES ('1627','HDW100088','4')

So the SQL query should be laid out in the line below correctly

' AddResults = "INSERT INTO Output (ID,PartOut,QtyOut)VALUES ('" & IDVar & "','" & PartVar & "','" & QtyVar& "')"
' BOMDBConnection.execute(AddResults)
BOMdbresults.movenext()
      wend
else
end if      

There's more below here, but the rest works

If I remove the ' from the BOMDBConnection.execute(AddResults) and above line, I get that irritating Syntax Error

Please help !!!

Thanks
Si

0
Comment
Question by:legalsrl
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 16

Expert Comment

by:golfDoctor
ID: 11716611
What are the datatypes of the fields, in your example they all must be text.

What is the exact error messgae?
0
 
LVL 15

Accepted Solution

by:
joeposter649 earned 500 total points
ID: 11716612
Perhaps "Output" is causing the problem because it's an ODBC reserved word.  Not sure if it would apply to OLE/DB.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11716617
Missing space before "VALUES"

try
AddResults = "INSERT INTO [Output] (ID,PartOut,QtyOut) VALUES ('" & IDVar & "','" & PartVar & "','" & QtyVar& "')"
BOMDBConnection.execute(AddResults)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 16

Expert Comment

by:golfDoctor
ID: 11716647
Here's reserved words: (doesn't appear to be the issue)

http://support.microsoft.com/default.aspx?scid=kb;EN-US;209187
0
 
LVL 16

Author Comment

by:legalsrl
ID: 11723627
Thanks all for your help, the problem is now I get this

Error Type:
Microsoft JET Database Engine (0x80004005)
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
/BOM/dbquery3.asp, line 54

If I run this asp script more than once.........this will be run on a daily basis, and will probably sometimes be run 2 or 3 times a day.

What I was going to do would be to run the import querys (5 of them for the 5 Bill of Materials needed), then write all of the details to the OutVar (it replaced Output) table, summarise the data on the Outvar table and output it, then delete the Outvar table

Surely this would stop this error happening if I clear the table after a successful run ?

Thanks again for your help
Si
0
 
LVL 16

Author Comment

by:legalsrl
ID: 11723880
Thanks for your help guys,

I managed to fix the last error by removing the primary key from the OutVar table

Works swimmingly now :-)


Si
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…

579 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