Database

Re: DSN / SQL Connection String

5-Apr-06 7:28

rahsi wrote:
where A means alias of Server

I assume by this you mean that in the actual environment you replace A with the name of the SQL Server instance?

rahsi wrote:
This code is working at my office and not at my home. whats the difference in this case.

You are on a different network? If I connect to my office through my company's VPN I have to specify the full name of the SQL Server as servername.companyname.co.uk

rahsi wrote:
USER ID=sa; PASSWORD=password

If you wrote that where I work the code would NOT pass a coding review. Connecting as sa is highly dangerous and exposes your SQL Server to unnecessary risks.

"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

-- modified at 13:28 Wednesday 5th April, 2006

rahsi6-Apr-06 8:30

rahsi

6-Apr-06 8:30

Hi..Thanks Mackay...Its working now and the problem was with server name..which i got now...Its working properly.
Anyways, thanks a lot. see u soon.

Gurpreet

SQL/VB.Net

MikeUPMC5-Apr-06 5:34

MikeUPMC

5-Apr-06 5:34

Any one have this issue?

I'm developing a data entry application in SQL Server/VB.Net 2005.
I noticed that when I load in data onto my form it takes a very long time to load, I'm only talking about 91K records.
I'm loading my data into a form, I have the required table adapter, dataset,
bindingsource and bindingnavigator.
Is this normal or is it inherit in Sql Server & or Vb.net?

I also notice that for changes to be propageted to the db, the user must move to the next record for the changes to be recognized.
Is there a way to update automatically?

Any help appreciated.
Mike

Re: SQL/VB.Net

Colin Angus Mackay5-Apr-06 5:58

5-Apr-06 5:58

MikeUPMC wrote:
I noticed that when I load in data onto my form it takes a very long time to load, I'm only talking about 91K records.

Don't pull 91K rows across from the database to your application. It is inefficient.

Do you desperately need all 91K rows on the client at once? Are you processing all 91K rows at the same time in your client?

MikeUPMC wrote:
Is this normal or is it inherit in Sql Server & or Vb.net?

Neither. I would suggest it is the .NET Framework - DataGrids and the like really are not designed to handly 91K rows all at once. It will accept it, and your application will guzzle the memory it needs to support it, but it really is a bad idea.

When you make a request like that through the DataAdapter the call will complete only when all 91K rows have been pulled out of SQL Server, across the network (how much bandwidth is all this data taking?) and into your client application.

Re: SQL/VB.Net

MikeUPMC5-Apr-06 9:14

MikeUPMC

5-Apr-06 9:14

I need all records to load, users will be adding/deleting modifying to the table based on records already entered.

The app I'm designing is replacing one developed in Access.
In Access, the form loads quickly (only waiting for the record count).
All needed functions are within the database like search & replace on
a filtered dataset. Queries to Insert records into the table, and copy the table to another table for exporting.

I'm trying to mirror the functionality of the current system to that of Sql Server/VB.Net & I'm running into issues i.e. time, speed, search & replace function within a datagrid. This conversion might be more involved than I originally thought. I have the functions for the above insert/copy, and they complete, but updating the dataset is time consuming.

Mike

Re: SQL/VB.Net

Colin Angus Mackay5-Apr-06 9:54

5-Apr-06 9:54

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.

Update 100 000 rows....

Pius__X5-Apr-06 4:53

5-Apr-06 4:53

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 Mad | :mad:

When I used VB 6 with DAO it's takes me only 5 sec.

How to do this thing? please help Sniff | :^)

*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()

Colin Angus Mackay5-Apr-06 5:29

5-Apr-06 5:29

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

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)

Pius__X5-Apr-06 11:09

5-Apr-06 11:09

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.... Smile | :)

Colin Angus Mackay5-Apr-06 11:15

5-Apr-06 11:15

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?

Colin Angus Mackay5-Apr-06 11:17

5-Apr-06 11:17

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()

Pius__X5-Apr-06 23:39

5-Apr-06 23:39

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....... Smile | :)

Colin Angus Mackay6-Apr-06 0:09

Exceptionally dumb SQL Server question

6-Apr-06 0:09

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!

BambooMoon5-Apr-06 4:52

BambooMoon

5-Apr-06 4:52

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 Laugh | :laugh:

Re: Exceptionally dumb SQL Server question

Colin Angus Mackay5-Apr-06 5:21

Re: Exceptionally dumb SQL Server question

5-Apr-06 5:21

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.

BambooMoon5-Apr-06 8:24

BambooMoon

5-Apr-06 8:24

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

Re: Exceptionally dumb SQL Server question

Colin Angus Mackay5-Apr-06 9:49

5-Apr-06 9:49

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.

How to do that?????

Pius__X5-Apr-06 4:35

5-Apr-06 4:35

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

Re: How to do that?????

Colin Angus Mackay5-Apr-06 5:11