Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Error message when passing criteria to Stored procedure via C#

I am attempting to pass a Parameter to a stored procedure but get an error stating: Incorrect syntax near 'Report'.   If I excute the stored procedure with the same parameter string directly with SQL  server I do not get an error message.  My code is below.  Is it something with the double single quotes in my string?
sqlCommand.Parameters.AddWithValue("@selectFields",  Convert.ToString("b.[SystemId] as [fp_empno], ''Custom Report'' as reportTitle,  a.[Department] as CustomField0, '' '' AS CustomField1, '' '' AS CustomField2, '' '' AS CustomField3, '' '' AS CustomField4, ''Department'' as CustomLabel0, '' '' AS CustomLabel1, '' '' AS CustomLabel2, '' '' AS CustomLabel3, '' '' AS CustomLabel4 "));
 
                    SqlDataAdapter dataAdapter = new SqlDataAdapter();
                    dataAdapter.SelectCommand = sqlCommand;
                    dataAdapter.Fill(ds,"Search");

Open in new window

0
eshurak
Asked:
eshurak
  • 6
  • 4
  • 2
  • +1
1 Solution
 
Daniel WilsonCommented:
>>Is it something with the double single quotes in my string?

I think so.  Try dropping them to single single quotes.
0
 
eshurakAuthor Commented:
When I tried that I got the following error message:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

SQL Server needs the double single qoutes, but C# does not seem to like them.
0
 
Daniel WilsonCommented:
would you post the procedure that uses that parameter?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
eshurakAuthor Commented:
The procedure is not relevent it's a c# issue as the procedure itself accepts the same string.
0
 
Daniel WilsonCommented:
SQL Server is throwing the error.

If you can't post the procedure, can you at least confirm that the parameter is being used as a portion of a dynamic SQL statement?

With that in mind ... would you try this?

sqlCommand.Parameters.AddWithValue("@selectFields",  Convert.ToString("'b.[SystemId] as [fp_empno], ''Custom Report'' as reportTitle,  a.[Department] as CustomField0, '' '' AS CustomField1, '' '' AS CustomField2, '' '' AS CustomField3, '' '' AS CustomField4, ''Department'' as CustomLabel0, '' '' AS CustomLabel1, '' '' AS CustomLabel2, '' '' AS CustomLabel3, '' '' AS CustomLabel4 '"));

Open in new window

0
 
eshurakAuthor Commented:
I don't get the error, but it the dataset does not contain any data now.
0
 
CyrexCore2kCommented:
Using a sproc to execute dynamic sql kind of defeats the purpose...

Why not just execute the query itself and save yourself the trouble?
0
 
eshurakAuthor Commented:
The sproc returns values from several sources.  In addition to criteria I am passing it the fields (@selectFields) to be returned as choosen by the user.  This creates a dynamic custom report in crystal.  But at this point I'm going to scrap the selectfields part and have the sproc return all fields and use C# to drop the fields I don't need from the dataset and have it return several other ones.

Thanks guys.
0
 
Anurag ThakurTechnical ManagerCommented:
the query sent by you is the issue as its not a well formed query
your query contains lines like this (copying only a part of the line)

'' '' AS CustomField4 -- what does this line mean to sql

select '' '' AS CustomField4 -- if you jsut run this line you get error

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

this is because you are saying select this column and the output column name will be CustomField4 but what column to select?

Can you please tell us what you are trying to achieve so that we can help better
0
 
Daniel WilsonCommented:
Obviously you're doubling up the quotes for use w/ dynamic SQL.

We need to see the procedure, I'm afraid, in order to say how best to do that.
0
 
Daniel WilsonCommented:
Hold on, looks like some escape sequences may solve this ...
http://blogs.msdn.com/csharpfaq/archive/2004/03/12/88415.aspx


sqlCommand.Parameters.AddWithValue("@selectFields",  Convert.ToString("b.[SystemId] as [fp_empno], \'\'Custom Report\'\' as reportTitle,  a.[Department] as CustomField0, \'\' \'\' AS CustomField1, \'\' \'\' AS CustomField2, \'\' \'\' AS CustomField3, \'\' \'\' AS CustomField4, \'\'Department\'\' as CustomLabel0, \'\' \'\' AS CustomLabel1, \'\' \'\' AS CustomLabel2, \'\' \'\' AS CustomLabel3, \'\' \'\' AS CustomLabel4 "));

Open in new window

0
 
Anurag ThakurTechnical ManagerCommented:
whatever you do with escape sequences but this line will always going to thorw an error
\'\' \'\' AS CustomField1

Select '' '' AS CustomField1
0
 
Daniel WilsonCommented:
You double up the single quotes when building a string to use as a dynamic SQL statement.  That's how T-SQL escapes them.

So ...

Declare @MyString varchar(8000)
set @MyString= 'Select '' '' AS CustomField1'
Exec (@MyString)

... is the same as ...

Select ' ' AS CustomField1

... and we're all comfortable with that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now