By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,627 Members | 2,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,627 IT Pros & Developers. It's quick & easy.

Line 1: Incorrect syntax near 'sp_Collect'.

P: n/a
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
Feb 13 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Feb 13 '08 #2

P: n/a
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
Feb 13 '08 #3

P: n/a
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

Feb 13 '08 #4

P: n/a
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
Feb 13 '08 #5

P: n/a
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

Feb 15 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.