Error - "Query-based update failed because the row"

[Yarn]

If I run the code I mentioned I get the error. If I run any other pages that
update the database I get the same error.. these pages do not contain the
same code. If I wait a few minutes the error goes away the first time I
perform an update, after that I get the same error.

This was not happening before I started using an "insert" statement as
opposed to using a recordset, addnew etc.

So it seems my insert statement is leaving the database in an unhappy place.

The statement below generates the same error only after running the original
insert statement I posted-





Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open dsn
Set Rs = Server.CreateObject("ADODB.RecordSet")
sSQL = "SELECT * FROM "&calendar&" WHERE ID =
'"&cint(trim(request("ID")))&"'"
Rs.Open sSQL, Conn, 1, 3


fileName = request("file")

if right(fileName,1) = "^" then
newFileName = replace(fileName,"^","@")
rs("documents") = replace(rs("documents"),filename,newFileName)
rs.update
else
newFileName = replace(fileName,"@","^")
rs("documents") = replace(rs("documents"),filename,newFileName)
rs.update
end if

rs.close
conn.close
set rs = nothing
set conn = nothing




I hope that clarifies things!

Thank you.
















"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:u$uH6KpcDHA.1828@TK2MSFTNGP10.phx.gbl...

> Sorry - can you clarify?
>
> Running the code below does not cause an error, but runnng any *other*

page

> afterwards causes an error? That sounds a little bit absurd. What if the
> "other page" doesn't even have any database code in it?
>
> Cheers
> Ken
>
> "Yarn" <nope@noaddress.com> wrote in message
> news:%23PwXv$ocDHA.1872@TK2MSFTNGP12.phx.gbl...
> : Ok, my code was running fine until I made one change; instead of using a
> : "select" and and than an "update" I'm using an "insert" statement.
> :
> : Any page I run *after* running the code below result in the following
> error:
> :
> : "Error Type:
> : Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> : Query-based update failed because the row to update could not be found."
> :
> :
> :
> : sSQL = "SET NOCOUNT ON;"&_
> : "INSERT INTO "&calendar&_
> : " (appointmentstart, appointmentend, ownerID, clientID, comments,
> : privateComments)"&_
> : "VALUES
> :
>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

> :
>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> : )&"');"&_
> : "SELECT @@IDENTITY AS NewID;"
> :
> : Set loRs = Conn.Execute(sSQL)
> : llID = loRs.Fields("NewID").value
> : newID = llID
> :
> :
> :
> : It seems that the above insert statement is causing cursor problems or
> : something... not sure.
> :
> : Any suggestions would be great.
> :
> : TIA
> :
> :
> :
> :
> :
> :
> :
> :
> :
>
>

[Ken Schaefer]

Hi,

What database are you using?

Cheers
Ken


"Yarn" <nope@noaddress.com> wrote in message
news:%23aQa8VpcDHA.300@TK2MSFTNGP12.phx.gbl...
:
:
: If I run the code I mentioned I get the error. If I run any other pages
that
: update the database I get the same error.. these pages do not contain the
: same code. If I wait a few minutes the error goes away the first time I
: perform an update, after that I get the same error.
:
: This was not happening before I started using an "insert" statement as
: opposed to using a recordset, addnew etc.
:
: So it seems my insert statement is leaving the database in an unhappy
place.
:
: The statement below generates the same error only after running the
original
: insert statement I posted-
:
:
:
:
:
: Set Conn = Server.CreateObject("ADODB.Connection")
: Conn.Open dsn
: Set Rs = Server.CreateObject("ADODB.RecordSet")
: sSQL = "SELECT * FROM "&calendar&" WHERE ID =
: '"&cint(trim(request("ID")))&"'"
: Rs.Open sSQL, Conn, 1, 3
:
:
: fileName = request("file")
:
: if right(fileName,1) = "^" then
: newFileName = replace(fileName,"^","@")
: rs("documents") = replace(rs("documents"),filename,newFileName)
: rs.update
: else
: newFileName = replace(fileName,"@","^")
: rs("documents") = replace(rs("documents"),filename,newFileName)
: rs.update
: end if
:
: rs.close
: conn.close
: set rs = nothing
: set conn = nothing
:
:
:
:
: I hope that clarifies things!
:
: Thank you.
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:u$uH6KpcDHA.1828@TK2MSFTNGP10.phx.gbl...
: > Sorry - can you clarify?
: >
: > Running the code below does not cause an error, but runnng any *other*
: page
: > afterwards causes an error? That sounds a little bit absurd. What if the
: > "other page" doesn't even have any database code in it?
: >
: > Cheers
: > Ken
: >
: > "Yarn" <nope@noaddress.com> wrote in message
: > news:%23PwXv$ocDHA.1872@TK2MSFTNGP12.phx.gbl...
: > : Ok, my code was running fine until I made one change; instead of using
a
: > : "select" and and than an "update" I'm using an "insert" statement.
: > :
: > : Any page I run *after* running the code below result in the following
: > error:
: > :
: > : "Error Type:
: > : Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
: > : Query-based update failed because the row to update could not be
found."
: > :
: > :
: > :
: > : sSQL = "SET NOCOUNT ON;"&_
: > : "INSERT INTO "&calendar&_
: > : " (appointmentstart, appointmentend, ownerID, clientID, comments,
: > : privateComments)"&_
: > : "VALUES
: > :
: >
:
('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R
: > :
: >
:
equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"
: > : )&"');"&_
: > : "SELECT @@IDENTITY AS NewID;"
: > :
: > : Set loRs = Conn.Execute(sSQL)
: > : llID = loRs.Fields("NewID").value
: > : newID = llID
: > :
: > :
: > :
: > : It seems that the above insert statement is causing cursor problems or
: > : something... not sure.
: > :
: > : Any suggestions would be great.
: > :
: > : TIA
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: >
: >
:
:

[Yarn]

Whoops.

SQL Server 2000. Minor detail :-)





"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:uFe1RepcDHA.1744@TK2MSFTNGP12.phx.gbl...

> Hi,
>
> What database are you using?
>
> Cheers
> Ken
>
>
> "Yarn" <nope@noaddress.com> wrote in message
> news:%23aQa8VpcDHA.300@TK2MSFTNGP12.phx.gbl...
> :
> :
> : If I run the code I mentioned I get the error. If I run any other pages
> that
> : update the database I get the same error.. these pages do not contain

the

> : same code. If I wait a few minutes the error goes away the first time I
> : perform an update, after that I get the same error.
> :
> : This was not happening before I started using an "insert" statement as
> : opposed to using a recordset, addnew etc.
> :
> : So it seems my insert statement is leaving the database in an unhappy
> place.
> :
> : The statement below generates the same error only after running the
> original
> : insert statement I posted-
> :
> :
> :
> :
> :
> : Set Conn = Server.CreateObject("ADODB.Connection")
> : Conn.Open dsn
> : Set Rs = Server.CreateObject("ADODB.RecordSet")
> : sSQL = "SELECT * FROM "&calendar&" WHERE ID =
> : '"&cint(trim(request("ID")))&"'"
> : Rs.Open sSQL, Conn, 1, 3
> :
> :
> : fileName = request("file")
> :
> : if right(fileName,1) = "^" then
> : newFileName = replace(fileName,"^","@")
> : rs("documents") = replace(rs("documents"),filename,newFileName)
> : rs.update
> : else
> : newFileName = replace(fileName,"@","^")
> : rs("documents") = replace(rs("documents"),filename,newFileName)
> : rs.update
> : end if
> :
> : rs.close
> : conn.close
> : set rs = nothing
> : set conn = nothing
> :
> :
> :
> :
> : I hope that clarifies things!
> :
> : Thank you.
> :
> :
> :
> :
> :
> :
> :
> :
> :
> :
> :
> :
> :
> :
> :
> :
> : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : news:u$uH6KpcDHA.1828@TK2MSFTNGP10.phx.gbl...
> : > Sorry - can you clarify?
> : >
> : > Running the code below does not cause an error, but runnng any *other*
> : page
> : > afterwards causes an error? That sounds a little bit absurd. What if

the

> : > "other page" doesn't even have any database code in it?
> : >
> : > Cheers
> : > Ken
> : >
> : > "Yarn" <nope@noaddress.com> wrote in message
> : > news:%23PwXv$ocDHA.1872@TK2MSFTNGP12.phx.gbl...
> : > : Ok, my code was running fine until I made one change; instead of

using

> a
> : > : "select" and and than an "update" I'm using an "insert" statement.
> : > :
> : > : Any page I run *after* running the code below result in the

following

> : > error:
> : > :
> : > : "Error Type:
> : > : Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> : > : Query-based update failed because the row to update could not be
> found."
> : > :
> : > :
> : > :
> : > : sSQL = "SET NOCOUNT ON;"&_
> : > : "INSERT INTO "&calendar&_
> : > : " (appointmentstart, appointmentend, ownerID, clientID, comments,
> : > : privateComments)"&_
> : > : "VALUES
> : > :
> : >
> :
>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

> : > :
> : >
> :
>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> : > : )&"');"&_
> : > : "SELECT @@IDENTITY AS NewID;"
> : > :
> : > : Set loRs = Conn.Execute(sSQL)
> : > : llID = loRs.Fields("NewID").value
> : > : newID = llID
> : > :
> : > :
> : > :
> : > : It seems that the above insert statement is causing cursor problems

or

> : > : something... not sure.
> : > :
> : > : Any suggestions would be great.
> : > :
> : > : TIA
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : >
> : >
> :
> :
>
>

[Ken Schaefer]

hmm,

I have a couple of things - could you try them out please?

a) First, response.write your SQL statement so you can see if the statement
is correct (I notice that you're using a variable for your tablename, so I
think we want to make sure that everything's OK first):

<%
strSQL = _
"INSERT INTO "&calendar&_
"(appointmentstart, appointmentend, ownerID, clientID, comments,
privateComments)"&_
"VALUES

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"
)&"')"

Response.Write(strSQL)
Response.End
%>

b) Then try using two statements:


<%
strSQL = _
"INSERT INTO "&calendar&_
"(appointmentstart, appointmentend, ownerID, clientID, comments,
privateComments)"&_
"VALUES

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"
)&"')"

objConn.Execute(strSQL)

strSQL = _
"SELECT @@IDENTITY"

Set objRS = objConn.Execute(strSQL)
%>

just to make sure it's no a problem with your batch delimiter.

c) Lastly, to see if it's an ODBC problem, can you change from using ODBC to
OLEDB, and see if that fixes the problem?

[url]http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer[/url]

Cheers
Ken


"Yarn" <nope@noaddress.com> wrote in message
news:OnHBTjpcDHA.2024@TK2MSFTNGP12.phx.gbl...
:
: Whoops.
:
: SQL Server 2000. Minor detail :-)
:
:
:
:
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:uFe1RepcDHA.1744@TK2MSFTNGP12.phx.gbl...
: > Hi,
: >
: > What database are you using?
: >
: > Cheers
: > Ken
: >
: >
: > "Yarn" <nope@noaddress.com> wrote in message
: > news:%23aQa8VpcDHA.300@TK2MSFTNGP12.phx.gbl...
: > :
: > :
: > : If I run the code I mentioned I get the error. If I run any other
pages
: > that
: > : update the database I get the same error.. these pages do not contain
: the
: > : same code. If I wait a few minutes the error goes away the first time
I
: > : perform an update, after that I get the same error.
: > :
: > : This was not happening before I started using an "insert" statement as
: > : opposed to using a recordset, addnew etc.
: > :
: > : So it seems my insert statement is leaving the database in an unhappy
: > place.
: > :
: > : The statement below generates the same error only after running the
: > original
: > : insert statement I posted-
: > :
: > :
: > :
: > :
: > :
: > : Set Conn = Server.CreateObject("ADODB.Connection")
: > : Conn.Open dsn
: > : Set Rs = Server.CreateObject("ADODB.RecordSet")
: > : sSQL = "SELECT * FROM "&calendar&" WHERE ID =
: > : '"&cint(trim(request("ID")))&"'"
: > : Rs.Open sSQL, Conn, 1, 3
: > :
: > :
: > : fileName = request("file")
: > :
: > : if right(fileName,1) = "^" then
: > : newFileName = replace(fileName,"^","@")
: > : rs("documents") = replace(rs("documents"),filename,newFileName)
: > : rs.update
: > : else
: > : newFileName = replace(fileName,"@","^")
: > : rs("documents") = replace(rs("documents"),filename,newFileName)
: > : rs.update
: > : end if
: > :
: > : rs.close
: > : conn.close
: > : set rs = nothing
: > : set conn = nothing
: > :
: > :
: > :
: > :
: > : I hope that clarifies things!
: > :
: > : Thank you.
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > :
: > : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: > : news:u$uH6KpcDHA.1828@TK2MSFTNGP10.phx.gbl...
: > : > Sorry - can you clarify?
: > : >
: > : > Running the code below does not cause an error, but runnng any
*other*
: > : page
: > : > afterwards causes an error? That sounds a little bit absurd. What if
: the
: > : > "other page" doesn't even have any database code in it?
: > : >
: > : > Cheers
: > : > Ken
: > : >
: > : > "Yarn" <nope@noaddress.com> wrote in message
: > : > news:%23PwXv$ocDHA.1872@TK2MSFTNGP12.phx.gbl...
: > : > : Ok, my code was running fine until I made one change; instead of
: using
: > a
: > : > : "select" and and than an "update" I'm using an "insert" statement.
: > : > :
: > : > : Any page I run *after* running the code below result in the
: following
: > : > error:
: > : > :
: > : > : "Error Type:
: > : > : Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
: > : > : Query-based update failed because the row to update could not be
: > found."
: > : > :
: > : > :
: > : > :
: > : > : sSQL = "SET NOCOUNT ON;"&_
: > : > : "INSERT INTO "&calendar&_
: > : > : " (appointmentstart, appointmentend, ownerID, clientID,
comments,
: > : > : privateComments)"&_
: > : > : "VALUES
: > : > :
: > : >
: > :
: >
:
('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R
: > : > :
: > : >
: > :
: >
:
equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"
: > : > : )&"');"&_
: > : > : "SELECT @@IDENTITY AS NewID;"
: > : > :
: > : > : Set loRs = Conn.Execute(sSQL)
: > : > : llID = loRs.Fields("NewID").value
: > : > : newID = llID
: > : > :
: > : > :
: > : > :
: > : > : It seems that the above insert statement is causing cursor
problems
: or
: > : > : something... not sure.
: > : > :
: > : > : Any suggestions would be great.
: > : > :
: > : > : TIA
: > : > :
: > : > :
: > : > :
: > : > :
: > : > :
: > : > :
: > : > :
: > : > :
: > : > :
: > : >
: > : >
: > :
: > :
: >
: >
:
:

[Yarn]

Thank you!

Here is the before and after-


strSQL = "INSERT INTO "&calendar&_
"(appointmentstart, appointmentend, ownerID, clientID, comments,
privateComments)"&_

"VALUES('"&appointmentStart2&"','"&appointmentEnd2 &"','"&Request("ownerID")&
"','"&Request("clientID")&"','"&Request("comments" )&"','"&Request("privateCo
mments")&"')"
conn.Execute(strSQL)
strSQL = "SELECT @@IDENTITY as NewID"
Set loRs = Conn.Execute(strSQL)
llID = loRs.Fields("NewID").value
newID = llID
Set Rs = Nothing
Set loRs = Nothing
set conn = nothing


'sSQL = "SET NOCOUNT ON;"&_
'"INSERT INTO "&calendar&_
'" (appointmentstart, appointmentend, ownerID, clientID, comments,
privateComments)"&_
'"VALUES
('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R
equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"
)&"');"&_
"SELECT @@IDENTITY AS NewID;"
'Set loRs = Conn.Execute(sSQL)
'llID = loRs.Fields("NewID").value
'newID = llID
'Set Rs = Nothing
'Set loRs = Nothing
'set conn = nothing





So it seems that "nocount on" was causing the problem?
I'm new here, will you help me understand?

Thanks again for the help.
























"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:O8HLFtpcDHA.2112@TK2MSFTNGP10.phx.gbl...

> hmm,
>
> I have a couple of things - could you try them out please?
>
> a) First, response.write your SQL statement so you can see if the

statement

> is correct (I notice that you're using a variable for your tablename, so I
> think we want to make sure that everything's OK first):
>
> <%
> strSQL = _
> "INSERT INTO "&calendar&_
> "(appointmentstart, appointmentend, ownerID, clientID, comments,
> privateComments)"&_
> "VALUES
>
>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

>
>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> )&"')"
>
> Response.Write(strSQL)
> Response.End
> %>
>
> b) Then try using two statements:
>
>
> <%
> strSQL = _
> "INSERT INTO "&calendar&_
> "(appointmentstart, appointmentend, ownerID, clientID, comments,
> privateComments)"&_
> "VALUES
>
>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

>
>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> )&"')"
>
> objConn.Execute(strSQL)
>
> strSQL = _
> "SELECT @@IDENTITY"
>
> Set objRS = objConn.Execute(strSQL)
> %>
>
> just to make sure it's no a problem with your batch delimiter.
>
> c) Lastly, to see if it's an ODBC problem, can you change from using ODBC

to

> OLEDB, and see if that fixes the problem?
>
>

[url]http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDB[/url]
ProviderForSQLServer

>
> Cheers
> Ken
>
>
> "Yarn" <nope@noaddress.com> wrote in message
> news:OnHBTjpcDHA.2024@TK2MSFTNGP12.phx.gbl...
> :
> : Whoops.
> :
> : SQL Server 2000. Minor detail :-)
> :
> :
> :
> :
> :
> : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : news:uFe1RepcDHA.1744@TK2MSFTNGP12.phx.gbl...
> : > Hi,
> : >
> : > What database are you using?
> : >
> : > Cheers
> : > Ken
> : >
> : >
> : > "Yarn" <nope@noaddress.com> wrote in message
> : > news:%23aQa8VpcDHA.300@TK2MSFTNGP12.phx.gbl...
> : > :
> : > :
> : > : If I run the code I mentioned I get the error. If I run any other
> pages
> : > that
> : > : update the database I get the same error.. these pages do not

contain

> : the
> : > : same code. If I wait a few minutes the error goes away the first

time

> I
> : > : perform an update, after that I get the same error.
> : > :
> : > : This was not happening before I started using an "insert" statement

as

> : > : opposed to using a recordset, addnew etc.
> : > :
> : > : So it seems my insert statement is leaving the database in an

unhappy

> : > place.
> : > :
> : > : The statement below generates the same error only after running the
> : > original
> : > : insert statement I posted-
> : > :
> : > :
> : > :
> : > :
> : > :
> : > : Set Conn = Server.CreateObject("ADODB.Connection")
> : > : Conn.Open dsn
> : > : Set Rs = Server.CreateObject("ADODB.RecordSet")
> : > : sSQL = "SELECT * FROM "&calendar&" WHERE ID =
> : > : '"&cint(trim(request("ID")))&"'"
> : > : Rs.Open sSQL, Conn, 1, 3
> : > :
> : > :
> : > : fileName = request("file")
> : > :
> : > : if right(fileName,1) = "^" then
> : > : newFileName = replace(fileName,"^","@")
> : > : rs("documents") = replace(rs("documents"),filename,newFileName)
> : > : rs.update
> : > : else
> : > : newFileName = replace(fileName,"@","^")
> : > : rs("documents") = replace(rs("documents"),filename,newFileName)
> : > : rs.update
> : > : end if
> : > :
> : > : rs.close
> : > : conn.close
> : > : set rs = nothing
> : > : set conn = nothing
> : > :
> : > :
> : > :
> : > :
> : > : I hope that clarifies things!
> : > :
> : > : Thank you.
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > :
> : > : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : > : news:u$uH6KpcDHA.1828@TK2MSFTNGP10.phx.gbl...
> : > : > Sorry - can you clarify?
> : > : >
> : > : > Running the code below does not cause an error, but runnng any
> *other*
> : > : page
> : > : > afterwards causes an error? That sounds a little bit absurd. What

if

> : the
> : > : > "other page" doesn't even have any database code in it?
> : > : >
> : > : > Cheers
> : > : > Ken
> : > : >
> : > : > "Yarn" <nope@noaddress.com> wrote in message
> : > : > news:%23PwXv$ocDHA.1872@TK2MSFTNGP12.phx.gbl...
> : > : > : Ok, my code was running fine until I made one change; instead of
> : using
> : > a
> : > : > : "select" and and than an "update" I'm using an "insert"

statement.

> : > : > :
> : > : > : Any page I run *after* running the code below result in the
> : following
> : > : > error:
> : > : > :
> : > : > : "Error Type:
> : > : > : Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> : > : > : Query-based update failed because the row to update could not be
> : > found."
> : > : > :
> : > : > :
> : > : > :
> : > : > : sSQL = "SET NOCOUNT ON;"&_
> : > : > : "INSERT INTO "&calendar&_
> : > : > : " (appointmentstart, appointmentend, ownerID, clientID,
> comments,
> : > : > : privateComments)"&_
> : > : > : "VALUES
> : > : > :
> : > : >
> : > :
> : >
> :
>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

> : > : > :
> : > : >
> : > :
> : >
> :
>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> : > : > : )&"');"&_
> : > : > : "SELECT @@IDENTITY AS NewID;"
> : > : > :
> : > : > : Set loRs = Conn.Execute(sSQL)
> : > : > : llID = loRs.Fields("NewID").value
> : > : > : newID = llID
> : > : > :
> : > : > :
> : > : > :
> : > : > : It seems that the above insert statement is causing cursor
> problems
> : or
> : > : > : something... not sure.
> : > : > :
> : > : > : Any suggestions would be great.
> : > : > :
> : > : > : TIA
> : > : > :
> : > : > :
> : > : > :
> : > : > :
> : > : > :
> : > : > :
> : > : > :
> : > : > :
> : > : > :
> : > : >
> : > : >
> : > :
> : > :
> : >
> : >
> :
> :
>
>

[Yarn]

"SET NOCOUNT ON" is used to return the number rows affected.. I guess that
means it wan't the problem. Was it-

"SELECT @@IDENTITY AS NewID;"

??

I'm lost.









"Yarn" <nope@noaddress.com> wrote in message
news:#OciTMqcDHA.3620@TK2MSFTNGP11.phx.gbl...

>
> Thank you!
>
> Here is the before and after-
>
>
> strSQL = "INSERT INTO "&calendar&_
> "(appointmentstart, appointmentend, ownerID, clientID, comments,
> privateComments)"&_
>
>

"VALUES('"&appointmentStart2&"','"&appointmentEnd2 &"','"&Request("ownerID")&

>

"','"&Request("clientID")&"','"&Request("comments" )&"','"&Request("privateCo

> mments")&"')"
> conn.Execute(strSQL)
> strSQL = "SELECT @@IDENTITY as NewID"
> Set loRs = Conn.Execute(strSQL)
> llID = loRs.Fields("NewID").value
> newID = llID
> Set Rs = Nothing
> Set loRs = Nothing
> set conn = nothing
>
>
> 'sSQL = "SET NOCOUNT ON;"&_
> '"INSERT INTO "&calendar&_
> '" (appointmentstart, appointmentend, ownerID, clientID, comments,
> privateComments)"&_
> '"VALUES
>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> )&"');"&_
> "SELECT @@IDENTITY AS NewID;"
> 'Set loRs = Conn.Execute(sSQL)
> 'llID = loRs.Fields("NewID").value
> 'newID = llID
> 'Set Rs = Nothing
> 'Set loRs = Nothing
> 'set conn = nothing
>
>
>
>
>
> So it seems that "nocount on" was causing the problem?
> I'm new here, will you help me understand?
>
> Thanks again for the help.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> news:O8HLFtpcDHA.2112@TK2MSFTNGP10.phx.gbl...

> > hmm,
> >
> > I have a couple of things - could you try them out please?
> >
> > a) First, response.write your SQL statement so you can see if the

> statement

> > is correct (I notice that you're using a variable for your tablename, so

I

> > think we want to make sure that everything's OK first):
> >
> > <%
> > strSQL = _
> > "INSERT INTO "&calendar&_
> > "(appointmentstart, appointmentend, ownerID, clientID, comments,
> > privateComments)"&_
> > "VALUES
> >
> >

>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

> >
> >

>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> > )&"')"
> >
> > Response.Write(strSQL)
> > Response.End
> > %>
> >
> > b) Then try using two statements:
> >
> >
> > <%
> > strSQL = _
> > "INSERT INTO "&calendar&_
> > "(appointmentstart, appointmentend, ownerID, clientID, comments,
> > privateComments)"&_
> > "VALUES
> >
> >

>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

> >
> >

>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> > )&"')"
> >
> > objConn.Execute(strSQL)
> >
> > strSQL = _
> > "SELECT @@IDENTITY"
> >
> > Set objRS = objConn.Execute(strSQL)
> > %>
> >
> > just to make sure it's no a problem with your batch delimiter.
> >
> > c) Lastly, to see if it's an ODBC problem, can you change from using

ODBC

> to

> > OLEDB, and see if that fixes the problem?
> >
> >

>

[url]http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDB[/url]

> ProviderForSQLServer

> >
> > Cheers
> > Ken
> >
> >
> > "Yarn" <nope@noaddress.com> wrote in message
> > news:OnHBTjpcDHA.2024@TK2MSFTNGP12.phx.gbl...
> > :
> > : Whoops.
> > :
> > : SQL Server 2000. Minor detail :-)
> > :
> > :
> > :
> > :
> > :
> > : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> > : news:uFe1RepcDHA.1744@TK2MSFTNGP12.phx.gbl...
> > : > Hi,
> > : >
> > : > What database are you using?
> > : >
> > : > Cheers
> > : > Ken
> > : >
> > : >
> > : > "Yarn" <nope@noaddress.com> wrote in message
> > : > news:%23aQa8VpcDHA.300@TK2MSFTNGP12.phx.gbl...
> > : > :
> > : > :
> > : > : If I run the code I mentioned I get the error. If I run any other
> > pages
> > : > that
> > : > : update the database I get the same error.. these pages do not

> contain

> > : the
> > : > : same code. If I wait a few minutes the error goes away the first

> time

> > I
> > : > : perform an update, after that I get the same error.
> > : > :
> > : > : This was not happening before I started using an "insert"

statement

> as

> > : > : opposed to using a recordset, addnew etc.
> > : > :
> > : > : So it seems my insert statement is leaving the database in an

> unhappy

> > : > place.
> > : > :
> > : > : The statement below generates the same error only after running

the

> > : > original
> > : > : insert statement I posted-
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > : Set Conn = Server.CreateObject("ADODB.Connection")
> > : > : Conn.Open dsn
> > : > : Set Rs = Server.CreateObject("ADODB.RecordSet")
> > : > : sSQL = "SELECT * FROM "&calendar&" WHERE ID =
> > : > : '"&cint(trim(request("ID")))&"'"
> > : > : Rs.Open sSQL, Conn, 1, 3
> > : > :
> > : > :
> > : > : fileName = request("file")
> > : > :
> > : > : if right(fileName,1) = "^" then
> > : > : newFileName = replace(fileName,"^","@")
> > : > : rs("documents") = replace(rs("documents"),filename,newFileName)
> > : > : rs.update
> > : > : else
> > : > : newFileName = replace(fileName,"@","^")
> > : > : rs("documents") = replace(rs("documents"),filename,newFileName)
> > : > : rs.update
> > : > : end if
> > : > :
> > : > : rs.close
> > : > : conn.close
> > : > : set rs = nothing
> > : > : set conn = nothing
> > : > :
> > : > :
> > : > :
> > : > :
> > : > : I hope that clarifies things!
> > : > :
> > : > : Thank you.
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > :
> > : > : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> > : > : news:u$uH6KpcDHA.1828@TK2MSFTNGP10.phx.gbl...
> > : > : > Sorry - can you clarify?
> > : > : >
> > : > : > Running the code below does not cause an error, but runnng any
> > *other*
> > : > : page
> > : > : > afterwards causes an error? That sounds a little bit absurd.

What

> if

> > : the
> > : > : > "other page" doesn't even have any database code in it?
> > : > : >
> > : > : > Cheers
> > : > : > Ken
> > : > : >
> > : > : > "Yarn" <nope@noaddress.com> wrote in message
> > : > : > news:%23PwXv$ocDHA.1872@TK2MSFTNGP12.phx.gbl...
> > : > : > : Ok, my code was running fine until I made one change; instead

of

> > : using
> > : > a
> > : > : > : "select" and and than an "update" I'm using an "insert"

> statement.

> > : > : > :
> > : > : > : Any page I run *after* running the code below result in the
> > : following
> > : > : > error:
> > : > : > :
> > : > : > : "Error Type:
> > : > : > : Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> > : > : > : Query-based update failed because the row to update could not

be

> > : > found."
> > : > : > :
> > : > : > :
> > : > : > :
> > : > : > : sSQL = "SET NOCOUNT ON;"&_
> > : > : > : "INSERT INTO "&calendar&_
> > : > : > : " (appointmentstart, appointmentend, ownerID, clientID,
> > comments,
> > : > : > : privateComments)"&_
> > : > : > : "VALUES
> > : > : > :
> > : > : >
> > : > :
> > : >
> > :
> >

>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

> > : > : > :
> > : > : >
> > : > :
> > : >
> > :
> >

>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> > : > : > : )&"');"&_
> > : > : > : "SELECT @@IDENTITY AS NewID;"
> > : > : > :
> > : > : > : Set loRs = Conn.Execute(sSQL)
> > : > : > : llID = loRs.Fields("NewID").value
> > : > : > : newID = llID
> > : > : > :
> > : > : > :
> > : > : > :
> > : > : > : It seems that the above insert statement is causing cursor
> > problems
> > : or
> > : > : > : something... not sure.
> > : > : > :
> > : > : > : Any suggestions would be great.
> > : > : > :
> > : > : > : TIA
> > : > : > :
> > : > : > :
> > : > : > :
> > : > : > :
> > : > : > :
> > : > : > :
> > : > : > :
> > : > : > :
> > : > : > :
> > : > : >
> > : > : >
> > : > :
> > : > :
> > : >
> > : >
> > :
> > :
> >
> >

>
>

[Yarn]

>>>>a) use a sproc to do this (Stored Procudure)

I'd love to use SP's, just haven't gotten around to learning them yet.. seem
to be a little trickier than plain ol' SQL.

>>>>>> b) do not directly place Request.Form/QueryString() data directly

into your

I usually just *replace* single qoutes with two single qoutes.. is that good
enough? (Replace(Chars, "'", "''"))

>>>>>> c) use the OLEDB Provider, not ODBC Driver

My DSN-

DSN = "Driver={SQL
Server};Server=127.0.0.1;Database=mydb;UID=usernam e;PWD=password"

Is OLE faster? More efficient?



Thank you.





"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:On9dgtqcDHA.1828@TK2MSFTNGP10.phx.gbl...

> I'm not sure what the problem is exactly. None of the queries that you had
> should really have generated any problems. However, you usually don't use
> SET NOCOUNT ON using the ODBC Driver because the "xx records affected"
> message is automatically suppressed by the ODBC driver. Odd how splitting

it

> up into distinct queries has solved the problem...
>
> That said, you really should:
> a) use a sproc to do this (Stored Procudure)
> b) do not directly place Request.Form/QueryString() data directly into

your

> SQL statement, because you're vulnerable to SQL Injection attacks
> c) use the OLEDB Provider, not ODBC Driver
>
> Cheers
> Ken
>
>
> "Yarn" <nope@noaddress.com> wrote in message
> news:%239ojvOqcDHA.616@TK2MSFTNGP11.phx.gbl...
> :
> : "SET NOCOUNT ON" is used to return the number rows affected.. I guess

that

> : means it wan't the problem. Was it-
> :
> : "SELECT @@IDENTITY AS NewID;"
> :
> : ??
> :
> : I'm lost.
> :
> :
> :
> :
> :
> :
> :
> :
> :
> : "Yarn" <nope@noaddress.com> wrote in message
> : news:#OciTMqcDHA.3620@TK2MSFTNGP11.phx.gbl...
> : >
> : > Thank you!
> : >
> : > Here is the before and after-
> : >
> : >
> : > strSQL = "INSERT INTO "&calendar&_
> : > "(appointmentstart, appointmentend, ownerID, clientID, comments,
> : > privateComments)"&_
> : >
> : >
> :
>

"VALUES('"&appointmentStart2&"','"&appointmentEnd2 &"','"&Request("ownerID")&

> : >
> :
>

"','"&Request("clientID")&"','"&Request("comments" )&"','"&Request("privateCo

> : > mments")&"')"
> : > conn.Execute(strSQL)
> : > strSQL = "SELECT @@IDENTITY as NewID"
> : > Set loRs = Conn.Execute(strSQL)
> : > llID = loRs.Fields("NewID").value
> : > newID = llID
> : > Set Rs = Nothing
> : > Set loRs = Nothing
> : > set conn = nothing
> : >
> : >
> : > 'sSQL = "SET NOCOUNT ON;"&_
> : > '"INSERT INTO "&calendar&_
> : > '" (appointmentstart, appointmentend, ownerID, clientID, comments,
> : > privateComments)"&_
> : > '"VALUES
> : >
> :
>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

> : >
> :
>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> : > )&"');"&_
> : > "SELECT @@IDENTITY AS NewID;"
> : > 'Set loRs = Conn.Execute(sSQL)
> : > 'llID = loRs.Fields("NewID").value
> : > 'newID = llID
> : > 'Set Rs = Nothing
> : > 'Set loRs = Nothing
> : > 'set conn = nothing
> : >
> : >
> : >
> : >
> : >
> : > So it seems that "nocount on" was causing the problem?
> : > I'm new here, will you help me understand?
> : >
> : > Thanks again for the help.
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : >
> : > "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : > news:O8HLFtpcDHA.2112@TK2MSFTNGP10.phx.gbl...
> : > > hmm,
> : > >
> : > > I have a couple of things - could you try them out please?
> : > >
> : > > a) First, response.write your SQL statement so you can see if the
> : > statement
> : > > is correct (I notice that you're using a variable for your

tablename,

> so
> : I
> : > > think we want to make sure that everything's OK first):
> : > >
> : > > <%
> : > > strSQL = _
> : > > "INSERT INTO "&calendar&_
> : > > "(appointmentstart, appointmentend, ownerID, clientID, comments,
> : > > privateComments)"&_
> : > > "VALUES
> : > >
> : > >
> : >
> :
>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

> : > >
> : > >
> : >
> :
>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> : > > )&"')"
> : > >
> : > > Response.Write(strSQL)
> : > > Response.End
> : > > %>
> : > >
> : > > b) Then try using two statements:
> : > >
> : > >
> : > > <%
> : > > strSQL = _
> : > > "INSERT INTO "&calendar&_
> : > > "(appointmentstart, appointmentend, ownerID, clientID, comments,
> : > > privateComments)"&_
> : > > "VALUES
> : > >
> : > >
> : >
> :
>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

> : > >
> : > >
> : >
> :
>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> : > > )&"')"
> : > >
> : > > objConn.Execute(strSQL)
> : > >
> : > > strSQL = _
> : > > "SELECT @@IDENTITY"
> : > >
> : > > Set objRS = objConn.Execute(strSQL)
> : > > %>
> : > >
> : > > just to make sure it's no a problem with your batch delimiter.
> : > >
> : > > c) Lastly, to see if it's an ODBC problem, can you change from using
> : ODBC
> : > to
> : > > OLEDB, and see if that fixes the problem?
> : > >
> : > >
> : >
> :
>

[url]http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDB[/url]

> : > ProviderForSQLServer
> : > >
> : > > Cheers
> : > > Ken
> : > >
> : > >
> : > > "Yarn" <nope@noaddress.com> wrote in message
> : > > news:OnHBTjpcDHA.2024@TK2MSFTNGP12.phx.gbl...
> : > > :
> : > > : Whoops.
> : > > :
> : > > : SQL Server 2000. Minor detail :-)
> : > > :
> : > > :
> : > > :
> : > > :
> : > > :
> : > > : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : > > : news:uFe1RepcDHA.1744@TK2MSFTNGP12.phx.gbl...
> : > > : > Hi,
> : > > : >
> : > > : > What database are you using?
> : > > : >
> : > > : > Cheers
> : > > : > Ken
> : > > : >
> : > > : >
> : > > : > "Yarn" <nope@noaddress.com> wrote in message
> : > > : > news:%23aQa8VpcDHA.300@TK2MSFTNGP12.phx.gbl...
> : > > : > :
> : > > : > :
> : > > : > : If I run the code I mentioned I get the error. If I run any
> other
> : > > pages
> : > > : > that
> : > > : > : update the database I get the same error.. these pages do not
> : > contain
> : > > : the
> : > > : > : same code. If I wait a few minutes the error goes away the

first

> : > time
> : > > I
> : > > : > : perform an update, after that I get the same error.
> : > > : > :
> : > > : > : This was not happening before I started using an "insert"
> : statement
> : > as
> : > > : > : opposed to using a recordset, addnew etc.
> : > > : > :
> : > > : > : So it seems my insert statement is leaving the database in an
> : > unhappy
> : > > : > place.
> : > > : > :
> : > > : > : The statement below generates the same error only after

running

> : the
> : > > : > original
> : > > : > : insert statement I posted-
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > : Set Conn = Server.CreateObject("ADODB.Connection")
> : > > : > : Conn.Open dsn
> : > > : > : Set Rs = Server.CreateObject("ADODB.RecordSet")
> : > > : > : sSQL = "SELECT * FROM "&calendar&" WHERE ID =
> : > > : > : '"&cint(trim(request("ID")))&"'"
> : > > : > : Rs.Open sSQL, Conn, 1, 3
> : > > : > :
> : > > : > :
> : > > : > : fileName = request("file")
> : > > : > :
> : > > : > : if right(fileName,1) = "^" then
> : > > : > : newFileName = replace(fileName,"^","@")
> : > > : > : rs("documents") =
> replace(rs("documents"),filename,newFileName)
> : > > : > : rs.update
> : > > : > : else
> : > > : > : newFileName = replace(fileName,"@","^")
> : > > : > : rs("documents") =
> replace(rs("documents"),filename,newFileName)
> : > > : > : rs.update
> : > > : > : end if
> : > > : > :
> : > > : > : rs.close
> : > > : > : conn.close
> : > > : > : set rs = nothing
> : > > : > : set conn = nothing
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > : I hope that clarifies things!
> : > > : > :
> : > > : > : Thank you.
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > :
> : > > : > : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in

message

> : > > : > : news:u$uH6KpcDHA.1828@TK2MSFTNGP10.phx.gbl...
> : > > : > : > Sorry - can you clarify?
> : > > : > : >
> : > > : > : > Running the code below does not cause an error, but runnng

any

> : > > *other*
> : > > : > : page
> : > > : > : > afterwards causes an error? That sounds a little bit absurd.
> : What
> : > if
> : > > : the
> : > > : > : > "other page" doesn't even have any database code in it?
> : > > : > : >
> : > > : > : > Cheers
> : > > : > : > Ken
> : > > : > : >
> : > > : > : > "Yarn" <nope@noaddress.com> wrote in message
> : > > : > : > news:%23PwXv$ocDHA.1872@TK2MSFTNGP12.phx.gbl...
> : > > : > : > : Ok, my code was running fine until I made one change;
> instead
> : of
> : > > : using
> : > > : > a
> : > > : > : > : "select" and and than an "update" I'm using an "insert"
> : > statement.
> : > > : > : > :
> : > > : > : > : Any page I run *after* running the code below result in

the

> : > > : following
> : > > : > : > error:
> : > > : > : > :
> : > > : > : > : "Error Type:
> : > > : > : > : Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> : > > : > : > : Query-based update failed because the row to update could
> not
> : be
> : > > : > found."
> : > > : > : > :
> : > > : > : > :
> : > > : > : > :
> : > > : > : > : sSQL = "SET NOCOUNT ON;"&_
> : > > : > : > : "INSERT INTO "&calendar&_
> : > > : > : > : " (appointmentstart, appointmentend, ownerID, clientID,
> : > > comments,
> : > > : > : > : privateComments)"&_
> : > > : > : > : "VALUES
> : > > : > : > :
> : > > : > : >
> : > > : > :
> : > > : >
> : > > :
> : > >
> : >
> :
>

('"&appointmentStart2&"','"&appointmentEnd2&"','"& Request("ownerID")&"','"&R

> : > > : > : > :
> : > > : > : >
> : > > : > :
> : > > : >
> : > > :
> : > >
> : >
> :
>

equest("clientID")&"','"&Request("comments")&"','" &Request("privateComments"

> : > > : > : > : )&"');"&_
> : > > : > : > : "SELECT @@IDENTITY AS NewID;"
> : > > : > : > :
> : > > : > : > : Set loRs = Conn.Execute(sSQL)
> : > > : > : > : llID = loRs.Fields("NewID").value
> : > > : > : > : newID = llID
> : > > : > : > :
> : > > : > : > :
> : > > : > : > :
> : > > : > : > : It seems that the above insert statement is causing cursor
> : > > problems
> : > > : or
> : > > : > : > : something... not sure.
> : > > : > : > :
> : > > : > : > : Any suggestions would be great.
> : > > : > : > :
> : > > : > : > : TIA
> : > > : > : > :
> : > > : > : > :
> : > > : > : > :
> : > > : > : > :
> : > > : > : > :
> : > > : > : > :
> : > > : > : > :
> : > > : > : > :
> : > > : > : > :
> : > > : > : >
> : > > : > : >
> : > > : > :
> : > > : > :
> : > > : >
> : > > : >
> : > > :
> : > > :
> : > >
> : > >
> : >
> : >
> :
> :
>
>

[Bob Barrows]

Yarn wrote:
not ODBC Driver

>
> My DSN-
>
> DSN = "Driver={SQL
> Server};Server=127.0.0.1;Database=mydb;UID=usernam e;PWD=password"
>
> Is OLE faster? More efficient?
>

It's "OLEDB". Yes and Yes