Cannot find SQL variable in SqlDataSource

Question

I have the following SqlDataSource on a GridView. All works except for Delete Function.

What am I doing wrong , no error is given but no result either.

How can this be solved?

 <asp:SqlDataSource ID="BookingsSQL" runat="server" ConnectionString="<%$    ConnectionStrings:BookingsConnectionString %>"
 OldValuesParameterFormatString="original_{0}" ConflictDetection="OverwriteChanges"
 SelectCommand="SELECT [ID], [programme name] AS programme_name, [Start], [Finish], [Source], [Destination], [Comment] FROM [Bookings]"
 DeleteCommand="DELETE FROM [Bookings] WHERE [programme name] = @ID" InsertCommand="INSERT INTO [Bookings] ([programme name], [Start], [Finish], [Source], [Destination], [Comment]) VALUES (@programme_name, @Start, @Finish, @Source, @Destination, @Comment)"
 UpdateCommand="UPDATE [Bookings] SET [programme name] = @programme_name, [Start] = @Start, [Finish] = @Finish, [Source] = @Source, [Destination] = @Destination, [Comment] = @Comment WHERE [ID] = @ID">
 <DeleteParameters>
      <asp:Parameter Name="ID" Type="Int32" />
 </DeleteParameters>
 <InsertParameters>
      <asp:Parameter Name="programme_name" Type="String" />
      <asp:Parameter Name="Start" Type="DateTime" />
      <asp:Parameter Name="Finish" Type="DateTime" />
      <asp:Parameter Name="Source" Type="String" />
      <asp:Parameter Name="Destination" Type="String" />
      <asp:Parameter Name="Comment" Type="String" />
 </InsertParameters>
 <UpdateParameters>
      <asp:Parameter Name="ID" Type="Int32" />
      <asp:Parameter Name="Start" Type="DateTime" />
      <asp:Parameter Name="Finish" Type="DateTime" />
      <asp:Parameter Name="Source" Type="String" />
      <asp:Parameter Name="Destination" Type="String" />
      <asp:Parameter Name="Comment" Type="String" />
      <asp:Parameter Name="programme_name" Type="String" />
 </UpdateParameters>

Mark Byers · Accepted Answer · 2011-11-08 17:11:17Z

4

I think this:

DELETE FROM [Bookings] WHERE [programme name] = @ID

should be this:

DELETE FROM [Bookings] WHERE [ID] = @ID

answered Nov 8, 2011 at 17:11

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

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

1 Comment

user685590 Over a year ago

I changed that DeleteCommand="DELETE FROM [Bookings] WHERE [ID] = @ID" , I receive the same result.. nothing . Some how its not able to refer to ID , if I try a valid integer eg WHERE [ID] = 9" the delete command works.

pjvds · Accepted Answer · 2011-11-08 17:13:40Z

1

You delete by [programme name] where from your other queries it looks like you want to delete by the [ID] column.

Since that columns exists, you won't get an error but also no matches to delete. Change the query to:

 DELETE FROM Bookings WHERE ID=@ID

answered Nov 8, 2011 at 17:13

pjvds

9566 silver badges11 bronze badges

Collectives™ on Stack Overflow

Cannot find SQL variable in SqlDataSource

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related