Could someone paste the code to pass text box values to a recordset 1

[bryant89]

I dont know how to pass textbox values to a recordset that is linked to a stored procedure that does an insert. if you select the procedure in the textbox properties none of the field names that I am inserting show up. So there must be some way of setting the parameters of the recordset to the values input into the text boxes to pass them to the to the stored procedure/sql query

Any help appreciated

Bryant Moore

 

[palbano]

Bryant,

Check this article out:

HOWTO: Call a Parameterized Query to SQL Server with Recordset DTC

http://support.microsoft.com/support/kb/articles/Q232/2/08.ASP

Hope this helps
-pete

 

[bryant89]

Thats is a good explanation,
Thanks Pete.

That is exactly what I have done. I have done many using a select query(stored procedure) that work fine. but in this particular case I am doing an insert and a select. And when I try to open the recordset I get an error.

The error just tells me there was an error in the recordset.asp line 636 which is the line to open it. So for some reason the recordset will not open. I have set the rights the same as for other tables and stored procedures that work so I know it isnt a rights problem. I think it is because of a parameters problem, I am not sure. On the onclick event for my button I set the parameters and then I open the recordset. I tried opening the recordset first and then setting the parameters but that didnt work so I tried this. And this doesnt work either. Her is my current code for my button click:

Sub btnNext_onclick()

'purpose: set the parameters for the recordset

dim name,addr,city,prov,coun,estc,stat

name=txtName.value
addr=txtAddr.value
city=txtCity.value
prov=txtProv.value
coun=txtCountry.value
estc=txtEstCost.value
stat=txtStatus.value


rcsContProj.setParameter 1,name
rcsContProj.setParameter 2,addr
rcsContProj.setParameter 3,city
rcsContProj.setParameter 4,prov
rcsContProj.setParameter 5,coun
rcsContProj.setParameter 6,estc
rcsContProj.setParameter 7,stat

rcsContProj.open

backNextFin1 rcsContProj,frmContProjDescription

rcsContProj.close
End Sub


I should mention that my recordset rcsContProj accesses a stored procedure that does an insert and a select. The code for the stored procedure is this:


Alter Procedure stpCreateReturn
(
@copName varchar(70),
@copCity varchar(40),
@copProv char(2),
@copCountry varchar(30),
@copAddress varchar(70),
@copStatus varchar(70),
@copEstCost int
)
AS

DECLARE @copID int

set nocount on

INSERT INTO tblContProj (copName, copCity, copProv, copCountry, copAddress, copStatus, copEstCost)
VALUES (@copName, @copCity, @copProv, @copCountry, @copAddress, @copStatus, @copEstCost)

SET @copID = @@IDENTITY
SELECT @copID as copID

return


I hope you can help me

 

[bryant89]

ok now I am getting this error

Error Type:
ADODB.Parameter (0x800A0D5D)
Application uses a value of the wrong type for the current operation.
/NetworkForms/_ScriptLibrary/Recordset.ASP, line 609

 

[palbano]

Bryant,

> I hope you can help me

Well I tried to reproduce your problem in my own environment without success. Here is my stored proc


CREATE PROCEDURE t_tableInsert (@sName as varchar(50),
@nNum as int) AS
DECLARE @rID int

set nocount on

insert into t_table ( t_name, t_num) Values( @sName, @nNum)

set @rID = @@IDENTITY
select @rID as RID
return


Here is my server side code:

function BtnJunk_onclick() {
performInsert();
}
function performInsert(){
rs.setParameter( 1, "onclick"
rs.setParameter( 2, 27);
rs.open();
}

function getInsertID(){
if ( rs.isOpen()){
var oRs = rs.getRecordSource();
return oRs("RID"
}
return "0";
// return g_insertID;
}


Then I display the results inline with HTML like this:

<P>Stored Proc Result: <%=getInsertID()%></P>

It works

So I guess there must be an actual problem with your input paramters as your error message states

Hope this helps
-pete

 

[bryant89]

ya thanks Pete.

I found the problem.
I wasnt opening my recordset at the appropriate time.
I was opening the recordset and then setting the parameters.

I changed it to set the parameters and then open it. Now it works fine.

Thank you very much for the Help.

Have a good day