I have stored procedure like following,
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,
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!