|
 |
asp_databases thread: Does Row Exist Problem
Message #1 by Gregory_Griffiths@c... on Wed, 29 Nov 2000 12:53:16 +0000
|
|
I am trying to check for existance of a row in my database using :
' execute the SQL
set results = dbConn.execute("SELECT claimstatus FROM
form_cclive_header WHERE claimnum=" & claimnum)
' put the data into a variable
for each oField in results.fields
claimstatus = oField.value & ""
next
results.close
if (len(Cstr(claimstatus))>0) then
response.write("WORKS " & claimstatus)
else
response.write("FAILED")
end if
This works fine if the row exists, but I get the following if it does
not :
ADODB.Field error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted; the
operation requested by the application requires a current record.
/scripts/gregtest.asp, line 70
What I am trying to achieve is to determine if the row exists, if it
does then I use an UPDATE statement, otherwise I use an insert.
Does anyone have a solution or a better approach ?
Message #2 by Imar Spaanjaars <Imar@S...> on Wed, 29 Nov 2000 14:17:35 +0100
|
|
You can use the EOF or BOF property to check if the recordset contains a
record. .EOF will return true if there are no records, or when you are at
the last record.
Here is an example:
' execute the SQL
Dim rsResults ' as Recordset
set rsResults = dbConn.execute("SELECT claimstatus FROM form_cclive_header
WHERE claimnum=" & claimnum)
if rsResults.EOF then
' Recordset is empty.
Response.Write("No records found")
Response.Write("FAILED")
else
' The record does exist. Do something with it
Since you only select one field (claimstatus) there is no need to
loop through all the fields.
' put the data into a variable 'remmed out
' for each oField in results.fields 'remmed out
' claimstatus = oField.value & ""
' next
claimstatus = rsResults(0)
' or you can use:
claimstatus = rsResults("claimstatus")
end if
rsResults.close
HtH
Imar
At 01:21 PM 11/29/2000 -0800, you wrote:
>I am trying to check for existance of a row in my database using :
>
>' execute the SQL
>set results = dbConn.execute("SELECT claimstatus FROM
>form_cclive_header WHERE claimnum=" & claimnum)
>
>' put the data into a variable
>for each oField in results.fields
> claimstatus = oField.value & ""
>next
>
>results.close
>
>if (len(Cstr(claimstatus))>0) then
> response.write("WORKS " & claimstatus)
>else
> response.write("FAILED")
>end if
>
>This works fine if the row exists, but I get the following if it does
>not :
>
>ADODB.Field error '800a0bcd'
>
>Either BOF or EOF is True, or the current record has been deleted; the
>operation requested by the application requires a current record.
>
>/scripts/gregtest.asp, line 70
>
>What I am trying to achieve is to determine if the row exists, if it
>does then I use an UPDATE statement, otherwise I use an insert.
>
>Does anyone have a solution or a better approach ?
>
>
Message #3 by Stephane_Dattenny@D... on Wed, 29 Nov 2000 08:51:52 -0600
|
|
To test the existance, do like this:
' execute the SQL
set results = dbConn.execute("SELECT claimstatus FROM
form_cclive_header WHERE claimnum=" & claimnum)
if results.EOF then
' The recordset is empty : the row didn't exist
response.write("NO_ROW")
else
' put the data into a variable
for each oField in results.fields
claimstatus = oField.value & ""
next
results.close
response.write("WORKS " & claimstatus)
end if
results.close
set results = nothing
Best regards / Cordialement
Stephane Dattenny
Dell Computers - EMEA IT - VB and Web developer
Phone: +33 (0)4 99 75 49 88
-----Original Message-----
From: Gregory_Griffiths@c...
[mailto:Gregory_Griffiths@c...]
Sent: 29 November 2000 22:21
To: ASP Databases
Subject: [asp_databases] Does Row Exist Problem
I am trying to check for existance of a row in my database using :
' execute the SQL
set results = dbConn.execute("SELECT claimstatus FROM
form_cclive_header WHERE claimnum=" & claimnum)
' put the data into a variable
for each oField in results.fields
claimstatus = oField.value & ""
next
results.close
if (len(Cstr(claimstatus))>0) then
response.write("WORKS " & claimstatus)
else
response.write("FAILED")
end if
This works fine if the row exists, but I get the following if it does
not :
ADODB.Field error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted; the
operation requested by the application requires a current record.
/scripts/gregtest.asp, line 70
What I am trying to achieve is to determine if the row exists, if it
does then I use an UPDATE statement, otherwise I use an insert.
Does anyone have a solution or a better approach ?
Message #4 by Robert Chartier <rchartierh@a...> on Wed, 29 Nov 2000 10:16:49 -0500
|
|
what out with the below code...if, after just attempting to open a
recordset, it tests to be .EOF = TRUE, then the recordset is NOT
opened..thus the .CLOSE will have to only exist in the ELSE clause (in the
example below)....attempting to close a recordset that is not open will
generate an error.
(just move the "rsResults.close" line up into the "ELSE" block)
dont forget to destroy your obejcts also. (set rsResults = nothing, etc...)
At 08:17 AM 11/29/00, you wrote:
>You can use the EOF or BOF property to check if the recordset contains a
>record. .EOF will return true if there are no records, or when you are at
>the last record.
>
>Here is an example:
>
>' execute the SQL
>Dim rsResults ' as Recordset
>set rsResults = dbConn.execute("SELECT claimstatus FROM form_cclive_header
>WHERE claimnum=" & claimnum)
>
>if rsResults.EOF then
> ' Recordset is empty.
> Response.Write("No records found")
> Response.Write("FAILED")
>else
> ' The record does exist. Do something with it
> Since you only select one field (claimstatus) there is no need to
> loop through all the fields.
> ' put the data into a variable 'remmed out
> ' for each oField in results.fields 'remmed out
> ' claimstatus = oField.value & ""
> ' next
> claimstatus = rsResults(0)
> ' or you can use:
> claimstatus = rsResults("claimstatus")
>end if
>rsResults.close
>
>HtH
>
>Imar
>
>
>
>
>
>At 01:21 PM 11/29/2000 -0800, you wrote:
>>I am trying to check for existance of a row in my database using :
>>
>>' execute the SQL
>>set results = dbConn.execute("SELECT claimstatus FROM
>>form_cclive_header WHERE claimnum=" & claimnum)
>>
>>' put the data into a variable
>>for each oField in results.fields
>> claimstatus = oField.value & ""
>>next
>>
>>results.close
>>
>>if (len(Cstr(claimstatus))>0) then
>> response.write("WORKS " & claimstatus)
>>else
>> response.write("FAILED")
>>end if
>>
>>This works fine if the row exists, but I get the following if it does
>>not :
>>
>>ADODB.Field error '800a0bcd'
>>
>>Either BOF or EOF is True, or the current record has been deleted; the
>>operation requested by the application requires a current record.
>>
>>/scripts/gregtest.asp, line 70
>>
>>What I am trying to achieve is to determine if the row exists, if it
>>does then I use an UPDATE statement, otherwise I use an insert.
>>
>>Does anyone have a solution or a better approach ?
>>
>
Robert Chartier
Author, AspFree.com
xxx-xxx-xxxx
rchartierh@a...
http://www.aspfree.com/devlinks
http://www.aspfree.com/authors/robert
http://www.aspalliance.com/nothingmn
Message #5 by Gregory_Griffiths@c... on Wed, 29 Nov 2000 16:38:12 +0000
|
|
Thanks for everyones help on this, go the problem sorted now.
> -----Original Message-----
> From: Stephane_Dattenny@D... [mailto:Stephane_Dattenny@D...]
> Sent: 29 November 2000 14:52
> To: asp_databases@p...
> Subject: [asp_databases] RE: Does Row Exist Problem
>
>
> To test the existance, do like this:
>
> ' execute the SQL
> set results = dbConn.execute("SELECT claimstatus FROM
> form_cclive_header WHERE claimnum=" & claimnum)
>
> if results.EOF then
> ' The recordset is empty : the row didn't exist
> response.write("NO_ROW")
> else
> ' put the data into a variable
> for each oField in results.fields
> claimstatus = oField.value & ""
> next
>
> results.close
>
> response.write("WORKS " & claimstatus)
> end if
> results.close
> set results = nothing
>
> Best regards / Cordialement
>
> Stephane Dattenny
> Dell Computers - EMEA IT - VB and Web developer
> Phone: +33 (0)4 99 75 49 88
>
>
>
>
> -----Original Message-----
> From: Gregory_Griffiths@c...
> [mailto:Gregory_Griffiths@c...]
> Sent: 29 November 2000 22:21
> To: ASP Databases
> Subject: [asp_databases] Does Row Exist Problem
>
>
> I am trying to check for existance of a row in my database using :
>
> ' execute the SQL
> set results = dbConn.execute("SELECT claimstatus FROM
> form_cclive_header WHERE claimnum=" & claimnum)
>
> ' put the data into a variable
> for each oField in results.fields
> claimstatus = oField.value & ""
> next
>
> results.close
>
> if (len(Cstr(claimstatus))>0) then
> response.write("WORKS " & claimstatus)
> else
> response.write("FAILED")
> end if
>
> This works fine if the row exists, but I get the following if it does
> not :
>
> ADODB.Field error '800a0bcd'
>
> Either BOF or EOF is True, or the current record has been
> deleted; the
> operation requested by the application requires a current record.
>
> /scripts/gregtest.asp, line 70
>
> What I am trying to achieve is to determine if the row exists, if it
> does then I use an UPDATE statement, otherwise I use an insert.
>
> Does anyone have a solution or a better approach ?
>
>
>
Message #6 by Imar Spaanjaars <Imar@S...> on Wed, 29 Nov 2000 18:37:12 +0100
|
|
Hi Robert,
AFAIK, this is not true. I think that the execute method will return an
open, read-only, forward-only recordset, no matter how many records are in it.
I tried the following to test this out:
<!--#INCLUDE VIRTUAL="/ADOVBS.INC" -->
set rsResults = dbConn.execute("SELECT ID FROM aTABLE WHERE ID = 1")
if rsResults.EOF then
Response.Write("Recordset is EOF<BR>")
Select Case rsResult.state
Case adStateOpen
Response.write("adStateOpen")
Case adStateClosed
Response.write("adStateClosed")
End select
rsResult.close
else
Response.Write("Recordset does contain records<BR>")
Select Case rsResult.state
Case adStateOpen
Response.write("adStateOpen")
Case adStateClosed
Response.write("adStateClosed")
End select
rsResult.close
end if
Set rsResult = Nothing
In my case this resulted in:
Recordset is EOF
adStateOpen
when I selected an ID that didn't exist in the database, and
Recordset does contain records
adStateOpen
when the ID did exist.
So I think it is safe to move the rsResult.Close after the if statement so
the recordset will be always be closed.
Of course setting it to Nothing has to be there as well ;-)
Imar
At 10:16 AM 11/29/2000 -0500, you wrote:
>what out with the below code...if, after just attempting to open a
>recordset, it tests to be .EOF = TRUE, then the recordset is NOT
>opened..thus the .CLOSE will have to only exist in the ELSE clause (in the
>example below)....attempting to close a recordset that is not open will
>generate an error.
>
>(just move the "rsResults.close" line up into the "ELSE" block)
>
>dont forget to destroy your obejcts also. (set rsResults = nothing, etc...)
>
>
>
>
>
>At 08:17 AM 11/29/00, you wrote:
>>You can use the EOF or BOF property to check if the recordset contains a
>>record. .EOF will return true if there are no records, or when you are at
>>the last record.
>>
>>Here is an example:
>>
>>' execute the SQL
>>Dim rsResults ' as Recordset
>>set rsResults = dbConn.execute("SELECT claimstatus FROM
>>form_cclive_header WHERE claimnum=" & claimnum)
>>
>>if rsResults.EOF then
>> ' Recordset is empty.
>> Response.Write("No records found")
>> Response.Write("FAILED")
>>else
>> ' The record does exist. Do something with it
>> Since you only select one field (claimstatus) there is no need
>> to loop through all the fields.
>> ' put the data into a variable 'remmed out
>> ' for each oField in results.fields 'remmed out
>> ' claimstatus = oField.value & ""
>> ' next
>> claimstatus = rsResults(0)
>> ' or you can use:
>> claimstatus = rsResults("claimstatus")
>>end if
>>rsResults.close
>>
>>HtH
>>
>>Imar
>>
>>
>>
>>
>>
>>At 01:21 PM 11/29/2000 -0800, you wrote:
>>>I am trying to check for existance of a row in my database using :
>>>
>>>' execute the SQL
>>>set results = dbConn.execute("SELECT claimstatus FROM
>>>form_cclive_header WHERE claimnum=" & claimnum)
>>>
>>>' put the data into a variable
>>>for each oField in results.fields
>>> claimstatus = oField.value & ""
>>>next
>>>
>>>results.close
>>>
>>>if (len(Cstr(claimstatus))>0) then
>>> response.write("WORKS " & claimstatus)
>>>else
>>> response.write("FAILED")
>>>end if
>>>
>>>This works fine if the row exists, but I get the following if it does
>>>not :
>>>
>>>ADODB.Field error '800a0bcd'
>>>
>>>Either BOF or EOF is True, or the current record has been deleted; the
>>>operation requested by the application requires a current record.
>>>
>>>/scripts/gregtest.asp, line 70
>>>
>>>What I am trying to achieve is to determine if the row exists, if it
>>>does then I use an UPDATE statement, otherwise I use an insert.
>>>
>>>Does anyone have a solution or a better approach ?
>Robert Chartier
>Author, AspFree.com
>xxx-xxx-xxxx
>rchartierh@a...
>http://www.aspfree.com/devlinks
>http://www.aspfree.com/authors/robert
>http://www.aspalliance.com/nothingmn
>
>
Message #7 by "Dallas Martin" <dmartin@z...> on Wed, 29 Nov 2000 21:15:54 -0500
|
|
Ok, you need to check for eof before you try to assign the field
values to variables:
if NOT rs.EOF then.....
----- Original Message -----
From: <Gregory_Griffiths@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, November 29, 2000 4:21 PM
Subject: [asp_databases] Does Row Exist Problem
> I am trying to check for existance of a row in my database using :
>
> ' execute the SQL
> set results = dbConn.execute("SELECT claimstatus FROM
> form_cclive_header WHERE claimnum=" & claimnum)
>
> ' put the data into a variable
> for each oField in results.fields
> claimstatus = oField.value & ""
> next
>
> results.close
>
> if (len(Cstr(claimstatus))>0) then
> response.write("WORKS " & claimstatus)
> else
> response.write("FAILED")
> end if
>
> This works fine if the row exists, but I get the following if it does
> not :
>
> ADODB.Field error '800a0bcd'
>
> Either BOF or EOF is True, or the current record has been deleted; the
> operation requested by the application requires a current record.
>
> /scripts/gregtest.asp, line 70
>
> What I am trying to achieve is to determine if the row exists, if it
> does then I use an UPDATE statement, otherwise I use an insert.
>
> Does anyone have a solution or a better approach ?
>
>
Message #8 by Stephane_Dattenny@D... on Thu, 30 Nov 2000 01:34:01 -0600
|
|
I think you are wrong because .EOF doesn't not seem the recordset is not
opened but is empty !!
So the rs.Close must be where it is now (after END IF).
Best regards / Cordialement
Stephane Dattenny
Dell Computers - EMEA IT - VB and Web developer
Phone: +33 (0)4 99 75 49 88
-----Original Message-----
From: Robert Chartier [mailto:rchartierh@a...]
Sent: 29 November 2000 16:17
To: ASP Databases
Subject: [asp_databases] Re: Does Row Exist Problem
what out with the below code...if, after just attempting to open a
recordset, it tests to be .EOF = TRUE, then the recordset is NOT
opened..thus the .CLOSE will have to only exist in the ELSE clause (in the
example below)....attempting to close a recordset that is not open will
generate an error.
(just move the "rsResults.close" line up into the "ELSE" block)
dont forget to destroy your obejcts also. (set rsResults = nothing, etc...)
At 08:17 AM 11/29/00, you wrote:
>You can use the EOF or BOF property to check if the recordset contains a
>record. .EOF will return true if there are no records, or when you are at
>the last record.
>
>Here is an example:
>
>' execute the SQL
>Dim rsResults ' as Recordset
>set rsResults = dbConn.execute("SELECT claimstatus FROM form_cclive_header
>WHERE claimnum=" & claimnum)
>
>if rsResults.EOF then
> ' Recordset is empty.
> Response.Write("No records found")
> Response.Write("FAILED")
>else
> ' The record does exist. Do something with it
> Since you only select one field (claimstatus) there is no need to
> loop through all the fields.
> ' put the data into a variable 'remmed out
> ' for each oField in results.fields 'remmed out
> ' claimstatus = oField.value & ""
> ' next
> claimstatus = rsResults(0)
> ' or you can use:
> claimstatus = rsResults("claimstatus")
>end if
>rsResults.close
>
>HtH
>
>Imar
>
>
>
>
>
>At 01:21 PM 11/29/2000 -0800, you wrote:
>>I am trying to check for existance of a row in my database using :
>>
>>' execute the SQL
>>set results = dbConn.execute("SELECT claimstatus FROM
>>form_cclive_header WHERE claimnum=" & claimnum)
>>
>>' put the data into a variable
>>for each oField in results.fields
>> claimstatus = oField.value & ""
>>next
>>
>>results.close
>>
>>if (len(Cstr(claimstatus))>0) then
>> response.write("WORKS " & claimstatus)
>>else
>> response.write("FAILED")
>>end if
>>
>>This works fine if the row exists, but I get the following if it does
>>not :
>>
>>ADODB.Field error '800a0bcd'
>>
>>Either BOF or EOF is True, or the current record has been deleted; the
>>operation requested by the application requires a current record.
>>
>>/scripts/gregtest.asp, line 70
>>
>>What I am trying to achieve is to determine if the row exists, if it
>>does then I use an UPDATE statement, otherwise I use an insert.
>>
>>Does anyone have a solution or a better approach ?
>>
>
Robert Chartier
Author, AspFree.com
xxx-xxx-xxxx
rchartierh@a...
http://www.aspfree.com/devlinks
http://www.aspfree.com/authors/robert
http://www.aspalliance.com/nothingmn
|
|
|
View Cart
