p2p.wrox.com: Archives - asp_web

asp_web_howto thread: Finding Database Value

Message #1 by "Jonathan Marshall" <jdmarsh2g@h...> on Wed, 15 May 2002 02:05:10 +0000

<html><div style='background-color:'><DIV>What is the best way to check and see if a
certain value is present in a database. If I want to check for a Id number being present in a field how can manipulate that
collection of ID's and check for a number. <BR>jdm</DIV></div><br clear=all><hr>Send and receive
Hotmail on your mobile device: <a href='http://g.msn.com/1HM305401/45'>Click Here</a><br></html>

Message #2 by "Ken Schaefer" <ken@a...> on Wed, 15 May 2002 12:39:08 +1000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jonathan Marshall" <jdmarsh2g@h...>
Subject: [asp_web_howto] Finding Database Value

: What is the best way to check and see if a certain value is
: present in a database. If I want to check for a Id number being
: present in a field how can manipulate that collection of ID's
: and check for a number.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Depends on where you want to implement this business logic. In ASP it would
be something like:

<%
strVar = "this-is-a-test-ID"

strSQL = _
    "SELECT field1 " & _
    "FROM table1 " & _
    "WHERE IDField = '" & strVar & "'"

objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

If objRS.EOF then
    ' Value is not present
Else
    ' Value is present
End If
%>

Alternatively, you could put it into your database:

CREATE PROC usp_mySproc

    @UserID    int

AS

    IF EXISTS
    (
        SELECT NULL
        FROM Users
        WHERE UserID = @UserID
    )
    --UserID Exists
    BEGIN
        -- return an error code
        RETURN 1
    END
    ELSE
    BEGIN
        -- do something else
    END

Cheers
Ken

Message #3 by "Jonathan Marshall" <jdmarsh2g@h...> on Wed, 15 May 2002 05:15:38 +0000

<html><div style='background-color:'><DIV>
<P>thanks Ken<BR></P></DIV>
<DIV></DIV>
<DIV></DIV>&gt;From: "Ken Schaefer" <KEN@A...>
<DIV></DIV>&gt;Reply-To: "ASP Web HowTo" <ASP_WEB_HOWTO@P...>
<DIV></DIV>&gt;To: "ASP Web HowTo" <ASP_WEB_HOWTO@P...>
<DIV></DIV>&gt;Subject: [asp_web_howto] Re: Finding Database Value 
<DIV></DIV>&gt;Date: Wed, 15 May 2002 12:39:08 +1000 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
<DIV></DIV>&gt;From: "Jonathan Marshall" <JDMARSH2G@H...>
<DIV></DIV>&gt;Subject: [asp_web_howto] Finding Database Value 
<DIV></DIV>&gt; 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;: What is the best way to check and see if a certain value is 
<DIV></DIV>&gt;: present in a database. If I want to check for a Id number being 
<DIV></DIV>&gt;: present in a field how can manipulate that collection of ID's 
<DIV></DIV>&gt;: and check for a number. 
<DIV></DIV>&gt;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;Depends on where you want to implement this business logic. In ASP it would 
<DIV></DIV>&gt;be something like: 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;<%
</DIV>>strVar = "this-is-a-test-ID"
</DIV>>
</DIV>>strSQL = _
</DIV>>     "SELECT field1 " & _
</DIV>>     "FROM table1 " & _
</DIV>>     "WHERE IDField = '" & strVar & "'"
</DIV>>
</DIV>>objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
</DIV>>
</DIV>>If objRS.EOF then
</DIV>>     ' Value is not present
</DIV>>Else
</DIV>>     ' Value is present
</DIV>>End If
</DIV>>%> 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;Alternatively, you could put it into your database: 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;CREATE PROC usp_mySproc 
<DIV></DIV>&gt; 
<DIV></DIV>&gt; @UserID int 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;AS 
<DIV></DIV>&gt; 
<DIV></DIV>&gt; IF EXISTS 
<DIV></DIV>&gt; ( 
<DIV></DIV>&gt; SELECT NULL 
<DIV></DIV>&gt; FROM Users 
<DIV></DIV>&gt; WHERE UserID = @UserID 
<DIV></DIV>&gt; ) 
<DIV></DIV>&gt; --UserID Exists 
<DIV></DIV>&gt; BEGIN 
<DIV></DIV>&gt; -- return an error code 
<DIV></DIV>&gt; RETURN 1 
<DIV></DIV>&gt; END 
<DIV></DIV>&gt; ELSE 
<DIV></DIV>&gt; BEGIN 
<DIV></DIV>&gt; -- do something else 
<DIV></DIV>&gt; END 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;Cheers 
<DIV></DIV>&gt;Ken 
<DIV></DIV>&gt; 
<DIV></DIV>&gt; 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;--- 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;Improve your web design skills with these new books from Glasshaus. 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;Usable Web Menus 
<DIV></DIV>&gt;http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme 
<DIV></DIV>&gt;r-20 
<DIV></DIV>&gt;Constructing Accessible Web Sites 
<DIV></DIV>&gt;http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme 
<DIV></DIV>&gt;r-20 
<DIV></DIV>&gt;Practical JavaScript for the Usable Web 
<DIV></DIV>&gt;http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme 
<DIV></DIV>&gt;r-20 
<DIV></DIV>&gt;--- 
<DIV></DIV>&gt;
<DIV></DIV>&gt;
<DIV></DIV></div><br clear=all><hr>MSN Photos is the easiest way to share and print your photos: <a
href='http://g.msn.com/1HM305401/46'>Click Here</a><br></html>

Message #4 by "Drew, Ron" <RDrew@B...> on Wed, 15 May 2002 07:46:32 -0400

This is a multi-part message in MIME format.

------_=_NextPart_001_01C1FC06.288200B3
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Depends on what you are trying to look for.

SELECT blah FROM tablename
WHERE
theID =3D 123       ...or... < or >
theID IN (123,124,125)    ....or....if looking for an alpha the above
plus
theID LIKE 'alpha%'   ...or...  theID LIKE '%alpha'  ...or...theID LIKE
'%alpha%'

Dont forget the check for EOF BOF

-----Original Message-----
From: Jonathan Marshall [mailto:jdmarsh2g@h...]
Sent: Tuesday, May 14, 2002 10:05 PM
To: ASP Web HowTo
Subject: [asp_web_howto] Finding Database Value


What is the best way to check and see if a certain value is present in a
database. If I want to check for a Id number being present in a field
how can manipulate that collection of ID's and check for a number.
jdm

  _____ 

Send and receive Hotmail on your mobile device: Click Here
<http://g.msn.com/1HM305401/45>
--- Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=3Dnosim/theprogramm
e
r-20 Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=3Dnosim/theprogramm
e
r-20 Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=3Dnosim/theprogramm
e
r-20 ---

Message #5 by "Jonathan Marshall" <jdmarsh2g@h...> on Wed, 15 May 2002 20:01:05 +0000

<html><div style='background-color:'><DIV>Okay ken but when I try that I stilll get an
error saying that an exception occurred I will try some other things and see what happens thanks</DIV>
<DIV></DIV>
<DIV></DIV>&gt;From: "Ken Schaefer" <KEN@A...>
<DIV></DIV>&gt;Reply-To: "ASP Web HowTo" <ASP_WEB_HOWTO@P...>
<DIV></DIV>&gt;To: "ASP Web HowTo" <ASP_WEB_HOWTO@P...>
<DIV></DIV>&gt;Subject: [asp_web_howto] Re: Finding Database Value 
<DIV></DIV>&gt;Date: Wed, 15 May 2002 12:39:08 +1000 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
<DIV></DIV>&gt;From: "Jonathan Marshall" <JDMARSH2G@H...>
<DIV></DIV>&gt;Subject: [asp_web_howto] Finding Database Value 
<DIV></DIV>&gt; 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;: What is the best way to check and see if a certain value is 
<DIV></DIV>&gt;: present in a database. If I want to check for a Id number being 
<DIV></DIV>&gt;: present in a field how can manipulate that collection of ID's 
<DIV></DIV>&gt;: and check for a number. 
<DIV></DIV>&gt;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;Depends on where you want to implement this business logic. In ASP it would 
<DIV></DIV>&gt;be something like: 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;<%
</DIV>>strVar = "this-is-a-test-ID"
</DIV>>
</DIV>>strSQL = _
</DIV>>     "SELECT field1 " & _
</DIV>>     "FROM table1 " & _
</DIV>>     "WHERE IDField = '" & strVar & "'"
</DIV>>
</DIV>>objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
</DIV>>
</DIV>>If objRS.EOF then
</DIV>>     ' Value is not present
</DIV>>Else
</DIV>>     ' Value is present
</DIV>>End If
</DIV>>%> 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;Alternatively, you could put it into your database: 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;CREATE PROC usp_mySproc 
<DIV></DIV>&gt; 
<DIV></DIV>&gt; @UserID int 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;AS 
<DIV></DIV>&gt; 
<DIV></DIV>&gt; IF EXISTS 
<DIV></DIV>&gt; ( 
<DIV></DIV>&gt; SELECT NULL 
<DIV></DIV>&gt; FROM Users 
<DIV></DIV>&gt; WHERE UserID = @UserID 
<DIV></DIV>&gt; ) 
<DIV></DIV>&gt; --UserID Exists 
<DIV></DIV>&gt; BEGIN 
<DIV></DIV>&gt; -- return an error code 
<DIV></DIV>&gt; RETURN 1 
<DIV></DIV>&gt; END 
<DIV></DIV>&gt; ELSE 
<DIV></DIV>&gt; BEGIN 
<DIV></DIV>&gt; -- do something else 
<DIV></DIV>&gt; END 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;Cheers 
<DIV></DIV>&gt;Ken 
<DIV></DIV>&gt; 
<DIV></DIV>&gt; 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;--- 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;Improve your web design skills with these new books from Glasshaus. 
<DIV></DIV>&gt; 
<DIV></DIV>&gt;Usable Web Menus 
<DIV></DIV>&gt;http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme 
<DIV></DIV>&gt;r-20 
<DIV></DIV>&gt;Constructing Accessible Web Sites 
<DIV></DIV>&gt;http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme 
<DIV></DIV>&gt;r-20 
<DIV></DIV>&gt;Practical JavaScript for the Usable Web 
<DIV></DIV>&gt;http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme 
<DIV></DIV>&gt;r-20 
<DIV></DIV>&gt;--- 
<DIV></DIV>&gt;
<DIV></DIV>&gt;
<DIV></DIV></div><br clear=all><hr>MSN Photos is the easiest way to share and print your photos: <a
href='http://g.msn.com/1HM305401/46'>Click Here</a><br></html>

Return to Index

P2P Archives