Line 1: Incorrect syntax near 'sp_Collect'.

[Andrew]

Hi,

What is wrong with this code ? I got this error:

" Line 1: Incorrect syntax near 'sp_Collect'. "

which appears at the last line when | call the cm.ExecuteScalar . The sp
takes an int ID and returns a string Data. Using the debugger, I see that the
value of ID is correct.
Is this a C# problem, or SQL Server 7 problem ?

public string GetID(int ID)
{
SqlCommand cm = new SqlCommand("sp_Collect'", sqlCon);
cm.Parameters.Add(new SqlParameter("@ID", ID));
return (string) cm.ExecuteScalar();
}

Thanks
Andrew

 

[Pete Kane]

Hi,

What is wrong with this code ? I got this error:

" Line 1: Incorrect syntax near 'sp_Collect'. "

which appears at the last line when | call the cm.ExecuteScalar . The sp
takes an int ID and returns a string Data. Using the debugger, I see that the
value of ID is correct.
Is this a C# problem, or SQL Server 7 problem ?

public string GetID(int ID)
{
SqlCommand cm = new SqlCommand("sp_Collect'", sqlCon);
cm.Parameters.Add(new SqlParameter("@ID", ID));
return (string) cm.ExecuteScalar();
}

Thanks
Andrew

Wrap it in a try catch and output the message

 

[Andrew]

I did that and the error message is :
" Line 1: Incorrect syntax near 'sp_Collect'. "

Is it a problem w the sp itself ? coz when I test it in QueryAnalyzer it
works fine.

cheers
Andrew

 

[Marc Gravell]

Try adding:
cm.CommandType = CommandType.StoredProcedure;
(befoer the ExecuteScalar())

You might also need to check whether your proc *selects* the value or
*prints* the value; ExecuteScalar() will only work for a SELECT - but
if this was the problem I would expect a different error message.

Marc

 

[Rene]

Not sure if this is the problem but if you copied and paste the code from
your project to the post, it looks like you have an extra apostrophe
character at the end of sp_Collect.

 

[Andrew]

The line:
cm.CommandType = CommandType.StoredProcedure;

did the trick.
Thanks

 

[Kelly Herald]

First of all, you really haven't showed how the stored procedure is defined.
Are you using a RETURN statement to return the string in the stored
procedure? Or are you using a SELECT statement to return the string as a
record?

Stored procedures only return INT values. If you need a string returned you
can use a parameter in the stored procedure with the OUTPUT tag. Short
example below:

CREATE PROCEDURE sp_Collect
@ID INT,
@ReturnValue VARCHAR(255) OUTPUT
AS
SELECT @ReturnValue = stringfield FROM sometable WHERE idfield = @ID
GO

C# code:
public string GetID(int ID)
{
SqlCommand cm = new SqlCommand("sp_Collect", sqlCon);
cm.Parameters.Add(new SqlParameter("@ID", ID));
SqlParameter paramReturnValue = cm.Parameters.Add("@ReturnValue",
SqlDbType.VarChar, 14);
paramReturnValue.Direction = ParameterDirection.Output;
cm.ExecuteNonQuery();
return (string) paramReturnValue;
}