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(objec t sender, EventArgs e)
{
if (!Page.IsPostBa ck)
{
// GridView_De1_Co mponents.DataBi nd();
}
}

protected void btn_ImportCompo nent_Click(obje ct sender, EventArgs e)
{
// int returnValue = 0;

SqlConnection cnn = new SqlConnection() ;
cnn.ConnectionS tring = ConfigurationMa nager.Connectio nStrings["ComponentLogin "].ConnectionStri ng;
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandType = CommandType.Sto redProcedure;
cmd.CommandText = "insert_Compone nts";

foreach (GridViewRow gvr in GridView_De1_Co mponents.Rows)
{
SqlParameter pp = new SqlParameter();
pp.ParameterNam e = "@ID_sql";
pp.Value = gvr.Cells[0].Text;
cmd.Parameters. Add(pp);

pp = new SqlParameter();
pp.ParameterNam e = "@ComponentNo_s ql";
pp.Value = gvr.Cells[1].Text;
cmd.Parameters. Add(pp);

pp = new SqlParameter();
pp.ParameterNam e = "@Lenght_sq l";
pp.Value = gvr.Cells[2].Text;
cmd.Parameters. Add(pp);

pp = new SqlParameter();
pp.ParameterNam e = "@ISMultiCompon ent_sql";
pp.Value = gvr.Cells[3].Text;
cmd.Parameters. Add(pp);

//pp = new SqlParameter();
//pp.ParameterNam e = "@ModuleID_sql" ;
//pp.Value = gvr.Cells[4].Text;
//cmd.Parameters. Add(pp);

//SqlParameter parameterReturn Value = new SqlParameter("R eturnValue", SqlDbType.Int);
//parameterReturn Value.Direction = ParameterDirect ion.ReturnValue ;
//cmd.Parameters. Add(parameterRe turnValue);

try
{
cnn.Open();
cmd.ExecuteNonQ uery();
// returnValue = (int)cmd.Parame ters["ReturnValu e"].Value;
cnn.Close();
}
catch (Exception ex)
{
//MessageBox.Show ("DataBase connection error saving Components. Please try again!");
throw ex;

}
}//foreach
}

"Default.as px"

<%@ Page Title="Home Page" Language="C#" MasterPageFile= "~/Site.master" AutoEventCompon entup="true"
CodeBehind="Def ault.aspx.cs" Inherits="WebWe bApp._Default" %>

<asp:Content ID="HeaderConte nt" runat="server" ContentPlaceHol derID="HeadCont ent">
</asp:Content>
<asp:Content ID="BodyContent " runat="server" ContentPlaceHol derID="MainCont ent">
<asp:XmlDataSou rce ID="sourceDe1" runat="server" DataFile = "De_1.xml"
XPath = "/ProductDocument/Product/Components/Component"> </asp:XmlDataSour ce>
<asp:Button ID="btn_Import " runat="server" Text="ImportCom ponent" />
<asp:GridView ID="GridView_De 1_Components" runat="server" DataSourceID = "sourceDe1"
AutoGenerateCol umns = "false" Height="141px" Width="712px">
<Columns>
<asp:TemplateFi eld HeaderText = "ID">
<ItemTemplate >
<%# XPath("./@ID")%>
</ItemTemplate>
</asp:TemplateFie ld>
<asp:TemplateFi eld HeaderText = "ComponentN o">
<ItemTemplate >
<%# XPath("./@ComponentNo")% >
</ItemTemplate>
</asp:TemplateFie ld>
<asp:TemplateFi eld HeaderText = "Length">
<ItemTemplate >
<%# XPath("./@Length")%>
</ItemTemplate>
</asp:TemplateFie ld>
<asp:TemplateFi eld HeaderText = "IsMultiCompone nt">
<ItemTemplate >
<%# XPath("./@IsMultiCompone nt")%>
</ItemTemplate>
</asp:TemplateFie ld>

<asp:TemplateFi eld HeaderText = "Modules">
<ItemTemplate >
<asp:GridView ID="GridView_De 1_Components_Mo dules" AutoGenerateCol umns = "false"
DataSource = '<%# XPathSelect("./ComponentModule Refs/ComponentModule Ref") %>'

runat="server">
<Columns>
<asp:TemplateFi eld>
<ItemTemplate >
<%# XPath("./@ModuleID")%>
</ItemTemplate>
</asp:TemplateFie ld>
</Columns>
</asp:GridView>
</ItemTemplate>
</asp:TemplateFie ld>

</Columns>
</asp:GridView>

</asp:Content>


"Store Procedure: insert_Componen ts"

USE [ProductsDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO

ALTER PROCEDURE [dbo].[insert_Componen ts]
-- Add the parameters for the stored procedure here
@ID_sql varchar(30),
@ComponentNo_sq l varchar(30),
@Lenght_sql varchar(30),
@ISMultiCompone nt_sql varchar(30)
--@ModuleID_sql nvarchar(50)

AS
BEGIN
SET NOCOUNT ON;

insert into Components
(ID,ComponentNo ,Lenght,ISMulti Component)
values (@ID_sql,@Compo nentNo_sql,@Len ght_sql,@ISMult iComponent_sql)

--return scope_identity( )
END

"Script for DB table: Components"

USE [ProductsDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFI ER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Components](
[ID] [varchar](30) NULL,
[ComponentNo] [varchar](30) NULL,
[Lenght] [varchar](30) NULL,
[ISMultiComponen t] [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?