Solved

Microsoft OLE DB Provider for SQL Server (0x80040E14)

Posted on 2004-09-29
11
9,841 Views
Last Modified: 2011-08-18
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Incorrect syntax near the keyword 'AND'.
/SABC/charactervoting.asp, line 23

line 23 is :

set rs=conn.execute("UPDATE tbEpisode SET Counter = Counter + 1 WHERE ID = " & EpisodeID & " AND VoteTypeID = " & VoteTypeID & "")

whats up with this?
0
Comment
Question by:blue-genie
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 15

Assisted Solution

by:Colosseo
Colosseo earned 200 total points
ID: 12177862
Hi blue

what data types are ID and VoteTypeID.. integer or string?

you might have to use

set rs=conn.execute("UPDATE tbEpisode SET Counter = Counter + 1 WHERE ID = '" & EpisodeID & "' AND VoteTypeID = '" & VoteTypeID & "'")

Cheers

Scott
0
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 12177900
try check values of EpisodeID and VoteTypeID , i suspect they are or one of them is empty value...
0
 
LVL 39

Author Comment

by:blue-genie
ID: 12177916
ok, changed it to integers, now my error is

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E09)
SELECT permission denied on object 'tbEpisode', database 'Tsha Tsha', owner 'dbo'.
/SABC/charactervoting.asp, line 21

ps, i'm using MSSQL Server( for the first time ) - is this now a server problem,no longer asp?

blu
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 50

Accepted Solution

by:
Ryan Chong earned 250 total points
ID: 12177979
try assign permission to your login user, so that it got permission to acccess table 'tbEpisode' in database 'Tsha Tsha'
0
 
LVL 39

Author Comment

by:blue-genie
ID: 12178405
ok, now i have

set rs=conn.execute("UPDATE tbEpisode SET Counter = Counter + 1 WHERE ID = "&EpisodeID&" AND VoteTypeID = "&VoteTypeID&"")
set rs2=conn.execute("UPDATE tbCharacter SET ID = "&CharacterID&", Name = '"&CharacterName&"', Counter = Counter + 1 WHERE EpisodeID = " &EpisodeID& " AND Sequence = "&Sequence&" AND VoteTypeID = " &VoteTypeID&"")
response.write "success=true"

no error messages,
however, rs gets executed, but not rs2
can anyone think why?
I've checked that field names are correct, data types are correct.

blu.
0
 
LVL 6

Expert Comment

by:pkaleda
ID: 12178565
I suspect it shoudl read like this since some are text:

set rs2=conn.execute("UPDATE tbCharacter SET ID = '"&CharacterID&"', Name = '"&CharacterName&"', Counter = Counter + 1 WHERE EpisodeID = '" &EpisodeID& "' AND Sequence = '"&Sequence&"' AND VoteTypeID = '" &VoteTypeID&"'")
0
 
LVL 39

Author Comment

by:blue-genie
ID: 12178579
but surely, i know what datatypes i'm returning and sending, they should all be numbers with exception to name/charactername, so why would I want to put them all as strings?
0
 
LVL 6

Assisted Solution

by:pkaleda
pkaleda earned 25 total points
ID: 12178607
I would do this:

Response.write("set rs2=conn.execute("UPDATE tbCharacter SET ID = "&CharacterID&", Name = '"&CharacterName&"', Counter = Counter + 1 WHERE EpisodeID = " &EpisodeID& " AND Sequence = "&Sequence&" AND VoteTypeID = " &VoteTypeID&"")

Copy the line that is written into Query Analyser and run it and see if it works there.  This is a good debug method and will tell you what your exact issue is.  If it runs ok, then it is an ASP issue, if it bombs there, you can make your changed based on what it gives as an error.

PK


0
 
LVL 9

Assisted Solution

by:dan_neal
dan_neal earned 25 total points
ID: 12178879
try running the rs2 as an open command on the recordset instead of an execute command on the connection like this:

set rs2 = createobject("adodb.recordset")
rs2.open "UPDATE tbCharacter SET ID = "&CharacterID&", Name = '"&CharacterName&"', Counter = Counter + 1 WHERE EpisodeID = " &EpisodeID& " AND Sequence = "&Sequence&" AND VoteTypeID = " &VoteTypeID, conn, 1

doing this will let you know if an error in your sql string.

I've seen in some of my own work that the execute command doesn't throw an error if there is a problem with the command itself.  It acts as though the the command executed successfully until you try to access the data.

Let us know
0
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 12179712
>set rs2 = createobject("adodb.recordset")
rs2.open "UPDATE tbCharacter SET ID = "&CharacterID&", Name = '"&CharacterName&"', Counter = Counter + 1 WHERE EpisodeID = " &EpisodeID& " AND Sequence = "&Sequence&" AND VoteTypeID = " &VoteTypeID, conn, 1 <<

This should be the way you ALWAYS execute recordsets on SQL server. You can control whether the recordset is local (Held by IIS) or remote (held by SQL server).

before calling the .open command set this:

rs2.CursorLocation = 3

so your recordset is local. Then rs2.recordCount will give you a count of your records without you having to scroll through. There are a few performance penalties for working with remote recordsets in most cases, so I tend to use client-side ones.
0
 
LVL 39

Author Comment

by:blue-genie
ID: 12179811
fritter... what you said there made no sense to me whatsoever.

however, i managed to get someone who actually knows asp to rewrite the code and so far, all seems well.

i implemented various changes from each of the earlier posts as they made me think of something to try which at the end resolved the problem.
especially about the permissions.

thanks all for your prompt and expert advice.
blu.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

803 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