?
Solved

Deleting a row from datagrid

Posted on 2005-05-13
4
Medium Priority
?
690 Views
Last Modified: 2007-12-19
Hi Below is the code I am using for deleting a row from datagrid using MS-Access.Hope someone is going to help me. I am getting error when I press delete button as follows:

Exception Details: System.Data.OleDb.OleDbException: Could not delete from specified tables.

Source Error:
Line 191:            odadp = new OleDbDataAdapter(QuerySQL, objconn)
Line 192:            ds = new dataset
Line 193:            odadp.fill(ds)
Line 194:            dt = ds.tables(0)
Line 195:
 

Source File: D:\wwwroot\Employee\Marketing\JCCC\customer.aspx    Line: 193

Stack Trace:


[OleDbException (0x80004005): Could not delete from specified tables.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
   System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +69
   System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
   System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
   ASP.customer_aspx.dg1_Delete(Object sender, DataGridCommandEventArgs e) in D:\wwwroot\Employee\Marketing\JCCC\customer.aspx:193
   System.Web.UI.WebControls.DataGrid.OnDeleteCommand(DataGridCommandEventArgs e) +110
   System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +589
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
   System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +100
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +121
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1277

---------------------------------Here is the code --------------------------------------------------------------------------------------


<% @Import Namespace="System" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.OleDb" %>
<%@ register tagprefix="JCCC" tagname="hello"
       src="footer.ascx" %>
<html>

<script Language="VB" runat="server">


Sub Page_Load(Sender As Object, E As EventArgs)
   If Not (IsPostBack)
         Bindgrid
   End If    
End Sub

Function binddata() as ICollection
   
        Dim ConnectionString As String
        Dim QuerySQL as string
        dim odadp  as OleDbDataAdapter
        Dim ds as dataset
        Dim dt as datatable

        ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Server.MapPath("CSUTelemarketing.mdb") & ";"
   
       

' ----------------Retrieving data from access database through SQL query-------------------
       
        QuerySQL = _
            "select * from Contact_Info "

      
'--------------------- OLEDBConnection -----------------------------------------
   
        Dim objConn As New OleDbConnection(ConnectionString)

            odadp = new OleDbDataAdapter(QuerySQL, objconn)
            ds = new dataset
            odadp.fill(ds)
            dt = ds.tables(0)

        Dim objCmd As OleDbCommand
   
        objCmd = New OleDbCommand
        objCmd.CommandText = QuerySQL
        objCmd.Connection = objConn
        Try
            objConn.Open()
   
            dg1.DataSource = objCmd.ExecuteReader()
               
        Catch Err As Exception
            ltlError.Text = Err.ToString()
   
        Finally
            binddata = new dataview(dt)
            objConn.Close()
   
        End Try
   

end Function



Sub dg1_Page(sender As Object, e As DataGridPageChangedEventArgs)

    dg1.CurrentPageIndex = e.NewPageIndex
   
    bindgrid

End Sub

Sub bindgrid()

        dg1.DataSource = binddata()
        dg1.DataBind()
   
end sub

sub dg1_Delete(sender as Object, e as DataGridCommandEventArgs)

     dim i as integer

      i=dg1.DataKeys(int(e.Item.ItemIndex))
      key.text = i

        Dim ConnectionString As String
        Dim QuerySQL as string
        dim odadp  as OleDbDataAdapter
        Dim ds as dataset
        Dim dt as datatable

        ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Server.MapPath("CSUTelemarketing.mdb") & ";"
   
       

' ----------------Retrieving data from access database through SQL query-------------------
       
        QuerySQL = _
            "delete Record_Id,Account_Name,Last_Name,First_Name,AssistantName," + _
        "Title,Office_Phone,Cell_Phone,Other_Phone,e_mail from contact_info " + _
        "where Record_Id =" & i

      
'--------------------- OLEDBConnection -----------------------------------------
   
        Dim objConn As New OleDbConnection(ConnectionString)

            odadp = new OleDbDataAdapter(QuerySQL, objconn)
            ds = new dataset
            odadp.fill(ds)
            dt = ds.tables(0)

        Dim objCmd As OleDbCommand
   
        objCmd = New OleDbCommand
        objCmd.CommandText = QuerySQL
        objCmd.Connection = objConn
        Try
            objConn.Open()
            objCmd.ExecuteNonQuery()

            Bindgrid()
   
            'dg1.DataSource = objCmd.ExecuteReader()
               
        Catch Err As Exception
            ltlError.Text = Err.ToString()
   
        Finally
            'binddata = new dataview(dt)
            objConn.Close()
   
        End Try

End Sub

 
</script>

<form runat="server">

<center>

<h4>CUSTOMER INFORMATION</h4>

<asp:label id="key" Font-Bold="True" runat="server"
            Font-Italic="True" ForeColor="#aa0000" />
<ASP:DataGrid id="dg1" runat="server"
    AllowPaging="True"
    PageSize="3"
    autoGenerateColumns="false"
    PagerStyle-Mode="NumericPages"
    PagerStyle-HorizontalAlign="Right"
    PagerStyle-NextPageText="Next"
    PagerStyle-PrevPageText="Prev"
    OnPageIndexChanged="dg1_Page"
    BorderWidth="1px"
    cellpadding="4"
    headerstyle-font-bold="true"
    HeaderStyle-BackColor="#aaaadd"
    AlternatingItemStyle-BackColor="#eeeeee"
    datakeyfield="Record_Id"
    OndeleteCommand="dg1_Delete"
>


<Columns>
   <asp:ButtonColumn Text="Delete" ButtonType="PushButton"
        CommandName="Delete" />
   <asp:BoundColumn HeaderText="RECORD ID"
       DataField="Record_Id" />
   <asp:boundcolumn headertext="ACCOUNT NAME"
        datafield="Account_Name" />
   <asp:boundcolumn headertext="FIRST NAME"
        datafield="First_Name" />
   <asp:boundcolumn headertext="LAST NAME"
        datafield="Last_Name" />
   <asp:boundcolumn headertext="ASSISTANT NAME"
        datafield="AssistantName" />
   <asp:boundcolumn headertext="TITLE"
        datafield="Title" />
   <asp:boundcolumn headertext="OFFICE PHONE"
        datafield="Office_Phone" />
   <asp:boundcolumn headertext="CELL PHONE"
        datafield="Cell_Phone" />
   <asp:boundcolumn headertext="OTHER PHONE"
        datafield="Other_Phone" />
   <asp:boundcolumn headertext="EMAIL"
        datafield="e_mail" />
</Columns>
</asp:datagrid>

<p>
      <asp:Checkbox id="chk1" runat="server"
        Text="Show numeric page navigation buttons"
        Font-Name="Verdana"
        Font-Size="8pt"
        AutoPostBack="true"
        />
</p>
<br><br>

  <jccc:hello runat="server" />
<BR>  
   
<asp:Literal id="ltlError" runat="server"></asp:Literal>
</center>
</form>
0
Comment
Question by:dhar116
  • 2
4 Comments
 
LVL 23

Expert Comment

by:b1xml2
ID: 13998367
errr:

 ----------------Retrieving data from access database through SQL query-------------------
       
        QuerySQL = _
           "delete Record_Id,Account_Name,Last_Name,First_Name,AssistantName," + _
        "Title,Office_Phone,Cell_Phone,Other_Phone,e_mail from contact_info " + _
        "where Record_Id =" & i

     
'--------------------- OLEDBConnection -----------------------------------------
   
        Dim objConn As New OleDbConnection(ConnectionString)

            odadp = new OleDbDataAdapter(QuerySQL, objconn)
            ds = new dataset
            odadp.fill(ds)
            dt = ds.tables(0)

        Dim objCmd As OleDbCommand
   
        objCmd = New OleDbCommand
        objCmd.CommandText = QuerySQL

your are deleting twice?? and your select query is not selecting but deleting, so there is no rowset returned by the adapter...
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 14005526
delete these lines ... I think you made a mistake while copying some code from above (for creating the connection) and manipulating it as per ur needs .. so as b1xml2 has mentioned u r trying to delete twice .. and i think the lines below are not needed .. remove them and ur code should work ...

            odadp = new OleDbDataAdapter(QuerySQL, objconn)
            ds = new dataset
            odadp.fill(ds)
            dt = ds.tables(0)
0
 

Author Comment

by:dhar116
ID: 14011236
Hi Rejojohny,

             Thanks for the support. I deleted the above lines bit still I am getting error.This time it is not crashing I am getting exception from the Try-catch block.  Below is the error I am getting:

System.Data.OleDb.OleDbException: Could not delete from specified tables. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at ASP.customer_aspx.dg1_Delete(Object sender, DataGridCommandEventArgs e) in D:\wwwroot\Employee\Marketing\JCCC\customer.aspx:line 203
0
 
LVL 26

Accepted Solution

by:
Rejojohny earned 2000 total points
ID: 14016280
the delete statement does not require fieldnames to be specified
the query should be
        QuerySQL = _
           "delete from contact_info " + _
        "where Record_Id =" & i
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question