Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-05-14 : 19:37:41
|
| Hiya!This is an ADO question: I'm bringing out a two table joined recordset set, one table is Orders, and two columns from Products (Description and Price). I'd like to be able to insert into the Orders table without SQL Server trying to insert new rows into the Products table. I'm using VB6 w/ ADO 2.5, and SQL Server 7, and this particular situation is that I'm trying to enter orders and payments in a Janus Gridex. I'd like to batchupdate the recordset for data integrity reasons. Any ideas?Thanks,SarahSarah Berger MCSD |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-14 : 20:21:51
|
| If instead of performing the JOIN in the recordset, you instead created a view using the same SQL as the recordset uses now, the view might be updateable/insertable and it would solve your problem. You'd have to provide the exact SQL of the query you're using, and the structure of all of the tables involved, in order for us to determine that. You can try testing this by creating the view, opening it in Enterprise Manager or as a linked table in Access, and attempt to add a new row. I'm somewhat doubtful it will work though.I personally never used the Update method of the ADO Recordset. I also never used data grid controls that were directly bound to the data source, so I also never had a live, connected recordset. The data was retrieved using regular SELECT statements or stored procedures and written to the screen. All of my INSERT and UPDATE operations were done with sprocs. Row ID's and other data were passed as parameters and the stored procedure would do the rest. Since the data was entered directly in the form, there was no need to refresh the screen once the data was inserted or updated. I don't know how viable this is for you without knowing how the application uses the datagrid, but it really is a better way to do it, especially if you ever plan to go with a web-based form, you'll have to get used to disconnected recordsets.As far as batch insert or update operations, you can passed comma-separated values to a stored procedure and parse them out:http://www.sqlteam.com/SearchResults.asp?SearchTerms=csvhttp://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13459The 2nd one handles multiple CSV values, so you could pass multiple orders worth of data in one call and have the database INSERT everything in one operation. It simulates a batch update, although in truth it eliminates the need for a batch.HTH |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-05-14 : 23:26:31
|
| Yeah, I tried a view. Didn't work. (Logically, isn't a recordset the same thing as a view?)This app. will never be a web page, unless some ASP (Application Service Provider) decides to buy it, which is quite farfetched.I have experimented with so many grids out there, I'm swearing that one day I'll write my own, which will be perfect. But now I'm dependent on third party controls. DBITech, whose grid you would like, RobVolk, because it offers good unbound support, and you could use your own SQL, is extremely inflexible in other respects (no invisible columns, etc.). Other grids don't have good support for unbound data - you have to manage your own arrays of data, which entails constantly redimensioning your arrays to fit the data. As far as bound grids go, they make life easy sometimes, but their internal data handling features can be a pain sometimes - like now when I want to control the data.Sarah Berger MCSD |
|
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2002-05-15 : 01:05:25
|
| You want to make good friends with your DBA (especially a performance fanatic)? Learn stored procedures.Get comfortable with set based execution (vs. procedural). One should use forward-only, read-only recordsets whenever possible and use stored procedures as much as possible, especially on the processing side. desktop apps and web apps are totally different things when it comes to data flow and my experience is with web (I run a 13GB primary db plus 6 other 50-200MB secondary dbs and all the apps are web sites). But recordsets are CURSORS and cursors are POISON. I'd try to minimize there use and with recordsets stick to the good ole firehose cursor (which aren't cursors at all :) ) |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-05-15 : 10:20:56
|
| Hey, GreatInca, I do use stored procs. Anything requiring set based processing, I will put into a stored proc. But sometimes a forward only, read only cursor just doesn't do it, or the performance gain isn't worth the extra effort to implement it. This doesn't apply to web apps which all use disconnected recordsets anyhow, so they might as well be firehose cursors, with T-SQL to do the actual processing, besides for the fact that in web apps the performance gain is real significant. The app I'm doing is strictly a desktop app, as I wrote before, and third party controls like grids are a way of life. Anyone got an ADO compatible solution?Sarah Berger MCSD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-15 : 11:07:03
|
| The real problem is that the bound grid interface works when you have a local database, like Access, and only a few users (less than 100 anyway). Unfortunately the database technology has seized on web-based stuff and is only developing towards that kind of interface, where bound grids are death. ADO really isn't meant to support it. Data Access Objects (DAO) handled it better, but its support for SQL Server isn't too good. If you're brave, you might want to give DAO a shot, but it'll make you cry.Did any of those links help out? I think you can wrap up something like that in an OnUpdate event on the grid control, if it supports something like that (Access does, that's what I basing it on; I've done it and seen it done in Access)Edited by - robvolk on 05/15/2002 11:07:30 |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-05-15 : 21:14:57
|
| Hiya!Thanks, guys, but I got the darned thing to work by using a workaround. I unbound the description & price columns, store their values in dynamic arrays, and fetch and refetch the array values for each row. Now the grid is bound only to the Orders table, with no more errors of the kind.CheersSarah Berger MCSD |
|
|
|
|
|
|
|
|
Inserting into multi-table recordset with .updateb