xml to gridview then save to database

EDN Admin · Jul 16, 2012

Hi,
I have to work with a very complex XML file, i use XPath to get to the elements i am interested from the file, as you can see in my .cs code, in XMLDataSource. 
I have a gridview populated with the desired data from xml. 
I wish to import my data from gridview to a table in database. Below you can find all my codes (sql,cs,aspx) 
If i execute the SP in SSMS it work fine. 
When i click the Import btn, i get no error message, but the import is not ok, a have new lines in the DB table but they are empty.
Why is there anything in gvr.Cells[2].Text; ? 
 
Is there another way to do this. Any advice is welcome. Thanks.
"Default.cs" 
 
protected void Page_Load(object sender, EventArgs e) 
{ 
if (!Page.IsPostBack) 
{ 
// GridView_De1_Components.DataBind(); 
} 
} 
 
protected void btn_ImportComponent_Click(object sender, EventArgs e) 
{ 
// int returnValue = 0; 
 
SqlConnection cnn = new SqlConnection(); 
cnn.ConnectionString = ConfigurationManager.ConnectionStrings["ComponentLogin"].ConnectionString; 
SqlCommand cmd = new SqlCommand(); 
cmd.Connection = cnn; 
cmd.CommandType = CommandType.StoredProcedure; 
cmd.CommandText = "insert_Components"; 
 
foreach (GridViewRow gvr in GridView_De1_Components.Rows) 
{
cmd.Parameters.Clear(); 
SqlParameter p = new SqlParameter();
pp.ParameterName = "@ID_sql"; 
pp.Value = gvr.Cells[0].Text; 
cmd.Parameters.Add(pp); 
 
pp = new SqlParameter(); 
pp.ParameterName = "@ComponentNo_sql"; 
pp.Value = gvr.Cells[1].Text; 
cmd.Parameters.Add(pp); 
 
pp = new SqlParameter(); 
pp.ParameterName = "@Lenght_sql"; 
pp.Value = gvr.Cells[2].Text; 
cmd.Parameters.Add(pp); 
 
pp = new SqlParameter(); 
pp.ParameterName = "@ISMultiComponent_sql"; 
pp.Value = gvr.Cells[3].Text; 
cmd.Parameters.Add(pp); 
 
//pp = new SqlParameter(); 
//pp.ParameterName = "@ModuleID_sql"; 
//pp.Value = gvr.Cells[4].Text; 
//cmd.Parameters.Add(pp); 
 
//SqlParameter parameterReturnValue = new SqlParameter("ReturnValue", SqlDbType.Int); 
//parameterReturnValue.Direction = ParameterDirection.ReturnValue; 
//cmd.Parameters.Add(parameterReturnValue); 
 
try 
{ 
cnn.Open(); 
cmd.ExecuteNonQuery(); 
// returnValue = (int)cmd.Parameters["ReturnValue"].Value; 
cnn.Close(); 
} 
catch (Exception ex) 
{ 
//MessageBox.Show("DataBase connection error saving Components. Please try again!"); 
throw ex; 
 
} 
}//foreach 
} 
 
"Default.aspx" 
 
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventComponentup="true" 
CodeBehind="Default.aspx.cs" Inherits="WebWebApp._Default" %> 
 
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent 
</asp:Content> 
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent 
<asp:XmlDataSource ID="sourceDe1" runat="server" DataFile = "De_1.xml" 
XPath = "/ProductDocument/Product/Components/Component </asp:XmlDataSource> 
<asp:Button ID="btn_Import" runat="server" Text="ImportComponent" /> 
<asp:GridView ID="GridView_De1_Components" runat="server" DataSourceID = "sourceDe1" 
AutoGenerateColumns = "false" Height="141px" Width="712px 
<Columns> 
<asp:TemplateField HeaderText = "ID 
<ItemTemplate> 
<%# XPath("./@ID")%> 
</ItemTemplate> 
</asp:TemplateField> 
<asp:TemplateField HeaderText = "ComponentNo 
<ItemTemplate> 
<%# XPath("./@ComponentNo")%> 
</ItemTemplate> 
</asp:TemplateField> 
<asp:TemplateField HeaderText = "Length 
<ItemTemplate> 
<%# XPath("./@Length")%> 
</ItemTemplate> 
</asp:TemplateField> 
<asp:TemplateField HeaderText = "IsMultiComponent 
<ItemTemplate> 
<%# XPath("./@IsMultiComponent")%> 
</ItemTemplate> 
</asp:TemplateField> 
 
<asp:TemplateField HeaderText = "Modules 
<ItemTemplate> 
<asp:GridView ID="GridView_De1_Components_Modules" AutoGenerateColumns = "false"
 
DataSource = <%# XPathSelect("./ComponentModuleRefs/ComponentModuleRef") %>
 
 
runat="server 
<Columns> 
<asp:TemplateField> 
<ItemTemplate> 
<%# XPath("./@ModuleID")%> 
</ItemTemplate> 
</asp:TemplateField> 
</Columns> 
</asp:GridView> 
</ItemTemplate> 
</asp:TemplateField> 
 
</Columns> 
</asp:GridView> 
 
</asp:Content> 
 
 
"Store Procedure: insert_Components" 
 
USE [ProductsDB] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
 
CREATE PROCEDURE [dbo].[insert_Components] 
-- Add the parameters for the stored procedure here 
@ID_sql varchar(30), 
@ComponentNo_sql varchar(30), 
@Lenght_sql varchar(30), 
@ISMultiComponent_sql varchar(30) 
--@ModuleID_sql nvarchar(50) 
 
AS 
BEGIN 
SET NOCOUNT ON; 
 
insert into Components 
(ID,ComponentNo,Lenght,ISMultiComponent) 
values (@ID_sql,@ComponentNo_sql,@Lenght_sql,@ISMultiComp onent_sql) 
 
--return scope_identity() 
END 
 
"Script for DB table: Components" 
 
USE [ProductsDB] 
GO 
 
SET ANSI_NULLS ON 
GO 
 
SET QUOTED_IDENTIFIER ON 
GO 
 
SET ANSI_PADDING ON 
GO 
 
CREATE TABLE [dbo].[Components]( 
[ID] [varchar](30) NULL, 
[ComponentNo] [varchar](30) NULL, 
[Lenght] [varchar](30) NULL, 
[ISMultiComponent] [varchar](30) NULL 
) ON [PRIMARY] 
 
GO 
 
SET ANSI_PADDING OFF 
GO

View the full article

xml to gridview then save to database

EDN Admin

Well-known member

Similar threads