Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 I need to be able to create and save for datagrid

Author  Topic 

macupryk
Starting Member

9 Posts
Posted - 2004-09-17 : 19:12:48
I need to be able to create and save a dropdownlist for ReportsToID
(ReportTo) will be an a number pointing to the person they report to
that is ReportsToID will contain theUsersID they report to.
The dropdownlist will need to keep the values of the User Name.
Every else is being saved in the update method.


ID User Name StoreName Full Name Access Level ReportsTo
1 Admin Store 1 Matt Administrator Admin
2 Cashier Store 1 Fred Administrator Cashier
3 Chief cashier Store 1 Shelly Administrator Chief cashier


CREATE TABLE [tbl_Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PasswordHash] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[PasswordSalt] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[StoreID] [int] NOT NULL ,
[FullName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AccessLevelID] [int] NOT NULL ,
[ReportToID] [int] NOT NULL ,
[ModifiedDate] [datetime] NOT NULL ,
[CreationDate] [datetime] NOT NULL ,
[LogCount] [int] NOT NULL ,
CONSTRAINT [PK_tbl_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tbl_Users_tbl_Users_AccessLevelID] FOREIGN KEY
(
[AccessLevelID]
) REFERENCES [tbl_Users_AccessLevelID] (
[AccessLevelID]
),
CONSTRAINT [FK_tbl_Users_tbl_Users_StoreID] FOREIGN KEY
(
[StoreID]
) REFERENCES [tbl_Users_StoreID] (
[StoreID]
)
) ON [PRIMARY]
GO
==============================================


CREATE TABLE [tbl_Users_AccessLevelID] (
[AccessLevelID] [int] NOT NULL ,
[AccessLevel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
,
CONSTRAINT [PK_tbl_Users_AccessLevelID] PRIMARY KEY CLUSTERED
(
[AccessLevelID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

=============================================

CREATE TABLE [tbl_Users_StoreID] (
[StoreID] [int] NOT NULL ,
[Store] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
CONSTRAINT [PK_tbl_Users_StoreID] PRIMARY KEY CLUSTERED
(
[StoreID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

=============================================



<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.SqlClient" %>
<%@ import namespace="System.Text" %>
<%@ Register TagPrefix="mspos" TagName="footer"
Src="~/Engine/Controls/footer.ascx" %>
<%@ Register TagPrefix="mspos" TagName="header"
Src="~/Engine/Controls/header.ascx" %>
<HTML>
<HEAD>
<script language="C#" runat="server">

private DataView DTProducts;
private DataSet ddlDataSet = new DataSet();
private SqlConnection myConnection = new
SqlConnection("server=(local);database=MSPOS;uid=sa;pwd=");

protected void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
}
}


// Get Stores and populate dataset with STOREID and STORENAME
private DataSet GetStores()
{
// Populate the ddlDataSet
const string strSQLDDL = @"SELECT * FROM tbl_Users_StoreID order By
Store";
SqlDataAdapter myDataAdapter = new SqlDataAdapter(strSQLDDL,
myConnection);
myDataAdapter.Fill(ddlDataSet, "StoreDataAptapter");
return ddlDataSet;
}
//************************************************************



// Get Stores and populate dataset with ACCCESSLEVELID and ACCESSLEVEL
private DataSet GetAccessLevels()
{
// Populate the ddlDataSet
const string strSQLDDL = @"SELECT * FROM tbl_Users_AccessLevelID
order
By AccessLevel";
SqlDataAdapter myDataAdapter = new SqlDataAdapter(strSQLDDL,
myConnection);
myDataAdapter.Fill(ddlDataSet, "AccessLevelDataAptapter");
return ddlDataSet;
}
//************************************************************



private void BindData()
{
// Create the command object, passing in the SQL string
// connect to the Bugs database
string connectionString =
"server=(local);uid=sa;pwd=;database=MSPOS";
System.Data.SqlClient.SqlConnection connection = new
System.Data.SqlClient.SqlConnection(connectionString);
connection.Open();


// Set the datagrid's datasource to the datareader and databind

// get records from the Bugs table
string commandString = "SELECT tbl_Users.UserID as UserID, ";
commandString += "tbl_Users_StoreID.Store as StoreName, ";
commandString += "tbl_Users.UserName as UserName, ";
commandString += "tbl_Users.FullName as FullName, ";
commandString += "tbl_Users.ReportToID as ReportToID, ";
commandString += "tbl_Users_AccessLevelID.AccessLevel as
AccessLevelName, ";
commandString += "tbl_Users.CreationDate as CreationDate, ";
commandString += "tbl_Users.ReportToID as ReportToID, ";
commandString += "tbl_Users.LogCount as LogCount ";
commandString += "FROM (tbl_Users_AccessLevelID INNER JOIN
tbl_Users ON tbl_Users_AccessLevelID.AccessLevelID =
tbl_Users.AccessLevelID) INNER JOIN tbl_Users_StoreID ON
tbl_Users_StoreID.StoreID = tbl_Users.StoreID";
System.Data.SqlClient.SqlCommand command = new
System.Data.SqlClient.SqlCommand();
command.CommandText = commandString;
command.Connection = connection;

// Create the Reader and bind it to the datagrid
SqlDataReader reader =
command.ExecuteReader(CommandBehavior.CloseConnection);
DataGridUserManagementID.DataSource=reader;
DataGridUserManagementID.DataBind();
}
//****************************************************************



private void DataGridUserManagementID_Edit(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
DataGridUserManagementID.EditItemIndex = e.Item.ItemIndex;
BindData();
}
//****************************************************************


private void DataGridUserManagementID_Cancel(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
DataGridUserManagementID.EditItemIndex = -1;
BindData();
}
//****************************************************************


//**************************************************************************
****
private void DataGridUserManagementID_ItemDataBound(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{




}
//***************************************************************






//***************************************************************
private void DataGridUserManagementID_Update(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
// Determine what store was selected
string strStoreID, strStore;
string strAccessLevelID, strAccessLevel;
string strReportToID, strReportTo;
// Declare Textboxes
string strUserName, strFullName;

strStoreID =
((DropDownList)(e.Item.FindControl("editStores"))).SelectedItem.Value;
strStore =
((DropDownList)(e.Item.FindControl("editStores"))).SelectedItem.Text;

strAccessLevelID =
((DropDownList)(e.Item.FindControl("editAccessLevels"))).SelectedItem.Value;
strAccessLevel =
((DropDownList)(e.Item.FindControl("editAccessLevels"))).SelectedItem.Text;

strReportToID =
((DropDownList)(e.Item.FindControl("editReportTo"))).SelectedItem.Value;
strReportTo =
((DropDownList)(e.Item.FindControl("editReportTo"))).SelectedItem.Text;

strUserName = ((TextBox)e.Item.FindControl("editUserName")).Text;
strFullName = ((TextBox)e.Item.FindControl("editFullName")).Text;

Response.Write ("Updating Inprogress");
Response.Write ("<br> " + " StoreID: " + strStoreID);
Response.Write ("<br> " + " Store Name: " + strStore);
Response.Write ("<br> " + " Access Level: " + strAccessLevel);
Response.Write ("<br> " + " UserName: " + strUserName);
Response.Write ("<br> " + " FullName: " + strFullName);
Response.Write ("<br> " + " ReportToID: " + strReportTo);

DataGridUserManagementID.EditItemIndex = -1;
BindData();
}
//************************************************************************




// Given the name of a table, return a DataReader for
// all values from that table
private SqlDataReader GetValues(string tableName)
{
// connect to the database
string connectionString =
"server=(local);uid=sa;pwd=;database=MSPOS";

// create and open the connection object
System.Data.SqlClient.SqlConnection connection =
new System.Data.SqlClient.SqlConnection(connectionString);
connection.Open( );

// get records from the tbl_Users table
string commandString = "Select * from " + tableName;

// create the command object and set its
// command string and connection
System.Data.SqlClient.SqlCommand command =
new System.Data.SqlClient.SqlCommand( );
command.CommandText = commandString;
command.Connection = connection;

// create the DataReader and return it
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
//********************************************************************



private void SetSelectedIndex(object e, DataGridItemEventArgs args)
{
Response.Write ("SetSelectedIndexn");
if ((ListItemType)args.Item.ItemType == ListItemType.EditItem)
{
DataTable dt = ddlDataSet.Tables["StoreID"];
DataRowView rv = (DataRowView)args.Item.DataItem;
String au_id = rv["StoreID"].ToString();
DropDownList ctlList =
(DropDownList)args.Item.FindControl("lstStores");

for (int iLoop=0; iLoop < ctlList.Items.Count; iLoop++)
{
if (ctlList.Items[iLoop].Value == au_id)
{
ctlList.SelectedIndex = iLoop;
break;
}
}
}
}
</script>
</HEAD>
<body>
<!-- Include start of header
-------------------------------------------->
<form runat="server" ID="Form1">
<TABLE height="497" cellSpacing="0" cellPadding="0" width="881"
border="0" ms_2d_layout="TRUE">
<TR vAlign="top">
<TD width="0" height="3"></TD>
<TD width="681" rowSpan="2">
<CENTER>
<TABLE height="496" cellSpacing="0" cellPadding="0" width="680"
border="0" ms_2d_layout="TRUE">
<TR vAlign="top">
<TD width="680" height="496">
<CENTER>
<TABLE height="452" cellSpacing="0" cellPadding="0"
width="678"
border="0" ms_2d_layout="TRUE">
<TR vAlign="top">
<TD width="678" height="452">
<CENTER>
<TABLE height="452" cellSpacing="0" cellPadding="0"
width="678" border="0" ms_2d_layout="TRUE">
<p>
<asp:datagrid id="DataGridUserManagementID"
runat="server"
font-size="X-Small" font-names="arial"
CellSpacing="2" CellPadding="2" BorderWidth="1px"
BorderColor="Black" BorderStyle="Solid"
GridLines="None" AutoGenerateColumns="False"
AllowCustomPaging="True" AllowPaging="True"
OnEditCommand="DataGridUserManagementID_Edit"
OnCancelCommand="DataGridUserManagementID_Cancel"
OnUpdateCommand="DataGridUserManagementID_Update">
<FooterStyle ForeColor="#330099"
BackColor="#FFFFCC"></FooterStyle>
<AlternatingItemStyle
BackColor="PaleGoldenrod"></AlternatingItemStyle>
<ItemStyle BackColor="Beige"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White"
BackColor="#954896"></HeaderStyle>
<Columns>
<asp:EditCommandColumn ButtonType="LinkButton"
UpdateText="<img src=../images/Ok.gif border=0 align=absmiddle
alt='Save
changes'>"
CancelText="<img src=../images/Cancel.gif
border=0
align=absmiddle alt='Cancel editing'>" EditText="<img
src=../images/Edit.gif border=0 align=absmiddle alt='Edit this
item'>">
<ItemStyle HorizontalAlign="Center"
BackColor="Yellow"></ItemStyle>
</asp:EditCommandColumn>
<asp:TemplateColumn HeaderText="ID">
<ItemTemplate>
<asp:Label Text='<%#
Convert.ToString(DataBinder.Eval(Container.DataItem, "UserID")) %>'
BackColor="lightblue" Font-Bold="true" Runat="server" ID="lblUserID">
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="User Name">
<ItemTemplate>
<asp:Label Text='<%#
Convert.ToString(DataBinder.Eval(Container.DataItem,"UserName")) %>'
Runat="server" ID="lblUserName">
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox Runat="server" ID="editUserName" Text
=
'<%# Convert.ToString(DataBinder.Eval(Container.DataItem,"UserName"))
%>'
Width="150">
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Store">
<ItemTemplate>
<%#
Convert.ToString(DataBinder.Eval(Container.DataItem, "StoreName")) %>
</ItemTemplate>
<EditItemTemplate>
<asp:DropdownList runat="server" id="editStores"
DataTextField="Store" DataValueField="StoreID" DataSource='<%#
GetValues("tbl_Users_StoreID") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Full Name">
<ItemTemplate>
<asp:Label Text='<%#
Convert.ToString(DataBinder.Eval(Container.DataItem,"FullName")) %>'
Runat="server" ID="Label2">
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox Runat="server" ID="editFullName" Text
=
'<%# Convert.ToString(DataBinder.Eval(Container.DataItem,"FullName"))
%>'
Width="150">
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Access Level">
<ItemTemplate>
<%#
Convert.ToString(DataBinder.Eval(Container.DataItem,
"AccessLevelName")) %>
</ItemTemplate>
<EditItemTemplate>
<asp:DropdownList runat="server"
id="editAccessLevels"
DataTextField="AccessLevel" DataValueField="AccessLevelID"
DataSource='<%#
GetValues("tbl_Users_AccessLevelID") %>'>
</asp:DropdownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Reports To">
<ItemTemplate>
<%#
Convert.ToString(DataBinder.Eval(Container.DataItem, "UserName")) %>
</ItemTemplate>
<EditItemTemplate>
<asp:DropdownList runat="server" id="editReportTo"
DataTextField="UserName" DataValueField="ReportToID" DataSource='<%#
GetValues("tbl_Users") %>'>
</asp:DropdownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Account Created">
<ItemTemplate>
<asp:Label Text='<%#
Convert.ToString(DataBinder.Eval(Container.DataItem, "CreationDate"))
%>'
Runat="server" ID="lblCreationDate">
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid></p>
</TABLE>
</CENTER>
<P></P>
</TD>
</TR>
</TABLE>
</CENTER>
</TD>
</TR>
</TABLE>
</CENTER>
</TD>
<TD width="73" rowSpan="2"><IMG height="27"
src="images/AddNew_Button.jpg" width="71"></TD>
<TD width="127"></TD>
</TR>
<TR vAlign="top">
<TD width="0" height="494"></TD>
<TD>
<asp:label id="lblUpdateResults" runat="server"
Font-Bold="True"></asp:label></TD>
</TR>
</TABLE>
</form>
</TABLE>
</body>
</HTML>
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site