Calling SP from ASP 2006-07-04 - By Jesse
Back
I'm running into problems with both ASP.Net and ASP with this thing, and
it's driving me nutz. I have the same stored procedure which I'm calling
from my ASP app (some asp pages, some asp.net pages). Here is the
procedure:
CREATE PROCEDURE `sp_GetNextInv`(
in nChapterID Int,
out cInvNo VarChar(7)
)
BEGIN
Declare cPrefix VarChar(1);
Declare cNextInv VarChar(7);
Set cInvNo = '';
IF nChapterID > 0 THEN
SELECT TempInvNo INTO cInvNo FROM Chapters WHERE ID=nChapterID;
END IF;
IF (cInvNo = '') or (cInvNo IS NULL) THEN
SELECT NextInvoiceNo INTO cInvNo FROM Config;
SET cNextInv = Right('0000000' + CONVERT(CONVERT(cInvNo, UNSIGNED) + 1,
CHAR), 7);
UPDATE Config SET NextInvoiceNo=cNextInv;
IF nChapterID = -1 THEN
Set cInvNo = CONCAT('L',Right(CONCAT('000000',cInvNo),6));
END IF;
IF nChapterID = -2 THEN
Set cInvNo = CONCAT('C',Right(CONCAT('000000',cInvNo),6));
END IF;
IF nChapterID > 0 THEN
SELECT CT.InvPrefix INTO cPrefix FROM ChapterType CT, Chapters C
WHERE C.ID=nChapterID AND CT.ChapterType=C.ChapterType;
Set cInvNo = CONCAT(cPrefix,Right(CONCAT('000000',cInvNo),6));
UPDATE Chapters SET TempInvNo=cInvNo WHERE ID=nChapterID;
END IF;
END IF;
END
Here is the asp code:
function GetNextInv(nChapterID)
Dim adocmd
Set adocmd = Server.CreateObject("ADODB.Command")
adocmd.CommandText = "sp_GetNextInv"
adocmd.ActiveConnection = Conn
adocmd.CommandType = adCmdStoredProc
adocmd.Parameters.Append adocmd.CreateParameter("?nChapterID", adInteger,
adParamInput, 16, nChapterID)
adocmd.Parameters.Append adocmd.CreateParameter("?cInvNo", adVarChar,
adParamOutput,7)
adocmd.Execute
GetNextInv = adocmd.Parameters("?cInvNo").Value
set adocmd=Nothing
end function
The error I'm getting is "MySQL][ODBC 3.51 Driver][mysqld-5 (See http://qld-5.ora-code.com).0.15-nt]You have
an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near '{ call sp_GetNextInv(326,
'') }'" I have double-checked, and it appears to be putting curley brackets
around the function call. I have tried this sp in straight MySQL command,
and it works fine, it's just calling it from ASP and ASP.net that seems to
be causing the problem... Does anyone know how to resolve this?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
