Sorry I was so vague with my question. I am experienced with SQL Server but
fairly new to .Net. Anyway, here's the gist of my problem. I have a number
of small tables in SQL Server and want to write an aspx page that will allow
users to select a table from a list, populate a datagrid (or other control,
datatable, etc) with the rows from that table and update, add, delete
records. I want to write it so that as I specify new tables that the users
can access, the code, datagird and the update/delete/ inserts adjust
according to the selected table which will have a varying number of columns.
I have already created part of the page which allows displays the data from
the available tables in the datagrid (with an editcolumn), however I do not
know how to put together the update/add/deletes so that they are generic
enough to work for any table I select. In addition, most of my lookup
tables have a key that is an Identity column that I do not want to allow the
user to change (but need to retain for updates).
Basically I want to write an scaled down ASP.Net version of the tool on
www.genericdb.com. I could write something specific for each table to allow
adds/updates/deletes as I would a normal datagrid similar to the QuickStart
samples, but obviously want to avoid this as I may have to adjust or
replicate code whenever I add a new lookup table to the list.
My code so far is as follows:
<%@ Control Language="C#"
Inherits="ASPNET.StarterKit.Portal.PortalModuleCon trol" %>
<%@ Register TagPrefix="Portal" TagName="Title"
Src="~/DesktopModuleTitle.ascx" %>
<%@ Register TagPrefix="cc1" Namespace="StrengthControls.Scrolling"
Assembly="StrengthControls.Scrolling" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>
<script language="C#" runat="server">
//################################################## ########################
#####################
// Common strings/variables used throughout the procedure
//################################################## ########################
#####################
public SqlConnection con = new
SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
public DataSet myDataSet = new DataSet();
String SQLStatement = null;
//################################################## ########################
#####################
// Clears the DataGird when the page loads initially
//################################################## ########################
#####################
void Page_Load(Object sender, EventArgs e) {
if(!IsPostBack) {
}
}
//################################################## ########################
#####################
// Calls the function that populates the DataGird when a linkbutton is
clicked and sets the
// title label based on the Table selected
//################################################## ########################
#####################
void SelTable(Object sender, CommandEventArgs Args) {
Title.Text = "Data for Table: " + Args.CommandName;
ViewState["SelTbl"] = Args.CommandName.ToString();
BindGrid();
}
//################################################## ########################
#####################
// Function to Edit the DataGrid
//################################################## ########################
#####################
void DataGrid1_Edit(Object obj, DataGridCommandEventArgs e){
DataGrid1.EditItemIndex = (int)e.Item.ItemIndex;
BindGrid();
}
//################################################## ########################
#####################
// Function to Update the DB information from the DataGrid
//################################################## ########################
#####################
void DataGrid1_Update(object source, DataGridCommandEventArgs E) {
string Identity = (E.Item.Cells[1]).Text;
// ?????????????????????????????????????????????????? ????????????
// ?????????????????????????????????????????????????? ????????????
// ?????????????????????????????????????????????????? ????????????
}
//################################################## ########################
#####################
// Function to Cancel the Update from the DataGrid
//################################################## ########################
#####################
void DataGrid1_Cancel(Object obj, DataGridCommandEventArgs e) {
DataGrid1.EditItemIndex = -1;
BindGrid();
}
//################################################## ########################
#####################
// Function to Bind the Data to the Datagrid when it is populated from the
Database
//################################################## ########################
#####################
void BindGrid(){
DataGrid1.DataSource=GetData().Tables["xxx"].DefaultView;
DataGrid1.DataBind();
}
//################################################## ########################
#####################
// Dataset populated from the Select Statement called from the Databind
//################################################## ########################
#####################
private DataSet GetData(){
SQLStatement="Select * FROM " + ViewState["SelTbl"];
SqlDataAdapter myCommand = new SqlDataAdapter(SQLStatement, con);
myCommand.Fill(myDataSet, "xxx");
return myDataSet;
}
</script>
<html>
<head>
<style>
..links {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px;
font-weight: bold; color: #660000; height="18"
}
..title{FONT-FAMILY: Verdana; FONT-WEIGHT: 600; FONT-SIZE: 14;
text-decoration:underline;}
</style>
</head>
<body bottommargin="0" leftmargin="0" topmargin="0" rightmargin="0"
marginheight="0" marginwidth="0">
<cc1:SmartScroller id="SmartScroller1" runat="server" />
<portal:title runat="server" />
<table border="0" cellpadding="5" cellspacing="0" width="100%">
<tbody>
<tr>
<td width="150" valign="top" rowspan="2">
<span class="links"><asp:LinkButton runat="server" id="Departments"
Text="Departments" CommandName="Departments" OnCommand="SelTable"
/></span><br />
<span class="links"><asp:LinkButton runat="server" id="ProjectTypes"
Text="ProjectTypes" CommandName="ProjectTypes" OnCommand="SelTable"
/></span><br />
<span class="links"><asp:LinkButton runat="server" id="ProjectStatuses"
Text="ProjectStatuses" CommandName="ProjectStatuses" OnCommand="SelTable"
/></span><br />
<span class="links"><asp:LinkButton runat="server" id="Services"
Text="Services" CommandName="Services" OnCommand="SelTable" /></span><br />
</td>
<td valign="top">
<asp:Label runat="server" id="Title" cssclass="title" /><br />
</td>
</tr>
<tr>
<td valign="top">
<asp:DataGrid id="DataGrid1" cellpadding="4" BackColor="#EEDDEE"
runat="server"
GridLines="None" BorderWidth="1px" BorderColor="DarkSlateBlue"
ForeColor="Black"
Font-Size="8pt"
OnEditCommand="DataGrid1_Edit"
OnUpdateCommand="DataGrid1_Update"
OnCancelCommand="DataGrid1_Cancel"
<FooterStyle backcolor="Tan" />
<HeaderStyle font-bold="True" backcolor="DarkSlateBlue"
ForeColor="white" />
<PagerStyle horizontalalign="Center" forecolor="DarkSlateBlue"
backcolor="#CCC7E5" />
<SelectedItemStyle forecolor="GhostWhite" backcolor="DarkSlateBlue" />
<AlternatingItemStyle backcolor="#CCC7E5" />
<Columns>
<asp:EditCommandColumn
EditText="<img src='/PortalCSSDK/images/edit.gif' border='0'
alt='Edit' />"
CancelText="Cancel"
UpdateText="Update"
Itemstyle-Wrap="False"
HeaderText="Edit" />
</Columns>
</asp:DataGrid>
<asp:Label runat="server" id="Message" />
</td>
</tr>
</tbody>
</table>
</body>
</html>
Any suggestions, recommendations, changes, rewrites, useful links, etc would
be greatly appreciated.
Thanks,
Jeff
"Mark Rae" <ma**@mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:ez**************@TK2MSFTNGP11.phx.gbl... ".Net Newbie" <jc******@hotmail.com> wrote in message
news:uG*************@TK2MSFTNGP09.phx.gbl...
I'm new to .Net and need to create a generic (free) way to update lookup
tables in SQL Server (using C#) in ASP.Net pages. I found an article
at:
http://www.dotnetjunkies.com/Tutoria...0B0429881.dcik which explains how this can be done but does not supply enough example
code my newbie self to go through in detail where I understand it. So, does
anyone have some example code I could use to accomplish this or know
where I can find something to get me started?
Not sure what you mean by "generic (free)" - if you've got Visual Studio,
you've got all the tools you need to interface with SQL Server. What is it
that you actually need? Do you know how SQL works? Do you know how to
write stored procedures? Do you need to create the database and its objects
yourself, or is it already created for you?
