xml to gridview then save to database

[sasha3]

Hi,

I never worked before with XML, so i don't know if what i'm trying to do is the best way or not.

My XML is very complex, so i work with XPath to get to the elements i am interested from the file, as you can see in my .cs code, in XMLDataSource.

What i made until now and works fine is, my 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 code (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 isn't done.

Why is there anything in gvr.Cells[2].Text; ?

Is there anothor 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)
{
SqlParameter pp = 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

ALTER 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

[sasha3]

Sometimes i get the error: (i don't know why sometimes i don't get this error).

"Procedure or function ... has too many arguments specified."

I added in the for loop from cs code:

cmd.Parameters.Clear();

so know it save to my database blank rows. Can anyone help me?