Need to insert a data row multiple times in the database

Sivajihero Hero

I have two data tables in sql. I need to insert data feom one table to another. My first data table Table1 contain data column Code,Model,Num,Qty and second table Table2 also contain the same. But I need to insert the data from table1 to table2 multiple times. If Qty in Table1 is 4 then, insert the data of

Table1 for 4 times according to the quantity.

EX: Table1

Code Model Num Qty

a1 m12 12 4

a2 n42 10 2

Then the result may be in Table2 is

Code Model Num Qty

a1 m12 12 1

a2 n42 10 1

How can I achieve this? This is my code for insertion. But its not working.

string connetionString = null;

SqlConnection connection ;

SqlDataAdapter adapter = new SqlDataAdapter();

DataSet ds = new DataSet();

int i = 0;

connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";

connection = new SqlConnection(connetionString);

try

{

connection.Open();

adapter.SelectCommand = new SqlCommand("insert into Table2(Code, Model, Num, Qty)

select Code, Model, Num, Qty from Table1", connection);

adapter.Fill(ds);

connection.Close();

for (i = 0; i <= Convert.ToInt32(ds.Tables[0].Rows[0]["Qty"].ToString()); i++)

{

String str1 = "insert into Table2(Code, Model, Num, Qty)

select Code, Model, Num, Qty from Table1;";

SqlCommand xp1 = new SqlCommand(str1, con);

con.Open();

SqlDataAdapter da1 = new SqlDataAdapter();

da1.SelectCommand = xp1;

DataSet ds1 = new DataSet();

da1.Fill(ds1, "Code");

GridView1.DataSource = ds1;

con.Close();

}

catch (Exception ex)

{

MessageBox.Show(ex.ToString());

}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
Accepted answer
You need to write select statement instead of insert statement in SelectCommand property. But you are providing insert statement, so you are getting null value dataset. And when you are looping over null dataset and trying access the property with index 0 it throws you exception.

connection.Open();
adapter.SelectCommand = new SqlCommand("select Code, Model, Num, Qty from Table1", connection);
adapter.Fill(ds);
connection.Close();

Please try to implement my suggestion given before.
+2
Sivajihero Hero10y ago
Thanks a lot for the correction
0
Sivajihero Hero10y ago
System.IndexOutOfRangeException: Cannot find table 0. at System.Data.DataTableCollection.get_Item(Int32 index) at Default3.Button15_Click3

This is the error message I am getting
0
Sivajihero Hero10y ago
This above my code shows that there is no row at ds(0)
0
Manas Mohapatra10y ago
Hi Siva,

You are doing correct to accomplish your requirement. You are looping over quantity value and executing the Insert statement each time.

It would be better if you use a StoredProcedure(SP) instead of doing application side. All these looping functionality will present inside SP. So that at single shot to database call you will achieve your functionality. However in your code, it is closing and opening so many times to DB calls which is not good for performance.

http://net-informations.com/csprj/data-providers/cs-procedure-parameter.htm

http://www.c-sharpcorner.com/uploadfile/01fc26/how-to-use-stored-procedure-in-simple-easy-way-with-web-config-file/
+1