Getting InvalidCastException using VS2005 TableAdapter generator to call a stored procedure

[Brian C Hart]

I have a stored procedure defined thusly -- actual variable, database, and table names have been altered for NDA reasons:

CREATE PROCEDURE [dbo].[CountUniqueBirthdays]
@PersonID bigint
AS

BEGIN

SET NOCOUNT ON;

DECLARE @CalendarDaysToSearch int

SET @CalendarDaysToSearch = 90

SELECT  COUNT(DISTINCT o.Birthdays) 
	FROM
		\[MYDATABASE\].\[dbo\].\[PersonInfo\] n,
		\[MYDATABASE\].\[dbo\].\[PersonInfo\] o
	WHERE
		n.PersonID = @PersonID AND
		--n.PersonID <> o.PersonID AND

		-- all leads with...
		n.KeyPerson=o.KeyPersonAND
		
		-- within X days of current lead
		o.CalendarDate between dateadd(dd, (-1 \* @CalendarDaysToSearch ), n.CalendarDate ) and dateadd(dd, 0, n.CalendarDate ) 

END

For the sake of argument, assume this stored procedure is written correctly and is known to work. Now, I opened up VS2005 (my boss wants me to use it) and in the Data Sources window, I did the whole Add New Data Source/Data Source Configuration Wizard yada yada and added a TableAdapter to my DataSet for PersonInfo (not the real table name again). I configured the TableAdapterto do a SELECT PersonId FROM PersonInfo and then also to call the Stored Procedure, CountUniqueBirthdays, above. ok, so i write the code to grab the records containing only the PersonIDcolumn and then I iterate over the column, passing each ID into the stored procedure one by one to check if the number of distinct persons with birthdays in a certain 90-day period is bigger than 1 -- AND DON'T ASK ME WHY DID I NOT JUST USE A CURSOR, CURSORS ARE SLOW AND WE HATE THEM, as below. You know how you can open .xsd files in the Dataset Designer in VS2005? Well, I did, right-clicked the PersonInfo TableAdapter, clicked Add > New Query... and in the Add New Query Wizard I picked Existing stored procedure, and then configured CountUniqueBirthdays as the stored procedure to use. try { Console.WriteLine("Querying the MYDATABASE.dbo.PersonInfo table...Please wait."); MYDATABASEDataSetTableAdapters.PersonInfoTableAdapter adapter = new MYDATABASEDataSetTableAdapters.PersonInfoTableAdapter(); MYDATABASEDataSet.PersonInfoDataTable table = adapter.GetData(); Console.WriteLine("Finished getting data."); Console.WriteLine("Running the CountUniqueBirthdays stored procdure. Searc

[John Gathogo]

The return type for CountUniqueBirthdays may not be long (or long? for that matter). Have you tried [int]? Or just receive it into an [object] variable and use Watch to see what it contains? i.e.

object value = adapter.CountUniqueBirthdays(leadID);

[Brian C Hart]

Thank you for your help. Yes, actually the IntelliSense was off and doing a Build Solution fixed it so that the IntelliSense told me the return type was object, and I was off and running! :D Brian

Sincerely Yours, Brian Hart