webmasterbeta
New Member
I'm working on an ASP.NET web form that pulls XML data through a DataSet, and inserts the data into a DataGrid that also contains built-in edit link buttons (see ASP.NET code below).
I'd now like to add Edit/Update/Cancel jobs for these buttons that works in the same way as it would for a SQL Server DB table (see sample SQL code below).
Every way that I've found so far seems to be somewhat complicated, so I'm looking for a simple way to edit XML data through an ASP.NET DataGrid. Any & all help is appreciated. Thanks.
ASP.NET/XML Code
<%@ Page Language="VB" Debug="true" EnableSessionState="false" %>
<%@ import Namespace="System.Data" %>
<script runat="server">
Sub Page_Load
if Not Page.IsPostBack then
Dim xmlAN = New DataSet
xmlAN.ReadXml("http://DOMAIN/FOLDER/SUBFOLDER1/SUBFOLDER2/XMLFILE.xml")
sqlAN.DataSource = xmlAN
sqlAN.DataBind()
End if
End Sub
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Douglas County, Kansas - Appraisal Newsletter</title>
<link href=http://www.webdeveloper.com/forum/archive/index.php/"STYLESHEET.css" rel="stylesheet" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<form runat="server">
<p>
<asp
ataGrid id="sqlAN" runat="server" AutoGenerateColumns="False" BorderWidth="0px" CellSpacing="1" CellPadding="1" Width="100%">
<AlternatingItemStyle verticalalign="Top" backcolor="#CCCCCC"></AlternatingItemStyle>
<ItemStyle verticalalign="Top"></ItemStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#330066"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="articleID" ReadOnly="True" HeaderText="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="title" HeaderText="Title"></asp:BoundColumn>
<asp:BoundColumn DataField="article" HeaderText="Article"></asp:BoundColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" HeaderText="Edit" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
</Columns>
</aspataGrid>
</p>
</form>
</body>
</html>
ASP.NET/SQL Code
<%@ Page Language="VB" Debug="true" %>
<script runat="server">'-----------------------BEGIN MAIN FUNCTION-------------------------
Function AN() As System.Data.IDataReader
'NEED TO: Pull XML data into a DataReader
Dim connectionString As String = "server='SERVERNAME'; user id='UID'; password='PWD'; database='DBNAME'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [COL1],[COL2],[COL3] FROM [TABLENAME]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
'-----------------------END MAIN FUNCTION-------------------------
'-----------------------BEGIN EDIT JOB-------------------------
Sub dbAN_Edit(sender As Object, e As DataGridCommandEventArgs)
dbAN.EditItemIndex = e.Item.ItemIndex
dbAN.DataSource = AN()
dbAN.DataBind()
End Sub
'-----------------------END EDIT JOB-------------------------
'-----------------------BEGIN UPDATE JOB-------------------------
Sub dbAN_Update(sender As Object, e As DataGridCommandEventArgs)
'Determine the value of the COL1 Column
Dim COL1 as Integer = e.Item.Cells(0).Text
'Reference each TextBox
Dim txtCOL2 as TextBox = e.Item.Cells(1).Controls(0)
Dim txtCOL3 as TextBox = e.Item.Cells(2).Controls(0)
update_AN(COL1, txtCOL2.Text, txtCOL3.Text)
dbAN.EditItemIndex = -1
dbAN.DataSource = AN()
dbAN.DataBind()
End Sub
'-----------------------END UPDATE JOB-------------------------
'-----------------------BEGIN CANCEL JOB-------------------------
Sub dbAN_Cancel(sender As Object, e As DataGridCommandEventArgs)
dbAN.EditItemIndex = -1
dbAN.DataSource = AN()
dbAN.DataBind()
End Sub
'-----------------------END CANCEL JOB-------------------------
'-----------------------BEGIN UPDATE FUNCTION-------------------------
Function update_AN(ByVal COL1 As Integer, ByVal COL2 As String, ByVal COL3 As String) As Integer
Dim connectionString As String = "server='SERVERNAME'; user id='UID'; password='PWD'; database='DBNAME'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "UPDATE [TABLENAME] SET [COL3]=@COL3, [COL2]=@COL2 WHERE ([TABLENAME].[COL1] = @COL1)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_COL1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_COL1.ParameterName = "@COL1"
dbParam_COL1.Value = COL1
dbParam_COL1.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_COL1)
Dim dbParam_COL2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_COL2.ParameterName = "@COL2"
dbParam_COL2.Value = COL2
dbParam_COL2.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_COL2)
Dim dbParam_COL3 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_COL3.ParameterName = "@COL3"
dbParam_COL3.Value = COL3
dbParam_COL3.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_COL3)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
'-----------------------END UPDATE FUNCTION-------------------------
'-----------------------BEGIN LOADING DATA-------------------------
Sub Page_Load(sender as Object, e as EventArgs)
If Not Page.IsPostBack then
dbAN.DataSource = AN()
dbAN.DataBind()
End If
End Sub
'-----------------------END LOADING DATA-------------------------
</script>
<html>
<head>
<link href=http://www.webdeveloper.com/forum/archive/index.php/"STYLESHEET.css" rel="stylesheet" />
</head>
<body>
<form runat="server">
<p>
</p>
<h1>PAGE TITLE </h1>
<p align="center">
<aspataGrid id="dbAN" runat="server" BorderWidth="1px" CellPadding="1" Width="100%" OnEditCommand="dbAN_Edit" OnUpdateCommand="dbAN_Update" OnCancelCommand="dbAN_Cancel" AutoGenerateColumns="False">
<SelectedItemStyle verticalalign="Top"></SelectedItemStyle>
<EditItemStyle verticalalign="Top"></EditItemStyle>
<AlternatingItemStyle verticalalign="Top" backcolor="#FFFFCC"></AlternatingItemStyle>
<ItemStyle verticalalign="Top"></ItemStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#330066"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="COL1" ReadOnly="True" HeaderText="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="COL2" HeaderText="COL2"></asp:BoundColumn>
<asp:BoundColumn DataField="COL3" HeaderText="COL3"></asp:BoundColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Delete" CancelText="Cancel" EditText="Delete"></asp:EditCommandColumn>
</Columns>
</aspataGrid>
</p>
<p>
<strong>Date: </strong>
<asp:TextBox id="TextBox1" runat="server"></asp:TextBox>
<strong>Volume: </strong>
<asp:TextBox id="TextBox2" runat="server"></asp:TextBox>
<strong>Number: </strong>
<asp:TextBox id="TextBox3" runat="server"></asp:TextBox>
</p>
<p align="center">
<asp:Button id="btn_Add" runat="server" Text="Add New Item"></asp:Button>
<asp:Button id="btn_Clear" runat="server" Text="Clear All Data"></asp:Button>
<asp:Button id="btn_Preview" runat="server" Text="Preview"></asp:Button>
<asp:Button id="btn_Upload" runat="server" Text="Upload to Site"></asp:Button>
</p>
</form>
</body>
</html>
I'd now like to add Edit/Update/Cancel jobs for these buttons that works in the same way as it would for a SQL Server DB table (see sample SQL code below).
Every way that I've found so far seems to be somewhat complicated, so I'm looking for a simple way to edit XML data through an ASP.NET DataGrid. Any & all help is appreciated. Thanks.
ASP.NET/XML Code
<%@ Page Language="VB" Debug="true" EnableSessionState="false" %>
<%@ import Namespace="System.Data" %>
<script runat="server">
Sub Page_Load
if Not Page.IsPostBack then
Dim xmlAN = New DataSet
xmlAN.ReadXml("http://DOMAIN/FOLDER/SUBFOLDER1/SUBFOLDER2/XMLFILE.xml")
sqlAN.DataSource = xmlAN
sqlAN.DataBind()
End if
End Sub
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Douglas County, Kansas - Appraisal Newsletter</title>
<link href=http://www.webdeveloper.com/forum/archive/index.php/"STYLESHEET.css" rel="stylesheet" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<form runat="server">
<p>
<asp
ataGrid id="sqlAN" runat="server" AutoGenerateColumns="False" BorderWidth="0px" CellSpacing="1" CellPadding="1" Width="100%"><AlternatingItemStyle verticalalign="Top" backcolor="#CCCCCC"></AlternatingItemStyle>
<ItemStyle verticalalign="Top"></ItemStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#330066"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="articleID" ReadOnly="True" HeaderText="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="title" HeaderText="Title"></asp:BoundColumn>
<asp:BoundColumn DataField="article" HeaderText="Article"></asp:BoundColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" HeaderText="Edit" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
</Columns>
</asp
ataGrid></p>
</form>
</body>
</html>
ASP.NET/SQL Code
<%@ Page Language="VB" Debug="true" %>
<script runat="server">'-----------------------BEGIN MAIN FUNCTION-------------------------
Function AN() As System.Data.IDataReader
'NEED TO: Pull XML data into a DataReader
Dim connectionString As String = "server='SERVERNAME'; user id='UID'; password='PWD'; database='DBNAME'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [COL1],[COL2],[COL3] FROM [TABLENAME]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
'-----------------------END MAIN FUNCTION-------------------------
'-----------------------BEGIN EDIT JOB-------------------------
Sub dbAN_Edit(sender As Object, e As DataGridCommandEventArgs)
dbAN.EditItemIndex = e.Item.ItemIndex
dbAN.DataSource = AN()
dbAN.DataBind()
End Sub
'-----------------------END EDIT JOB-------------------------
'-----------------------BEGIN UPDATE JOB-------------------------
Sub dbAN_Update(sender As Object, e As DataGridCommandEventArgs)
'Determine the value of the COL1 Column
Dim COL1 as Integer = e.Item.Cells(0).Text
'Reference each TextBox
Dim txtCOL2 as TextBox = e.Item.Cells(1).Controls(0)
Dim txtCOL3 as TextBox = e.Item.Cells(2).Controls(0)
update_AN(COL1, txtCOL2.Text, txtCOL3.Text)
dbAN.EditItemIndex = -1
dbAN.DataSource = AN()
dbAN.DataBind()
End Sub
'-----------------------END UPDATE JOB-------------------------
'-----------------------BEGIN CANCEL JOB-------------------------
Sub dbAN_Cancel(sender As Object, e As DataGridCommandEventArgs)
dbAN.EditItemIndex = -1
dbAN.DataSource = AN()
dbAN.DataBind()
End Sub
'-----------------------END CANCEL JOB-------------------------
'-----------------------BEGIN UPDATE FUNCTION-------------------------
Function update_AN(ByVal COL1 As Integer, ByVal COL2 As String, ByVal COL3 As String) As Integer
Dim connectionString As String = "server='SERVERNAME'; user id='UID'; password='PWD'; database='DBNAME'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "UPDATE [TABLENAME] SET [COL3]=@COL3, [COL2]=@COL2 WHERE ([TABLENAME].[COL1] = @COL1)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_COL1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_COL1.ParameterName = "@COL1"
dbParam_COL1.Value = COL1
dbParam_COL1.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_COL1)
Dim dbParam_COL2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_COL2.ParameterName = "@COL2"
dbParam_COL2.Value = COL2
dbParam_COL2.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_COL2)
Dim dbParam_COL3 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_COL3.ParameterName = "@COL3"
dbParam_COL3.Value = COL3
dbParam_COL3.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_COL3)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
'-----------------------END UPDATE FUNCTION-------------------------
'-----------------------BEGIN LOADING DATA-------------------------
Sub Page_Load(sender as Object, e as EventArgs)
If Not Page.IsPostBack then
dbAN.DataSource = AN()
dbAN.DataBind()
End If
End Sub
'-----------------------END LOADING DATA-------------------------
</script>
<html>
<head>
<link href=http://www.webdeveloper.com/forum/archive/index.php/"STYLESHEET.css" rel="stylesheet" />
</head>
<body>
<form runat="server">
<p>
</p>
<h1>PAGE TITLE </h1>
<p align="center">
<asp
ataGrid id="dbAN" runat="server" BorderWidth="1px" CellPadding="1" Width="100%" OnEditCommand="dbAN_Edit" OnUpdateCommand="dbAN_Update" OnCancelCommand="dbAN_Cancel" AutoGenerateColumns="False"><SelectedItemStyle verticalalign="Top"></SelectedItemStyle>
<EditItemStyle verticalalign="Top"></EditItemStyle>
<AlternatingItemStyle verticalalign="Top" backcolor="#FFFFCC"></AlternatingItemStyle>
<ItemStyle verticalalign="Top"></ItemStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#330066"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="COL1" ReadOnly="True" HeaderText="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="COL2" HeaderText="COL2"></asp:BoundColumn>
<asp:BoundColumn DataField="COL3" HeaderText="COL3"></asp:BoundColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Delete" CancelText="Cancel" EditText="Delete"></asp:EditCommandColumn>
</Columns>
</asp
ataGrid></p>
<p>
<strong>Date: </strong>
<asp:TextBox id="TextBox1" runat="server"></asp:TextBox>
<strong>Volume: </strong>
<asp:TextBox id="TextBox2" runat="server"></asp:TextBox>
<strong>Number: </strong>
<asp:TextBox id="TextBox3" runat="server"></asp:TextBox>
</p>
<p align="center">
<asp:Button id="btn_Add" runat="server" Text="Add New Item"></asp:Button>
<asp:Button id="btn_Clear" runat="server" Text="Clear All Data"></asp:Button>
<asp:Button id="btn_Preview" runat="server" Text="Preview"></asp:Button>
<asp:Button id="btn_Upload" runat="server" Text="Upload to Site"></asp:Button>
</p>
</form>
</body>
</html>