delete rows in dataset

Discussion in 'ASP .Net Web Controls' started by janetb, May 2, 2006.

  1. janetb

    janetb Guest

    Gave up on earlier post of using imageButtons and command arguements, but
    can't get the following to work either. When trying to delete a row, and
    using the SELECT rows, I get an error of the selected index out of range even
    though I'm asking it to select the record in the dataset based on a primary
    key field (the rows commented out). When using the FIND rows, it stops on
    the row.delete() line with an error of "Object reference not set to an
    instance of an object." Really frustrated and would appreciate some help.

    <%@ Import Namespace="System.Data.SqlClient" %>
    <%@ Import Namespace="System.Web.UI.WebControls" %>
    <%@ Import Namespace="System.Data" %>
    <%@Page Language="VB" debug="True"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <script language="vb" runat="server">
    Public strConn As String = "user id=myUser;data source=serverName;persist
    security info=True;initial catalog=myDatabase;password=pwd;"
    Public cn As New SqlClient.SqlConnection(strConn)
    Public ds as New Dataset
    Public da as New SQLDataAdapter
    Public varID as integer, varUPD as integer, varPG as integer

    Sub Page_load(sender as Object, e as EventArgs)
    if request("upd")="" or request("upd")="0" then varUPD=0 else
    varUPD=int(request("upd"))
    if request("eventID")="" or request("eventID")="0" then varID=0 else
    varID=int(request("eventID"))
    if request("pageID")="" or request("pageID")="0" then varPG=0 else
    varPG=int(request("pageID"))
    eventID.text=varID

    da = New SqlDataAdapter("sp_eCoursePage", cn)
    da.SelectCommand.CommandType = CommandType.StoredProcedure
    Dim p_ID as New SqlParameter("@ID", varID)
    da.SelectCommand.Parameters.Add(p_ID)
    da.Fill(ds, "pages")
    Dim autogen As New SqlCommandBuilder(da)
    Dim dt As DataTable = ds.Tables("pages")

    select case varUPD
    case 1 ' Delete a record.
    dt.PrimaryKey = New DataColumn() {dt.Columns("pageID_pk")} 'used find
    Dim row As DataRow = dt.Rows.Find(varPG)
    'used find
    row.Delete()
    'used find
    'dim mySQL as string = "pageID_pk=" & varPG 'used select
    'Dim row As DataRow = dt.Select(mySQL)(0) 'used select
    'row.Delete() 'usedselect
    da.Update(ds, "pages")' Update the database.
    lblMsg.text=mySQL
    case 2
    dim myURL="adminEcourseUpdate.aspx?pageID=" & varPG
    response.redirect(myURL)
    end select

    If ds.Tables("pages").Rows.Count = 0 Then
    dgCourseList.visible=False
    else
    dgCourseList.DataSource = ds.tables("pages")
    dgCourseList.dataBind()
    dgCourseList.visible=True
    end if
    if Not Page.IsPostback then
    pageAdd.visible=False
    else
    pageAdd.visible=True
    end if
    cn.close
    'OnItemCommand="dgCourseListItemCommand" - for imageButtons that couldn't
    get to work
    end sub
    </script>

    <asp:DataGrid id="dgCourseList" autogenerateColumns="False" runat="server" >
    <Columns>
    <asp:BoundColumn DataField="pageID_pk" Visible="False"></asp:BoundColumn>
    <asp:hyperlinkcolumn dataNavigateurlField="pageID_pk"
    dataNavigateUrlFormatString="adminECourse.aspx?upd=1&pageID={0}"
    text="Del"></asp:hyperlinkcolumn>
    <asp:hyperlinkcolumn dataNavigateurlField="pageID_pk"
    dataNavigateUrlFormatString="adminECourse.aspx?upd=2&pageID={0}"
    text="Upd"></asp:hyperlinkcolumn>
    </Columns>
    blah, blah.....
     
    janetb, May 2, 2006
    #1
    1. Advertising

  2. janetb

    Kelly Leahy Guest

    Janet,

    I don't see a DeleteCommand being populated anywhere in your code. You'll
    need to provide one for the data adapter if you want it to be able to delete
    rows from the server... On the other hand, the SqlCommandBuilder may be able
    to handle generating the other commands - I'm not sure, myself, as I don't
    think it would be able to go from the SP name to finding the appropriate
    DELETE statements... Maybe it's smarter than I think.

    That said, your seem to be saying that your problem is with row.delete( ),
    right? That has nothing to do with the dataadapter or the commandbuilder.
    That is simply a client-side dataset delete (which marks the row as deleted -
    the DA actually does the delete on the server only when you call da.Update(
    )).

    Are you sure that "Find" actually returned something? Find will return a
    null reference (Nothing in VB) when it doesn't locate the requested record.
    You should check for this - for instance by putting the rest of the code
    (row.Delete( ) and da.Update( )) into an IF statement that tests whether row
    is Nothing - and see if the problem goes away. Also, your lblMsg=mySql is
    referencing a variable that is no longer declared (commented out) so you
    should probably comment it out too.

    Cheers,
    Kelly

    "janetb" wrote:

    > Gave up on earlier post of using imageButtons and command arguements, but
    > can't get the following to work either. When trying to delete a row, and
    > using the SELECT rows, I get an error of the selected index out of range even
    > though I'm asking it to select the record in the dataset based on a primary
    > key field (the rows commented out). When using the FIND rows, it stops on
    > the row.delete() line with an error of "Object reference not set to an
    > instance of an object." Really frustrated and would appreciate some help.
    >
    > <%@ Import Namespace="System.Data.SqlClient" %>
    > <%@ Import Namespace="System.Web.UI.WebControls" %>
    > <%@ Import Namespace="System.Data" %>
    > <%@Page Language="VB" debug="True"%>
    > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    > <script language="vb" runat="server">
    > Public strConn As String = "user id=myUser;data source=serverName;persist
    > security info=True;initial catalog=myDatabase;password=pwd;"
    > Public cn As New SqlClient.SqlConnection(strConn)
    > Public ds as New Dataset
    > Public da as New SQLDataAdapter
    > Public varID as integer, varUPD as integer, varPG as integer
    >
    > Sub Page_load(sender as Object, e as EventArgs)
    > if request("upd")="" or request("upd")="0" then varUPD=0 else
    > varUPD=int(request("upd"))
    > if request("eventID")="" or request("eventID")="0" then varID=0 else
    > varID=int(request("eventID"))
    > if request("pageID")="" or request("pageID")="0" then varPG=0 else
    > varPG=int(request("pageID"))
    > eventID.text=varID
    >
    > da = New SqlDataAdapter("sp_eCoursePage", cn)
    > da.SelectCommand.CommandType = CommandType.StoredProcedure
    > Dim p_ID as New SqlParameter("@ID", varID)
    > da.SelectCommand.Parameters.Add(p_ID)
    > da.Fill(ds, "pages")
    > Dim autogen As New SqlCommandBuilder(da)
    > Dim dt As DataTable = ds.Tables("pages")
    >
    > select case varUPD
    > case 1 ' Delete a record.
    > dt.PrimaryKey = New DataColumn() {dt.Columns("pageID_pk")} 'used find
    > Dim row As DataRow = dt.Rows.Find(varPG)
    > 'used find
    > row.Delete()
    > 'used find
    > 'dim mySQL as string = "pageID_pk=" & varPG 'used select
    > 'Dim row As DataRow = dt.Select(mySQL)(0) 'used select
    > 'row.Delete() 'usedselect
    > da.Update(ds, "pages")' Update the database.
    > lblMsg.text=mySQL
    > case 2
    > dim myURL="adminEcourseUpdate.aspx?pageID=" & varPG
    > response.redirect(myURL)
    > end select
    >
    > If ds.Tables("pages").Rows.Count = 0 Then
    > dgCourseList.visible=False
    > else
    > dgCourseList.DataSource = ds.tables("pages")
    > dgCourseList.dataBind()
    > dgCourseList.visible=True
    > end if
    > if Not Page.IsPostback then
    > pageAdd.visible=False
    > else
    > pageAdd.visible=True
    > end if
    > cn.close
    > 'OnItemCommand="dgCourseListItemCommand" - for imageButtons that couldn't
    > get to work
    > end sub
    > </script>
    >
    > <asp:DataGrid id="dgCourseList" autogenerateColumns="False" runat="server" >
    > <Columns>
    > <asp:BoundColumn DataField="pageID_pk" Visible="False"></asp:BoundColumn>
    > <asp:hyperlinkcolumn dataNavigateurlField="pageID_pk"
    > dataNavigateUrlFormatString="adminECourse.aspx?upd=1&pageID={0}"
    > text="Del"></asp:hyperlinkcolumn>
    > <asp:hyperlinkcolumn dataNavigateurlField="pageID_pk"
    > dataNavigateUrlFormatString="adminECourse.aspx?upd=2&pageID={0}"
    > text="Upd"></asp:hyperlinkcolumn>
    > </Columns>
    > blah, blah.....
    >
     
    Kelly Leahy, May 3, 2006
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Subba Rao via DotNetMonster.com

    script for moving rows up and down and traverse thru rows of HTML table

    Subba Rao via DotNetMonster.com, Mar 19, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    8,242
    Subba Rao via DotNetMonster.com
    Mar 19, 2005
  2. helpful sql
    Replies:
    0
    Views:
    819
    helpful sql
    May 19, 2005
  3. Arjen Hoekstra
    Replies:
    0
    Views:
    565
    Arjen Hoekstra
    Aug 2, 2005
  4. Jason James

    Rows the rows of a grid view?

    Jason James, Aug 10, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    398
    Jason James
    Aug 10, 2006
  5. VijayRama
    Replies:
    2
    Views:
    2,333
    Gregory A. Beamer
    Oct 12, 2009
Loading...

Share This Page