Data Access with GridView [ASP.Net 2.0] question

[Robert Smith jr.]

Hello,

Please pardon my newbie question ...

I am building an ASP.NET page that displays a recordset with a Delete
statement enabled (this all works fine). I want to Insert the current
row *that is going to be deleted* into another table, before the
original data is deleted.

I am trying to use the RowDeleting method to call an Update or Insert
statement to insert the row into the other table before the original one
gets deleted. I am using a GridView and a SqlDataSource control on my
page (using a preconfigured DSN that points to an Access database).

Here's the Rowdeleting Event code (SQL statement removed for clarity):



Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.GridViewDeleteEventArgs)

Dim sql As String
SqlDataSource1.InsertCommand = sql
MsgBox(SqlDataSource1.InsertCommand) 'I see my query when event fires
SqlDataSource1.Insert()

End Sub

Nothing gets inserted into the other table. Any help would be greatly
appreciated.

Thanks in advance,

Bob

 

[Bruce Barker]

a cleaner approach is to write a delete stored proc, that does the copy
before the delete. if you love dynamic sql, then do it in the delete
statement.

now why your code doesn't work. a dataset set row has a status of unchanged,
added, deleted, or modified. this status controls whether the delete, insert
or update command if called for this row.

-- bruce (sqlwork.com)

 

[Angelo Cook]

ok, i have an ObjectDataSource defined to handle the deleting or
deactivating of a row in the database;
here is the ASPX code and the ODS code. the select, display, modify and
update works great no problems
but the Delete command part of the Gridview does not pass the key to the
Deactivate routine, in fact none of the fields
are passed.


-----------------------------------------------snip------------------------------------------------
<%@ Page Language="C#" MasterPageFile="~/PackInformation.master"
AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="_Default" Title="Pack Admin" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<table border="0" cellpadding="0" cellspacing="0" style="position:
relative">
<tr>
<td style="width: 100px">
Pack #</td>
<td style="width: 100px">
<asp:Label ID="Label4" runat="server" Style="position:
relative" Text="Label"></asp:Label></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td colspan="3">
<asp:GridView ID="gdvDens" runat="server" AllowPaging="True"
AllowSorting="True"
AutoGenerateColumns="False" CellPadding="0"
DataSourceID="odsDenInfo"
ForeColor="#333333" GridLines="None" Style="position:
relative" EmptyDataText="No data to display." PageSize="15"
ShowFooter="True">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True"
ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White"
HorizontalAlign="Right" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True"
ForeColor="White" HorizontalAlign="Left" />
<AlternatingRowStyle BackColor="White"
ForeColor="#284775" />
<Columns>
<asp:TemplateField HeaderText="Number" >
<EditItemTemplate>
<asp:Label ID="Label1" runat="server"
Style="position: relative" Text='<%# Bind("Number", "{0}") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="TextBox1" runat="server"
Style="position: relative" Width="40px"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server"
Text='<%# Bind("Number", "{0}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server"
Width="250px" Text='<%# Bind("Name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="TextBox3" runat="server"
Style="position: relative" Width="250px"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server"
Text='<%# Bind("Name", "{0}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Rank">
<EditItemTemplate>
<aspropDownList ID="DropDownList1"
runat="server" DataSourceID="odcRankInfo" Style="position: relative"
DataTextField="Name" DataValueField="ID" SelectedValue='<%# Bind("RankID")
%>'>
</aspropDownList>
</EditItemTemplate>
<FooterTemplate>
<aspropDownList ID="DropDownList1"
runat="server" DataSourceID="odcRankInfo" Style="position: relative"
DataTextField="Name" DataValueField="ID">
</aspropDownList>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server"
Text='<%# Bind("RankName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Action">
<EditItemTemplate>
<asp:ImageButton ID="ImageButton1"
runat="server" CausesValidation="True" CommandName="Update"
ImageUrl="~/images/save.gif"
Text="Update" />&nbsp;<asp:ImageButton ID="ImageButton2"
runat="server"
CausesValidation="False" CommandName="Cancel" ImageUrl="~/images/cancel.gif"
Text="Cancel" />
</EditItemTemplate>
<ItemTemplate>
<asp:ImageButton ID="ImageButton1"
runat="server" CausesValidation="False" CommandName="Edit"
ImageUrl="~/images/edit.gif" Text="Edit"
/>&nbsp;<asp:ImageButton ID="ImageButton2"
runat="server"
CausesValidation="False" CommandName="Delete" ImageUrl="~/images/delete.gif"
Text="Delete" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
<asp:ObjectDataSource ID="odsDenInfo" runat="server"
SelectMethod="GetDens"
TypeName="DenDAL"
UpdateMethod="UpdateDenInfo"
DeleteMethod="DeactivateDen"
InsertMethod="AddDenInfo">
<UpdateParameters>
<asparameter Name="Number" Type="Int16" />
<asparameter Name="Name" Type="String" />
<asparameter Name="RankID" Type="Int16" />
</UpdateParameters>
<DeleteParameters>
<asparameter Name="Number" Type="Int16" />
<asparameter Name="Name" Type="String" />
<asparameter Name="RankID" Type="Int16" />
</DeleteParameters>
<InsertParameters>
<asparameter Name="Number" Type="Int16" />
<asparameter Name="Name" Type="String" />
<asparameter Name="RankID" Type="Int16" />
</InsertParameters>
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="odcRankInfo" runat="server"
SelectMethod="GetRanks" TypeName="RankDAL"></asp:ObjectDataSource>
</asp:Content>
-----------------------------------------------snip------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for DenDAL
/// </summary>
public class DenDAL
{
public static readonly string _ConnectString =
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

/*
private static readonly string ConnectString = string.Format(
"workstation id={0};packet size=4096;user id={1};password={2};data
source={3};persist security info=False;initial catalog={4}"
, System.Net.Dns.GetHostName().ToString()
, DBUserID, DBPassword, DBServerName, DBDatabase
);
*/

private const string SP_DEN_GETALL = "[net_Den_GetAll]";
private const string SP_DEN_UPDATE = "[net_Den_Update]";
private const string SP_DEN_ADD = "[net_Den_Add]";
private const string SP_DEN_DEACT = "[net_Den_Remove]";

private const string PARM_DenNumber = "@DenNumber";
private const string PARM_DenName = "@DenName";
private const string PARM_RankID = "@RankID";

public static List<Den> GetDens()
{ // returns a list of Product instances based on the data in the Den table
using(SqlConnection myConnection = new SqlConnection(_ConnectString))
{
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = SP_DEN_GETALL;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Connection = myConnection;
myConnection.Open();
SqlDataReader reader =
myCommand.ExecuteReader(CommandBehavior.CloseConnection);
List<Den> results = new List<Den>();
while(reader.Read())
{
Den mDen = new Den();
mDen.Number = Convert.ToInt32(reader["Number"]);
mDen.Name = reader["Name"].ToString();
mDen.Rank.ID = Convert.ToInt32(reader["RankID"]);
mDen.Rank.Name = reader["RankName"].ToString();
mDen.Rank.Description = reader["Description"].ToString();
//if(reader["UnitPrice"].Equals(DBNull.Value))
// mDen.UnitPrice = 0;
//else
// mDen.UnitPrice =Convert.ToDecimal(reader["UnitPrice"]);
//if(reader["UnitsInStock"].Equals(DBNull.Value))
// mDen.UnitsInStock = 0;
//else
// mDen.UnitsInStock =Convert.ToInt32(reader["UnitsInStock"]);
results.Add(mDen);
}
reader.Close();
myConnection.Close();
return results;
}
}

public static void UpdateDenInfo(short Number, string Name, short RankID)
{
// returns a list of Product instances based on the data in the Den table
SqlParameter[] parms = new SqlParameter[]
{
new SqlParameter(PARM_DenNumber, SqlDbType.SmallInt)
, new SqlParameter(PARM_DenName, SqlDbType.VarChar, 50)
, new SqlParameter(PARM_RankID, SqlDbType.SmallInt)
};

parms[0].Direction = ParameterDirection.Input;
parms[0].Value = Number;

parms[1].Direction = ParameterDirection.Input;
parms[1].Value = Name;

parms[2].Direction = ParameterDirection.Input;
parms[2].Value = RankID;

SqlConnection myConnection = new SqlConnection(_ConnectString);

if(myConnection.State != ConnectionState.Open)
myConnection.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandTimeout = 600;
cmd.CommandText = SP_DEN_UPDATE;
cmd.CommandType = CommandType.StoredProcedure;

foreach(SqlParameter parm in parms)
cmd.Parameters.Add(parm);

int mResults = cmd.ExecuteNonQuery();
myConnection.Close();
if(mResults != 1)
throw new Exception(string.Format("{0} records updated, expected 1 row.",
mResults));
}

public static void AddDenInfo(short Number, string Name, short RankID)
{
// returns a list of Product instances based on the data in the Den table
SqlParameter[] parms = new SqlParameter[]
{
new SqlParameter(PARM_DenNumber, SqlDbType.SmallInt)
, new SqlParameter(PARM_DenName, SqlDbType.VarChar, 50)
, new SqlParameter(PARM_RankID, SqlDbType.SmallInt)
};

parms[0].Direction = ParameterDirection.Input;
parms[0].Value = Number;

parms[1].Direction = ParameterDirection.Input;
parms[1].Value = Name;

parms[2].Direction = ParameterDirection.Input;
parms[2].Value = RankID;

SqlConnection myConnection = new SqlConnection(_ConnectString);

if(myConnection.State != ConnectionState.Open)
myConnection.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandTimeout = 600;
cmd.CommandText = SP_DEN_ADD;
cmd.CommandType = CommandType.StoredProcedure;

foreach(SqlParameter parm in parms)
cmd.Parameters.Add(parm);

int mResults = cmd.ExecuteNonQuery();
myConnection.Close();
if(mResults != 1)
throw new Exception(string.Format("{0} records updated, expected 1 row.",
mResults));
}

public static void DeactivateDen(short Number, string Name, short RankID)
{
// returns a list of Product instances based on the data in the Den table
SqlParameter parm = new SqlParameter(PARM_DenNumber, SqlDbType.SmallInt);

parm.Direction = ParameterDirection.Input;
parm.Value = Number;

SqlConnection myConnection = new SqlConnection(_ConnectString);

if(myConnection.State != ConnectionState.Open)
myConnection.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandTimeout = 600;
cmd.CommandText = SP_DEN_DEACT;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(parm);

int mResults = cmd.ExecuteNonQuery();
myConnection.Close();
if(mResults != 1)
throw new Exception(string.Format("{0} records updated, expected 1 row.",
mResults));
}
}