|
 |
asp_databases thread: Probs with INSERT SQL
Message #1 by "Colin Montgomery" <colin.montgomery@c...> on Wed, 14 Nov 2001 12:04:57
|
|
Hi
I've got a simple page taking form variables and inserting them into an
Access 97 db, via a conn string:
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\IntranetSiteFiles\EnquiriesFeedback\Feedback.mdb;Persist
Security Info=False
I'm getting a 'Syntax error in INSERT INTO statement' error.
When I Response.Write the SQL I get:
INSERT INTO tblFeedback
(Name, position, office, practice, timewithfacm,
usedesk, notusedesk, useother, deskfreq, enquirytype,
othertype, rating, othercomments)
VALUES
('myname', 'manager', 'London', 'finance',
'less than 1 year', 'yes',
'intranet, otherlawyers',
'other', 'occasionally',
'templates, transbibles, knowhow casesorlegislation',
'other', 'excellent', 'other')
However when I run it in Access' query builder it runs fine.
I'm baffled - can anyone help?
For info i've included my code below (appologies for wrapping).
Thanks,
Colin
<% @Language=VBScript %>
<%
option explicit
response.expires = 0
%>
<!--#include file="Connections/connFeedback.asp" -->
<%
Dim strName, strPosition, strOffice, strGroup, strTimeWithFaCM
Dim strUseDesk, strNotUseDesk, strUseOther, strDeskFreq
Dim strEnquiryType, strOtherType, strRateDesk, strOtherComments
Dim strSQL, comInsertFeedback
'get all variables from Forms Collection
strName = Request.Form("Name")
strPosition = Request.Form("Position")
strOffice = Request.Form("Office")
strGroup = Request.Form("Group")
strTimeWIthFacm = Request.Form("timeWithFaCM")
strUseDesk = Request.Form("usedesk")
strNotUseDesk = Request.Form("notusedesk")
strUseOther = Request.Form("useother")
strDeskFreq = Request.Form("deskfreq")
strEnquiryType = Request.Form("enquirytype")
strOtherType = Request.Form("othertype")
strRateDesk = Request.Form("ratedesk")
strOtherComments = Request.Form("othercomments")
strSQL = "INSERT INTO tblFeedback " _
& "(Name, position, office, practice, timewithfacm, " _
& "usedesk, notusedesk, useother, deskfreq, enquirytype, " _
& "othertype, rating, othercomments) " _
& "VALUES ('" & strName & "', '" & strPosition & "', '" &
strOffice _
& "', '" & strGroup & "', '" & strTimeWithFaCM & "', '" &
strUseDesk _
& "', '" & strNotUseDesk & "', '" & strUseOther & "', '" &
strDeskFreq _
& "', '" & strEnquiryType & "', '" & strOtherType & "', '" &
strRateDesk _
& "', '" & strOtherComments & "')"
response.write strSQL
set comInsertFeedback = Server.CreateObject("ADODB.Command")
comInsertFeedback.ActiveConnection = MM_connFeedback_STRING
comInsertFeedback.CommandText = strSQL
comInsertFeedback.CommandType = 1
comInsertFeedback.CommandTimeout = 0
comInsertFeedback.Prepared = true
comInsertFeedback.Execute()
comInsertFeedback.Close
set comInsertFeedback = Nothing
%>
Message #2 by "Andrew Haslett" <scooter@p...> on Wed, 14 Nov 2001 23:55:24 +1030
|
|
is 'Name' a reserved word in Access?? Try surrounding it in [].
INSERT INTO ... ([Name],position, ...)
HTH,
Andrew
-----Original Message-----
From: Colin Montgomery [mailto:colin.montgomery@c...]
Sent: Wednesday, 14 November 2001 12:05 PM
To: ASP Databases
Subject: [asp_databases] Probs with INSERT SQL
Hi
I've got a simple page taking form variables and inserting them into an
Access 97 db, via a conn string:
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\IntranetSiteFiles\EnquiriesFeedback\Feedback.mdb;Persist
Security Info=False
I'm getting a 'Syntax error in INSERT INTO statement' error.
When I Response.Write the SQL I get:
INSERT INTO tblFeedback
(Name, position, office, practice, timewithfacm,
usedesk, notusedesk, useother, deskfreq, enquirytype,
othertype, rating, othercomments)
VALUES
('myname', 'manager', 'London', 'finance',
'less than 1 year', 'yes',
'intranet, otherlawyers',
'other', 'occasionally',
'templates, transbibles, knowhow casesorlegislation',
'other', 'excellent', 'other')
However when I run it in Access' query builder it runs fine.
I'm baffled - can anyone help?
For info i've included my code below (appologies for wrapping).
Thanks,
Colin
<% @Language=VBScript %>
<%
option explicit
response.expires = 0
%>
<!--#include file="Connections/connFeedback.asp" -->
<%
Dim strName, strPosition, strOffice, strGroup, strTimeWithFaCM
Dim strUseDesk, strNotUseDesk, strUseOther, strDeskFreq
Dim strEnquiryType, strOtherType, strRateDesk, strOtherComments
Dim strSQL, comInsertFeedback
'get all variables from Forms Collection
strName = Request.Form("Name")
strPosition = Request.Form("Position")
strOffice = Request.Form("Office")
strGroup = Request.Form("Group")
strTimeWIthFacm = Request.Form("timeWithFaCM")
strUseDesk = Request.Form("usedesk")
strNotUseDesk = Request.Form("notusedesk")
strUseOther = Request.Form("useother")
strDeskFreq = Request.Form("deskfreq")
strEnquiryType = Request.Form("enquirytype")
strOtherType = Request.Form("othertype")
strRateDesk = Request.Form("ratedesk")
strOtherComments = Request.Form("othercomments")
strSQL = "INSERT INTO tblFeedback " _
& "(Name, position, office, practice, timewithfacm, " _
& "usedesk, notusedesk, useother, deskfreq, enquirytype, " _
& "othertype, rating, othercomments) " _
& "VALUES ('" & strName & "', '" & strPosition & "', '" &
strOffice _
& "', '" & strGroup & "', '" & strTimeWithFaCM & "', '" &
strUseDesk _
& "', '" & strNotUseDesk & "', '" & strUseOther & "', '" &
strDeskFreq _
& "', '" & strEnquiryType & "', '" & strOtherType & "', '" &
strRateDesk _
& "', '" & strOtherComments & "')"
response.write strSQL
set comInsertFeedback = Server.CreateObject("ADODB.Command")
comInsertFeedback.ActiveConnection = MM_connFeedback_STRING
comInsertFeedback.CommandText = strSQL
comInsertFeedback.CommandType = 1
comInsertFeedback.CommandTimeout = 0
comInsertFeedback.Prepared = true
comInsertFeedback.Execute()
comInsertFeedback.Close
set comInsertFeedback = Nothing
%>
$subst('Email.Unsub')
Message #3 by "Colin Montgomery" <colin.montgomery@c...> on Wed, 14 Nov 2001 15:39:22
|
|
unfortunately this doesn't seem to be the cause of the error (it does run
in the query builder in Access*). I've changed the field name and it still
throws the same error.
*Access changes the query to the format:
INSERT INTO
tblName (EnqName, position...)
SELECT 'MyName' As Expr1, 'MyPosition' As Expr2...
but I can't see how this would change anything.
Thanks (but still in the dark),
Colin
> is 'Name' a reserved word in Access?? Try surrounding it in [].
>
> INSERT INTO ... ([Name],position, ...)
>
> HTH,
>
> Andrew
Message #4 by "Drew, Ron" <RDrew@B...> on Wed, 14 Nov 2001 11:35:09 -0500
|
|
In the values I see variables with imbeded "," i.e. 'intranet,
otherlawyers',
I think query builder would run, but I am not sure how this would work
within an ASP
-----Original Message-----
From: Colin Montgomery [mailto:colin.montgomery@c...]
Sent: Wednesday, November 14, 2001 7:05 AM
To: ASP Databases
Subject: [asp_databases] Probs with INSERT SQL
Hi
I've got a simple page taking form variables and inserting them into an
Access 97 db, via a conn string:
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\IntranetSiteFiles\EnquiriesFeedback\Feedback.mdb;Persist
Security Info=False
I'm getting a 'Syntax error in INSERT INTO statement' error.
When I Response.Write the SQL I get:
INSERT INTO tblFeedback
(Name, position, office, practice, timewithfacm,
usedesk, notusedesk, useother, deskfreq, enquirytype,
othertype, rating, othercomments)
VALUES
('myname', 'manager', 'London', 'finance',
'less than 1 year', 'yes',
'intranet, otherlawyers',
'other', 'occasionally',
'templates, transbibles, knowhow casesorlegislation',
'other', 'excellent', 'other')
However when I run it in Access' query builder it runs fine.
I'm baffled - can anyone help?
For info i've included my code below (appologies for wrapping).
Thanks,
Colin
<% @Language=VBScript %>
<%
option explicit
response.expires = 0
%>
<!--#include file="Connections/connFeedback.asp" -->
<%
Dim strName, strPosition, strOffice, strGroup, strTimeWithFaCM Dim
strUseDesk, strNotUseDesk, strUseOther, strDeskFreq Dim strEnquiryType,
strOtherType, strRateDesk, strOtherComments Dim strSQL, comInsertFeedback
'get all variables from Forms Collection
strName = Request.Form("Name")
strPosition = Request.Form("Position")
strOffice = Request.Form("Office")
strGroup = Request.Form("Group")
strTimeWIthFacm = Request.Form("timeWithFaCM")
strUseDesk = Request.Form("usedesk")
strNotUseDesk = Request.Form("notusedesk")
strUseOther = Request.Form("useother")
strDeskFreq = Request.Form("deskfreq")
strEnquiryType = Request.Form("enquirytype")
strOtherType = Request.Form("othertype")
strRateDesk = Request.Form("ratedesk")
strOtherComments = Request.Form("othercomments")
strSQL = "INSERT INTO tblFeedback " _
& "(Name, position, office, practice, timewithfacm, " _
& "usedesk, notusedesk, useother, deskfreq, enquirytype, " _
& "othertype, rating, othercomments) " _
& "VALUES ('" & strName & "', '" & strPosition & "', '" &
strOffice _
& "', '" & strGroup & "', '" & strTimeWithFaCM & "', '" &
strUseDesk _
& "', '" & strNotUseDesk & "', '" & strUseOther & "', '" &
strDeskFreq _
& "', '" & strEnquiryType & "', '" & strOtherType & "', '" &
strRateDesk _
& "', '" & strOtherComments & "')"
response.write strSQL
set comInsertFeedback = Server.CreateObject("ADODB.Command")
comInsertFeedback.ActiveConnection = MM_connFeedback_STRING
comInsertFeedback.CommandText = strSQL comInsertFeedback.CommandType = 1
comInsertFeedback.CommandTimeout = 0 comInsertFeedback.Prepared = true
comInsertFeedback.Execute()
comInsertFeedback.Close
set comInsertFeedback = Nothing
%>
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
Message #5 by Pappas Nikos <pappas@c...> on Wed, 14 Nov 2001 19:59:55 +0200
|
|
Hi there
Just after a quick look
If I am not mistaken I can see about 13 fields to place the values
in your statement and about 16 values to insert in the 13 fields.
I hope it helps
Nikos
At Wednesday14/11/2001, you wrote:
>In the values I see variables with imbeded "," i.e. 'intranet,
>otherlawyers',
>I think query builder would run, but I am not sure how this would work
>within an ASP
>
>-----Original Message-----
>From: Colin Montgomery [mailto:colin.montgomery@c...]
>Sent: Wednesday, November 14, 2001 7:05 AM
>To: ASP Databases
>Subject: [asp_databases] Probs with INSERT SQL
>
>
>Hi
>
>I've got a simple page taking form variables and inserting them into an
>Access 97 db, via a conn string:
>
>Provider=Microsoft.Jet.OLEDB.4.0;Data
>Source=C:\IntranetSiteFiles\EnquiriesFeedback\Feedback.mdb;Persist
>Security Info=False
>
>I'm getting a 'Syntax error in INSERT INTO statement' error.
>
>When I Response.Write the SQL I get:
>
>INSERT INTO tblFeedback
> (Name, position, office, practice, timewithfacm,
> usedesk, notusedesk, useother, deskfreq, enquirytype,
> othertype, rating, othercomments)
>VALUES
> ('myname', 'manager', 'London', 'finance',
> 'less than 1 year', 'yes',
> 'intranet, otherlawyers',
> 'other', 'occasionally',
> 'templates, transbibles, knowhow casesorlegislation',
> 'other', 'excellent', 'other')
>
>However when I run it in Access' query builder it runs fine.
>
>I'm baffled - can anyone help?
>
>For info i've included my code below (appologies for wrapping).
>
>Thanks,
>Colin
>
><% @Language=VBScript %>
><%
>option explicit
>response.expires = 0
>%>
><!--#include file="Connections/connFeedback.asp" -->
><%
>Dim strName, strPosition, strOffice, strGroup, strTimeWithFaCM Dim
>strUseDesk, strNotUseDesk, strUseOther, strDeskFreq Dim strEnquiryType,
>strOtherType, strRateDesk, strOtherComments Dim strSQL, comInsertFeedback
>
>'get all variables from Forms Collection
>strName = Request.Form("Name")
>strPosition = Request.Form("Position")
>strOffice = Request.Form("Office")
>strGroup = Request.Form("Group")
>strTimeWIthFacm = Request.Form("timeWithFaCM")
>strUseDesk = Request.Form("usedesk")
>strNotUseDesk = Request.Form("notusedesk")
>strUseOther = Request.Form("useother")
>strDeskFreq = Request.Form("deskfreq")
>strEnquiryType = Request.Form("enquirytype")
>strOtherType = Request.Form("othertype")
>strRateDesk = Request.Form("ratedesk")
>strOtherComments = Request.Form("othercomments")
>
>strSQL = "INSERT INTO tblFeedback " _
> & "(Name, position, office, practice, timewithfacm, " _
> & "usedesk, notusedesk, useother, deskfreq, enquirytype, " _
> & "othertype, rating, othercomments) " _
> & "VALUES ('" & strName & "', '" & strPosition & "', '" &
>strOffice _
> & "', '" & strGroup & "', '" & strTimeWithFaCM & "', '" &
>strUseDesk _
> & "', '" & strNotUseDesk & "', '" & strUseOther & "', '" &
>strDeskFreq _
> & "', '" & strEnquiryType & "', '" & strOtherType & "', '" &
>strRateDesk _
> & "', '" & strOtherComments & "')"
>
>response.write strSQL
>
>set comInsertFeedback = Server.CreateObject("ADODB.Command")
>comInsertFeedback.ActiveConnection = MM_connFeedback_STRING
>comInsertFeedback.CommandText = strSQL comInsertFeedback.CommandType = 1
>comInsertFeedback.CommandTimeout = 0 comInsertFeedback.Prepared = true
>comInsertFeedback.Execute()
>
>comInsertFeedback.Close
>set comInsertFeedback = Nothing
>%>
>
>---
>You are currently subscribed to asp_databases as: RDrew@B... To
>unsubscribe send a blank email to $subst('Email.Unsub')
>
>
Message #6 by "Colin Montgomery" <colin.montgomery@c...> on Wed, 14 Nov 2001 18:20:01
|
|
thanks for all your input guys.
Are commas like apostrophes in SQL - I didn't think they were? i.e. one
value is: 'transbibles, templates, other'
I have option groups (i.e. with the same name in the HTML) which I had
anticipated storing in one text field as a comma separated list, as this
is how they are added to the Forms variables. This is why some of the
values have commas in them.
Is there a better way to implement this?
If it helps, it's a very basic system used for gathering statistics on
enquiries received - so it will be used for running reports off etc.
Thanks,
Colin
Message #7 by David Cameron <dcameron@i...> on Thu, 15 Nov 2001 09:52:11 +1100
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C16D5F.02D548DC
Content-Type: text/plain
There are actually 13 values for 13 fields. Some values have commas in the
string.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Pappas Nikos [mailto:pappas@c...]
Sent: Thursday, 15 November 2001 4:00 AM
To: ASP Databases
Subject: [asp_databases] RE: Probs with INSERT SQL
Hi there
Just after a quick look
If I am not mistaken I can see about 13 fields to place the values
in your statement and about 16 values to insert in the 13 fields.
I hope it helps
Nikos
At Wednesday14/11/2001, you wrote:
>In the values I see variables with imbeded "," i.e. 'intranet,
>otherlawyers',
>I think query builder would run, but I am not sure how this would work
>within an ASP
>
>-----Original Message-----
>From: Colin Montgomery [mailto:colin.montgomery@c...]
>Sent: Wednesday, November 14, 2001 7:05 AM
>To: ASP Databases
>Subject: [asp_databases] Probs with INSERT SQL
>
>
>Hi
>
>I've got a simple page taking form variables and inserting them into an
>Access 97 db, via a conn string:
>
>Provider=Microsoft.Jet.OLEDB.4.0;Data
>Source=C:\IntranetSiteFiles\EnquiriesFeedback\Feedback.mdb;Persist
>Security Info=False
>
>I'm getting a 'Syntax error in INSERT INTO statement' error.
>
>When I Response.Write the SQL I get:
>
>INSERT INTO tblFeedback
> (Name, position, office, practice, timewithfacm,
> usedesk, notusedesk, useother, deskfreq, enquirytype,
> othertype, rating, othercomments)
>VALUES
> ('myname', 'manager', 'London', 'finance',
> 'less than 1 year', 'yes',
> 'intranet, otherlawyers',
> 'other', 'occasionally',
> 'templates, transbibles, knowhow casesorlegislation',
> 'other', 'excellent', 'other')
>
>However when I run it in Access' query builder it runs fine.
>
>I'm baffled - can anyone help?
>
>For info i've included my code below (appologies for wrapping).
>
>Thanks,
>Colin
>
><% @Language=VBScript %>
><%
>option explicit
>response.expires = 0
>%>
><!--#include file="Connections/connFeedback.asp" -->
><%
>Dim strName, strPosition, strOffice, strGroup, strTimeWithFaCM Dim
>strUseDesk, strNotUseDesk, strUseOther, strDeskFreq Dim strEnquiryType,
>strOtherType, strRateDesk, strOtherComments Dim strSQL, comInsertFeedback
>
>'get all variables from Forms Collection
>strName = Request.Form("Name")
>strPosition = Request.Form("Position")
>strOffice = Request.Form("Office")
>strGroup = Request.Form("Group")
>strTimeWIthFacm = Request.Form("timeWithFaCM")
>strUseDesk = Request.Form("usedesk")
>strNotUseDesk = Request.Form("notusedesk")
>strUseOther = Request.Form("useother")
>strDeskFreq = Request.Form("deskfreq")
>strEnquiryType = Request.Form("enquirytype")
>strOtherType = Request.Form("othertype")
>strRateDesk = Request.Form("ratedesk")
>strOtherComments = Request.Form("othercomments")
>
>strSQL = "INSERT INTO tblFeedback " _
> & "(Name, position, office, practice, timewithfacm, " _
> & "usedesk, notusedesk, useother, deskfreq, enquirytype, " _
> & "othertype, rating, othercomments) " _
> & "VALUES ('" & strName & "', '" & strPosition & "', '" &
>strOffice _
> & "', '" & strGroup & "', '" & strTimeWithFaCM & "', '" &
>strUseDesk _
> & "', '" & strNotUseDesk & "', '" & strUseOther & "', '" &
>strDeskFreq _
> & "', '" & strEnquiryType & "', '" & strOtherType & "', '" &
>strRateDesk _
> & "', '" & strOtherComments & "')"
>
>response.write strSQL
>
>set comInsertFeedback = Server.CreateObject("ADODB.Command")
>comInsertFeedback.ActiveConnection = MM_connFeedback_STRING
>comInsertFeedback.CommandText = strSQL comInsertFeedback.CommandType = 1
>comInsertFeedback.CommandTimeout = 0 comInsertFeedback.Prepared = true
>comInsertFeedback.Execute()
>
>comInsertFeedback.Close
>set comInsertFeedback = Nothing
>%>
>
>---
>You are currently subscribed to asp_databases as: RDrew@B... To
>unsubscribe send a blank email to $subst('Email.Unsub')
>
>
$subst('Email.Unsub')
$subst('Email.Unsub')
|
|
|
View Cart
