Also, with a problem like this, you should run the EXEC statement and not
the SQL code.
Running:
SELECT ID as visitorid
FROM tblVisitor
WHERE first_name = 'adrian'
AND last_name = 'lynch'
Would have given you a result, whereas:
EXEC yourSP 'adrian', 'lynch'
would have returned 0 rows.
So what I'm basically saying is, there are two levels you can test at to
exclude the CF as having the problem if you're using SPs
Ade
-----Original Message-----
From: Clint [mailto:[EMAIL PROTECTED]
Sent: 02 June 2003 17:26
To: CF-Talk
Subject: Re: Correct syntax for stored procedures
In your stored procedure you have to put the length of that column.
Otherwise SQL Server defaults it to a length of 1.
Change that and rerun your stored procedure. Also, you can run this in SQL
Query analyzer and see what you get back. That way you can determine if it
is the procedure or CF that is causing the problem.
HTH
Clint
----- Original Message -----
From: "Adrian Lynch" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, June 02, 2003 11:20 AM
Subject: RE: Correct syntax for stored procedures
> I think, and somebody correct me if I am wrong, but if you don't have a
> value in the varchar after the SP params it defaults to something quite
> small.
>
> Try
>
> CREATE PROCEDURE spFindVisitor
> @fname varchar(sizeIfField),
> @lname varchar(sizeIfField)
>
> Also, put resultset="1" in the cfprocresult, I think it defaults to this
> anyway, but worth a try.
>
> See if that helps
>
> Ade
>
>
> -----Original Message-----
> From: stas [mailto:[EMAIL PROTECTED]
> Sent: 02 June 2003 17:05
> To: CF-Talk
> Subject: Correct syntax for stored procedures
>
>
> I am having a problem getting a result set.
>
>
> Proc:
>
> CREATE PROCEDURE spFindVisitor
> @fname varchar,
> @lname varchar
>
> AS
> SELECT ID as visitorid
> FROM tblVisitor
> WHERE first_name = @fname and last_name = @lname
> GO
>
> I am calling it like this:
>
> <cfstoredproc procedure = "spFindVisitor"
datasource="#request.datasource#">
> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR"
> dbvarname="@fname" value="holly">
> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR"
> dbvarname="@lname" value="bennet">
> <cfprocresult maxrows="1" name="qFindVisitor">
> </cfstoredproc>
>
> I am not getting any results back, even though I know a record is there.
Is
> my syntax correct? I've done this times and again under CF 4.5, but now I
> am debugging an app under MX, MS SQL 2000. I tried both JDBC and MS SQL
> drivers in MX, same results.
>
> Also, is there an advantage in using JDBC over MS SQL and vice versa? If
> there is a link on MM's site, I'd greatly appreciate that.
>
> Much thanks
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
