|
 |
asp_ado_rds thread: Getting multiple select values into the database
Message #1 by bob.jones@t... on Tue, 20 Aug 2002 21:55:05
|
|
This has had me stumped for WEEKS. Is the solution harder and more obscure
than I can imagine???
I'm passing multiple values from a <select multiple ...> via the
Request.Form("fkeywords"). How do I get them into my MS SQL Server 7
database?
I would think the solution is to iterate through the values, creating a
new recordset record for each. Then trigger the stored procedure. But it
simply isn't working.
I know the values are in Request.Form("fkeywords").
I know the stored procedure works.
I know the recorset is created.
I know the connection is working because a command object used it to
update the database immediately before what I'm trying to do here.
I know the other field values I'm trying to stuff into the record exist.
The recordset records are not being created.
[code]
set rsDBKeywords = Server.CreateObject("ADODB.Recordset")
rsDBKeywords.ActiveConnection = objConnection
rsDBKeywords.Source = "exec up_scDBKeywordsAdd 'DbID'"
rsDBKeywords.CursorType = 2 'adOpenDynamic (a dynamic cursor)
rsDBKeywords.CursorLocation = 2 'on the server
rsDBKeywords.LockType = 3
rsDBKeywords.Open
for i=1 to Request.Form("fKeywords").Count
rsDBKeywords.AddNew
rsDBKeywords.Fields("person_ID") = SessPersonID
rsDBKeywords.Fields("database_ID") = DbID
rsDBKeywords.Fields("keyword") = Request.Form("fKeywords")(i)
next
rsDBKeywords.UpdateBatch
[/code]
This code does nothing that I can see. It times out (server.scripttimeout
= 600). The best I've been able to do is successfully test for
rsDBKeywords.EOF and BOF & display all the recordset's properties.
Here is the stored procedure:
[code]
CREATE PROCEDURE dbo.up_scDBKeywordsAdd
(
@iPerson_ID int,
@iDatabase_ID int,
@cKeyword varchar (50)
) AS
DECLARE @iKeyword_ID int,
@iError int,
@dNow datetime
SELECT @iKeyword_ID = NULL,
@iError = NULL,
@dNow = GETDATE()
SELECT @iKeyword_ID = Keyword_ID
FROM tKeywords
WHERE Keyword = @cKeyword
-- Do we have a new keyword? insert it into the master keyword table
IF @iKeyword_ID IS NULL
BEGIN
INSERT INTO dbo.tKeywords
(Keyword, UpdateDt, UpdateBy)
VALUES
(@cKeyword, @dNow, @iPerson_ID)
SELECT @iError = @@ERROR
IF @iERROR > 0
BEGIN
RAISERROR ('syscat2 - INSERT into tKewords failed w/ error %d', 18,
1, @iError)
RETURN
END
SELECT @iKeyword_ID = @@IDENTITY
END
-- Now create a new tDBKeyword table entries
IF NOT EXISTS (SELECT 1 FROM dbo.tDBKeywords
WHERE keyword_ID = @iKeyword_ID AND
Database_ID = @iDatabase_ID)
BEGIN
INSERT INTO dbo.tDBKeywords
(keyword_ID, Database_ID, UpdateDt, updateBy)
VALUES
(@iKeyword_ID, @iDatabase_ID, @dNow, @iPerson_ID)
SELECT @iError = @@ERROR
IF @iERROR > 0
BEGIN
RAISERROR ('syscat2 - INSERT into tDBKeywords failed w/ error %d',
18, 1, @iError)
RETURN
END
END
[/code]
Message #2 by Josh King <JoshK@g...> on Tue, 20 Aug 2002 16:00:56 -0500
|
|
You could just loop through the forms collection that calls a Sub which
calls the stored procedure:
Start Loop
Call AddValue(FormValue)
Next
Sub AddValue(ByVal SelectValue)
Use Command Object to call and execute stored procedure
End Sub
Josh King
GeoAccess Inc.
xxx.xxx.xxxx x5237
-----Original Message-----
From: bob.jones@t... [mailto:bob.jones@t...]
Sent: Tuesday, August 20, 2002 4:55 PM
To: ASP_ADO_RDS
Subject: [asp_ado_rds] Getting multiple select values into the
database
This has had me stumped for WEEKS. Is the solution harder and more obscure
than I can imagine???
I'm passing multiple values from a <select multiple ...> via the
Request.Form("fkeywords"). How do I get them into my MS SQL Server 7
database?
I would think the solution is to iterate through the values, creating a
new recordset record for each. Then trigger the stored procedure. But it
simply isn't working.
I know the values are in Request.Form("fkeywords").
I know the stored procedure works.
I know the recorset is created.
I know the connection is working because a command object used it to
update the database immediately before what I'm trying to do here.
I know the other field values I'm trying to stuff into the record exist.
The recordset records are not being created.
[code]
set rsDBKeywords = Server.CreateObject("ADODB.Recordset")
rsDBKeywords.ActiveConnection = objConnection
rsDBKeywords.Source = "exec up_scDBKeywordsAdd 'DbID'"
rsDBKeywords.CursorType = 2 'adOpenDynamic (a dynamic cursor)
rsDBKeywords.CursorLocation = 2 'on the server
rsDBKeywords.LockType = 3
rsDBKeywords.Open
for i=1 to Request.Form("fKeywords").Count
rsDBKeywords.AddNew
rsDBKeywords.Fields("person_ID") = SessPersonID
rsDBKeywords.Fields("database_ID") = DbID
rsDBKeywords.Fields("keyword") = Request.Form("fKeywords")(i)
next
rsDBKeywords.UpdateBatch
[/code]
This code does nothing that I can see. It times out (server.scripttimeout
= 600). The best I've been able to do is successfully test for
rsDBKeywords.EOF and BOF & display all the recordset's properties.
Here is the stored procedure:
[code]
CREATE PROCEDURE dbo.up_scDBKeywordsAdd
(
@iPerson_ID int,
@iDatabase_ID int,
@cKeyword varchar (50)
) AS
DECLARE @iKeyword_ID int,
@iError int,
@dNow datetime
SELECT @iKeyword_ID = NULL,
@iError = NULL,
@dNow = GETDATE()
SELECT @iKeyword_ID = Keyword_ID
FROM tKeywords
WHERE Keyword = @cKeyword
-- Do we have a new keyword? insert it into the master keyword table
IF @iKeyword_ID IS NULL
BEGIN
INSERT INTO dbo.tKeywords
(Keyword, UpdateDt, UpdateBy)
VALUES
(@cKeyword, @dNow, @iPerson_ID)
SELECT @iError = @@ERROR
IF @iERROR > 0
BEGIN
RAISERROR ('syscat2 - INSERT into tKewords failed w/ error %d', 18,
1, @iError)
RETURN
END
SELECT @iKeyword_ID = @@IDENTITY
END
-- Now create a new tDBKeyword table entries
IF NOT EXISTS (SELECT 1 FROM dbo.tDBKeywords
WHERE keyword_ID = @iKeyword_ID AND
Database_ID = @iDatabase_ID)
BEGIN
INSERT INTO dbo.tDBKeywords
(keyword_ID, Database_ID, UpdateDt, updateBy)
VALUES
(@iKeyword_ID, @iDatabase_ID, @dNow, @iPerson_ID)
SELECT @iError = @@ERROR
IF @iERROR > 0
BEGIN
RAISERROR ('syscat2 - INSERT into tDBKeywords failed w/ error %d',
18, 1, @iError)
RETURN
END
END
[/code]
To unsubscribe send a blank email to %%email.unsub%%
Message #3 by "Valentino Osterwalder" <valentino@t...> on Wed, 21 Aug 2002 12:05:41 +0200
|
|
Hi, I believe it was not my problem... Please address this to the right
person.
Regards,
Valentino
-----Original Message-----
From: Josh King [mailto:JoshK@g...]
Sent: marted́, 20. agosto 2002 23:01
To: ASP_ADO_RDS
Subject: [asp_ado_rds] RE: Getting multiple select values into the
databas e
You could just loop through the forms collection that calls a Sub which
calls the stored procedure:
Start Loop
Call AddValue(FormValue)
Next
Sub AddValue(ByVal SelectValue)
Use Command Object to call and execute stored procedure
End Sub
Josh King
GeoAccess Inc.
xxx.xxx.xxxx x5237
-----Original Message-----
From: bob.jones@t... [mailto:bob.jones@t...]
Sent: Tuesday, August 20, 2002 4:55 PM
To: ASP_ADO_RDS
Subject: [asp_ado_rds] Getting multiple select values into the
database
This has had me stumped for WEEKS. Is the solution harder and more obscure
than I can imagine???
I'm passing multiple values from a <select multiple ...> via the
Request.Form("fkeywords"). How do I get them into my MS SQL Server 7
database?
I would think the solution is to iterate through the values, creating a
new recordset record for each. Then trigger the stored procedure. But it
simply isn't working.
I know the values are in Request.Form("fkeywords").
I know the stored procedure works.
I know the recorset is created.
I know the connection is working because a command object used it to
update the database immediately before what I'm trying to do here.
I know the other field values I'm trying to stuff into the record exist.
The recordset records are not being created.
[code]
set rsDBKeywords = Server.CreateObject("ADODB.Recordset")
rsDBKeywords.ActiveConnection = objConnection
rsDBKeywords.Source = "exec up_scDBKeywordsAdd 'DbID'"
rsDBKeywords.CursorType = 2 'adOpenDynamic (a dynamic cursor)
rsDBKeywords.CursorLocation = 2 'on the server
rsDBKeywords.LockType = 3
rsDBKeywords.Open
for i=1 to Request.Form("fKeywords").Count
rsDBKeywords.AddNew
rsDBKeywords.Fields("person_ID") = SessPersonID
rsDBKeywords.Fields("database_ID") = DbID
rsDBKeywords.Fields("keyword") = Request.Form("fKeywords")(i)
next
rsDBKeywords.UpdateBatch
[/code]
This code does nothing that I can see. It times out (server.scripttimeout
= 600). The best I've been able to do is successfully test for
rsDBKeywords.EOF and BOF & display all the recordset's properties.
Here is the stored procedure:
[code]
CREATE PROCEDURE dbo.up_scDBKeywordsAdd
(
@iPerson_ID int,
@iDatabase_ID int,
@cKeyword varchar (50)
) AS
DECLARE @iKeyword_ID int,
@iError int,
@dNow datetime
SELECT @iKeyword_ID = NULL,
@iError = NULL,
@dNow = GETDATE()
SELECT @iKeyword_ID = Keyword_ID
FROM tKeywords
WHERE Keyword = @cKeyword
-- Do we have a new keyword? insert it into the master keyword table
IF @iKeyword_ID IS NULL
BEGIN
INSERT INTO dbo.tKeywords
(Keyword, UpdateDt, UpdateBy)
VALUES
(@cKeyword, @dNow, @iPerson_ID)
SELECT @iError = @@ERROR
IF @iERROR > 0
BEGIN
RAISERROR ('syscat2 - INSERT into tKewords failed w/ error %d', 18,
1, @iError)
RETURN
END
SELECT @iKeyword_ID = @@IDENTITY
END
-- Now create a new tDBKeyword table entries
IF NOT EXISTS (SELECT 1 FROM dbo.tDBKeywords
WHERE keyword_ID = @iKeyword_ID AND
Database_ID = @iDatabase_ID)
BEGIN
INSERT INTO dbo.tDBKeywords
(keyword_ID, Database_ID, UpdateDt, updateBy)
VALUES
(@iKeyword_ID, @iDatabase_ID, @dNow, @iPerson_ID)
SELECT @iError = @@ERROR
IF @iERROR > 0
BEGIN
RAISERROR ('syscat2 - INSERT into tDBKeywords failed w/ error %d',
18, 1, @iError)
RETURN
END
END
[/code]
To unsubscribe send a blank email to %%email.unsub%%
To unsubscribe send a blank email to %%email.unsub%%
|
|
|
View Cart
