By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Data Access with GridView [ASP.Net 2.0] question

P: n/a
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
Nov 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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)
"Robert Smith jr." <ro******@cs.cmu.edu> wrote in message
news:O2**************@TK2MSFTNGP15.phx.gbl...
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

Nov 19 '05 #2

P: n/a
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>
<asp:DropDownList ID="DropDownList1"
runat="server" DataSourceID="odcRankInfo" Style="position: relative"
DataTextField="Name" DataValueField="ID" SelectedValue='<%# Bind("RankID")
%>'>
</asp:DropDownList>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="DropDownList1"
runat="server" DataSourceID="odcRankInfo" Style="position: relative"
DataTextField="Name" DataValueField="ID">
</asp:DropDownList>
</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>
<asp:Parameter Name="Number" Type="Int16" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="RankID" Type="Int16" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="Number" Type="Int16" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="RankID" Type="Int16" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="Number" Type="Int16" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter 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.CloseConne ction);
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));
}
}


"Bruce Barker" <br******************@safeco.com> wrote in message
news:OE**************@TK2MSFTNGP12.phx.gbl...
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)
"Robert Smith jr." <ro******@cs.cmu.edu> wrote in message
news:O2**************@TK2MSFTNGP15.phx.gbl...
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


Nov 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.