|
Also, look up dataprovider factory on msdn. This will give you some good info.
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
Hi,
I am getting the exception " Procedure or function SummerizeInsert has too many arguments specified." But so far my coding is correct. also i noticed this error comes when the loop is going through for the second time.
regards
ruwandi
rkherath
|
|
|
|
|
rkherath wrote: I am getting the exception " Procedure or function SummerizeInsert has too many arguments specified." But so far my coding is correct. also i noticed this error comes when the loop is going through for the second time.
Would you care to show the code so that we can verify this?
|
|
|
|
|
Hi,
this is the stored procedure
ALTER PROCEDURE dbo.SummerizeInsert
@cYear char(4),
@cMonth char(2),
@cLocationCode nchar(3),
@cAlias char(3),
@cLocationName char(10),
@cSKU char(10),
@cCategoryCode char(10),
@cCategory char(10),
@cDescs char(10),
@nOpeningBalance float(8),
@nReceipts float(8),
@nIssues float(8),
@nStkAdj float(8),
@nClosingBalance float(8),
@nCOS float(8),
@nSales float(8),
@nSalesRefunds float(8),
@nNetSales float(8),
@nPromotionsDiscount float(8),
@nOtherDiscount float(8),
@nPGPrs float(8),
@nPGP float(8),
@nAGPrs float(8),
@nAGP float(8),
@nStockDays float(8)
AS
insert into tblStock_Sales_Monthly
(cYear,cMonth,cLocationCode,cAlias,cLocationName,cSKU,cCategoryCode,cCategory,cDescs,nOpeningBalance,nReceipts,nIssues,nStkAdj,nClosingBalance,nCOS,nSales,nSalesRefunds,nNetSales,nPromotionsDiscount,nOtherDiscount,nPGPrs,nPGP,nAGPrs,nAGP,nStockDays)
Values
(@cYear,@cMonth,@cLocationCode,@cAlias,@cLocationName,@cSKU,@cCategoryCode,@cCategory,@cDescs,@nOpeningBalance,@nReceipts,@nIssues,@nStkAdj,@nClosingBalance,@nCOS,@nSales,@nSalesRefunds,@nNetSales,@nPromotionsDiscount,@nOtherDiscount,@nPGPrs,@nPGP,@nAGPrs,@nAGP,@nStockDays)
RETURN
this is the coding
public void insert()
{
DataTable dt = SelectFields().Tables[0];
foreach (DataRow dr in dt.Rows)
{
if (dr["OpeningBal"].ToString() == "")
dr["OpeningBal"] = 0.0;
if (dr["Reciepts"].ToString() == "")
dr["Reciepts"] = 0.0;
if (dr["Issues"].ToString() == "")
dr["Issues"] = 0.0;
if (dr["ClosingBal"].ToString() == "")
dr["ClosingBal"] = 0.0;
if (dr["Sales"].ToString() == "")
dr["Sales"] = 0.0;
if (dr["SalesReturn"].ToString() == "")
dr["SalesReturn"] = 0.0;
if (dr["PromDiscounts"].ToString() == "")
dr["PromDiscounts"] = 0.0;
if (dr["OtherDiscount"].ToString() == "")
dr["OtherDiscount"] = 0.0;
if (dr["Adj"].ToString() == "")
dr["Adj"] = 0.0;
if (dr["ReFrom"].ToString() == "")
dr["ReFrom"] = 0.0;
if (dr["ReTo"].ToString() == "")
dr["ReTo"] = 0.0;
StkAdj = (double)dr["Adj"] - (double)dr["ReFrom"] + (double)dr["ReTo"];
COS = (double)dr["OpeningBal"] + (double)dr["Reciepts"] - (double)dr["Issues"] - (double)dr["ClosingBal"] + StkAdj;
netSales = (double)dr["Sales"] - (double)dr["SalesReturn"];
nPGPrs = (netSales - COS) + (double)dr["PromDiscounts"] + (double)dr["OtherDiscount"];
double DevideBy=(netSales + (double)dr["PromDiscounts"] + (double)dr["OtherDiscount"]);
if (nPGPrs != 0.0 & DevideBy!=0.0)
{
nPGP = (nPGPrs / DevideBy) * 100;
}
else
{
nPGP = 0.0;
}
nAGPrs = nPGPrs + StkAdj - (double)dr["OtherDiscount"];
if (nAGPrs != 0.0 & DevideBy!=0.0)
{
nAGP = (nAGPrs / DevideBy) * 100;
}
else
{
nAGP = 0.0;
}
if ((double)dr["ClosingBal"] != 0.0 & COS != 0.0)
{
StockDays = ((double)dr["ClosingBal"] / COS) * 100;
}
else
{
StockDays=0.0;
}
InsertCom.CommandText = "dbo.SummerizeInsert";
InsertCom.CommandType = CommandType.StoredProcedure;
InsertCom.Parameters.Add(new SqlParameter("@cYear", year));
InsertCom.Parameters.Add(new SqlParameter("@cMonth", Month));
InsertCom.Parameters.Add(new SqlParameter("@cLocationCode", dr["Code"]));
InsertCom.Parameters.Add(new SqlParameter("@cAlias", dr["Alias"]));
InsertCom.Parameters.Add(new SqlParameter("@cLocationName", dr["LocationName"]));
InsertCom.Parameters.Add(new SqlParameter("@cSKU", dr["SKU"]));
InsertCom.Parameters.Add(new SqlParameter("@cCategoryCode", dr["CategoryCode"]));
InsertCom.Parameters.Add(new SqlParameter("@cCategory", dr["Category"]));
InsertCom.Parameters.Add(new SqlParameter("@cDescs", dr["CategoryName"]));
InsertCom.Parameters.Add(new SqlParameter("@nOpeningBalance", dr["OpeningBal"]));
InsertCom.Parameters.Add(new SqlParameter("@nReceipts", dr["Reciepts"]));
InsertCom.Parameters.Add(new SqlParameter("@nIssues", dr["Issues"]));
InsertCom.Parameters.Add(new SqlParameter("@nStkAdj", StkAdj));
InsertCom.Parameters.Add(new SqlParameter("@nClosingBalance", dr["ClosingBal"]));
InsertCom.Parameters.Add(new SqlParameter("@nCOS", COS));
InsertCom.Parameters.Add(new SqlParameter("@nSales", dr["Sales"]));
InsertCom.Parameters.Add(new SqlParameter("@nSalesRefunds", dr["SalesReturn"]));
InsertCom.Parameters.Add(new SqlParameter("@nNetSales", netSales));
InsertCom.Parameters.Add(new SqlParameter("@nPromotionsDiscount", dr["PromDiscounts"]));
InsertCom.Parameters.Add(new SqlParameter("@nOtherDiscount", dr["OtherDiscount"]));
InsertCom.Parameters.Add(new SqlParameter("@nPGPrs", nPGPrs));
InsertCom.Parameters.Add(new SqlParameter("@nPGP", nPGP));
InsertCom.Parameters.Add(new SqlParameter("@nAGPrs", nAGPrs));
InsertCom.Parameters.Add(new SqlParameter("@nAGP", nAGP));
InsertCom.Parameters.Add(new SqlParameter("@nStockDays", StockDays));
InsertCom.Connection = sqlconnection;
if (sqlconnection.State == ConnectionState.Closed)
{
sqlconnection.Open();
}
InsertCom.ExecuteNonQuery();
}
regards
Ruwandi
rkherath
|
|
|
|
|
It appears your insert command exists prior to the loop. The loop adds the necessary parameters. On the second iteration it adds the parameters again to the command. So there are now two sets of parameters in the command. (Hence the too many parameters exception)
You need to either
* setup the parameters once (before the loop) and assign the values only on each loop.
Or
* remove all the parameters in the command at the start of the loop.
|
|
|
|
|
Thanks, I will try on this.
Regards
Ruwandi
rkherath
|
|
|
|
|
Hey Thanks It worked . Thanks a lot.
rkherath
|
|
|
|
|
It seems, the connection is not closed, so you are passing into stored procedure
number of ther required parameters multpileid by number of rows in your DataTable.
I am not sure, this was your intend
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
AS THE ERROR INDICATES YOU HAVE PASSED MORE VALUES TO THE PROCEDURE/FUNCTION THAN REQUIRED.
Gautham
|
|
|
|
|
You may wish to fix your caps lock key - it appears to be broken.
|
|
|
|
|
if you can see i have have have the exact number of parameters sent as required.
Regards
Ruwandi
rkherath
|
|
|
|
|
rkherath wrote: also i noticed this error comes when the loop is going through for the second time.
This means, you haven't closed the connection to your db server. So, you are passing the parameters twice!
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
mysql has limit to show limited rows
but how what does mssql have?
i want to make a paging like code
i have stumbled on this code
select tag, difference from (
select top 4 * from (
select top 8 *
from flexi_pump
order by tag asc
) as newtbl order by tag desc
) as newtbl2 order by tag asc
it works great, but what if the last page only has one row. this sql query shows the last four, not only the last one. (my table has 9 rows)
|
|
|
|
|
|
thanks, this is a good article
edit: i tried this on a windows mobile and it seems not be working
-- modified at 5:11 Tuesday 24th April, 2007
|
|
|
|
|
I want to study about automated tasks,relication in SQL but I don't have documents about it.If you have it,send it to me,please.Thank you very much
|
|
|
|
|
|
Hi All,
I want to know how to insert multiple rows
in SQL Server 2005.
Please anybody can help me.
|
|
|
|
|
Some ideas:
* Call INSERT multiple times
* Call INSERT with a SELECT that returns multiple rows.
|
|
|
|
|
how can i remove duplicate row in any table.
i have no column having constraint.
so how can i do it.
how can i find number of diplicate rows in any table.
|
|
|
|
|
Are all your values of your rows the same or just a special collum?
I think it might work with:
delete from TableName t1 where collum = (select colum from TableName t2 where t1.Value = t2.Value)
;
I am not quiet sure if this work, but it might.
I hope I could help you
See you
|
|
|
|
|
i use one table.in that many row having equal values in all column.i want anly first row of duplicate rows and other rows deleted.
|
|
|
|
|
In this case you might have to write t1.Value = t2.Value and t1.Value2 = t2.Value2 and ...
depend on how many collums you have. I hope it work coudn´t test it. But I think it will do.
|
|
|
|
|
Just to make sure "Value" stands for "columName"
|
|
|
|
|
This works for me and is way more efficient! Give'r a try, but remember to back up first and test the results to make sure you what you are expecting happens!
DELETE FROM [Table with Duplicates]
WHERE [Primary Key Field] IN
(
SELECT a.[Primary Key Field]
FROM [Table with Duplicates] a,
[Table with Duplicates] b
WHERE a.[Primary Key Field]!= b.[Primary Key Field] -- i.e. Userkey
AND a.[Value to check]= b.[Value to Check] -- i.e. Lastname
AND a.[Second Value to Check] = b.[Second Value to Check] -- i.e. Firstname
AND a.[Primary Key Field] < b.[Primary Key Field] -- i.e. Userkey
)
Rocky
|
|
|
|
|