Line 1: Incorrect syntax near 'sp_Collect'.

[=?Utf-8?B?QW5kcmV3?=]

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]

Andrew wrote:

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

[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" <An****@discussions.microsoft.comwrote in message
news:7C**********************************@microsof t.com...

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

[=?Utf-8?B?QW5kcmV3?=]

The line:
cm.CommandType = CommandType.StoredProcedure;

did the trick.
Thanks

"Marc Gravell" wrote:

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

[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;
}


"Andrew" <An****@discussions.microsoft.comwrote in message
news:7C**********************************@microsof t.com...

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