Calling a Stored Function 1

[BoulderBum]

I have a stored SQL Server function fnValidate that I want to call in my code-behind, but I can't figure out how it's supposed to work.

So far, I have this:

String useName = txtUserName.Value;
myCommand = new SqlCommand( "fnValidate", myConnection );

SqlParameter pUserName = new SqlParameter( "@userN", SqlDbType.Char, 20 );
SqlParameter pPassword = new SqlParameter( "@pass", SqlDbType.Char, 16 );
myCommand.Parameters.Add( pUserName );
myCommand.Parameters.Add( pPassword );
pUserName.Value = useName;
pUserName.Value = pswPassword.Value;
myConnection.Open();


and I'm trying to do something like:

if( myCommand.ExecuteScalar() == 1 )
//do something


but it doesn't work. I've also tried calling:

if( myCommand.ExecuteScalar().Equals( 1 ) )
//do something


but I keep getting the error message:

Line 1: Incorrect syntax near 'fnValidate'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'fnValidate'.


What am I doing wrong?

Oh, here's the stored function:

CREATE FUNCTION fnValidate( @userN char(20), @pass char(16) )
RETURNS int

AS
BEGIN
declare @retVal int
IF @userN = null
set @retVal = 0
ELSE IF ( SELECT client_password FROM user_info WHERE client_user_name = @userN ) = @pass
set @retVal = 1
ELSE
set @retVal = 0

RETURN @retVal
END

 

[Rhys666]

Shouldn't...
<CODE>
String useName = txtUserName.Value;
myCommand = new SqlCommand( &quot;fnValidate&quot;, myConnection );
</CODE>

be...
<CODE>
String useName = txtUserName.Value;
SqlCommand myCommand = new SqlCommand( &quot;fnValidate&quot;, myConnection );
</CODE>

...?


Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[BoulderBum]

Thanks for the reply. The SqlCommand &quot;myCommand&quot; was actually initialized with the empty constructor outside of the code I posted.

Any other ideas?

 

[crazyboybert]

Are you setting a commandType for the commansd object? This defaults to text i believe. I have never tried to call a function like this but do use stored procs this weay all the time. It may work the same for functions though there is nt a commandType specifcally for them. You could try

myCommand.CommandType = CommandType.StoredProcedure;

and see if that works. Alternatively (and this is a complete guess as i have never tried!!) you may be abel to leave the command type as text and get away with something like

myCommand = new SqlCommand();
myCommand.CommandText = &quot;exec fnValidate &quot; + useName + &quot;, &quot; + pswPassword.Value;

like i say entirely a guess though i would be interested to hear if it works...

hth

Rob

------------------------------------

On with the dance! let joy be unconfined;
No sleep till morn, when Youth and Pleasure meet
To chase the glowing hours with flying feet.
-Byron

------------------------------------

 

[BoulderBum]

Actually, it does appear that not setting the CommandType was my problem after all, there is an additional issue, however. Now I get an error message saying:

Object reference not set to an instance of an object.
How can I cast the return value of ExecuteSclaer to an int successfully (given that the stored function returns an int)?

I tried:

(int)myCommand.ExecuteScalar()

but with no luck.

 

[jfrost10]

When I return scaler, I usually assign it to a value.

For instance,

Dim value as integer

value = myCommand.ExecuteScaler()

That way, value gets whatever the value is comingback. Thats how I do it in my vb.net code, and it works fine.

hth
D'Arcy

 

[BoulderBum]

Hmmm... I actually tried:

int temp = (int)myCommand.ExecuteScalar();

earlier and got the same error message... maybe my problem is in the stored function itself (or how I'm calling it).

Any ideas anyone?

Oh, and is anyone familiar with how to set char strings in SQL Server Query anylizer?

I'm trying to do this:

DECLARE @pass char( 16 )
SET @pass = &quot;test&quot;

and it gives me an error message saying:

Invalid column name 'test'.

All I want to do is send some char strings to my function to see if it's working all right (if I change the function to accept ints, then hard code the name values it works fine).

 

[meph]

BoulderBum,

Did you ever resolve this problem? I am having the same issue

 

[crazyboybert]

Hi BB/Meph

Missed this when Boulder posted last year but I think the problem is the quotes. In TSQL you use single quotes to identify strings not double quotes. Therefore the following should work...

set @pass = 'test'

Rob

Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927