asked on

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?

SOLUTION

Colosseo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

try check values of EpisodeID and VoteTypeID , i suspect they are or one of them is empty value...

blue-genie

ASKER

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

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

blue-genie

ASKER

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.

pkaleda

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&"'")

blue-genie

ASKER

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?

SOLUTION

pkaleda

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dan_neal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thefritterfatboy

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

blue-genie

ASKER

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.