Database

15-Jan-03 5:25

Hi !

I'd want to be able to visualize data from a huge database's table using a ComboBox (like Access ComboBoxes).
In fact, I've got 50000 records in my table (from MySQL using Odbc), the data loading time into the DataTable is quite good but when I want to dropdown the ComboBox, it takes 3 or 4 seconds to paint it. Sleepy | :zzz:

My question is : Do you know a ComboBox control that can display the data progressively from its datasource member ? or Do you have another solution ? Confused | :confused:

Please help me, it's very important to get a faster painting time for my app.

Thanks

jpeg

Hesham Amin15-Jan-03 11:24

15-Jan-03 11:24

Hi..
do you use a static cursor ?
I think you will get better results if you use dynamic cursor..
let me know if this works

jpeg15-Jan-03 21:36

15-Jan-03 21:36

Thank you for your fast answer !

First of all, I've forgotten to say that my program is written with C# and I use DataSet/DataTable classes.

Next, I'm a newbie with developing Databases' app and I don't know if I use a static or dynamic cursor. So, can you tell me how do you use static/dynamic cursor with these classes ?

jpeg

Hesham Amin16-Jan-03 9:04

16-Jan-03 9:04

Hi..
As you are using C# so you don't use ADO .. you use ADO.NET which does not support dynamic cursor because ADO.NET is based on the disconnected model (you get all the data once then you close the connection)

so for your problem I recommend you to use DataReader
read this carefully:
in MSDN:

You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory.

jpeg16-Jan-03 20:53

16-Jan-03 20:53

I'll try to fill my ComboBox "dynamically" with a DataReader object but I think it won't be easy because of the stream's 'Forward Only' property. Dead | X|

I can't understand that the Microsoft Framework don't provide any class/control to visualize data dynamically & automatically in a control (old MS Access versions were able to do this) : It's incredible, don't you think ?

Thank you for your help. Big Grin | :-D

jpeg

Hesham Amin16-Jan-03 22:14

16-Jan-03 22:14

I agree with you that .NET has some strange things..data bound controls in the VB6 way was great..
ADO was much better than ADO.NET which I did not understand yet why it's based on the disconnected model, while ADO can give you the same functionality if you need..
So I never liked ADO.NET !!

SimonS15-Jan-03 21:59

SimonS

15-Jan-03 21:59

jpeg wrote:
it takes 3 or 4 seconds to paint it
I tried to load 50,000 elements into a combobox and it painted instantly.

What machine are you running off?
When are you doing the actual load into the combobox? i.e. which event

Cheers,
Simon

"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.

my svg article

jpeg15-Jan-03 22:24

15-Jan-03 22:24

My developing machine is a 1.7 Ghz Celeron. So it's a quite good machine. This is not the problem. Maybe the problem is that I use C#. The initializing code (which is located in the form's loading event-handler) is :

DataTable mytable=LoadMyTable();
myComboBox.DataSource=mytable;
myComboBox.DisplayMember="myfieldname";

All this code is fast (I see it in debugging mode) but the first painting takes a lot of time (Maybe because of the internal control initializing method). After the first painting, the dropdown takes a little time.

If you have a piece of code that permit the control to be painted instantly, I'm highly interested !!!

jpeg

How to use datetime fields in SQL server?

SimonS15-Jan-03 23:49

SimonS

15-Jan-03 23:49

Try and populate the combobox manually.
The delay may be in the data binding itself as painting a 50,000 element combox is very quick.

Cheers,
Simon

"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.

my svg article

Luca Leonardo Scorcia15-Jan-03 2:55

Luca Leonardo Scorcia

15-Jan-03 2:55

Hi!
I want to do a simple SELECT on a table, returning the count of records containing a greater date than a certain date I write in the query expression.

Using Access, I'd simply write:
SELECT Count(1) As nMsg FROM Bacheca WHERE ([Date] > #2003/01/01 13:59:07#)

But that select statement returns an error when used in SQL Server, it says Syntax error near '13'. Can you tell me how to fix it?

And, I'd like to make that query work for both Access and SQL Server - is it possible?

Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)

Re: How to use datetime fields in SQL server?

David Wulff15-Jan-03 4:50

David Wulff

15-Jan-03 4:50

Use ' instead of # and treat it as a string, e.g.:

SELECT Count(1) As nMsg FROM Bacheca WHERE ([Date] > '2003/01/01 13:59:07')

This won't work in Access though, AFAIK you'll need to know before hand and use the correct query for each one. Can anyone confirm or deny that?

David Wulff
http://www.davidwulff.co.uk

Re: How to use datetime fields in SQL server?

Richard Deeming15-Jan-03 7:27

Richard Deeming

15-Jan-03 7:27

Try using the ODBC timestamp format. It works in SQL server, and it should work for Access via ODBC/OLEDB. (I haven't tested it, though.)

SELECT 
    Count(1) As nMsg 
FROM 
    Bacheca 
WHERE 
    ([Date] > {ts '2003-01-01 13:59:07'})

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Re: How to use datetime fields in SQL server?

Luca Leonardo Scorcia15-Jan-03 10:10

Luca Leonardo Scorcia

15-Jan-03 10:10

Yep it works in SQL Server, but in Access it fails... he takes it as a GUID... BTW, this is my Access connection string, just in case... thanks for your effort anyway Smile | :)

adminDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("_private/db/skakkinostri.mdb")

Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)

How to detect that ADO Connction was failed

ernst.maurer15-Jan-03 2:21

ernst.maurer

15-Jan-03 2:21

How I can define that ADO Connection with MS SQL Server was failed before there are raised exceptions while ADO Command objects will run.

Re: How to detect that ADO Connction was failed

Mazdak15-Jan-03 19:31

Mazdak

15-Jan-03 19:31

Whats your code and when does it happend?

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Multiple connection problem

Hzi14-Jan-03 20:49

Hzi

14-Jan-03 20:49

I've an strange case where a second connection to a Sqlserver database hangs until time out has expired, the schema is below:

<br />
Connection1=New connection(database1)<br />
Connection1.Open<br />
Connection1.Begintrans<br />
...<br />
Connection2=New connection(database1)<br />
Connection2.Open<br />
Command2.Connection=Connection2<br />
Command2.ExecuteSelectionSQL   |-----------------------here it hangs<br />
Connection2.Close<br />
.......<br />
Connection1.Commit<br />
Connection1.Close<br />

The first connection remains opened and in transaction while connection2 is opened with the same querystring, it opens correctly but when I issue a command using connection2 it hangs.
I use System.Data.SqlClient and I've tried different options in querystring (Enlist=false,.....) but I cannot solve the problem.

Thanks for your help, Smile | :)

Hzi.

Anonymous15-Jan-03 20:23

Anonymous

15-Jan-03 20:23

seems like you have locking problems .. do you access the same tables ?
in both connections ?

Hzi15-Jan-03 20:27

Hzi

15-Jan-03 20:27

Yes, when I'm accessing the same table, it hangs, but when I access another table it not hangs, but SQLServer locks a whole table when I insert a single record ?.

Thanks

Hesham Amin15-Jan-03 20:32

15-Jan-03 20:32

No..
locking is not this simple..it differs according to how you access the table..
tell me the query so i can help !!

Hzi16-Jan-03 1:25

Hzi

16-Jan-03 1:25

Here is a trace of the steps shown in sqltrace tool:

<br />
with the first connection<br />
exec sp_reset_connection<br />
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION<br />
sp_reset_connection<br />
update objectes set [Caducitat]=NULL, [DataValid]=NULL, [UltimaMod]=CONVERT(DATETIME, '2003-01-16T13:18:00', 126) where id=100225<br />
with the second connection<br />
sp_reset_connection<br />
select * from objectes where idref=''BAINtiA000046'' and IM1=1'	<-------------It hangs here

It's all.

Thanks.