C# SQL statement - Syntax error in query. Incomplete query clause

Question

Below is the code I have, I can't for the life of me work out what is wrong with the query.

I originally had the error "no value given for 1 or more parameters", which seems to have gone away (although again I don't even know why I was getting it).

The connection is opened prior to this code. The parameter GVars.thisFY is a string = "FY13" - this table definitely exists. The parameter GVars.currentDate is a DateTime = today.

Records definitely exist for this [Destination] and [Next Collection] range:

string sql;
OleDbDataAdapter adapter;

sql = "SELECT * FROM @CurFY WHERE [Destination] = @Destination AND [Next Collection] BETWEEN @NextCollectionA AND @NextCollectionB;";

// Create the command object
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;

// Add values to the fields
cmd.Parameters.AddWithValue("@CurFY", GVars.thisFY);
cmd.Parameters.AddWithValue("@Destination", "Henwood");
cmd.Parameters.AddWithValue("@NextCollectionA", GVars.currentDate);
cmd.Parameters.AddWithValue("@NextCollectionB", GVars.currentDate.AddDays(1));

adapter = new OleDbDataAdapter(cmd.CommandText, conn);

try
{
    adapter.Fill(ds);

    GVars.bLblLastUpdate = DateTime.Now.ToString("HH:mm:ss");
}
catch (Exception ex)
{
}

EDIT: I have changed the code to remove the table parameter as below, still getting the "no value given for 1 or more parameters" though which I can't pin down..

EDIT2: I removed the extra stuff so the post relates only to the original question, which has been answered. I will make a new question for my strange "no value given" error

You can't have the table name as a parameter. See also this question: stackoverflow.com/questions/14124261/… — René Wolferink
– René Wolferink, Commented Jun 10, 2013 at 11:03
your main mistake here was swallowing the exception; there are very few good places to do that, and this isn't one of them — Marc Gravell
– Marc Gravell, Commented Jun 10, 2013 at 11:06
What database system (and which version) are you using? SQL is just the query language - and it's used by many database systems, that doesn't really tell us anything.... — marc_s
– marc_s, Commented Jun 10, 2013 at 11:07
@Marc Gravell what do you mean? The catch block is empty since I'm only using it to see the error my coding is producing, once this is sorted I will add a less generalized catch for network/database issues etc. Is that what you meant? — iabbott
– iabbott, Commented Jun 10, 2013 at 13:35

Sergey Kalinichenko · Accepted Answer · 2013-06-10 11:08:28Z

5

You cannot parameterize queries with names of tables, views, or columns. Only data members can be parameterized.

You need to make your SQL dynamically, e.g. like this:

sql = string.Format(
    "SELECT * FROM {0} WHERE [Destination] = @Destination AND [Next Collection] BETWEEN @NextCollectionA AND @NextCollectionB;"
,  GVars.thisFY
);

This should be done only if GVars.thisFY is controlled by your code, e.g. comes from a pre-defined list or checked for absence of non-alphanumeric characters to avoid SQL injection attacks.

edited Jun 10, 2013 at 11:08

answered Jun 10, 2013 at 11:02

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

iabbott Over a year ago

don't i feel stupid... ok removed that parameter, now it tells me again that there is no value given for 1 or more parameters

René Wolferink Over a year ago

If GVars.thisFY somehow comes from the client (e.g. a dropdown, which could be manually altered), this would impose a security vulnerability. Sanitise your input before sticking it into a query.

Sergey Kalinichenko Over a year ago

@IanAbbott "no value given for 1 or more parameters" That's strange... Did you also remove the @ from the query? Your query should have only 3 @s now, and there should be 3 calls of AddWithValue in the code.

iabbott Over a year ago

i have updated my original post with the current revised code

Gord Thompson Over a year ago

@IanAbbott Just a suggestion: The code would be more robust (and perhaps somewhat safer, too) if the table name was enclosed in square brackets, i.e., SELECT * FROM [{0}] ....

|

Devart · Accepted Answer · 2013-06-10 11:03:09Z

0

Try this one -

sql = Sring.Format(
    "SELECT * FROM {0} WHERE [Destination] = @Destination AND [Next Collection] BETWEEN @NextCollectionA AND @NextCollectionB;", 
    GVars.thisFY
)

cmd.Parameters.AddWithValue("@Destination", "Henwood");
cmd.Parameters.AddWithValue("@NextCollectionA", GVars.currentDate);
cmd.Parameters.AddWithValue("@NextCollectionB", GVars.currentDate.AddDays(1));

answered Jun 10, 2013 at 11:03

Devart

120k23 gold badges172 silver badges189 bronze badges

1 Comment

René Wolferink Over a year ago

If GVars.thisFY somehow comes from the client (e.g. a dropdown, which could be manually altered), this would impose a security vulnerability. Sanitise your input before sticking it into a query.

Collectives™ on Stack Overflow

C# SQL statement - Syntax error in query. Incomplete query clause

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related