CODEWITHSUNDEEP
c#sql-serverado.net
Matt
Matt

Reputation: 1155

SqlCommand error "Conversion failed," though variable and returned value both string

I'm new to C#, so forgive me if my lingo is amateur.

I have a method that executes a Stored Procedure in my Service-Based Database. The SP returns a single char(32) value, which I am attempting to assign to a string variable. But I get an error message indicating a conversion failure:

An unhandled exception of type 'System.Data.SqlClients.SqlException' occured in System.Data.dll Additional information: Conversion failed when converting the varchar value '...correct alphanumeric char(32) value here...' to datatype int.

I can't figure out why it thinks there's an INT conversion going on anywhere. I even add an explicit (string) declaration to the ExecuteScalar(), but with no effect.

Here's the method:

internal static string getAuthKey(string ClientNo)
{
    SqlConnection con = new SqlConnection(Constants.DbConnString); //Define db connection
    SqlCommand cmd = new SqlCommand("dbo.GetAuthKey", con); //Define db command
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@ClientNo",ClientNo); //Specify parameter value
    con.Open(); //Open connection
    string AuthKey = (string)cmd.ExecuteScalar(); //Execute procedure, store result
    /\ ***Error occurs on line above*** /\
    con.Close(); //Close connection
    return AuthKey;
}

... and the method call from Main()...

string ClientNo = "00500";
string AuthKey = Constants.getAuthKey(ClientNo);

Stored Procedure code:

CREATE PROCEDURE [dbo].[GetAuthKey] @ClientNo char(5) AS
DECLARE @AuthKey CHAR(32);
SET @AuthKey =
(   SELECT TOP 1 AuthKey
    FROM dbo.AuthKey
    WHERE ClientNo = @ClientNo )
RETURN @AuthKey

Upvotes: 0

Views: 378

Answers (1)

Cam Bruce
Cam Bruce

Reputation: 5689

The problem is that you are using the RETURN statement with your parameter, which is a char. RETURN types expect an int which is where the error is occurring.

ExecuteScalar() returns the first column in the first row, and you are not including a result set.

Change it to

 SELECT @AuthKey

Upvotes: 4

Related Questions