SqlException in Visual Studio 2005

First of all, I apologize if I'm not posting this in the appropriate group.
This is my first time using these Newsgroups, so I'm trying to find my way
around. That said, this is my problem:

I have a stored procedure I'm calling from Visual Studio 2005. The query
works just fine in query analyzer, but returns a very generic error in VS.
Here's my stored procedure:

CREATE PROCEDURE [dbo].[prc_AssetChangeVendorDetailID]
@OldVendorDetailID int,
@NewVendorDetailID int
AS
SET NOCOUNT ON

UPDATE [Asset]
SET

[VendorDetailID] = @NewVendorDetailID
WHERE
[VendorDetailID] = @OldVendorDetailID

and the code that calls it is:

//Update all asset records with the old vendor detail ID to new ID.
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@OldVendorDetailID", 1);
cmd.Parameters.AddWithValue("@NewVendorDetailID", 2);
cmd.CommandText = "[prc_AssetChangeVendorDetailID]";
cmd.ExecuteNonQuery()

I get an exception with the message of "Line 1: Incorrect syntax near
'prc_AssetChangeVendorDetailID'." Normally, I wouldn't argue with the editor,
but the fact that the procedure executes without a problem in query analyzer
is what has me stumped.

Is there something I'm doing wrong?

KerryMoorman
Wed Feb 01 18:51:27 CST 2006

ComputerGuyCJ,

You might try setting cmd.CommandType = CommandType.StoredProcedure

Kerry Moorman


"ComputerGuyCJ" wrote:

> First of all, I apologize if I'm not posting this in the appropriate group.
> This is my first time using these Newsgroups, so I'm trying to find my way
> around. That said, this is my problem:
>
> I have a stored procedure I'm calling from Visual Studio 2005. The query
> works just fine in query analyzer, but returns a very generic error in VS.
> Here's my stored procedure:
>
> CREATE PROCEDURE [dbo].[prc_AssetChangeVendorDetailID]
> @OldVendorDetailID int,
> @NewVendorDetailID int
> AS
> SET NOCOUNT ON
>
> UPDATE [Asset]
> SET
>
> [VendorDetailID] = @NewVendorDetailID
> WHERE
> [VendorDetailID] = @OldVendorDetailID
>
> and the code that calls it is:
>
> //Update all asset records with the old vendor detail ID to new ID.
> cmd = new SqlCommand();
> cmd.Connection = conn;
> cmd.Parameters.AddWithValue("@OldVendorDetailID", 1);
> cmd.Parameters.AddWithValue("@NewVendorDetailID", 2);
> cmd.CommandText = "[prc_AssetChangeVendorDetailID]";
> cmd.ExecuteNonQuery()
>
> I get an exception with the message of "Line 1: Incorrect syntax near
> 'prc_AssetChangeVendorDetailID'." Normally, I wouldn't argue with the editor,
> but the fact that the procedure executes without a problem in query analyzer
> is what has me stumped.
>
> Is there something I'm doing wrong?

ComputerGuyCJ
Wed Feb 01 19:01:27 CST 2006

Thanks Kerry, that was the answer. I actually did have it in there earlier
and must've deleted it on accident. You're a life-saver. I would've been
chasing my tale for hours. Thanks again!

"Kerry Moorman" wrote:

> ComputerGuyCJ,
>
> You might try setting cmd.CommandType = CommandType.StoredProcedure
>
> Kerry Moorman