Solved

Microsoft OLE DB Provider for SQL Server (0x80040E14)

Posted on 2004-09-29
11
9,849 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 52

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

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…
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/…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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