SQL Error

[invisible kid]

When calling a stored procedure, I get the following error:

"Syntax error converting the varchar value 'value' to column of datatype int"

But it doesn't make sense, as none of the columns I am referencing are of type int, and I am declaring my return parameter to be of type 'varchar'.

The stored procedure is as follows:



ALTER PROCEDURE sp_GetUserDetails /* How it would appear in QUERY ANALYZER */
(
@UserName VARCHAR(50) = NULL,
@ReturnFirstName VARCHAR(50) = NULL
)

AS
SET @ReturnFirstName = (SELECT FirstName FROM tblUser
WHERE UserName = @UserName)

RETURN @ReturnFirstName



and the code used to call it is:



public string FirstName(string txtUser)
{
SqlConnection myConn2 = new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
SqlCommand myCmd2 = new SqlCommand("sp_GetUserDetails", myConn2);
myCmd2.CommandType = CommandType.StoredProcedure;

SqlParameter objParam3;
SqlParameter returnParam2;

objParam3 = myCmd2.Parameters.Add ("@UserName", SqlDbType.VarChar);
returnParam2 = myCmd2.Parameters.Add ("@ReturnFirstName", SqlDbType.VarChar);

objParam3.Direction = ParameterDirection.Input;
returnParam2.Direction = ParameterDirection.ReturnValue;

objParam3.Value = txtUser;

if (myConn2.State.Equals(ConnectionState.Closed))
{
myConn2.Open();
myCmd2.ExecuteNonQuery();
}
else
{
myConn2.Close();
return (string)returnParam2.Value;
}

return (string)returnParam2.Value;



Any help greatly appreciated!

[takkie]

It is complaining that one of your database column is type int, not your sp or coding...

checked your column properties in your sql server...

tak

[invisible kid]

It is complaining that one of your database column is type int, not your sp or coding...

checked your column properties in your sql server...

tak

The only column I have in my DB which is an int is the 'id' column, but I am not referencing this at all in my stored procedure. And, I cannot change this column.

[sirpelidor]

ALTER PROCEDURE sp_GetUserDetails /* How it would appear in QUERY ANALYZER */
(
@UserName VARCHAR(50) = NULL,
@ReturnFirstName VARCHAR(50) = NULL
)

AS
SET @ReturnFirstName = (SELECT FirstName FROM tblUser
WHERE UserName = @UserName)

RETURN @ReturnFirstName



are you asking the stored procedure to return a first name when you give it a username? if So, I think you need to take off that line:

@ReturnFirstName VARCHAR(50) = NULL

you are telling stored procedure to take 2 input values when your only have 1 input value.

i don't remember my sql syntax, but it should be something like this:


CREATE PROCEDURE dbo.GetUserDetails
@UserName VARCHAR(50),
AS
Begin
SELECT tblUser.FirstName FROM tblUser WHERE tblUser.UserName = @UserName
END
GO


your asp.net code should be something like this:

//connectionString is defined
string result;
SqlConnection con = new SqlConnection(connectionString);
SqlDataReader reader = null;
con.Open();
SqlCommand cmd = new SqlCommand("GetUserDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@userName", txtUser)
reader = cmd.ExecuteReader();
if (reader.Read())
{
result = reader[0].ToString();
}
else
{
result = "No Such User";
}//end if/else

[invisible kid]

are you asking the stored procedure to return a first name when you give it a username? if So, I think you need to take off that line:

@ReturnFirstName VARCHAR(50) = NULL

you are telling stored procedure to take 2 input values when your only have 1 input value.

i don't remember my sql syntax, but it should be something like this:


CREATE PROCEDURE dbo.GetUserDetails
@UserName VARCHAR(50),
AS
Begin
SELECT tblUser.FirstName FROM tblUser WHERE tblUser.UserName = @UserName
END
GO


your asp.net code should be something like this:

//connectionString is defined
string result;
SqlConnection con = new SqlConnection(connectionString);
SqlDataReader reader = null;
con.Open();
SqlCommand cmd = new SqlCommand("GetUserDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@userName", txtUser)
reader = cmd.ExecuteReader();
if (reader.Read())
{
result = reader[0].ToString();
}
else
{
result = "No Such User";
}//end if/else


This worked a treat, thanks very much!