JDawg848 JDawg848 - 5 months ago 14
SQL Question

Conversion failed when converting the nvarchar value 'OrgID' to data type int

So I'm teaching myself how to do stored procedures in SQL Server Management Studio, and I am attempting to make one to replace a string query I had in my C# code in Visual Studio. My original bit of code that I had was

InsertQuery = "INSERT INTO License (OrgID, LicensingKey) VALUES (@OrgID, @LicensingKey)"; // Query to insert into the database table.
SqlCommand cmd = new SqlCommand(InsertQuery, con);
cmd.Parameters.Add("@LicensingKey", SqlDbType.Int).Value = LicensingKey;
cmd.Parameters.Add("@OrgID", SqlDbType.Int).Value = OrgID;
cmd.ExecuteNonQuery();


Where OrgID and LicensingKey are two ints passed by the method this code is in.

My stored procedure that I made in my database is this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LicenseInsert]
@OrgID int = OrgID,
@LicensingKey int = LicensingKey

AS
BEGIN
SET NOCOUNT ON;
INSERT INTO License (OrgID, LicensingKey)
VALUES (@OrgID, @LicensingKey)
END


And now that same bit of code I had I replaced with this:

InsertQuery = "EXEC LicenseInsert "; // Query to insert into the database table.
SqlCommand cmd = new SqlCommand(InsertQuery, con);
cmd.ExecuteNonQuery();


BUT this gives me the error of "Conversion failed when converting the nvarchar value 'OrgID' to data type int", and it's saying that

cmd.ExecuteNonQuery();


is the culprit.

Can someone walk me through what I am doing wrong?
Thank you kindly!

Answer

I would expect that you'd get some sort of error that a parameter is missing, but to execute a stored procedure you need to use just the stored procedure name and change the command type to StoredProcedure:

InsertQuery = "LicenseInsert"; // Query to insert into the database table.
SqlCommand cmd = new SqlCommand(InsertQuery, con);
cmd.CommandType = CommandType.StoredProcedure;  // <------------

cmd.Parameters.Add("@LicensingKey", SqlDbType.Int).Value = LicensingKey;  
cmd.Parameters.Add("@OrgID", SqlDbType.Int).Value = OrgID;

cmd.ExecuteNonQuery();

Also I think your stored procedure definition is invalid. it should be:

ALTER PROCEDURE [dbo].[LicenseInsert]
@OrgID int,
@LicensingKey int
Comments