InvalidCast Exception: DataRowView To String

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

InvalidCast Exception: DataRowView To String

Author

Topic

dd121
Starting Member

11 Posts

Posted - 2010-11-12 : 10:44:57

I have a function that takes in the Category Name from a combo box selection and then returns the categoryID of tht category from the Categories table.

Like for example:



dbo.Categories
CategoryID                          Category

1                                   Car Maintenance
2                                   House Rent
3                                   Telephone Bill

The user chooses for example car maintenance (from a combo box),the selected item is passed as the input parameter in the following function, and the CategoryId is returned. So the return value should be:

1

The Category ID is an auto incrementing field.

Here is the function code:


ALTER FUNCTION dbo.CatToCatID
	(
	@CategoryName nvarchar(50)
	)
RETURNS int
AS
	BEGIN

	DECLARE @CatID int;
	SELECT @CatID = CategoryID FROM Categories WHERE Category LIKE '%@CategoryName';
	RETURN @CatID;
	END

And i also made a stored procedure for the same function, just in case C#.NET doesnt accept user defined functions in its code.


ALTER PROCEDURE dbo.CategoryToCategoryID
	(
	@newCategory nvarchar(50)
	--@newCatID int OUTPUT
	)
	
AS
		
--SELECT @newCatID= CategoryID FROM dbo.Categories WHERE Category LIKE '%@newCategory';
SELECT CategoryID FROM dbo.Categories WHERE Category LIKE '%n@newCategory';

RETURN SCOPE_IDENTITY();

I used SCOPE_IDENTITY becuz i read it somewhere that in order to return identity column values, u can use this scope identity function.

I tried with the C#.NET code. This takes in the function:



 SqlConnection insertConn = new SqlConnection();
            insertConn.ConnectionString = "Data Source= PC\\SQLEXPRESS;Initial Catalog=finaldb;Integrated Security=True";
            insertConn.Open();
            MessageBox.Show("Connection Open!");

            
            SqlCommand newCatcmd = insertConn.CreateCommand();
            newCatcmd.CommandText = "SELECT dbo.CatToCatID(@CategoryName)";
            newCatcmd.CommandType = CommandType.Text;
            newCatcmd.Parameters.Add(new SqlParameter("@CategoryName", comboBoxCategory.SelectedItem));

            var result = newCatcmd.ExecuteScalar();
            result = (int)result;

            MessageBox.Show("Query Executed Successfully");
            MessageBox.Show(result.ToString());

And this is for the Stored Procedure:


SqlCommand categoryCmd = new SqlCommand("CategoryToCategoryID", insertConn);
            categoryCmd.CommandType = CommandType.StoredProcedure;
            categoryCmd.Parameters.Add("@newCategory", SqlDbType.NVarChar).Value = comboBoxCategory.SelectedItem;
            //For the output parameter, first initialize the parameter:

            SqlParameter outputParam = new SqlParameter("@newCatID",SqlDbType.Int);
            outputParam.Direction = ParameterDirection.Output;
            categoryCmd.Parameters.Add(outputParam);

            dataReader = categoryCmd.ExecuteReader();
           // dataReader = categoryCmd.ExecuteScalar();
             //categoryCmd.ExecuteReader();
            //categoryCmd.

            MessageBox.Show("Executing Category Query");
           // categoryCmd.ExecuteNonQuery();
           // int receiveCategoryID = (int)categoryCmd.ExecuteScalar();
            int receiveCategoryID = Convert.ToInt32(outputParam.Value);
           //int receiveCategoryID = Int32.Parse(categoryCmd.Parameters["@newCatID"].Value.ToString());

In the function case i am getting an "Argument Exception" error. and in the stored procedure case i am getting the invalid Cast exception.
Cannot convert from Data Row View to String.

Can anyone please give me the exact C#.net code on how to retrieve the scalar value.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-12 : 11:52:01

First. SCOPE_IDENTITY() returns the identity from an INSERT statement. So, I'm not sure what that is returning, I would guess it is returning zero (0).

Also, if you have IDs, why not populate your dropdown with Value equal to the ID (1) and the Text equal to the String Value (i.e.: Car Maintenance)? That way you don't need to do a lookup.

dd121
Starting Member

11 Posts

Posted - 2010-11-12 : 16:42:00

hmmm.. can u do that? but the combo box will have only one item populated ritE?? can u tell me how this is done? like:

Display Member: Category
Value Member: CategoryID
??

can u give me the C# Code on how to retrieve the category id.. cuz i need to pass this categoryID parameter, after retrieving it to another stored procedure, which will save this id in another table. so i need to retrieve this categoryID

dd121
Starting Member

11 Posts

Posted - 2010-11-12 : 17:04:30

thankyou for ur hint! i found the solution without having to use the stored procedure or the function:D just the simple:


int selectedValue = Convert.ToInt32(comboBoxCategory.SelectedValue);
            MessageBox.Show(selectedValue.ToString());

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources