|
Hy guys,
I have a question relative to passing parameters of Class Accessor:
Below an extract of my code:
string sqlcmd;
sqlcmd = "UPDATE FileLocation \
SET LastAccessTime = CONVERT(DateTime,???????,121), \
LastUpdateTime = CONVERT(DateTime,??????,121) \
WHERE ([Index] = 1)";
// if(FAILED(ThedboSPSelectAccessor.Open(DBSession, _T("{ ? = CALL dbo.SPdboTWOModifyTime;1 (?,?) }"))))
if(FAILED(ThedboSPSelectAccessor.Open(DBSession, sqlcmd.c_str() ) ) )
I tried to using the calling of store procedure and all it's OK,after I tried to use a sql command but I don't know like to pass the two parameters (indicated as ?????), I tried using ? or (?) or '?' but noone is correct...
Can someone help me???
Thanks in advance.
Andrea
|
|
|
|
|
Is it possible to define one db connection only, then define multiple dataReader assoiciated with that connection in ADO.net? If yes, how to do it? Thanks!
|
|
|
|
|
In general, it's not wise to do so. In a single threaded application you might orchestrate these DataReaders to use only one connection (that means one at a time, but that's probably not what you want), but in any multi-threaded environment (like ASP.NET) you will get into all kinds of trouble.
Rely on the underlying data provider to perform connection-pooling, and open a connection for each operation. For SQL Server you can specify a connectionstring a la: "Server=SVR1;Database=Northwind;...;Pooling=true"
This will force the re-use of connections, and makes opening one a lot cheaper.
Gertjan Schuurmans
Amsterdam
The Netherlands
|
|
|
|
|
 = Server=SVR1;Database=Northwind;...; Pooling=true"
Gertjan Schuurmans
Amsterdam
The Netherlands
|
|
|
|
|
No you can't have multiple datareaders open at the same time for the same database connection.
You might want to think of the following alternatives:- Open, read, then close each of your datareaders in sequence.
- Execute all of the queries using a single datareader (e.g. "select * from a ; select * from b ; select * from c"). The datareader is able to process multiple resultsets (for many database products).
- Read each of your queries into a DataSet object then do whatever processing you need against the DataSet.
- Open multiple database connections.
Your choice depends upon what processing you are trying to achieve.
Hope this helps.
Andy Harman
|
|
|
|
|
Thanks for replying! Initially what I did before is kind of as follows:
static void main()
{
....
func1();
....
}
static void func1()
{
....
string connectionstring = "...";
sqlconnection myconnection = new sqlconnection(connectionstring);
string sqlstr1 = "...";
sqlcommand mycommand = new sqlcommand(sqlstr1, myconnection);
myconnection.open()
sqldatareader myreader = mycommand.executereader();
string myvalue = "";
while (myreader.read()
{
myvalue = myreader["..."].tostring();
processThisValue(myvalue);
waitforresult(myvalue, connectionstring);
}
myreader.close();
myconnection.close();
}
static processThisValue(string myvalue)
{
..........
}
static void waitforresult(string inputvalue, string connectionstring)
{
sqlconnection myconnection2 = new sqlconnection(connectionstring);
string sqlstr2 = "select xxx from xxx when xxx=" + inputvalue;
sqlcommand mycommand2 = new sqlcommand(sqlstr2, myconnection2);
while (true)
{
myconnection2.open();
sqldatareader mydatareader2 = mycommand2.executereader();
string myvalue2 = "";
while (mydatareader2.read())
{
myvalue2 = mydatareader2["..."].toString();
}
if (myvalue2 == "...")
break;
}
mydatareader2.close();
myconnection2.close();
}
because of the cost for db connection is expensive, so I'm thinking to use one connection for 2 readers, etc. From your point of view, how should I simplify the above process? Thanks!
|
|
|
|
|
I would recommend that you try the simplest (easiest to code and maintain) way first, then optimise if you find that its performance is not good enough.
If your process is used a lot then you should find that the SQL-Server connection pooling will reduce the expense of database connections.
If your process is not used a lot then it is not worth the bother of optimising the code.
Andy Harman
|
|
|
|
|
Hi All,
I am using MS Access 97.
Basically I am trying to create a query with a running totals column that displays a running total of data for all rows in the query.
Here is what I have:
tblClients
(Fields: ClientID, ClientName)
tblData
(Fields: ClientName, YTDGross, YTDNetCom, YTDNetInt, CSCost, OtherCost)
qryClientData
(Fields: tblData.ClientName, tblData.YTDGross, tblData.YTDCom, tblData.YTDNetInt, tblData.CSCost, tblData.OtherCost, YTDNetRev: (tblData.YTDNetCom+tblData.YTDNetInt), TotalAlloCost: (tblData.CSCost+tblData.OtherCost), TotalNetRev: ([YTDNetRev]-[TotalAlloCost])
So, I want to add a RunningTotal column in the qryClientData that will provide me with a running total of the TotalNetRev for all rows in the query.
If you can help me out, I'd really appreciate it.
If you have any questions (if my explaination was not specific enough), please let me know.
Thanks for your time
Nick
|
|
|
|
|
Have you tried using the SUM() Function?
Here is the syntax:
SELECT SUM(field) AS [expression]<br />
FROM table<br />
[WHERE ...]<br />
[GROUPBY ...]<br />
[HAVING ...]
Hope this helps
Chris LaQuerre
eBusiness Analyst
|
|
|
|
|
Yeah, I've tried it...the problem with the sum function is it groups the clients individually...so all I get when I use the sum function is a copy of the TotalNetRev field...because all its doing is summing the TotalNetRev field for each client..and since there is only one TotalNetRev field for each client, there is nothing to sum it against...the sum function would work if I was grouping the clients by account type (hedge fund, mutual fund)...it would give me a total TotalNetRev for each group....but I need a running total that encompassess all the clients...
Thanks for your help Mr. LaQuerre
Nick
|
|
|
|
|
Does my request not make any sense???
Is it not possible to do this with SQL???
Please let me know
Thanks,
Nick
|
|
|
|
|
Does my request not make any sense???
Is it not possible to do this with SQL???
Please let me know
Thanks,
Nick
|
|
|
|
|
Nick
How about something like:
select a.ClientID, a.ClientName, b.YTDGross, b.YTDNetCom,
b.YTDNetInt, b.CSCost, b.OtherCost,
(b.YTDNetCom + b.YTDNetInt) as YTDNetRev,
(b.CSCost + b.OtherCost) as TotalAlloCost,
(b.YTDNetRev - (b.CSCost + b.OtherCost)) as TotalNetRev,
sum(c.YTDNetRev - (c.CSCost + c.OtherCost)) as RunningTotal
from tblClients as a, tblData as b, tblData as c
where a.ClientName = b.ClientName
and a.ClientName >= c.ClientName
group by a.ClientName, a.ClientID, b.YTDGross, b.YTDNetCom,
b.YTDNetInt, b.CSCost, b.OtherCost
order by a.ClientName, a.ClientID
The running total is calculated by joining to the tblData table a second time. The join from table "a" to table "c" relies upon the fact that the query is sorted by ClientName.
I haven't tested this, so you may need to build the SQL statement up, testing each step of the way.
I hope that this is helpful.
Andy Harman
|
|
|
|
|
Hi Andy
Thanks a lot for the help, but I am having trouble running the query properly.
Here is what I have:
SELECT a.CLIENTID, a.CLIENT, b.YTDGROSS, b.YTDNETCOM, b.YTDNETINT, b.CSGCOST, b.OTHERCOST, (b.YTDNETCOM+b.YTDNETINT) AS YTDNETREV, (b.CSGCOST+b.OTHERCOST) AS TOTALALLOCOST, (YTDNETREV-(b.CSGCOST+b.OTHERCOST)) AS TOTALNETREV, Sum(YTDNETREV-(c.CSGCOST+c.OTHERCOST)) AS RUNNINGTOTAL
FROM tblCLIENTS AS a, tblDATA AS b, tblDATA AS c
WHERE (([a].[CLIENT]=[b].[CLIENT] And [a].[CLIENT]>=[c].[CLIENT]))
GROUP BY a.CLIENTID, a.CLIENT, b.YTDGROSS, b.YTDNETCOM, b.YTDNETINT, b.CSGCOST, b.OTHERCOST;
I had to change:
(b.YTDNETREV - (b.CSGCOST + b.OTHERCOST)) as TOTALNETREV,
sum(c.YTDNETREV - (c.CSGCOST + c.OTHERCOST)) as RUNNINGTOTAL
TO
(YTDNETREV-(b.CSGCOST+b.OTHERCOST)) AS TOTALNETREV, Sum(YTDNETREV-(c.CSGCOST+c.OTHERCOST)) AS RUNNINGTOTAL
Also the field ClientName is actually named CLIENT
Access wouldn't recognize either b.YTDNETREV or c.YTDNETREV...so I had to change them both to YTDNETREV.
Now the query will run, but it doesn't capture any data.
Do you have anymore suggestions for me?
Again, thanks for your help!
Nick
|
|
|
|
|
Hi,
Can anybody point me towards info. regarding connection pooling specifically with the Oracle OleDb provider for .NET? I couldn't find anything about it in MSDN (although there's tons WRT SQLServer).
Cheers.
Dr Herbie.
Jugger, lion-tamer, fashion guru and man-about-town.
|
|
|
|
|
I'm not sure you can with the generic OLE provider. I do know you can with the native Oracle Provider for .NEt. Works alot like the native SQL provider.
Mark Conger
Sonork:100.28396
|
|
|
|
|
Hi All,
I have a client application which I don't like others to see the DB structure (Table, Views, StoreProcedures). Therefore I want to set an sa password when installing my MSDE instance and nobody can change it after that.
But the user can login using Windows Authentication via EnterpriseManager and change the sa password. Is there a way to prevent this action?
regards,
Sassan Komeili Zadeh
|
|
|
|
|
Hey all,
I just found out that when you delete a row from a DataTable it doesn't decrement the Count property associated with it's Rows collection. For example:
int rowcnt;<br />
DataTable t = GetTableWithManyRows();<br />
rowcnt = t.Rows.Count;<br />
t.Rows[0].Delete();
rowcnt = t.Rows.Count;
What is the idea behind this behavior? How does one get the number of rows *ignoring* the ones that have been deleted?
Thanks much -
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
DataView is handy in problem as above where we need current records count.
set dataview RowStateFilter property to as shown below to view only rows which are not deleted from table. All rows which have deleted status in underlying table will not visible.
DataView t=new DataView(someTable);<br />
t.RowStateFilter=DataViewRowState.CurrentRows;<br />
<br />
debug.WriteLine("Rows Count before deletion= "+ t.Count);<br />
t[0].Delete();<br />
debug.WriteLine("Rows Count after deletion = "+ t.Count);
this might help...........................
|
|
|
|
|
Thanks mughal, perfect solution.
Why are deleted rows still a part of the DataTable? Is this used to roll things back? I guess that is the only rational I could see for this type of behavior...
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Hi!
I will write an application that work with an Access DB.
My database have 5 table that have a relationship to each other.
tables in DB:
Table 1: Car: CarID, Manufactore, Colour, Hp, Displacement, Price, Model
Table 2: SaleContractCar: SaleContractID, CarID
Table 3: SaleContract: SaleContractID, CustomerID, Date
Table 4: Customer: CustomerID, CustomerAddressID, Tel, Age, SeconCar, Name
Table 5: CustomerAddress: CustomerAddressID, Street, StreetNr, Zip, City
relationship between the tables:
Table 1 (one to more) Table 2
Table 2 (more to one) Table 3
Table 3 (more to one) Table 4
Table 4 (more to one) Table 5
at this moment I don't know how to start.
should I first load all tables I one DataSet?
and create for each table a DataTable?
how I should start?
how I should write an update SQL statement for this DataSet?
where I can find some sample application that work with Relational Data?
thx!!
|
|
|
|
|
Hello,
The .NET Framework SDK 1.0 includes a collection of QuickStart Tutorials. These can be installed locally or viewed online.
The ADO.NET tutorials can be found at
http://samples.gotdotnet.com/QuickStart/howto/default.aspx?url=/quickstart/howto/doc/adoplus/adoplusoverview.aspx
Notes:
- In the tutorial, you can switch between C#, VB.NET, and JScript for the example code.
- To access MS Access, you should use the System.Data.OleDb data provider.
HTH,
Bernard
|
|
|
|
|
hi all
i open an access database from asp .. for some reason i need to know the number of records in the recordset before processing it as follows :
set con=Server.CreateObject("Adodb.Connection")<br />
set rs=Server.CreateObject("Adodb.Recordset")<br />
con.ConnectionString=strCon<br />
con.CursorLocation=1 'server<br />
con.Open<br />
set rs.ActiveConnection=con<br />
rs.CursorType=adOpenKeySet<br />
rs.open "select * from items"<br />
rs.MoveLast<br />
n=rs.RecordCount<br />
Response.Write n
i get an error :
Microsoft OLE DB Provider for ODBC Drivers (0x80040E24)
Rowset does not support fetching backward.
can you help with this ?
|
|
|
|
|
You could call SELECT COUNT(*) FROM items first and then your actual query.
--
karl
|
|
|
|
|
I've already decided to use this method.. I know it but I didn't want to make 2 queries while I can use a simple property call
thank you anyway
any other ideas ?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
