Solved

Microsoft OLE DB Provider for SQL Server (0x80040E14)

Posted on 2004-09-29
11
9,844 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 51

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
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.

 
LVL 51

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP Classic - Load test 2 54
Scheduled IIS .Net2 AppPools recycle and SQL connection Hangs 33 128
Need help editing script 3 80
Html CheckBox obtain Its Value 5 28
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 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/…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 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