How to delete a record with linq

Hi Experts,

I wrote this code to help me delete records from a gridview when a user clicks on the delete button

so far it needs a select keyword somewhere.

Error      1      A query body must end with a select clause or a group clause      

protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
    {
        PubsDataContext context = new PubsDataContext();
        var author = from a in context.authors
                     where a = a.au_id
                      context.authors.DeleteOnSubmit(author);
                      context.SubmitChanges()
        
        if (e.Exception != null)
        {
           // ErrorMessageLabel.Text = "Failed to DELETE due to foreign key contstraint on the table.  You may only delete rows which have no related records.";
            e.ExceptionHandled = true;
        }
    }

Open in new window

SirReadAlotAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SirReadAlotAuthor Commented:
protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
    {
        //try seting it to zero
        PubsDataContext context = new PubsDataContext();
        var author = from a in context.authors
                     where a = a.au_id
                     select a;
                      context.authors.DeleteOnSubmit(author);
                      context.SubmitChanges();
       
        if (e.Exception != null)
        {
           // ErrorMessageLabel.Text = "Failed to DELETE due to foreign key contstraint on the table.  You may only delete rows which have no related records.";
            e.ExceptionHandled = true;
        }
    }
Dirk HaestProject managerCommented:
SirReadAlotAuthor Commented:
Hi, I have checked those linqs but what most they all demonstate is how to add, delete, etc hard coded column. ie.e delete where itemid is = to something.

I have no idea what items will be deleted or updated so that is why I am trying to use this code

 protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
    {

        PubsDataContext context = new PubsDataContext();
        author a = context.authors.Single(a => a.au_id == a.au_id);
        context.authors.DeleteOnSubmit(a);
        context.SubmitChanges();
    }


I am also following this example but want to use linq instead


http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/detailsview.aspx

run the 3rd example.
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

SirReadAlotAuthor Commented:
protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
    {

        PubsDataContext context = new PubsDataContext();
        author a = context.authors.Single(a => a.au_id == a.au_id);
        context.authors.DeleteOnSubmit(a);
        context.SubmitChanges();
    }

Error      1      A local variable named 'a' cannot be declared in this scope because it would give a different meaning to 'a', which is already used in a 'parent or current' scope to denote something else      

how do i fix this
naspinskiCommented:
You are returning an IEnumerable ('list') of one authoer, not a single one, try using First(), FirstOrDefault(), SingleOrDefault() or Single()

I am using a lambda here because it is cleaner, but you can also wrap your statement in one:

var author = (from a in context.authors where a = a.au_id).First();
protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
    {
        PubsDataContext context = new PubsDataContext();
        var author = from a in context.authors.First(a => a=a.au_id);
        context.authors.DeleteOnSubmit(author);
        context.SubmitChanges();
        
        if (e.Exception != null)
        {
           //I wouldnt be so quick to assume this is the error, check it
           // ErrorMessageLabel.Text = "Failed to DELETE due to foreign key contstraint on the table.  You may only delete rows which have no related records.";
            e.ExceptionHandled = true;
        }
    }

Open in new window

SirReadAlotAuthor Commented:
thank naspinski,

but it says that
Error      1      A query body must end with a select clause or a group clause      
naspinskiCommented:
I just looked at the query again, and it makes no sense...

where a = a.au_id

what is 'a' and where does it come from?  It can't equal it's own au_id
SirReadAlotAuthor Commented:
it was my attempt to
rewrite this

"DELETE FROM [authors] WHERE [au_id] = @au_id"
naspinskiCommented:
oh, ok, you are going to need to get 'au_id' from somewhere else - the method will not know what it is unless you tell it.
{
    int au_id = 5;// make this into whatever id you want to delete
    PubsDataContext context = new PubsDataContext();
    var author = from a in context.authors.First(a => a.au_id = au_id);
    context.authors.DeleteOnSubmit(author);
    context.SubmitChanges();
        
    if (e.Exception != null)
    {
       //I wouldnt be so quick to assume this is the error, check it
       // ErrorMessageLabel.Text = "Failed to DELETE due to foreign key contstraint on the table.  You may only delete rows which have no related records.";
       e.ExceptionHandled = true;
    }
}

Open in new window

SirReadAlotAuthor Commented:
int au_id = 5;// make this into whatever id you want to delete==================>

this is the problem, the user will be deleting any item on the gridview. so i can's say item 1 0r 7
naspinskiCommented:
you have to pass the value somehow, this is a way I did it with a button click event:
http://naspinski.net/post/Getting-the-row-or-index-of-the-gridview-row-you-just-clicked.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SirReadAlotAuthor Commented:
.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.