CODEWITHSUNDEEP
c#asp.netsql
Cineno28
Cineno28

Reputation: 909

Incorrect syntax on SQL CommandText

I'm trying to build a SQL query and will be passing a string as a parameter. However, I'm getting and incorrect syntax error. I've printed out the values and all seems fine to me though.

I have a string "sqlString" which is built through a series of concatenations, producing this:

sqlString = " and (event_id=60 OR event_id=61 OR event_id=56 OR event_id=73)"

SqlCommand totalCmd = new SqlCommand();

totalCmd.CommandText = "SELECT sum(datediff(hour,[event_start],[event_end])) as Timeworked FROM event WHERE (event_start >= @StartDate and event_end <= @EndDate) @mySqlString";

totalCmd.Parameters.AddWithValue("StartDate", txtStartDate.Text);
totalCmd.Parameters.AddWithValue("EndDate", txtEndDate.Text);
totalCmd.Parameters.AddWithValue("mySqlString", sqlString);

totalDT = SqlComm.SqlDataTable(totalCmd);

This is producing an error

Incorrect syntax near '@mySqlString'

I've ran this query directly in SQL by entering in the parameters, and it runs fine. What is the issue with passing the mySqlString parameter?

Upvotes: 1

Views: 782

Answers (2)

David S.
David S.

Reputation: 6105

You can't add sqlString as a parameter. You should concatenate it directly to the CommandText instead:

totalCmd.CommandText = "SELECT sum(datediff(hour,[event_start],[event_end])) as Timeworked FROM event WHERE (event_start >= @StartDate and event_end <= @EndDate) " + sqlString;

The whole point (besides being tidy) of adding values to the Parameters instead of concatenating is to clean the values for malicious commands - i.e. the parameters should only be pure values. So the way you do it now, the sqlString is interpreted as a SQL injection attack, and will essentially be purged by that check.

Upvotes: 3

Karl Anderson
Karl Anderson

Reputation: 34846

You need to put the @ symbol in front of your parameters, like this:

totalCmd.Parameters.AddWithValue("@StartDate", txtStartDate.Text);
totalCmd.Parameters.AddWithValue("@EndDate", txtEndDate.Text);
totalCmd.Parameters.AddWithValue("@mySqlString", sqlString);

Note: The syntax you had was not finding a match, so the parameter value was not being substituted, thus it was passing @mySqlString in the query.

Upvotes: 2

Related Questions