advanced parameter tutorial, lesson 5, multipart identifier error

Hello,

Hope I'm asking this question in the correct forum.

I'm a newbie in Reporting Services and currently working my way through the tutorials with AdventureWorks. Came across this error while doing the MSDN tutorial for Advanced Features, lesson 5 - user defined functions.

http://msdn2.microsoft.com/en-us/library/aa337435.aspx

Created a new report, copied the following to the query screen:

SELECT udf.ContactID, udf.FirstName + N' ' + udf.LastName AS Name,
c.Phone, c.EmailAddress, udf.JobTitle, udf.ContactType
FROM ufnGetContactInformation(@.ContactID) udf
JOIN Person.Contact c ON ufn.ContactID = c.ContactID

I'm following the directions to the letter, and consistently get the following error:

"The multi-part identifier "ufn.ContactID" could not be bound."

"The multip-part identifier "ufn.ContactID" could not be bound. (Microsoft SQL Server, Error: 4104)"

I'm running SQL 2005 Enterprise on Windows XP.

Any help you can give will be much appreciated! Thank you.

Looks like typo in a sample query

try udf.ContactID instead of ufn.ContactID

|||Thank you very much! Now it works.

ADO error: incorrect syntax near keyword

Hello,
I'm trying to change a stored procedure to add another parameter
(qaType) and take one of two execution paths based on its value (=1 or <>
1). When I try to save the changes using VS2003.NET, I keep getting this
error:
ADO error: Incorrect syntax near the keyword 'AS'. I'm having trouble
spotting it, but I'm sure you guys will see it in a flash. Thanks!
*--Code begins here --*
ALTER PROCEDURE dbo.frmQASELECTQADetailDetails
@.qaID int, @.qaType int
AS
IF (@.qaType <> 1)
BEGIN
SELECT
dd.QADetailDetailID,
dd.QADetailID,
dd.DetailedFR,
dd.UnitQty,
dd.QtyPerUnit,
(dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
dd.MockUps,
dd.Spares,
dd.FabFallout,
dd.GFE,
dd.ITA,
dd.Other,
(((dd.UnitQty * dd.QtyPerUnit) + dd.MockUps + dd.Spares + dd.FabFallout) -
(dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
dd.Comments,
dd.ITAAmountPlanned,
dd.ITAAmountActual
FROM
tblQADetailDetail dd,
tblQADetail d
WHERE
dd.QADetailID = d.QADetailID AND
d.QAID = @.qaID
END
ELSE
BEGIN
SELECT
dd.QADetailDetailID,
dd.QADetailID,
dd.DetailedFR,
dd.UnitQty,
dd.QtyPerUnit,
(dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
dd.MockUps,
dd.Spares,
dd.FabFallout,
dd.GFE,
dd.ITA,
dd.Other,
(((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
dd.Comments,
dd.ITAAmountPlanned,
dd.ITAAmountActual
FROM
tblQADetailDetail dd,
tblQADetail d
WHERE
dd.QADetailID = d.QADetailID AND
d.QAID = @.qaID
END
________________________________________
____________________________________
___
Posted Via Uncensored-News.Com - Accounts Starting At $6.95 - http://www.uncensore
d-news.com
<><><><><><><> The Worlds Uncensored News Source <><><><><><><><>> (((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
> dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
It looks like you have an extra open parenthesis. Try
((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
Hope this helps.
Dan Guzman
SQL Server MVP
"Spurious Logic" <spurs> wrote in message
news:420955b0$1_2@.news4.uncensored-news.com...
> Hello,
> I'm trying to change a stored procedure to add another parameter
> (qaType) and take one of two execution paths based on its value (=1 or <>
> 1). When I try to save the changes using VS2003.NET, I keep getting this
> error:
> ADO error: Incorrect syntax near the keyword 'AS'. I'm having trouble
> spotting it, but I'm sure you guys will see it in a flash. Thanks!
> *--Code begins here --*
> ALTER PROCEDURE dbo.frmQASELECTQADetailDetails
> @.qaID int, @.qaType int
> AS
> IF (@.qaType <> 1)
> BEGIN
> SELECT
> dd.QADetailDetailID,
> dd.QADetailID,
> dd.DetailedFR,
> dd.UnitQty,
> dd.QtyPerUnit,
> (dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
> dd.MockUps,
> dd.Spares,
> dd.FabFallout,
> dd.GFE,
> dd.ITA,
> dd.Other,
> (((dd.UnitQty * dd.QtyPerUnit) + dd.MockUps + dd.Spares + dd.FabFallout) -
> (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
> dd.Comments,
> dd.ITAAmountPlanned,
> dd.ITAAmountActual
> FROM
> tblQADetailDetail dd,
> tblQADetail d
> WHERE
> dd.QADetailID = d.QADetailID AND
> d.QAID = @.qaID
> END
> ELSE
> BEGIN
> SELECT
> dd.QADetailDetailID,
> dd.QADetailID,
> dd.DetailedFR,
> dd.UnitQty,
> dd.QtyPerUnit,
> (dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
> dd.MockUps,
> dd.Spares,
> dd.FabFallout,
> dd.GFE,
> dd.ITA,
> dd.Other,
> (((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
> dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
> dd.Comments,
> dd.ITAAmountPlanned,
> dd.ITAAmountActual
> FROM
> tblQADetailDetail dd,
> tblQADetail d
> WHERE
> dd.QADetailID = d.QADetailID AND
> d.QAID = @.qaID
> END
>
> ________________________________________
__________________________________
_____
> Posted Via Uncensored-News.Com - Accounts Starting At $6.95 -
> http://www.uncensored-news.com
> <><><><><><><> The Worlds Uncensored News Source
> <><><><><><><><>
>

ADO - Cannot access the Return Parameter of a stored procedure on SQL Server 2005

Hello,

I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.

To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.

try{

pCmd.CreateInstance((__uuidof(Command)));

pCmd->ActiveConnection = m_pConnection;

pCmd->CommandType = adCmdStoredProc;

pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");

............................ code here ........................................

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));

VARIANT returnVal;

returnVal.vt = VT_I2;

returnVal.intVal = NULL;

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));

//Get Return value by executing the command

//The return value should be the DB unique ID.

pCmd->Execute(NULL, NULL, adCmdStoredProc);

int uniqueId = returnVal.intVal;

//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);

//GetFieldValue(0,pRst,uniqueId);

printf("The DB unique ID is: %i",uniqueId);

return uniqueId;

}

Cheers,

Seth

You should not include returnVal into the call of CreateParameter. What happens is that compiler creates a temporary object which is useless to you.

Here is what you should do:

_ParameterPtr pReturnParam = pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t));

pCmd->Parameters->Append(pReturnParam);
pCmd->Execute(0,0,adCmdStoredProc);
variant_t value = pReturnParam->GetValue();

ADO - Cannot access the Return Parameter of a stored procedure on SQL Server 2005

Hello,

I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.

To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.

try{

pCmd.CreateInstance((__uuidof(Command)));

pCmd->ActiveConnection = m_pConnection;

pCmd->CommandType = adCmdStoredProc;

pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");

............................ code here ........................................

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));

VARIANT returnVal;

returnVal.vt = VT_I2;

returnVal.intVal = NULL;

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));

//Get Return value by executing the command

//The return value should be the DB unique ID.

pCmd->Execute(NULL, NULL, adCmdStoredProc);

int uniqueId = returnVal.intVal;

//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);

//GetFieldValue(0,pRst,uniqueId);

printf("The DB unique ID is: %i",uniqueId);

return uniqueId;

}

Cheers,

Seth

You should not include returnVal into the call of CreateParameter. What happens is that compiler creates a temporary object which is useless to you.

Here is what you should do:

_ParameterPtr pReturnParam = pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t));

pCmd->Parameters->Append(pReturnParam);
pCmd->Execute(0,0,adCmdStoredProc);
variant_t value = pReturnParam->GetValue();