delete rows in dataset

2 May 2006 9:36 PM

janetb

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.....

3 May 2006 7:48 PM

Kelly Leahy

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

Show quoteHide quote

"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.....
>