Hello.
Could someone please take a look at this code and tell me what I'm doing 
wrong, as I really can't see the wood for the trees now.  It's just a 
simple ASP page that allows a user to enter data and to view existing
records.  The problem lies in calling a Javascript alert box within the 
ASP code once a user has entered their data.  Although the popup calls, 
it executes the same message regardless of whether the record does exist 
or not and then enters only unique records (thanks to the stored 
procedure barrier).  I tried adding an Else statement with Javascript 
alert, but that didn't help at all.  

THE REALLY WEIRD THING is that if I misspell the name of the recordset 
(ie If rsrsInsertAccessGroup... instead of If rsInsertAccessGroup... -- 
as shown below), it allows me to enter my unique record (and shows up in 
SQL server) and then plays up on the alert box.  Without the misspelling, 
none of it works!
Can anyone help???
Many thanks in advance if you can.
Susan


Relevant ASP code:
=========================
<% If Request("Submit") <> "" Then %>

<%
Dim cmdInsertAccessGroup__access_group
cmdInsertAccessGroup__access_group = ""
if(Request("access_group") <> "") then cmdInsertAccessGroup__access_group 
= Request("access_group")
%>
		
<%
set cmdInsertAccessGroup = Server.CreateObject("ADODB.Command")
cmdInsertAccessGroup.ActiveConnection = MM_gfg_STRING
cmdInsertAccessGroup.CommandText = "dbo.insert_Access_groups"
cmdInsertAccessGroup.Parameters.Append 
cmdInsertAccessGroup.CreateParameter("@access_group", 200, 
1,50,cmdInsertAccessGroup__access_group)
cmdInsertAccessGroup.CommandType = 4
cmdInsertAccessGroup.CommandTimeout = 0
cmdInsertAccessGroup.Prepared = true
set rsInsertAccessGroup = cmdInsertAccessGroup.Execute
rsInsertAccessGroup_numRows = 0
%>

<!-- Alert user re data entry 'success' -->
<% 
If  rsrsInsertAccessGroup = (0) Then 
Response.Write ("<script language=""javascript"">")
Response.Write ("alert ( ""That record already exists!""); ")
Response.Write ("</script>")
End If
%>

<% End If %>

<%
set cmdShowAccessGroups = Server.CreateObject("ADODB.Command")
cmdShowAccessGroups.ActiveConnection = MM_gfg_STRING
cmdShowAccessGroups.CommandText = "dbo.usp_show_accessgroups"
cmdShowAccessGroups.CommandType = 4
cmdShowAccessGroups.CommandTimeout = 0
cmdShowAccessGroups.Prepared = true
set rsShowAccessGroups = cmdShowAccessGroups.Execute
rsShowAccessGroups_numRows = 0
%>


Stored procedure code:
==========================
CREATE PROCEDURE [insert_Access_groups]
	(@access_group 	[varchar](50))

AS 

IF EXISTS(SELECT 'True' FROM Access_groups WHERE
access_group = @access_group)
  BEGIN
    SELECT 'This record already exists!'
  END
ELSE
  BEGIN
    SELECT 'Record Added'
INSERT INTO [gfg].[dbo].[Access_groups] ( [access_group]) 
VALUES 	( @access_group)
END

I think this line is "incorrect"

: If  rsrsInsertAccessGroup = (0) Then

Aren't you returning a record from the database, with one field, that
contains the text "This record already exists!"? Surely you should be
checking for that?

Also, I would change the sproc so that it does this:

CREATE PROCEDURE usp_Insert_Access_Groups

    @access_group varchar(50)

AS

    IF NOT EXISTS
    (
        SELECT NULL
        FROM Access_groups
        WHERE access_group = @access_group
    )
    BEGIN
        INSERT INTO [gfg].[dbo].[Access_groups]
        (
            [access_group]
        )
        VALUES
        (
            @access_group
        )
    END

GO

Then, in your code you do:

<%
'
'
cmdInsertAccessGroup.Execute, lngRecsAffected

If lngRecsAffected = 0 then
    ' no records where updated
    ' because the record already exists
    ' write out javascript
Elseif lngRecsAffected = 1 then
    ' 1 record was inserted
End If
%>

Also, do not use a connection string for the .ActiveConnection property as
you are defeating connection pooling (WHICH IS A VERY BAD THING!!!). Also
instantiate an explicit connection object, then do:

Set objCommand.ActiveConnection = objConn

Pooling Explained (Long article)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html
/pooling2.asp

Pooling in ASP:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191572

Cheers
Ken


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Susan Kent" <susan_kent9@y...>
Subject: [asp_web_howto] ASP, stored procedure, javascript -- spooky code!


: Hello.
: Could someone please take a look at this code and tell me what I'm doing
: wrong, as I really can't see the wood for the trees now.  It's just a
: simple ASP page that allows a user to enter data and to view existing
: records.  The problem lies in calling a Javascript alert box within the
: ASP code once a user has entered their data.  Although the popup calls,
: it executes the same message regardless of whether the record does exist
: or not and then enters only unique records (thanks to the stored
: procedure barrier).  I tried adding an Else statement with Javascript
: alert, but that didn't help at all.
:
: THE REALLY WEIRD THING is that if I misspell the name of the recordset
: (ie If rsrsInsertAccessGroup... instead of If rsInsertAccessGroup... --
: as shown below), it allows me to enter my unique record (and shows up in
: SQL server) and then plays up on the alert box.  Without the misspelling,
: none of it works!
: Can anyone help???
: Many thanks in advance if you can.
: Susan
:
:
: Relevant ASP code:
: =========================
: <% If Request("Submit") <> "" Then %>
:
: <%
: Dim cmdInsertAccessGroup__access_group
: cmdInsertAccessGroup__access_group = ""
: if(Request("access_group") <> "") then cmdInsertAccessGroup__access_group
: = Request("access_group")
: %>
:
: <%
: set cmdInsertAccessGroup = Server.CreateObject("ADODB.Command")
: cmdInsertAccessGroup.ActiveConnection = MM_gfg_STRING
: cmdInsertAccessGroup.CommandText = "dbo.insert_Access_groups"
: cmdInsertAccessGroup.Parameters.Append
: cmdInsertAccessGroup.CreateParameter("@access_group", 200,
: 1,50,cmdInsertAccessGroup__access_group)
: cmdInsertAccessGroup.CommandType = 4
: cmdInsertAccessGroup.CommandTimeout = 0
: cmdInsertAccessGroup.Prepared = true
: set rsInsertAccessGroup = cmdInsertAccessGroup.Execute
: rsInsertAccessGroup_numRows = 0
: %>
:
: <!-- Alert user re data entry 'success' -->
: <%
: If  rsrsInsertAccessGroup = (0) Then
: Response.Write ("<script language=""javascript"">")
: Response.Write ("alert ( ""That record already exists!""); ")
: Response.Write ("</script>")
: End If
: %>
:
: <% End If %>
:
: <%
: set cmdShowAccessGroups = Server.CreateObject("ADODB.Command")
: cmdShowAccessGroups.ActiveConnection = MM_gfg_STRING
: cmdShowAccessGroups.CommandText = "dbo.usp_show_accessgroups"
: cmdShowAccessGroups.CommandType = 4
: cmdShowAccessGroups.CommandTimeout = 0
: cmdShowAccessGroups.Prepared = true
: set rsShowAccessGroups = cmdShowAccessGroups.Execute
: rsShowAccessGroups_numRows = 0
: %>
:
:
: Stored procedure code:
: ==========================
: CREATE PROCEDURE [insert_Access_groups]
: (@access_group [varchar](50))
:
: AS
:
: IF EXISTS(SELECT 'True' FROM Access_groups WHERE
: access_group = @access_group)
:   BEGIN
:     SELECT 'This record already exists!'
:   END
: ELSE
:   BEGIN
:     SELECT 'Record Added'
: INSERT INTO [gfg].[dbo].[Access_groups] ( [access_group])
: VALUES ( @access_group)
: END
:
:
:
: ---
:
: Improve your web design skills with these new books from Glasshaus.
:
: Usable Web Menus
: http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
: r-20
: Constructing Accessible Web Sites
: http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: r-20
: Practical JavaScript for the Usable Web
: http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: r-20