Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error message when passing criteria to Stored procedure via C#

Posted on 2008-09-29
13
Medium Priority
?
240 Views
Last Modified: 2013-12-17
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
Comment
Question by:eshurak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22598673
>>Is it something with the double single quotes in my string?

I think so.  Try dropping them to single single quotes.
0
 
LVL 3

Author Comment

by:eshurak
ID: 22598697
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22598716
would you post the procedure that uses that parameter?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 3

Author Comment

by:eshurak
ID: 22598773
The procedure is not relevent it's a c# issue as the procedure itself accepts the same string.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22598915
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
 
LVL 3

Author Comment

by:eshurak
ID: 22599077
I don't get the error, but it the dataset does not contain any data now.
0
 
LVL 14

Expert Comment

by:CyrexCore2k
ID: 22599472
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
 
LVL 3

Author Comment

by:eshurak
ID: 22599526
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
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22602183
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22605236
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22605575
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
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22606350
whatever you do with escape sequences but this line will always going to thorw an error
\'\' \'\' AS CustomField1

Select '' '' AS CustomField1
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 total points
ID: 22606411
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question