472,958 Members | 2,514 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

Data Access with GridView [ASP.Net 2.0] question

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
2 6496
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: theKirk | last post by:
using Visual Studio 2005 C# ASP.NET I know there has to be a simple way to do this....I want to use C# in a code behind for aspx. Populate a GridView from an xml file Add Fields to the...
0
by: Khuzema | last post by:
Dear All, I am using issue tracker architecture and developed business object for my application. Now, in VS Beta 2, I humbly want to know how i can have same feature as dataset, in my business...
7
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I...
4
by: Congero | last post by:
I'm trying to find a way to bind hierarchical data to a gridview control. I've been able to do this with some third party controls and was wondering if this functionality is available with the...
1
by: Siva | last post by:
Hi, In my ASP.Net 2.0 app, I have a gridview whose data is being populated using an object data source which retrieves data from a data access layer. The Select method returns a static list of...
5
by: sutphinwb | last post by:
Hi - This could be a simple question. When I relate two tables in a datasetet, how do I get that relation to show up in a GridView? The only way I've done it, is to create a separate table in the...
2
by: murdock | last post by:
Is there a way to perform functions upon databound data that is to be used in a GridView? For example, in the following code where I am using a GridView to display a resulting asp:SqlDataSource ...
2
by: | last post by:
I want to know how to make a clickable button or Command field on a GridView, and have the user's action a) fire a function and b) pass a data value from one of the GridView's columns to that...
0
by: Sam | last post by:
I am fairly new to ASP. Net 2.0, background MS Access 2000/2003 and working knowledge of SQL 2000. Scenario: Page1: Blank Page loads with 2 Text Boxes (TxtLast, TxtFirst), a Command...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.