Database

FirstPrevNext

Re: @@Identity.....Help Me somebody Daniel Turini21-Oct-02 21:53 Daniel Turini 21-Oct-02 21:53  Can you send us the table definition? It seems that the ArtistID column is not an IDENTITY column.. Could you check it? Another tip: MS does not recommend you to use the @@IDENTITY for this, because it has a nasty definition that causes some weird bugs with triggers. Always use the SCOPE_IDENTITY() function. Q261186 - Computer Randomly Plays Classical Music Re: @@Identity.....Help Me somebody Morten Abrahamsen22-Oct-02 1:37 Morten Abrahamsen 22-Oct-02 1:37  Yes, the ArtistId can't be an IDENTITY column if that message appears. He should also consider using a different datatype than the all-mighty "text" for his input parameters. Morty Re: @@Identity.....Help Me somebody Daniel Turini22-Oct-02 1:42 Daniel Turini 22-Oct-02 1:42  Morten Abrahamsen wrote: He should also consider using a different datatype than the all-mighty "text" for his input parameters. God! I didn't even noticed that! Q261186 - Computer Randomly Plays Classical Music Re: @@Identity.....Help Me somebody Morten Abrahamsen22-Oct-02 1:48 Morten Abrahamsen 22-Oct-02 1:48  Yeah.. and that sort of sucks because that specific type is (sometimes?) transferred differently than normal SQL statements/types in order to handle the large amount of data it MIGHT contain. (2 147 483 647 bytes to be exact) Morty Binding null Dates Derek Lakin21-Oct-02 4:25 Derek Lakin 21-Oct-02 4:25  I have a form that binds controls to a dataset. Some of the fields in the dataset are date fields, so I am using DateTimePicker controls to display (and allow editing of) these fields. However, the date field in question can be null. When this occurs navigation through the records in the dataset is stopped, that is setting the Position property on the BindingContext does not update the controls. Is there a workaround that allows binding to null dates? Derek Lakin. I wish I was what I thought I was when I wished I was what I am. Salamander Software Ltd. Getting the row count from a SQLDataReader Paul Watson21-Oct-02 4:22 Paul Watson 21-Oct-02 4:22  Ok so I am using a SqlDataReader in ADO.NET because I do not want or need the overhead of a DataSet. But now I have just realised I cannot figure out how to get a row count from the reader without actually looping through it, which bites because I need the rowcount before I loop so that I can initialise a collection array. Doh. DOTNET247 seems to say that there is no way other than either looping through or using horrid SQL count statements. So anybody got a magic bullet? Or should I just bite the bullet and do that SQL count? Paul Watson Bluegrass Cape Town, South Africa Re: Getting the row count from a SQLDataReader Ray Cassick21-Oct-02 6:42 Ray Cassick 21-Oct-02 6:42  Do the SQL method... This bit me too [Edit] From what I rememebr, the fact that the datareader does not have a record count property stems from the fact that it realy contains NO records. It is more of a forward only conduit to the data. Not sure if that is true, but it certainly makes sense [/Edit] Re: Getting the row count from a SQLDataReader Daniel Turini21-Oct-02 22:09 Daniel Turini 21-Oct-02 22:09  You can't, because it's a forward-only cursor. Paul Watson wrote: But now I have just realised I cannot figure out how to get a row count from the reader without actually looping through it, which bites because I need the rowcount before I loop so that I can initialise a collection array. Doh. You can create an ArrayList and, at the end, use the ArrayList.CopyTo method for copying it to typed array. Since this will only copy object references, not the actual values, it's very fast. Normally much, much faster than a SQL count. Q261186 - Computer Randomly Plays Classical Music Re: Getting the row count from a SQLDataReader Paul Watson21-Oct-02 22:17 Paul Watson 21-Oct-02 22:17  Daniel Turini wrote: You can't, because it's a forward-only cursor. I know, was hoping someone had some trick up their sleave Daniel Turini wrote: Since this will only copy object references, not the actual values, it's very fast. Normally much, much faster than a SQL count. Great idea, thanks Daniel. Paul Watson Bluegrass Cape Town, South Africa Re: Getting the row count from a SQLDataReader Morten Abrahamsen22-Oct-02 1:43 Morten Abrahamsen 22-Oct-02 1:43  If you had used an SQL COUNT() function before you retrieved the it probably wouldn't be accurate either (atleast not in a multiuser enviornment), unless it's run in a serializable transaction. The SqlDataReader streams the data and will reflect all the changes in the database until the data has passed through the reader. So records could easily be added or deleted after the count was issued and while the reader is processing. Morty Re: Getting the row count from a SQLDataReader Daniel Turini22-Oct-02 1:45 Daniel Turini 22-Oct-02 1:45  Morten Abrahamsen wrote: If you had used an SQL COUNT() function before you retrieved the it probably wouldn't be accurate either (atleast not in a multiuser enviornment), unless it's run in a serializable transaction. Nice point Morten Abrahamsen wrote: The SqlDataReader streams the data and will reflect all the changes in the database until the data has passed through the reader. So records could easily be added or deleted after the count was issued and while the reader is processing. This is not true: The .NET framework does not provides support for server-side cursors, only client-side cursors. This way, only deleted records would be noticed by SqlDataReader. Q261186 - Computer Randomly Plays Classical Music Re: Getting the row count from a SQLDataReader Morten Abrahamsen22-Oct-02 1:58 Morten Abrahamsen 22-Oct-02 1:58  What do you base this on ? AFAIK, the data is streamed directly from the server, and even though it doesn't utilize server-side cursors it doesn't precalculate the entire dataset (it's streamed ... standard SQL Server dataset processing...). So if records are appended during the read it should be reflected. However I could be wrong, so if you have any docs / references please post them .NET SDK: Changes made to a resultset by another process or thread while data is being read may be visible to the user of the SqlDataReader. However, the precise behavior is timing dependent. Morty Re: Getting the row count from a SQLDataReader Daniel Turini22-Oct-02 2:14 Daniel Turini 22-Oct-02 2:14  You may be right: I'm sure the SqlDataReader is a client-side cursor (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/dvconChoosingRightDataAccessTechnology.asp[^]) but the rest was a conclusion of mine... Q261186 - Computer Randomly Plays Classical Music Re: Getting the row count from a SQLDataReader Morten Abrahamsen22-Oct-02 2:23 Morten Abrahamsen 22-Oct-02 2:23  Well... the SqlDataReader is by no means a client side cursor... it's simply a TDS parser. That's what the dataset is for Would be cool to check out the theory though... (how much locking does the TDS generator really support...) but as usual, I would probably never find the time. Morty Re: Getting the row count from a SQLDataReader Daniel Turini22-Oct-02 2:26 Daniel Turini 22-Oct-02 2:26  Morten Abrahamsen wrote: Well... the SqlDataReader is by no means a client side cursor... it's simply a TDS parser. That's what the dataset is for Well, if it is a TDS parser it should be pretty fast! I think you're right, but some Anakrino hacking may solve this issue Q261186 - Computer Randomly Plays Classical Music Re: Getting the row count from a SQLDataReader Morten Abrahamsen22-Oct-02 2:40 Morten Abrahamsen 22-Oct-02 2:40  Hehe... been there However, this is not an issue dependant on the .NET SQL Client layer. It's a question of how the SQL Server handles standard query processing. If you issue a select query with a 100.000 record resultset, it would be returned as a datastream (TDS) to the client. I would think that if there is no isolation (tx) there would just be a read lock on the current row (or index key) and not on the table, so records could easily be added or deleted. So if I'm correct the SQL Server would just read the data page by page and never precalculate the total amount and lock it. Hence the "imagined resultset" could be radically (add/delete) changed during the processing (which could take time), and a prior Count would be useless. Just my 2c Re: Getting the row count from a SQLDataReader Daniel Turini22-Oct-02 2:51 Daniel Turini 22-Oct-02 2:51  I deduced the inserts would not be visible and deletes would by this: what would happen with a forward only cursor if I do a SELECT ... ORDER BY table_field and someone inserts data before my current cursor position? Q261186 - Computer Randomly Plays Classical Music Re: Getting the row count from a SQLDataReader Morten Abrahamsen22-Oct-02 8:19 Morten Abrahamsen 22-Oct-02 8:19  I agree. If you do an order by and that order by is not the clustered index SQL Server would have to preprocess the entire dataset. (meaning the keys .. not necessarily the data). However, it would be interesting to see what happens if you don't use a postprocessing instruction. (ORDER BY, GROUP BY, UNION, DISTINCT etc) Morty Problem with Composite Attribute in access tongc20-Oct-02 20:56 tongc 20-Oct-02 20:56  Hi I have the following table schema Order(orderID, orderDate, creditCardNo, etc.....) OrderedItem(OrderID(pk), ProductID(pk) ) Product(productId, productName ....etc.._ In access, when i delete the product from the Product table, if it is associated with a particular OrderedItem then i can not delete it, else i will be able to delete it. However, I want to set it to ON DELETE SET NULL the ProductID in the OrderedItem How will i do it? Thanks Retrieving Exchange Information Anonymous20-Oct-02 18:39 Anonymous 20-Oct-02 18:39  Hi, I am trying to access password and aliases or for that matter every thing related to microsoft exchange users i am able to retrieve only usernames using mapi.session object, Please help in retrieving other things if any body has code plesae post it here. I have tried using ADSI also. Thanks, Amit Gupta Re: Retrieving Exchange Information Morten Abrahamsen22-Oct-02 1:59 Morten Abrahamsen 22-Oct-02 1:59  I would be very surprised if you were able to retrieve the user password using ANY form of API Can I query an XML file with SQL? Marc Clifton20-Oct-02 14:35 Marc Clifton 20-Oct-02 14:35  I'd like to query and XML file with an SQL statement. Can this be done, or do I have to use XPath stuff? I've been perusing all sorts of CP code and MSDN stuff, and I can't seem to find an example of this, but I can't seem to find something that says I can't do this either. Also, can the queries consist of a table join? Thanks, Marc Help! I'm an AI running around in someone's f*cked up universe simulator. Re: Can I query an XML file with SQL? Nick Parker20-Oct-02 16:21 Nick Parker 20-Oct-02 16:21  Marc Clifton wrote: Can this be done, or do I have to use XPath stuff? I believe you have to use XPath, however SQL Server can return your "recordset" as an XML document. If that is any help to you. Nick Parker The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill Re: Can I query an XML file with SQL? Daniel Turini20-Oct-02 22:35 Daniel Turini 20-Oct-02 22:35  In SQL Server 2000, sort of, you'd better look at this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_94mk.asp[^] Q261186 - Computer Randomly Plays Classical Music Re: Can I query an XML file with SQL? Nick Parker21-Oct-02 1:50 Nick Parker 21-Oct-02 1:50  Daniel Turini wrote: you'd better look at this: And this, an example: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5c89.asp[^] Nick Parker The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill

Last Visit: 31-Dec-99 18:00     Last Update: 10-Jun-24 7:29 Refresh ᐊ Prev1...2401240224032404240524062407240824092410 Next ᐅ

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.