Delete data from GridView and ObjectDataSource

The function that is supposed to delete a row, is not working. The function is called, and the windows is refreshed, but the row is not deleted.
Can anyone see anything wrong with this code:

public static void DeleteBlog(int original_BlogID)
{
string insertCommand = "DELETE FROM Blog WHERE BlogID = @.BlogID";
SqlConnection myConnection = new SqlConnection(Blog.ConnectionString);
SqlCommand command = new SqlCommand(insertCommand, myConnection);

command.Parameters.Add(new SqlParameter("@.BlogID", original_BlogID));

myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
}You must be using ASP.NET 1.1. The Parameters.Add contructor has 2 overloads with 2 parameters:

Add(parameterName, object)
Add(parameterName, SqlDbType)

Since your original_BlogID is numeric, ADO.NET is assuming you are using the 2nd overload (parameterName, SqlDbType), so your value is not assigned to the parameter. Try it this way instead:

command.Parameters.Add(new SqlParameter("@.BlogID", SqlDbType.Int)).Value = original_BlogID;

Or, as a shortcut:

command.Parameters.Add("@.BlogID", SqlDbType.Int).Value = original_BlogID;

The overload with the object parameter has been deprecated in ADO.NET 2.0 because of this issue, and has been replaced with the method AddWithValue.|||Still not working, and I cannot see what's wrong either :(
The page is refreshed, but no rows are deleted .

public static void DeleteBlogg(int original_blogID) { //System.Diagnostics.Debug.WriteLine("original_blogID og BlogID" + original_blogID + BlogID); string insertCommand = "DELETE FROM Blog WHERE BlogID = @.BlogID"; SqlConnection myConnection = new SqlConnection(Blog.ConnectionString); SqlCommand command = new SqlCommand(insertCommand, myConnection); command.Parameters.Add(new SqlParameter("@.BlogID", SqlDbType.Int)).Value = original_blogID; myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); }|||and this is the aspx page:
Still it is not working, the page is refreshed, but no rows are deleted

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="DeleteBlogg"
SelectMethod="ListMyBlogs" TypeName="Blog">
<DeleteParameters>
<asp:Parameter Name="original_blogID" Type="Int32" />
</DeleteParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="Message" HeaderText="Message" SortExpression="Message" />
<asp:BoundField DataField="MessageCreated" HeaderText="MessageCreated" ReadOnly="True"
SortExpression="MessageCreated" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="BlogID" HeaderText="BlogID" SortExpression="BlogID" />
<asp:BoundField DataField="MessageUpdated" HeaderText="MessageUpdated" ReadOnly="True"
SortExpression="MessageUpdated" />
</Columns>
</asp:GridView>

Delet function not working on Gridview

I 'm having trouble with the delete function on Gridview - the update works great but I keep getting the error below when I try to delete

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.
The statement has been terminated.

I using the products table in Northwind to learn this stuff.

The code that I used is below:

<%

@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Gigs.aspx.cs"Inherits="Gigs" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @.ProductID"InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@.ProductName, @.SupplierID, @.CategoryID, @.QuantityPerUnit, @.UnitPrice, @.UnitsInStock, @.UnitsOnOrder, @.ReorderLevel, @.Discontinued)"SelectCommand="SELECT * FROM [Products]"UpdateCommand="UPDATE [Products] SET [ProductName] = @.ProductName, [SupplierID] = @.SupplierID, [CategoryID] = @.CategoryID, [QuantityPerUnit] = @.QuantityPerUnit, [UnitPrice] = @.UnitPrice, [UnitsInStock] = @.UnitsInStock, [UnitsOnOrder] = @.UnitsOnOrder, [ReorderLevel] = @.ReorderLevel, [Discontinued] = @.Discontinued WHERE [ProductID] = @.ProductID"><DeleteParameters><asp:ParameterName="ProductID"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/><asp:ParameterName="ProductID"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/></InsertParameters></asp:SqlDataSource>

</div><asp:GridViewID="GridView1"runat="server"AllowPaging="True"AutoGenerateColumns="False"CellPadding="4"DataKeyNames="ProductID"DataSourceID="SqlDataSource1"ForeColor="#333333"GridLines="None"><FooterStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><Columns><asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="ProductName"HeaderText="ProductName"SortExpression="ProductName"/><asp:BoundFieldDataField="SupplierID"HeaderText="SupplierID"SortExpression="SupplierID"/><asp:BoundFieldDataField="CategoryID"HeaderText="CategoryID"SortExpression="CategoryID"/><asp:BoundFieldDataField="QuantityPerUnit"HeaderText="QuantityPerUnit"SortExpression="QuantityPerUnit"/><asp:BoundFieldDataField="UnitPrice"HeaderText="UnitPrice"SortExpression="UnitPrice"/><asp:BoundFieldDataField="UnitsInStock"HeaderText="UnitsInStock"SortExpression="UnitsInStock"/><asp:BoundFieldDataField="UnitsOnOrder"HeaderText="UnitsOnOrder"SortExpression="UnitsOnOrder"/><asp:BoundFieldDataField="ReorderLevel"HeaderText="ReorderLevel"SortExpression="ReorderLevel"/><asp:CheckBoxFieldDataField="Discontinued"HeaderText="Discontinued"SortExpression="Discontinued"/></Columns><RowStyleBackColor="#FFFBD6"ForeColor="#333333"/><SelectedRowStyleBackColor="#FFCC66"Font-Bold="True"ForeColor="Navy"/><PagerStyleBackColor="#FFCC66"ForeColor="#333333"HorizontalAlign="Center"/><HeaderStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><AlternatingRowStyleBackColor="White"/></asp:GridView></form>

</

body>

</

html>

Any help would be appreciated - thanx

The table has a foreign key constraint.

If a product is part of an order, you may not delete the product.

Otherwise, when someone tried to look at the order, they would get an error about a missing product.

|||

Thanx for your help - The reason I'm using the Products table in Northwind is, "I tried building a table based on my clients original data (his music shows itinerary) but it would not allow me to click the Checkbox in the Wizard - "Generate additional INSERT, UPDATE, and DELETE statements" when configuring the db

Can you tell me how I can remove the foreign key constraints (or) how can I duplicate the table, rename it and then remove all foreign key constraints

thanx

|||

Open up the Keys folder under the table.

The foreign keys have a prefix FK_. Right click one and select Modify.

When you built your original table, did you give it a primary key? If a table does not have a primary key, you will not be able to generate Insert, Delete and Update statements.,

|||

thank you so much - The primary key issue was the problem and now I have had no problems adding, deleting and modyfing the table. I am having a problem with the formatting of a datetime field. It keeps returning the date as well as the time (12:00 AM). Could you point me in the right direction to take care of this. Do I need to change the field or can I deal with it by formatting the column in the GridView?

thanx for all of your help

|||

In the GridView date column set the DataFormatString to {0:d} or {0:D} or {0:yyyy-MM-dd} etc.

And set the column's HtmlEncode property to false.

|||

Thanx Steve - Everything seems to be working fine now. Except,...

When I try to put this little app on my server (www.discountasp.net) - I get errors

Server Error in '/dave' Application.

---

Configuration Error

Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message:Unrecognized attribute 'xmlns'.

Source Error:

Line 8: \Windows\Microsoft.Net\Framework\v2.x\Config Line 9: -->Line 10: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">Line 11: <appSettings/>Line 12: <connectionStrings/>

Source File:E:\web\nyguitarsne\htdocs\dave\web.config Line:10

I must be missing something crucial about deployment?

|||

PS - the site is sitting here:

www.caterdata.com/dave

|||

It's hard to say without seeing the whole file but it looks like you have a closing connectionStrings tag without an opening tag. The fix below may help (or you could delete both tags)

Line 8: \Windows\Microsoft.Net\Framework\v2.x\Config Line 9: -->Line 10: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">Line 11: <appSettings/>

new: <connectionStrings>Line 12: <connectionStrings/>

|||

The reason I'm confused is that it is working fine on localhost.

Do I need to call the site Administrator and set something up that I'm not aware of?

|||

It looks like the web.config file on you local machine and the server are different.

Sometimes they should be. For instance, the datasource on a local machine may not exist on the server.

|||

Try making sure the server is setup top use ASP.NET 2.0 and not ASP.NET 1.0

I have seen this cause weird problems as you are having.

David

|||

Try making sure the server is setup to use ASP.NET 2.0 and not ASP.NET 1.0

I have seen this cause weird problems as you are having.

David

sql

define Select parameters

Hi

I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId

The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:Art %>"SelectCommand="Select * from Option WhereSourceId=@.SourceId AndCatId=@.CatId"><SelectParameters><asp:ControlParameterControlID="Drop1"Name="SourceId"/><asp:ControlParameterControlID="Drop1"Name="CatId"/></SelectParameters></asp:SqlDataSource>

Can anyone help me to define the parameters?

thanks

Hi engnouna,

We can bind the select parameters to Label controls' Text property. And set the Text every time DropDownList select index changed. Here is the demo code:

<asp:GridViewID="GridView1"runat="server"DataSourceID="SqlDataSource1">

</asp:GridView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:testConnectionString%>"

SelectCommand="SELECT [SourceID], [CatID], [Name] FROM [ForDynamicGridView] WHERE (([SourceID] = @.SourceID) AND ([CatID] = @.CatID))">

<SelectParameters>

<asp:ControlParameterControlID="SourceID"DefaultValue="1"Name="SourceID"PropertyName="Text"

Type="Int32"/>

<asp:ControlParameterControlID="CatID"DefaultValue="2"Name="CatID"PropertyName="Text"

Type="Int32"/>

</SelectParameters>

</asp:SqlDataSource>

</div>

<asp:DropDownListID="DropDownList1"runat="server"AutoPostBack="True"OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">

<asp:ListItem>1-2</asp:ListItem>

<asp:ListItem>2-4</asp:ListItem>

<asp:ListItem>3-4</asp:ListItem>

</asp:DropDownList>

<asp:LabelID="SourceID"runat="server"Text="1"Visible="false"></asp:Label>

<asp:LabelID="CatID"runat="server"Text="2"Visible="false"></asp:Label>

protectedvoid DropDownList1_SelectedIndexChanged(object sender,EventArgs e)

{

string[] value = DropDownList1.SelectedValue.Split('-');

SourceID.Text = value[0];

CatID.Text = value[1];

}

|||

Hi,

The ControlParameter itself cannot parse the text in your dropdownlist directly. So, I suggest you parse it in your select statement.

For example, if they are all 2 digits numbers, you can use

Select * from Option Where SourceId=LEFT(@.Input,2) And CatId=RIGHT(@.Input,2)

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!