I have created a stored proc in SQL 7 and for some reason cannot get it to
"work" using the input params I need.
here is the proc:
CREATE PROCEDURE CONSULTINFO
@user_n nvarchar,
@pass_w nvarchar,
@user_id int
AS
select u.cons_id, u.user_n, u.pass_w,c.first_n, c.last_n, c.phone, c.email,
c.city, c.state_id, c.level_id, c.resume, c.enterdate, c.availability, c.type
from reg_users u, consultants c
where u.pass_w = "@pass_w" and u.user_n="@user_n"
in the above I added the user_id field after for checking purposes. If I run
the proc using the user_id as the in param the proc returns a row correctly. (I
have also tried using single quotes in the where clause and no quotes, neither
helped)
I have checked the values of the user_n and pass_w and they are correct ( a
cfquery using the same values returns the right info)
here is the cf code:
<cfquery name="TEST" datasource="jobs_db" dbtype="ODBC">
SELECT u.cons_id, u.user_n, u.pass_w,c.first_n, c.last_n, c.phone,
c.email, c.city, c.state_id, c.level_id, c.resume, c.enterdate, c.availability,
c.type
FROM dbo.CONSULTANTS C, dbo.REG_USERS U
WHERE (U.USER_N='#user_n#' and U.PASS_W='#pass_w#')
and
(C.CONS_ID=U.CONS_ID)
</cfquery>
<!--- CFSTOREDPROC tag --->
<CFSTOREDPROC PROCEDURE="consultinfo"
DATASOURCE="jobs_db"
DEBUG>
<!--- CFPROCRESULT tags --->
<CFPROCRESULT NAME = cons>
<!--- CFPROCPARAM tags --->
<CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_VARCHAR"
VALUE="#user_n#" DBVARNAME=@user_n>
<CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_VARCHAR"
VALUE="#pass_w#" DBVARNAME=@pass_w>
<!--- this line was added for testing using the user_id and it then returned
the information I needed --->
<!---<CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_INTEGER"
VALUE="1" DBVARNAME=@user_id><!---
<!--- Close the CFSTOREDPROC tag --->
</CFSTOREDPROC>
<h3>The Results Information test</h3>
<CFOUTPUT>#cons.recordcount#
<br>#cons.USER_N#
</CFOUTPUT>
I have also tried hard-coding the values of @used_n and @pass_w in both the cf
and the stored proc with no better results.
Any ideas on what I am doing wrong?
thanks,
Frederic
--
/ \__
Frederic Valone ( @\___
Webmaster / O
American Kennel Club / (_____/
/_____/ U
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
