Click here to Skip to main content
15,888,461 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Edited:
I have stored procedure like following,

SQL
ALTER PROCEDURE [dbo].[Proc_insertdetails]
@patient_id int ,
@prod_name VARCHAR (50),
@quantity float ,
@batchno VARCHAR (30),
@mrp float,
@expiry_date datetime,
@bill_rate float

AS
BEGIN

INSERT into Patient_mstdtl (patient_id,prod_id, quantity, batchno, mrp, 
purchase_price, sale_price, sale_price2, bill_rate, Scan_code, 
global_trade_item_no, stocksum_id, expiry_date)

SELECT @patient_id ,pm.prod_id, @quantity, @batchno, @mrp, sk.purchase_price, sk.sale_price,sk.sale_price2, @bill_rate, sk.scan_code,
pm.global_trade_item_no, sk.stocksum_id, @expiry_date 

from Product_mst pm ,stocksummary sk ,Patient_mst ptm

where ptm.patient_id = @patient_id and
pm.prod_name = @prod_name and
sk.batchno =@batchno and
sk.mrp = @mrp and
sk.expiry_date = @expiry_date
END


and I called this procedure in program like following,

C#
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Proc_insertdetails";
cmd.Connection = con;

string[] splittedResult = r.Split('+');

for (int x = 0; x <= splittedResult.Length-2; x++)
{
   string[] myval = splittedResult[x].Split('<');
   string patientId = myval[0].TrimEnd(',').ToString();

   for (int y = 1; y <= myval.Length-1; y++)
   {
      string[] resValue = myval[y].Split(',');

      cmd.Parameters.Add("@patient_id",SqlDbType.Int).Value= patientId;
      cmd.Parameters.Add("@prod_name",SqlDbType.VarChar,50).Value= resValue[0];
      cmd.Parameters.Add("@quantity",SqlDbType.Float).Value= resValue[1];
      cmd.Parameters.Add("@batchno",SqlDbType.VarChar,50).Value= resValue[2];
      cmd.Parameters.Add("@mrp",SqlDbType.Float).Value= resValue[3];
      cmd.Parameters.Add("@expiry_date",SqlDbType.DateTime).Value= resValue[5];
      cmd.Parameters.Add("@bill_rate",SqlDbType.Float).Value= resValue[6]; 
   }

   table = new DataTable();
   adapter = new SqlDataAdapter(cmd);
   adapter.Fill(table);
   Form1.message("data inserted");
}


There is no problem or datatypes or value of parameters supplied and this works for first record but from second record it throws above exception I didnt get where is the actual problem , i checked in debug also it shows exception on above bold line.

Help me to solve it! Thank you!
Posted
Updated 19-Aug-15 20:30pm
v4

Hello ,
I think you want to insert record one by one by using loop .Then you must run Insert command one by one . Modify your code by this way
for (int y = 1; y <= myval.Length-1; y++)
  {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "Proc_insertdetails";
    cmd.Connection = con;

     string[] resValue = myval[y].Split(',');

     cmd.Parameters.Add("@patient_id",SqlDbType.Int).Value= patientId;
     cmd.Parameters.Add("@prod_name",SqlDbType.VarChar,50).Value= resValue[0];
     cmd.Parameters.Add("@quantity",SqlDbType.Float).Value= resValue[1];
     cmd.Parameters.Add("@batchno",SqlDbType.VarChar,50).Value= resValue[2];
     cmd.Parameters.Add("@mrp",SqlDbType.Float).Value= resValue[3];
     cmd.Parameters.Add("@expiry_date",SqlDbType.DateTime).Value= resValue[5];
     cmd.Parameters.Add("@bill_rate",SqlDbType.Float).Value= resValue[6];

     //Add below lines
     con.Open();
     cmd.ExecuteNonQuery();
     con.CLose();
  }
 
Share this answer
 
Comments
Member 11543226 20-Aug-15 6:22am    
Yes, I did exactly same but execute cmd in adapter and put it in table . But both ways Works well. Thanks.
Animesh Datta 20-Aug-15 7:00am    
glad to help you :)
No surprise, actually: you are adding parameters in a loop...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900