|
 |
asp_databases thread: ODBC Drivers error '80040e14'
Message #1 by Leo Clayton <claytonl@z...> on Thu, 26 Oct 2000 10:49:39 -0400
|
|
--=====================_173853698==_.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed
I am trying to INSERT a/some RECORDS into an EMPTY TABLE that I created in
ACCESS. These records are being created from data passed from a FORM. I
know that the CORRECT data is there because I DISPLAY IT in a LOOP BEFORE I
even get to the part of the script where I actually CREATE AND WRITE the
RECORD.
I am working with the COMMAND OBJECT (which is still murky to me). The
error occurs when I try to EXECUTE. Here is the code:
<!--#include file="adovbs.inc"-->
<!--#include file="DataStore.inc"-->
<%
'This is a LOOP that will let me see the CHECKED CONTENTS OF MY FORM
Dim item
Dim intLoop 'as Integer
Response.Write("////////// Form //////////<BR>" & vbCRLF)
For Each Item in Request.Form
For intLoop = 1 to Request.Form(Item).Count
Response.Write(Item & " has a value of " &
Request.Form(Item)(intLoop) & "<BR>")
Next
Next
Response.Write("//////////End Form//////////<BR>" & vbCRLF)
%>
<%
Dim cmdInsertStuff
Dim sSQL, entry 'a SQL string and an Item variable. An item holds a
specific form value
Set cmdInsertStuff = Server.CreateObject("ADODB.Command") 'Create a command
object
cmdInsertStuff.ActiveConnection = strConnect 'Set a connection to it
For each entry in Request.Form("skill") 'loop through all checkboxes that
are checked.
'generating Skill ID key
LastName = Session("LastName")
IDNumber = Session("IDNumber")
s=Len(Lastname)
a=1
do while a<=s
Textchar = Mid(Lastname,a,1)
if Textchar = "'"then
Newname=Newname
else
Newname=Newname & Textchar
end if
a=a+1
loop
IDLeft4 = Newname
SkillID=Left(IDLeft4,4) & Date() & Time()
sSQL = sSQL & "INSERT INTO EmployeeSkillsTable(SkillID, IDNumber)
VALUES("&entry&", "&IDNumber&")"
cmdInsertStuff.CommandText = sSQL 'set the statement you want to
execute
Response.Write(sSQL&"<br>")
cmdInsertStuff.Execute ,,adCmdText Or adExecuteNorecords
Next
%>
This is what was returned to my browser after the script was executed:
////////// Form //////////
skill has a value of 1
skill has a value of 6
skill has a value of 7
skill has a value of 27
skill has a value of 50
skill has a value of 115
//////////End Form//////////
INSERT INTO EmployeeSkillsTable(SkillID, IDNumber) VALUES(1,
Bank10/26/009:19:09 AM)
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in query expression 'Bank10/26/009:19:09 AM'.
/EmployeeMasterStuff/CreateSkillsRecord3.asp, line 48
The ACTUAL LINE OF CODE (line 48) is:
cmdInsertStuff.Execute ,,adCmdText Or adExecuteNorecords
This particular line of code, truthfully, I do not understand what it is
doing even though it was told to me that it would work.
Help and Clarification would be GREATLY APPRECIATED!
Message #2 by Imar Spaanjaars <Imar@S...> on Thu, 26 Oct 2000 17:20:41 +0200
|
|
Hi Leo,
It looks like you need to enclose the IDNumber in apostrophes, so the end
result looks likes this:
INSERT INTO EmployeeSkillsTable(SkillID, IDNumber) VALUES(1,
'Bank10/26/009:19:09 AM')
As far as I can see, the IDNumber, despite its name, is a string / varchar
type. Change the code that build the SQL string as follows:
sSQL = sSQL & "INSERT INTO EmployeeSkillsTable(SkillID, IDNumber) VALUES("
& entry & ", '" & IDNumber & "')"
This might be hard to read, but I inserted an ' just before the closing "
before the IDNumber, and one AFTER the " at the end.
HtH
Imar
P.S Got the form to work??
At 10:49 AM 10/26/2000 -0400, you wrote:
>I am trying to INSERT a/some RECORDS into an EMPTY TABLE that I created in
>ACCESS. These records are being created from data passed from a FORM. I
>know that the CORRECT data is there because I DISPLAY IT in a LOOP BEFORE
>I even get to the part of the script where I actually CREATE AND WRITE the
>RECORD.
>
>I am working with the COMMAND OBJECT (which is still murky to me). The
>error occurs when I try to EXECUTE. Here is the code:
>
><!--#include file="adovbs.inc"-->
><!--#include file="DataStore.inc"-->
>
><%
>'This is a LOOP that will let me see the CHECKED CONTENTS OF MY FORM
>Dim item
>Dim intLoop 'as Integer
>Response.Write("////////// Form //////////<BR>" & vbCRLF)
>For Each Item in Request.Form
> For intLoop = 1 to Request.Form(Item).Count
> Response.Write(Item & " has a value of " &
> Request.Form(Item)(intLoop) & "<BR>")
> Next
>Next
>Response.Write("//////////End Form//////////<BR>" & vbCRLF)
>%>
>
>
><%
>Dim cmdInsertStuff
>Dim sSQL, entry 'a SQL string and an Item variable. An item holds a
>specific form value
>Set cmdInsertStuff = Server.CreateObject("ADODB.Command") 'Create a
>command object
>cmdInsertStuff.ActiveConnection = strConnect 'Set a connection to it
>
>For each entry in Request.Form("skill") 'loop through all checkboxes that
>are checked.
>'generating Skill ID key
>LastName = Session("LastName")
>IDNumber = Session("IDNumber")
>
>s=Len(Lastname)
>a=1
> do while a<=s
> Textchar = Mid(Lastname,a,1)
> if Textchar = "'"then
> Newname=Newname
> else
> Newname=Newname & Textchar
> end if
> a=a+1
> loop
> IDLeft4 = Newname
>
> SkillID=Left(IDLeft4,4) & Date() & Time()
>
> sSQL = sSQL & "INSERT INTO EmployeeSkillsTable(SkillID, IDNumber)
> VALUES("&entry&", "&IDNumber&")"
> cmdInsertStuff.CommandText = sSQL 'set the statement you want to
> execute
> Response.Write(sSQL&"<br>")
> cmdInsertStuff.Execute ,,adCmdText Or adExecuteNorecords
>Next
>
>%>
>
>This is what was returned to my browser after the script was executed:
>
>////////// Form //////////
>skill has a value of 1
>skill has a value of 6
>skill has a value of 7
>skill has a value of 27
>skill has a value of 50
>skill has a value of 115
>//////////End Form//////////
>INSERT INTO EmployeeSkillsTable(SkillID, IDNumber) VALUES(1,
>Bank10/26/009:19:09 AM)
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
>in query expression 'Bank10/26/009:19:09 AM'.
>
>/EmployeeMasterStuff/CreateSkillsRecord3.asp, line 48
>
>The ACTUAL LINE OF CODE (line 48) is:
>
>cmdInsertStuff.Execute ,,adCmdText Or adExecuteNorecords
>
>This particular line of code, truthfully, I do not understand what it is
>doing even though it was told to me that it would work.
>
>Help and Clarification would be GREATLY APPRECIATED!
Message #3 by Leo Clayton <claytonl@z...> on Thu, 26 Oct 2000 16:29:41 -0400
|
|
--=====================_194254803==_.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed
I'm not sure what you mean. I did go into the code and changed "&entry&"
to "&SkillID&". I've been trying to track down and understand the meaning
of that error message and I went to this site:
http://support.microsoft.com/support/kb/articles/Q183/0/60.ASP?LN=EN-US&SD=gn&FR=0
and came up with this explanation (which doesn't clear anything up for me):
Error Message
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error in INSERT INTO
statement.
Cause
A column name may be a reserved word, such as DATE. Change the column name
to a non-reserved name, such as "SaleDate".
So could you PLEASE look at my code AGAIN (smile) and let me know whats wrong:
<%
Dim cmdInsertStuff
Dim sSQL, entry 'a SQL string and an Item variable. An item holds a
specific form value
Set cmdInsertStuff = Server.CreateObject("ADODB.Command") 'Create a command
object
cmdInsertStuff.ActiveConnection = strConnect 'Set a connection to it
For each entry in Request.Form("skill") 'loop through all checkboxes that
are checked.
'generating Skill ID key
LastName = Session("LastName")
IDNumber = Session("IDNumber")
s=Len(Lastname)
a=1
do while a<=s
Textchar = Mid(Lastname,a,1)
if Textchar = "'"then
Newname=Newname
else
Newname=Newname & Textchar
end if
a=a+1
loop
IDLeft4 = Newname
SkillID=Left(IDLeft4,4) & Date() & Time()
sSQL = sSQL & "INSERT INTO EmployeeSkillsTable(SkillID, IDNumber)
VALUES("&SkillID&", "&IDNumber&")"
cmdInsertStuff.CommandText = sSQL 'set the statement you want to
execute
Response.Write(sSQL&"<br>")
cmdInsertStuff.Execute ,,adCmdText Or adExecuteNorecords
Next
%>
P.S.
You won't hear from me until Monday morning (E.S.T.), I won't be in
tomorrow. I'll probably start taking Fridays off for a while. Anyhow, I'm
still taking my code and books (Beginning Active Server Pages 3.0 and
Beginning ASP Databases) with me! Maybe, the light will go off in my head,
at least I hope so!!
At 05:20 PM 10/26/00 +0200, you wrote:
>Hi Leo,
>
>It looks like you need to enclose the IDNumber in apostrophes, so the end
>result looks likes this:
>
> INSERT INTO EmployeeSkillsTable(SkillID, IDNumber) VALUES(1,
> 'Bank10/26/009:19:09 AM')
>
>As far as I can see, the IDNumber, despite its name, is a string / varchar
>type. Change the code that build the SQL string as follows:
>
>sSQL = sSQL & "INSERT INTO EmployeeSkillsTable(SkillID, IDNumber) VALUES("
>& entry & ", '" & IDNumber & "')"
>This might be hard to read, but I inserted an ' just before the closing "
>before the IDNumber, and one AFTER the " at the end.
>
>HtH
>
>Imar
>
>
>P.S Got the form to work??
>
>
>
>At 10:49 AM 10/26/2000 -0400, you wrote:
>>I am trying to INSERT a/some RECORDS into an EMPTY TABLE that I created
>>in ACCESS. These records are being created from data passed from a
>>FORM. I know that the CORRECT data is there because I DISPLAY IT in a
>>LOOP BEFORE I even get to the part of the script where I actually CREATE
>>AND WRITE the RECORD.
>>
>>I am working with the COMMAND OBJECT (which is still murky to me). The
>>error occurs when I try to EXECUTE. Here is the code:
>>
>><!--#include file="adovbs.inc"-->
>><!--#include file="DataStore.inc"-->
>>
>><%
>>'This is a LOOP that will let me see the CHECKED CONTENTS OF MY FORM
>>Dim item
>>Dim intLoop 'as Integer
>>Response.Write("////////// Form //////////<BR>" & vbCRLF)
>>For Each Item in Request.Form
>> For intLoop = 1 to Request.Form(Item).Count
>> Response.Write(Item & " has a value of " &
>> Request.Form(Item)(intLoop) & "<BR>")
>> Next
>>Next
>>Response.Write("//////////End Form//////////<BR>" & vbCRLF)
>>%>
>>
>>
>><%
>>Dim cmdInsertStuff
>>Dim sSQL, entry 'a SQL string and an Item variable. An item holds a
>>specific form value
>>Set cmdInsertStuff = Server.CreateObject("ADODB.Command") 'Create a
>>command object
>>cmdInsertStuff.ActiveConnection = strConnect 'Set a connection to it
>>
>>For each entry in Request.Form("skill") 'loop through all checkboxes that
>>are checked.
>>'generating Skill ID key
>>LastName = Session("LastName")
>>IDNumber = Session("IDNumber")
>>
>>s=Len(Lastname)
>>a=1
>> do while a<=s
>> Textchar = Mid(Lastname,a,1)
>> if Textchar = "'"then
>> Newname=Newname
>> else
>> Newname=Newname & Textchar
>> end if
>> a=a+1
>> loop
>> IDLeft4 = Newname
>>
>> SkillID=Left(IDLeft4,4) & Date() & Time()
>>
>> sSQL = sSQL & "INSERT INTO EmployeeSkillsTable(SkillID,
>> IDNumber) VALUES("&entry&", "&IDNumber&")"
>> cmdInsertStuff.CommandText = sSQL 'set the statement you want to
>> execute
>> Response.Write(sSQL&"<br>")
>> cmdInsertStuff.Execute ,,adCmdText Or adExecuteNorecords
>>Next
>>
>>%>
>>
>>This is what was returned to my browser after the script was executed:
>>
>>////////// Form //////////
>>skill has a value of 1
>>skill has a value of 6
>>skill has a value of 7
>>skill has a value of 27
>>skill has a value of 50
>>skill has a value of 115
>>//////////End Form//////////
>>INSERT INTO EmployeeSkillsTable(SkillID, IDNumber) VALUES(1,
>>Bank10/26/009:19:09 AM)
>>
>>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>>
>>[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
>>in query expression 'Bank10/26/009:19:09 AM'.
>>
>>/EmployeeMasterStuff/CreateSkillsRecord3.asp, line 48
>>
>>The ACTUAL LINE OF CODE (line 48) is:
>>
>>cmdInsertStuff.Execute ,,adCmdText Or adExecuteNorecords
>>
>>This particular line of code, truthfully, I do not understand what it is
>>doing even though it was told to me that it would work.
>>
>>Help and Clarification would be GREATLY APPRECIATED!
>
>--- FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
>INSIGHTS IN YOUR INBOX!
>Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips,
>tools, and developments from the experts. Sign up for one or more of
>EarthWeb?s
>FREE IT newsletters at http://www.earthweb.com today!
Message #4 by "Ken Schaefer" <ken@a...> on Mon, 30 Oct 2000 13:58:54 +1100
|
|
Look up Scenario 3b - Syntax Error - Missing Operator cause by incorrect
delimiters
http://www.adopenstatic.com/faq/80040e14.asp#scenario3b
Looking at this line:
> INSERT INTO EmployeeSkillsTable(SkillID, IDNumber) VALUES(1,
> Bank10/26/009:19:09 AM)
should make that obvious - you are trying to insert a text string but using
the delimiters (none) for a numeric type field. At the same time though
you've called your field IDNumber, which is odd to say the least...
> cmdInsertStuff.Execute ,,adCmdText Or adExecuteNorecords
> This particular line of code, truthfully, I do not understand what it is
> doing even though it was told to me that it would work.
>
> Help and Clarification would be GREATLY APPRECIATED!
should be:
cmdInsertStuff.Execute ,,adCmdText+adExecuteNoRecords
If you look in the MS Platform SDK (you can download it from
http://www.microsoft.com/data/) then you'd know that the .execute method of
the ADO Connection object accepts the following parameters:
objConn.execute source, lngRecordsAffected, options
source is the command you want to execute (eg an SQL string),
lngRecordsAffected is the name of a variable you want to use to hold an
integer equal to the number of records affected by your command, and options
are optional parameters (eg adExecuteNoRecords tells ADO that your command
will not be returning any records, so don't bother waiting to see if any
come back).
Cheers
Ken
----- Original Message -----
From: "Leo Clayton" <claytonl@z...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, October 27, 2000 1:49 AM
Subject: [asp_databases] ODBC Drivers error '80040e14'
> I am trying to INSERT a/some RECORDS into an EMPTY TABLE that I created in
> ACCESS. These records are being created from data passed from a FORM. I
> know that the CORRECT data is there because I DISPLAY IT in a LOOP BEFORE
I
> even get to the part of the script where I actually CREATE AND WRITE the
> RECORD.
>
> I am working with the COMMAND OBJECT (which is still murky to me). The
> error occurs when I try to EXECUTE. Here is the code:
>
> <!--#include file="adovbs.inc"-->
> <!--#include file="DataStore.inc"-->
>
> <%
> 'This is a LOOP that will let me see the CHECKED CONTENTS OF MY FORM
> Dim item
> Dim intLoop 'as Integer
> Response.Write("////////// Form //////////<BR>" & vbCRLF)
> For Each Item in Request.Form
> For intLoop = 1 to Request.Form(Item).Count
> Response.Write(Item & " has a value of " &
> Request.Form(Item)(intLoop) & "<BR>")
> Next
> Next
> Response.Write("//////////End Form//////////<BR>" & vbCRLF)
> %>
>
>
> <%
> Dim cmdInsertStuff
> Dim sSQL, entry 'a SQL string and an Item variable. An item holds a
> specific form value
> Set cmdInsertStuff = Server.CreateObject("ADODB.Command") 'Create a
command
> object
> cmdInsertStuff.ActiveConnection = strConnect 'Set a connection to it
>
> For each entry in Request.Form("skill") 'loop through all checkboxes that
> are checked.
> 'generating Skill ID key
> LastName = Session("LastName")
> IDNumber = Session("IDNumber")
>
> s=Len(Lastname)
> a=1
> do while a<=s
> Textchar = Mid(Lastname,a,1)
> if Textchar = "'"then
> Newname=Newname
> else
> Newname=Newname & Textchar
> end if
> a=a+1
> loop
> IDLeft4 = Newname
>
> SkillID=Left(IDLeft4,4) & Date() & Time()
>
> sSQL = sSQL & "INSERT INTO EmployeeSkillsTable(SkillID, IDNumber)
> VALUES("&entry&", "&IDNumber&")"
> cmdInsertStuff.CommandText = sSQL 'set the statement you want to
> execute
> Response.Write(sSQL&"<br>")
> cmdInsertStuff.Execute ,,adCmdText Or adExecuteNorecords
> Next
>
> %>
>
> This is what was returned to my browser after the script was executed:
>
> ////////// Form //////////
> skill has a value of 1
> skill has a value of 6
> skill has a value of 7
> skill has a value of 27
> skill has a value of 50
> skill has a value of 115
> //////////End Form//////////
> INSERT INTO EmployeeSkillsTable(SkillID, IDNumber) VALUES(1,
> Bank10/26/009:19:09 AM)
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
> in query expression 'Bank10/26/009:19:09 AM'.
>
> /EmployeeMasterStuff/CreateSkillsRecord3.asp, line 48
>
> The ACTUAL LINE OF CODE (line 48) is:
>
> cmdInsertStuff.Execute ,,adCmdText Or adExecuteNorecords
>
> This particular line of code, truthfully, I do not understand what it is
> doing even though it was told to me that it would work.
>
> Help and Clarification would be GREATLY APPRECIATED!
|
|
|
View Cart
