Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9859
  • Last Modified:

Microsoft OLE DB Provider for SQL Server (0x80040E14)

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
blue-genie
Asked:
blue-genie
  • 4
  • 2
  • 2
  • +3
4 Solutions
 
ColosseoCommented:
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
 
Ryan ChongCommented:
try check values of EpisodeID and VoteTypeID , i suspect they are or one of them is empty value...
0
 
blue-genieAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Ryan ChongCommented:
try assign permission to your login user, so that it got permission to acccess table 'tbEpisode' in database 'Tsha Tsha'
0
 
blue-genieAuthor Commented:
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
 
pkaledaCommented:
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
 
blue-genieAuthor Commented:
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
 
pkaledaCommented:
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
 
dan_nealCommented:
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
 
thefritterfatboyCommented:
>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
 
blue-genieAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now