|
Hi.
I'm writing an application able to work with Access, MS SQL Server 2005 or MySql database, depending on the user's will.
I want to use the classes of the System.Data.OleDb namespace for all three providers.
It's working with Access so for and now I'm trying to use the other two.
What connection strings must be specified for MySql and MS SQL 2005?
(Remote connection must be possible).
Thanks.
|
|
|
|
|
|
blackjack2150 wrote: I want to use the classes of the System.Data.OleDb namespace for all three providers.
Why? This will be inefficient when there are native drivers available. Why not take a look at the Factory pattern that MS used with .NET 2? This allows you to work in a reasonably database agnostic fashion. Take a look at the classes DbConnection and DbCommand in System.Data.Common to get an idea as to how it all works.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
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.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
