Solved

Microsoft OLE DB Provider for SQL Server (0x80040E14)

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

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
 
LVL 49

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now