CODEWITHSUNDEEP
c#asp.netsqlsql-server-2008
Cipher
Cipher

Reputation: 6082

Adapter Unable to fill data in dataset: Exception thrown

I have a drop down list on my web page, the selected item of which has to be passed to the Stored Procedure query in the database. However, I am getting a strange error here at adp.Fill(ds) in my bind method.

The exception always says "Incorrect syntax near the keyword 'to'.", where to is always the second word in the drop down option. For eg: Items in dropdown- 9 to 5 (2nd word: to) , age of empires (2nd word: of)
Exceptions:
Incorrect syntax near the keyword 'to'
Incorrect syntax near the keyword 'of' etc.

Here's the method that I am using:

private void Bind(string ss)
{
    SqlDataAdapter adp = new SqlDataAdapter("Retrieve", ConfigurationManager.ConnectionStrings["cn"].ConnectionString);
    DataSet ds = new DataSet();
    adp.SelectCommand.CommandType = CommandType.StoredProcedure;
    adp.SelectCommand.Parameters.Add("@s1", SqlDbType.NVarChar, 255).Value = ss;
    adp.SelectCommand.Parameters.Add("@s2", SqlDbType.NVarChar, 255).Value = DropDownList1.SelectedItem.ToString();
    adp.Fill(ds);
    DataList1.DataSource = ds;
    DataList1.DataBind();
}

StoredProcedure

ALTER PROCEDURE [dbo].[Retrieve_SegmentedQ]
(
    @s1 nvarchar(255),
    @s2 nvarchar(255)
)

AS
BEGIN
    DECLARE @query nvarchar(max)

    SET @query = 'SELECT DISTINCT Details from tbData WHERE Name IN (' + @s1 + ') AND UnsegmentedQuery=' + @s2

    exec sp_executesql @query
END

Any suggestions what's wrong here?

Upvotes: 1

Views: 2136

Answers (3)

Ankush Jain
Ankush Jain

Reputation: 71

Your command text name should be same as your procedure name....and here they both are different

Upvotes: 0

Marcin
Marcin

Reputation: 3262

The bug is here:

SET @query = 'SELECT DISTINCT Details from tbData WHERE Name IN (' + @s1 + ') AND UnsegmentedQuery=' + @s2

You have stored procedure but using it as query, so making something like sql injection. as result you will have following query:

SET @query = 'SELECT DISTINCT Details from tbData WHERE Name IN (5 to 9) AND UnsegmentedQuery=age of empires

which is wrong. make it in following way add single quotation marks to your params. 

SET @query = 'SELECT DISTINCT Details from tbData WHERE Name IN (''' + @s1 + ''') AND UnsegmentedQuery=''' + @s2 + ''''

Upvotes: 2

PraveenVenu
PraveenVenu

Reputation: 8337

update the procedure like below

ALTER PROCEDURE [dbo].[Retrieve_SegmentedQ]
(
@s1 nvarchar(255),
@s2 nvarchar(255)
)

AS
BEGIN
DECLARE @query nvarchar(max)

SET @query = 'SELECT DISTINCT Details from tbData WHERE Name IN (''' + @s1 + ''') AND UnsegmentedQuery=''' + @s2  + ''''

exec sp_executesql @query

END

Upvotes: 2

Related Questions