save multiple records in gridview

[virusisfound]

Hello,

I want to save multiple record in database from DataGridView. I have some code but their is an error i.e

Column named id cannot be found.
Paramater name:columnName

SqlConnection cn = new SqlConnection();
            cn.ConnectionString = "path of connection";
            cn.Open();
            string q = "select * from abc";
            SqlDataAdapter da = new SqlDataAdapter(q, cn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            DataTable dt = new DataTable();
            da.Fill(dt);

            foreach (dataGridView1 row in dataGridView1.Rows)
            {
                SqlDataAdapter da1 = new SqlDataAdapter();
                da1.InsertCommand = new SqlCommand("insert into abc(id,name,adderss)values(@id,@name,@address)", cn);
              [B]  da1.InsertCommand.Parameters.Add("@id", row.cells["id"].Value);[/B]                da1.InsertCommand.Parameters.Add("@name", row.cells["name"].Value);
                da1.InsertCommand.Parameters.Add("@address", row.cells["address"].Value);
                DataSet ds = new DataSet();
                da1.Fill(ds);
                dataGridView1.DataSource = ds;

            }
            da.Update(dt);

Please give me solution.

[abelLazm]

This exception arises when the entered column name doesn't match the column name in database please verify that aspect.... Check whether you are entering right column name or not

[Mitja Bonca]

What dont you put all the data into DataTable, and then only use SqlCommandBuilder object to do an Update of the data in DataTable to the Appropriate Table in DataBase?

Here are links how to do it:
http://support.microsoft.com/kb/308507 and
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand%28v=vs.71%29.aspx

[virusisfound]

content of data table:

table name : abc

id
name
address

i have enter the correct field name. I have check it for two three time. then also it give me error then i use copy paste option because it will get as it is name of content. but still it throwing error.

How to solve this if you know any other code to save multiple rows please give me.

[abelLazm]

have you checked to insert one row at a time with the same code?

[virusisfound]

no , i want save multiple record so i have use this code in foreach.

[abelLazm]

check this code

//rest of the code 
foreach (dataGridView1 row in dataGridView1.Rows)
            {
                 insertQuery("insert into abc(id,name,adderss)values("+row.cells["id"].Value+","+row.cells["name"].Value+","+row.cells["address"].Value+")");
            }

//rest of the code 
private static SqlCommand Command1 = new SqlCommand();
//rest of code 
public static int insertQuery(string query)
        {
                           
Command1.Parameters.Clear();                
Command1.Connection = cn;
Command1.CommandText = query;
Command1.CommandType = CommandType.Text;
return Command1.ExecuteNonQuery();
}

[virusisfound]

I will try this code. Thank You for code.

[abelLazm]

try to write separate functions for connection creation and other queries that arrange ur program and also make it easier to understand :)

[virusisfound]

I have tried this code but it still have the same error. To check the data i have crete new table new field but on that also it show the same error message.

[abelLazm]

please can you show your data and also the script of table?

[himanshusikka36]

This is the simplest procedure to insert data in the gridview:-
(Suppose you are inserting data from TextBox1 to the table 'table1' in the column 'name')
Drag an sqldatasource control on the page
Create connection in sqldatasource control by wizard.
In the GridView, choose datasource SqlDataSource1
Now, on the button click event write following commands-:

{
SqlDataSource1.InsertCommand = "insert into table1(name) values ('" + TextBox1.Text + "')";
        SqlDataSource1.Insert();
        GridView1.DataBind();
}

[virusisfound]

My problem is solved. I use this code to do the task:

SqlConnection cn = New SqlConnection(s);
cn.open();

foreach(DataGridView row in datagridView2.row)
{
string s = "insert into abc(id,name,address)values(@id,name,@address)";
SqlCommand cmd = new SqlCommand(s1,cn);

cmd.Parameters.Add("@id",SqlDbType.Char,20,"id" ).Values = row.Cells["id"].Value;
cmd.Parameters.Add("@name",SqlDbType.Char,20,"name" ).Values = row.Cells["name"].Value;
cmd.Parameters.Add("@address",SqlDbType.Char,20,"address" ).Values = row.Cells["address"].Value;

cmd.ExecuteNonQueary();
cmd.Parameters.clear();
cn.close();
}

With this code the multiple rows save from datagridview.

Thank You For your Reply.