|
MikeUPMC wrote: The app I'm designing is replacing one developed in Access.
In Access, the form loads quickly (only waiting for the record count).
That is because it didn't load all the data at once. It loaded what was on screen plus some rows before and after the current position. It would then dynamically get more rows as required in such a way that the user would be unaware.
ADO.NET 2.0 does provide some asynchronous access methods so that you can access information in the background however I have not used them so I cannot tell you how effective they would be, or how much extra complexity they would add to your appliation.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
EXAMPLE
Database format
ID Value1 Value2
1 val1 x
2 val2 x
3 val3 x
.. ... ...
.. ... ...
...... Next 100 000 rows
I want to change values in field Value2 to value from field Value1 how to do this things....
When I using this code*, It's takes me more than 2 minutes... but it is too much, What if I will use database with milion rows??? Is it possible....
.NET is only problem and problem
When I used VB 6 with DAO it's takes me only 5 sec.
How to do this thing? please help
*Code is here
Dim con As New OleDb.OleDbConnection(ConnectionString)<br />
Dim cmd As New OleDb.OleDbCommand("SELECT ID FROM Main", con)<br />
<br />
con.Open()<br />
<br />
Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()<br />
<br />
Dim U_cmd As New OleDb.OleDbCommand("", con)<br />
Dim IDNum As Integer<br />
<br />
<br />
Do While reader.Read<br />
IDNum = reader.GetInt32(0)<br />
<br />
<br />
U_cmd.CommandText = "UPDATE Main SET F3= '" & IDNum & "'WHERE ID= " & IDNum<br />
<br />
<br />
U_cmd.ExecuteNonQuery()<br />
Loop<br />
<br />
con.Close()
|
|
|
|
|
Pius__X wrote: .NET is only problem and problem
Seems to be a case of a bad workman blaming his tools.
Pius__X wrote: SELECT ID FROM Main
This will return every row in Main. Which you then loop over and update Main again. So you are updating EVERY row in Main.
You could reduce all this code into ONE SINGLE SQL statement - not 100,001 statements:
UPDATE Main SET F3=ID
This will do the same as your code. You need to call it exactly ONCE. It will likely take less that one second to execute (assuming the database doesn't need to update any indexes, run triggers, etc. also)
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Colin Angus Mackay
This will return every row in Main. Which you then loop over and update Main again. So you are updating EVERY row in Main.
You could reduce all this code into ONE SINGLE SQL statement - not 100,001 statements:
Did you read my message? Please do it again and see code here, and then send your reply again.... 
|
|
|
|
|
Pius__X wrote: Did you read my message?
Yes, I did.
Pius__X wrote: Please do it again and see code here
And I explained how what SQL Statement you could replace your code with to do the job in one very fast step.
Pius__X wrote: and then send your reply again
What was the problem with my reply? Do you want me to write out the .NET code for you as well?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
This will produce the same final result as the code your wrote above:
Dim con As New OleDb.OleDbConnection(ConnectionString)
Dim cmd As New OleDb.OleDbCommand("UPDATE Main SET F3=ID", con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
I want: Field3 = MyFunction(Field1, Field2)
MyFunction is copmlex mathematical function, and my question is how to update milion rows?
Please note your code, bye.......
|
|
|
|
|
Well why didn't you explain that? Why give a sample of code that you don't want?
Rather than pull across a million rows and perform a calculation in C# and return the result - which is slow - do it all in SQL.
If you want further help then you are going to have to tell us what the calculation is and what data is involved - not some substitute, because that way the wrong answer lies!
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Our many applications have always run with SQL Server 2000, which I have installed on my system, referencing a few databases. Now I want to test if the proper working of our programs will be affected by using SQL Server 2005 instead. I haven't yet installed SQL Server 2005.
1) When I install SQL Server 2005 (or for that matter, if I have multiple instances of SQL Server 2000 installed for some silly reason), I assume that each instance/verson of the product has its own list of databases, right? I mean, when I install SQL Server 2005, I assume that I cannot attach the exact same databases that SQL Server 2000 already has attached?
2) If I run a program using OLEDB (C++ code) or ADO.NET (C# code) that wants to access a database, how on earth does it know which instance/version of SQL Server contains the database? I would have thought it was through the connection string, but the connection string I have always used doesn't seem to specify anything that would identify a particular instance/version of SQL Server: Provider=SQLOLEDB;Data Source=<hostname>;Persist Security Info=True;User ID=<username>;Initial Catalog=<databasename>;Password=<password>
Thanks for the help. I'd rather know this before I install SQL Server 2005.
-- modified at 10:55 Wednesday 5th April, 2006
Hmmm. Those smiley's you see are code project misinterpreting my angle brackets. I don't actually put smileys in my connection string 
|
|
|
|
|
BambooMoon wrote: I assume that each instance/verson of the product has its own list of databases, right? I mean, when I install SQL Server 2005, I assume that I cannot attach the exact same databases that SQL Server 2000 already has attached?
Yes. You will need to upgrade a SQL Server 2000 database to work with SQL Server 2005.
However, it is possible to manage (mostly) a SQL Server 2000 instance from the SQL Server 2005 client tools. The back end database is still SQL Server 2000.
BambooMoon wrote: If I run a program using OLEDB (C++ code) or ADO.NET (C# code) that wants to access a database, how on earth does it know which instance/version of SQL Server contains the database?
The connection string identifies the instance.
BambooMoon wrote: I would have thought it was through the connection string, but the connection string I have always used doesn't seem to specify anything that would identify a particular instance/version of SQL Server
Then you have likely always connected to the default instance (it has no name)
Data Source=<machine_name>
for the default instance OR
Data Source=<machine_name>\<instance_name>
for specificly named instance.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Thank you, Colin, that is exactly what I needed to know.
Since I wanted a default instance of SQL Server 2005, I tried installing it and specifying that. It claimed that it would update my existing software (SQL Server 2000) and become the default instance, but it lied. Instead, it failed to install at all (or to rollback the installation). So after then uninstalling all versions of SQL Server from my machine, I could finally install SQL Server 2005 properly and it works without problems ......
except that I find that other machines, running SQL Server 2000, cannot go to Enterprise Manager and create a new server registration that connects them to my machine (even though I can create new server registrations in my SQL Server 2005 that connect me to their machines). Is it not possible for SQL Server 2000 installations to connect through Enterprise Manager to SQL Server 2005 installations?
Or perhaps it is some security issue. The computer I'm using has had mysterious security problems since I got it. For example, mine is the only computer in the office to which nobody can map a shared drive, and nobody understands why not.
-- modified at 14:27 Wednesday 5th April, 2006
|
|
|
|
|
BambooMoon wrote: Is it not possible for SQL Server 2000 installations to connect through Enterprise Manager to SQL Server 2005 installations?
Enterprise manager works through an intimate knowledge of how SQL Server works internally. It cannot make any assumptions about how SQL Server 2005 works and will therefore not connect to it.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Is it posible access to database whitout DataSet and DateReader and use open connectivity or there are only thease two ability?
Or how to set DataGrid to view data which sould be currently view in DataGrid, here is example.....
I have DataGrid where are show 10 rows, without using scroll bars, when I scroll down next 10 rows then some object read next 10 rows from databaze and show them to DataGrid... How to do this things in .NET?????
thx
|
|
|
|
|
Pius__X wrote: Is it posible access to database whitout DataSet and DateReader and use open connectivity or there are only thease two ability?
Unless the database vendor provides it the only access method is via the DataReader.
To clear up some misunderstandings in your statement:
There are no other access methods. The DataSet has no database connectivity at all. The DataAdapter uses a DataReader to extract data from the database and put it in a DataSet.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
hi i'm very novice in .net.
can any body tell me how can i read the field of database to make the login page.i have to connect the MYSql with vs.net 2005.
if could then please provide the appropriat code or detail steps.
thanx
Aavesh
|
|
|
|
|
Hello !
I want through a stored procedure to import a table from a CSV file.
I don't want to use bcp or other utilities, it needs to be through the stored procedure.
That means through SQL commands.
What I actually do is I build a CSV file in C# and I don't want to build a dataset and to fill it with data and to move it to SQL Server. I want that it will be done through the SQL Server directly.
Thanks,
Clint
|
|
|
|
|
Why not BCP from a stored proc using the xp_cmdshell
|
|
|
|
|
http://searchvb.techtarget.com/tip/1,289483,sid8_gci808621,00.html?bucket=ETA
|
|
|
|
|
i tried code from
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839#XSLTH4156121122120121120120
As we are using window2003 we need to use CDOSYS code from given URL.
Its working on our side.
But giving problem on client side.
Can any one help me this?????
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839#XSLTH4156121122120121120120
|
|
|
|
|
I am using Microsoft Patterns and Practices Enterprise Library Data Access Block. I have called a function which returns an object of type IDataReader. It has only single row in it.
I am writing this code
IDataReader dr =db.ExecuteReader(cmd);
string CityName = dr.GetValue(0).ToString();
On running this code I am getting the exception as
InvalidOperationException: Invalid attempt to read when no data is present.]
System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i)
System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
But when I add two lines of code in it which has nothing to do with the running code, I don't get any exception. The code is as
IDataReader dr =db.ExecuteReader(cmd);
if (dr.Read()==true)//yaha kuch gadbad hai
{
int mkio =0;
}
string CityName = dr.GetValue(0).ToString();
Why this problem is happening and why it is not occuring when i add code which has nothing to do with the results.
Thanks,
Sandy Sekhon
|
|
|
|
|
MudkiSekhon wrote: But when I add two lines of code in it which has nothing to do with the running code, I don't get any exception
This is incorrect. The line code dr.Read() had a lot to do with running the code correctly. You MUST read the first row into the Data Reader before you can access it.
You don't need the rest of the if statement, however.
MudkiSekhon wrote: Why this problem is happening and why it is not occuring when i add code which has nothing to do with the results.
It is happening because all you have is a Data Reader. You need to tell it what you want it to do with the data. In this case, read the first (and only) row of data. It will make no assumptions on what you want to do with it so it won't advance to any rows of data until you tell it to. This is the correct behaviour. The code you added does have something to do with the results - so the statement that it "has nothing to do with the results" is erroneous.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
hey guyz,
i am developiung an application
for that i need to know the architecture of oracle 9i
can some body suggest me sites fromn where i can download "FREE" PDF'S OR free online tutorilals
|
|
|
|
|
I have a copy of SQL Server 2005, but it wont install... it keeps saying there is a version already on my computer. I downloaded some trials, as well as MySql, but i deleted all of those... unless the regs were left behind. I dont supposed anyone knows the keys so i can look (or most likely a link to a page with them.)... i also installed Visual Studio 2005, which had SQL Server Mobile, if that is possibly interfering... Either way, when i try to use it by going into access and making a New Database Project, and set up UserName/Pass and try to connect to my local machine, it always come back with a ton of errors... all i want is to just use databases in VB2005, why is it to annoyingly hard... im considering just formating and going from scratch. lol... maybe theres something im missing... This is why i call on you wonderful sage of all that is SQL, please help a desperate noobie.
"Love, Life and Option Explicit"
|
|
|
|
|
You may ge this error if you have any of the .NET beta(s) installed on your systems as well as any of the SQL beta progs installed. Check to see if you have uninstalled them.
My Programming Library
/* You are not expected to understand this */
-- modified at 1:12 Wednesday 5th April, 2006
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
