CompareAllSearchableValues creates an update state too long for Access

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Typed datasets in the business layer Why the data access layer should use untyped datasets? and Why the business layer should use strongly typed datasets? Is this applicable to the 3 layered architecture in general or is it related with the implementation in .net? Can someone recommend me a good book to learn in depth the 3 layered architecture? Thanks. Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133554
  - 2
    - Any way to force an update after DBConcurrencyException thrown? I've tried everything I can think of, but I'm still struggling with a solution to force an update to a table, even if a DBConcurrencyException is thrown in my application. Is there any tried-and-true solution to allow an Update to occur, even if the particular record isn't concurrent - in essence, implementing a last-in-wins scenario? Thanks in advance for your responses! Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133550
  - 3
    - 3 different sqldatasource insert queries in 1 transaction...? Hi , In the asp.net application I am working on we use 3 different sqldatasource objects with each a insert command in it. I call: sqldatasource1.Insert(); sqldatasource2.Insert(); sqldatasource3.Insert(); what i want is wrap these three inserts in 1 transaction. I ve found solutions where only one insert is in the transaction (http://windowssdk.msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasourcecommandeventargs.aspx). How can this be solved..? Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133548
  - 4
    - SqlDataReader.GetSchemaTable returns too many columns I've run into a problem I can't seem to explain or work around. I'm calling SqlDataReader.GetSchemaTable() on a database view (SQL Server 2005). It is returning rows not only for the columns in the view, but also for another column that is not ouput in the view. For example, I execute the following code on the Northwind database view named [Alphabetical list of products]: command.CommandText = "SELECT * FROM [Alphabetical list of products]"; reader = cmd.ExecuteReader(CommandBehavior.KeyInfo); schemaTable = reader.GetSchemaTable(); This view selects all 10 columns from Products and also CategoryName from Categories (11 columns total). The tables are joined on CategoryID. When I run the above code, it returns a schema table that contains 12 rows--all 10 from Products, Categories.CategoryName and also Categories.CategoryID. It is this last one that is causing problems for me. CategoryID is appearing twice in my list (once for each table) even though only one of them is in the SELECT. So my questions are: 1. Why is Categories.CategoryID getting into the schema table even though it is not selected? 2. Is there any way to prevent it from getting in there and having the schema table only show the selected columns? For those who do not have the Northwind database handy, here's the view's sql: SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.SupplierID, dbo.Products.CategoryID, dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, dbo.Products.ReorderLevel, dbo.Products.Discontinued, dbo.Categories.CategoryName FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID WHERE (dbo.Products.Discontinued = 0) Thanks, John Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133541
  - 5
    - Formatting error in posted messages - arrows and quotes In the absence of a place to post these comments, I opted to post it here. Messages in posts have their double quote and right-arrow characters replaced by ampersand and other keywords. A message appears normally at first but on subsequent displayes, the text has embedded codes. It would also be nice if the font size while composing replies were increased. I work on a 1024 by 768 pixel screen and I have to read this stuff up real close. Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133532
  - 6
    - Updating sql05 with Stored Procedure I have a typical windows forms application that uses a DataGridView, a dataset, a bindingsource, and a TableAdapter. If I modify one row in the datagridview everything works fine. If I modify more than one row only the first modified row gets updated. My Code to write to the db is { if (this.Validate()) { this.lutCategoriesBindingSource.EndEdit(); if (this.dsLookUp.HasChanges()) this.lutCategoriesTableAdapter.Update(this.dsLookUp.lutCategories); } } The tableAdapter uses Stored Procedures for the commands. I have the same code going against an Access database without the stored procedures and it works fine. When I switched to SQL05 and stored procedures I got this behavior. Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133521
  - 7
    - quickly transferring data from a DbDataAdapter to TableAdapter? Basically, I have the following code which fills up a DbDataAdapter (which is of DataAdatper type). Is there a quick way to map the columns and transfer the data in this adapter to a new TableAdapter ? This new TableAdapter will be linked to a SQL database. Thanks! Chirag DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb"); DbDataAdapter dbDataAdapter = factory.CreateDataAdapter(); DbCommand selectCommand = factory.CreateCommand(); selectCommand.CommandText = excelCommand; DbConnection connection = factory.CreateConnection(); connection.ConnectionString = excelConnectionString; selectCommand.Connection = connection; dbDataAdapter.SelectCommand = selectCommand; DataSet aEESamples = new DataSet(); dbDataAdapter.Fill(aEESamples); Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133517
  - 8
    - Merge PreserveChanges = False not working -- need help I have been chasing my tail and need some help from someone who has succeeded in real-world-handling (i.e. not merely detecting) a concurrency violation in ADO.NET with SQL Server back-end. My data layer class invokes the DataAdapter.Update method and traps concurrency violations as a DBConcurrencyException, as follows: . . . Catch DBEX As System.Data.DBConcurrencyException '// read the 'fresh' row from the DB Dim Fresh As DataTable = SelectSingle(ID) '// at this point, Fresh contains the most recent version of the row '// merge it with the disconnected dataset DS.Merge(Fresh, False, MissingSchemaAction.Ignore) DS.AcceptChanges() . . . In the exception handler, the fresh version of the row is selected from the database, and then merged into the dataset. The intention is to replace the stale values in the dataset with fresh values from the database. Therefore, the PreserveChanges second parameter is set to False. However, this never works. As I step through the code in the debugger, Fresh does have the data as changed by another user, which is good. But after the merge the Dataset (DS) always retains the stale copy of the row. The merge is not overwriting the stale values with the fresh values from the database. I've tried changing the second parameter of the Merge method to True, but the results are the same, thinking it was perhaps a documentation bug. No luck. What am I doing wrong? SECOND RELATED PROBLEM My user wants to merge her desired changes with those made by another user a minute ago after my user had read the record. How do you properly clear the error condition which is visible in the OnRowUpdated method of the DataAdapter? If I set e.Status= Continue (or to SkipCurrentRow) then no System.Data.DBConcurrencyException is raised in the routine where the DataAdapter's update method is invoked. :-( I would be very grateful if someone could point me to some pseudo code which traps a concurrency violation (especially WHERE the trapping is best done, in OnRowUpdated or in the routine that invokes Adapter.Update); then reads the fresh row from the db and merges the fresh row with the stale dataset replacing stale values; and then clears the update error condition so that the dataset is "good to go" after the merge. I have read lots of general examples but found none where the stale data is replaced with fresh data. Thanks Liam Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133516
  - 9
    - Fill method ignoring CommandTimeout value Under the .NET framework 2.0.50727.42, we are encountering a problem with timeouts while performing a query using a SQL TableAdapter. Whatever we set the timeout to gets ignored if we call the Fill method overload that takes a DataTable (or DataSet) only as a parameter. No matter what the CommandTimeout is set to, the command will run to completion and never throw a timeout exception. (Note that, when we run this query in SQL Server Query Analyzer, it takes far longer to complete than the CommandTimeout we specified in code). The only way we can get it to time out is to call the Fill method overload which takes a DataSet, StartRow, MaxRow, Tablename. This is only true when MaxRow is set to a relatively low value like 100. We are also seeing that the specified timeout is honored when we use the ExecuteScalar method of a SQLCommand object. We have tried this code against 2 different SQL 2000 servers and one SQL 7 server Sample Code: Dim myconnection As New SqlConnection("Data Source=myDBServerName;Initial Catalog=DBName;Integrated Security=True") myconnection.Open() Dim mydatatable As New DataTable Dim mydataset As New DataSet Dim mycommand As New SqlCommand("SELECT * FROM tblSomeTable WHERE (memo LIKE '%') AND (refcode LIKE '2%') AND (acctnumber LIKE '%')", myconnection) mycommand.CommandTimeout = 5 Dim mytableadapter As New SqlDataAdapter(mycommand) Dim returnRows As Int32 mycommand.ExecuteScalar 'This Line WILL timeout returnRows = mytableadapter.Fill(mydataset, 0, 100, tblSomeTable) 'This line WILL timeout returnRows = mytableadapter.Fill(mydatatable) 'This line will NOT timeout Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133514
  - 10
    - Using a message queue as a buffer I'm developing a server application which will, basically, receive data from some TCP/IP clients and store them into a database; it's being developed using .NET 2.0/C#. The size of each data packet will be small (a few bytes), but the application need to be able to scale well and handle hundreds of clients, with some readings/second for each of them. The application will have two main set of worker threads, with a buffer between them (a classic producer-consumer model); some threads will acquire data from the clients and store them in the buffer, while some other will get data from the buffer and actually perform the database queries; this structure will reduce the workload on socket reading and allow more socket reads to be done quickly, instead of having to wait for a DB query to run after each read; it also will help in the (unlucky) case the DB server can't be accessed for a while. Now, the question. Someone suggested me to use a message queue as my buffer, due to its easy of programming and its automatic handling of thread synchronization; also, there seems to be an added bonus: if my server stops or crashes for some reason without being able to store all the buffered data into the DB, the same queue will be available upon restarting, with all queued messages being still there. This seems to be a really good suggestion, but I have a couple of concerns about this. First one: performance. Since MSMQ is a general-purpose system, it will surely have some overhead when confronted to an in-memory buffer; it also stores its data on disk, wich will introduce even more overhead. How does MSMQ behave when used for this purpose? If its performance penalty is significantly low compared to the time needed for a DB query, I don't care much about it; but if it's significantly high, this can be an issue. Also, how does it behave in terms of disk storage? Does it create log files which will need to be purged periodically (like SQL Server do)? Second one: data format. My program will acquire many kinds of different data from clients: integers, floating point numbers, text, even images; sometimes, a single block of data will be composed of combinations of these (i.e. two floating point numbers, or three integers and some text, and so on). These blocks of data will be managed as single objects by the application, and will need to be passed from socket-reading threads to DB-writing threads; this is quite easy to do with an in-memory buffer, since objects (and pointers!) are just the same across different threads in the same program; but what will happen when sending one of these objects to a queue and receiving it? Will it still be valid, even if internally it includes, say, a string or an image (which are pointers to other objects)? Thanks for any help/suggestion :-) Massimo Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133513
  - 11
    - logging driver for ADO.Net? I was wondering if anyone knew of a logging driver for ADO.Net. In Java/JDBC, there are a few projects that are essentially driver "shims" that get configured as a JDBC Driver which a program will use, that in turn log the calls that are made, then pass these calls on to the real driver down to the database. For example, there is P6spy/P6log (http://www.p6spy.com/) for JDBC. Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133512
  - 12
    - Identity Column & Dataset relation Using SQL server2000 VB.Net. I have a parent table that uses an identity column as its primary key. I also have a child table that uses the identity to create the relation between the parent & child rows. My form has a grid with a dataset as its source where these 2 tables are joined with a relationship. The user can add data to the dataset via the grid. When new rows are added to the parent table of the data set this identity field is incremented and i copy it to use for the child row to maintain the relation. The problem is that the identity that the dataset has generated for the parent table is not neccisarily the id that the SQL table will generate. Thus the relation between the 2 talbes is broken. I am using the Update method of the Data Adapter to add the new rows to SQL Server. So has anybody found a way to solve this kind of problem or should i be creating my own Id's that i can control better? Gary Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133505
  - 13
    - dbdirect methods with timestamp columns I inherited a SQL database for which I am building a .NET C# web front-end. The tables have a Timestamp column, called "ts", that I thought I might as well use for optimistic concurrency. I'm going VS 2005 all the way, so I have TableAdapter as my DAL that contains DBDirect methods. The TableAdapter is used from my BLL, a class library. My aspx page contains a GridView that uses an ObjectDataSource to the BLL. I've seen Scott Mitchell's wonderful tutorials on the asp.net site that detail building a BLL to a DLL, etc. His examples don't use Timestamp columns for updating and deleting, however, and neither do any of the other examples I could find on the web. That is where my confusion comes in, to wit: 1. The DBDirect methods created for Update and Delete have a parameter "int original_PK", which I get. But they also have "byte[] IsNull_ts" and a "byte[] Original_ts" parameters. The Orignal_ts must be what it sounds like, the Timestamp value that was retrieved when the record was read. But what is IsNull_ts? I don't understand that naming convention. If it is the "new" value, I don't get it because Timestamp values are auto-generated by SQL and I would never update them. 2. How should I handle the Timestamp on the aspx page? I created a bound column for it and set Visible to false, thinking that I would preserve the value for Original_ts on the update. But when the GridView calls my BLL Update, it passes a null value back for ts. 3. Maybe the logic will clear itself up when I understand the IsNull_ts parameters, but just in case... It seems to me that for both Update and Delete, I should first read the current data values so I can get the ts, then compare it to the Original_ts I saved on my aspx page. Only if they are the same should I issue the update/delete. Many thanks, Ray Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133504
  - 14
    - dataset record locking hello all, I have a webservice which writes requests to a requests log table in sql server I have a windows service with a function that kicks-off every 30 seconds to process the records in the requests log table. The function retrieves pending requests (STATUS = 0) and adds them to a datatable using a sqldataadapter once the record has been processed, the STATUS column is set to 1, so that it is not processed again. I know that records can be locked for update, but is there a way to lock records for select ? e.g. The requests log table has 20 pending requests which are picked up and added to the dataset.. in the 30 second interval between function iterations, only 5 are proocessed. The function runs again and 15 of the original requests are added to the dataset Is there some mechanism to prevent these 15 requests from being added to the dataset ? Thanks in advance Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133488
  - 15
    - TableAdapters are a joke. I have just flat given up on TableAdapters. What a joke. I'm not even going to detail all the nonsense I've gone through trying to get these godforsaken "features" to work properly. The concept was good but usability is worse than bad. They should come with that old warning, "don't try this at home folks". Save your energy for something that can consistently do what you expect: Break out the old SqlDataAdapters and stick with them. Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133486
  - 16
    - How to change the column type? I have a DataTable. How can I change the the type of one column from bool to int ? Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133484
  - 17
    - ¦p¦ó±NXP ìª©¥úºÐ¤É¯Å¨ìSP2 ¤p§Ì®Ú¾Úhttp://support.microsoft.com/kb/900871ªº§@ªk §ÚXP ªº¦w¸Ëµ{¦¡§ó·s¨ìSP2¤F ¦ýÁÙ¬O¦³¤@Ó°ÝÃD ¦bµwºÐ¤¤ªºI386¸ê®Æ§¨§ó·s¹L¤F, ¦ýn¦p¦ó§â³oÓ¤É¯Å«áªºXP¦w¸Ëµ{¦¡ °µ¦¨¹³ì¨Ó¦w¸Ëµ{¦¡¤@¼Ë¥i¥H¶}¾÷ªº¥úºÐ©O? Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133483
  - 18
    - Q: Advice on Client/Server app. Hi! Im developing my first client/server application. So ofcourse i got a cople of questions for you gurus out there ;) Created a simple order/detail, with fields and a datagrid, works excelent. Now, in a walkthru i read in the helpfile it updates the grid in three steps. first it removes deleted orders, then updates updated orders, finaly adds newly inserted orders. why is this? As far as i know i can use the .Update on booth tables to ensure save is done. Is there a specific reason it is done in three steps? Second question. got a simple customer table. When using the wizard to create the dataset, the dataset is filled when form loads. If i enter a customer record on a nother computer i need to close down the view and reopen or just issue a fill to reload data. is there no other way to let the dataset get the new data, besides using a timer to reload the dataset. Or can i work directly on the data in the sql server? Regards Martin Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133481
  - 19
    - ADO.NET the fastest? My foot! I just benchmarked a query against an Oracle database using three different access techniques (OLDDB, ADO.NET and ODBC) and I found that ADO.NET fared worse than all of them - almost twice as long as OLEDB! Surely something is wrong here. I was under the impression that ADO.NET was supposed to improve retrieval times, not slow it down! The results are as follows - it is an average of five executions of the same query. Code is included in case someone wants to tell me what I'm doing wrong. The query itself is a fairly long select query that I can't post here due to its proprietary nature - the actual query shouldn't affect these results, anyway. Please, someone, what's going on? ----------------------------------------------------------- OLEDB: 1.21 Seconds ----------------------------------------------------------- public String execute_query (String query, DataGridView data_grid) { Int32 rowcount = 0; OleDbCommand command = new OleDbCommand (query, connection); DataTable data_table = new DataTable (); OleDbDataAdapter data_reader = new OleDbDataAdapter (); try { data_reader.SelectCommand = command; data_reader.Fill (data_table); data_grid.DataSource = data_table; rowcount = data_table.Rows.Count; } catch (Exception e) { MessageBox.Show ("Failed" + Environment.NewLine + e.Message); } finally { data_table.Dispose (); data_reader.Dispose (); command.Dispose (); }// try..catch..finally return "Rows returned: " + rowcount.ToString (); }// execute_query; ----------------------------------------------------------- ODBC: 1.274 Seconds ----------------------------------------------------------- public String execute_query (String query) { Int32 rowcount = 0; OdbcCommand command = new OdbcCommand (query, connection); DataTable data_table = new DataTable (); OdbcDataAdapter data_reader = new OdbcDataAdapter (); try { data_reader.SelectCommand = command; data_reader.Fill (data_table); rowcount = data_table.Rows.Count; } catch (Exception e) { MessageBox.Show ("Failed" + Environment.NewLine + e.Message); } finally { data_table.Dispose (); data_reader.Dispose (); command.Dispose (); }// try..catch..finally return "Rows returned: " + rowcount.ToString (); }// execute_query; ----------------------------------------------------------- ADO.NET: 2.056 Seconds ----------------------------------------------------------- public String execute_query (String query) { Int32 rowcount = 0; OracleCommand command = new OracleCommand (query, connection); DataTable data_table = new DataTable (); OracleDataAdapter data_reader = new OracleDataAdapter (); try { data_reader.SelectCommand = command; data_reader.Fill (data_table); rowcount = data_table.Rows.Count; } catch (Exception e) { MessageBox.Show ("Failed" + Environment.NewLine + e.Message); } finally { data_table.Dispose (); data_reader.Dispose (); command.Dispose (); }// try..catch..finally return "Rows returned: " + rowcount.ToString (); }// execute_query; Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133479
  - 20
    - using DetailsView control, update works, insert fails Here are the commands in the sqlDataSource line. InsertCommand="INSERT INTO T_ADMIN_LOGIN (ALID, LNAME, FNAME, USERID, DOMAIN, STATUS) VALUES (S_ADMIN_LOGIN.NEXTVAL, :Lname, :Fname, :Userid, 'AAC', 1)" UpdateCommand='UPDATE T_ADMIN_LOGIN SET LNAME = :Lname, FNAME = :Fname, USERID = :Userid, STATUS = :Status where alid=:alid'> The update works fine. The insert fails with: ORA-01036: illegal variable name/number Anyone got any ideas? TIA Chris Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133475
  - 21
    - Process slows when do Re-Query of Database Hi I am taking a db creating a dataset then reading some textfiles for changes to insert into the database After I process a file I requery (I call FillDS) the database to update my dataset to get the new inserted records into my dataset Everything works fine except the requery (I call FillDS) is slowing down the process - If I comment out the calls to the requery between files it speeds right along but with the requery in the code it bogs down. I -Is there a better way to design this?? Thanks private void FillDS(string strSQL, string sTable) { m_strSQL = strSQL; m_sTable = sTable; da = new OleDbDataAdapter(strSQL, cnn); cmd = new OleDbCommandBuilder(da); try { da.Fill(ds, sTable); } catch (Exception e) { } } public void UpdateDS() { da.Update(ds.Tables["Trans"]); } public void InsertTrans(string newvalue, int nType, string sProjName,string form) { string strSQL = "insert into Trans (field1,field2) Values (fieldval1, fieldval2)"; OleDbCommand cmd = new OleDbCommand(strSQL, cnn); if (!isopen) { cnn.Open(); isopen = true; } try { cmd.ExecuteNonQuery(); } catch { } } Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133474
  - 22
    - Ado.Net and Unicode (N') So, we're converting a large ASP.Net application from ASCII only to Unicode. The only issue we've run into, on SQL Server, is the N prefix for literals. We create all our Sql statements dynamically, and most of the time we do not (yet) use bind variables, we just construct the Sqls. So the questions is: Is it possible to add the N prefix "automagically" somehow? I guess the answer is; No, unless you use bind variables (Parameters). If so; how would I make this happen? Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133471
  - 23
    - CommandBuilder ConflictOption fallback I've already read the post about "CommandBuilder ConflictOption usage" http://groups.google.ch/group/microsoft.public.dotnet.framework.adonet/browse_frm/thread/e2de6b2ec4fc0f08/3519015bed9c9ba2?lnk=st&q=ConflictOption&rnum=1#3519015bed9c9ba2 Because this option is new, I tried out some of the options: CompareAllSearchableValues: - Behaves always independent of the table. CompareRowVersion: - Takes the Timestamp column if one exists. If no Timestamp column exists, it will fall back to "OverwriteChanges". This behaviour isn't described in the help. OverwriteChanges: - Uses PrimaryKey if one exists, else falls back to "CompareAllSearchableValues" as described in the help. The default option of the SqlCommandBuilder is "CompareAllSearchableValues". This isn't described in the help. MS, can you please confirm the behaviour! Thomas Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133470
  - 24
    - Creating a datatable on a SQL server from a dataset This is probably covered somewhere, but I am having a total brain cramp. I have a DataSet that gets loaded from an XML file. I want to do some processing on it and then save it to a SQL Server data table. I am currently doing by the brute force expedient of creating a SqlConnection, building a string that contains the SQL statements to CREATE the data table and then executing the SQL via an ExecuteNonQuery. It works, but there is a very nagging loud voice in my head telling me that I am doing it the hard way. If anyone know of an easier way or has a link to some sample code that does a similar type of process, I would be greatly appreciative. TIA, If it ain't broke....fix it anyway! [To Email Replies Remove the nospam from the address] Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133463
  - 25
    - Help: DataGridView Invalid data DataError Problem I have two tables feeding through one dataset: A main table with a foreign key into a lookup table. Primary keys in both tables are defined as INTEGER UNSIGNED NOT NULL. The foreign key in the main tables is defined as INTEGER UNSIGNED NULL. The main table is displayed through textboxes on the form, and a separate Combobox displays the selected, and list of lookup values from the lookup table. No data relations have been defined in the dataset. A bindingsource is used, as well as a binding navigator. This part of the form works fine. On the same form I have placed a DGV, with a subset of main table columns and one DGVCombobox column. Initially, this worked just fine. However a minor change to the database column names â?? not the data - has generated the invalid DataErrors. I have defined an error handler to trap the error and decode some information. The Column number points to the DGVCombobox, and the Context bits decode to â??Formatting, Displayâ??. Using the debugger to examine the details on the dataset, I noted that the primary key for the lookup table is defined as FullName of System.Int32. The primary key of the main table is defined as the same. However the main tableâ??s foreign key is defined as having FullName of System.UInt32. I have tried deleting the DGV and recreating it. The result is consistent and the same: as each cell of the DGV Combo box is displayed, the error exception is generated. The resulting displayed value is the foreign key value of the main table. Recall: no constructs were changed, only the column names were adjusted, and they have been thoroughly checked. Here is the code to set up the DGV. DgvCpyStatus is the DGV combobox column. dgvCpyList->DataSource = bindingSource1; dgvCpyName->DataPropertyName="CpyName"; dgvCpyPriority->DataPropertyName="CpyPriority"; dgvCpyID->DataPropertyName="idCompany"; dgvCpyStatus->DataSource=dataSet1->Tables->default["tblStatus"]; //The next line ties the DisplayMember to the foreign key dgvCpyStatus->DataPropertyName ="tblStatus_idStatus"; dgvCpyStatus->DisplayMember="StatusName"; //Textual status column dgvCpyStatus->ValueMember="idStatus"; //primary key column in tblStatus Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133460
- Next
  - 1
    - DataGrid - Array List - Ordering Columns Hi, I am using a datagrid in a windows app and binding it to an ArrayList. The columns in the array list were appearing out of order so I fixed that by using the DataGridColumnStyle object in the DataGrid and setting the MappingName of each column appropriately. My question though is on properties in the object that is in my ArrayList that are booleans. Before I created the DataGridColumnStyle object, the boolean properties showed up as checkboxes. Now, they show up as a string type with the value written out as "true" or "false". I'd much rather have the checkboxes. I tried to set the Format value to System.Boolean or Bool in the DataGridColumnStyle object and neither worked. Does anyone know what I should be doing? Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133459
  - 2
    - ?fill dataTable created at design time with dataAdpter at runtime Hello, I created a dataset and dataTable in the designer (VB2005) as the datasource for a Reportviewer control. Up to now I have been creating dataTables/datasets/dataAdapters on the fly at runtime. But I need to set field properties on the reportviewer at design time - could probably do it at run time - but too much code. So at run time I do this to fill a datatable on the fly dim da As new sqldataAdapter dim ds as new dataset da.selectcommand = new sqlcommand da.selectcommand.connection = conn da.selectcommand.commantext = "select * from tbl1") da.Fill(ds, "tblx") Now my dataset exists at design time along with the datatable. How do I fill the existing datatable? the runtime code above complains if I reference the dataset/dataTable I created at design time. Any suggestions appreciated. Thanks, Rich Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133458
  - 3
    - Q: Creating a head / detail view, not uppdating correctly? Hi! I am doing my first tries to create a order / detail view. I have created two tables in SQL, set relation from the details table to the head table. In Studio 2005 i use the DataSet wizard to create connection, creating the dataset. I select to add th head as simple fields, and the details as a grid. the wizard adds the, toolstrip with appropriate buttons etc. I run the application, i can add the orderhead, adding details. When i save only the orderhead is saved?!? Am i missing something, i tought it should update the details as well?! Any ideas? Point me in a direction or perhaps a link to neet page that will describe on how to do this. Regards Martin Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133457
  - 4
    - Multidimension string into DataTable Is there an easy way to move a Multidimension string array (string [][]) into a DataTable? I tried: ru.employerList is a string[][]. DataTable testTable; testTable = ArrayToDataTable(ru.employerList); This gives me an error: The name 'ArrayToDataTable' does not exist in the class or namespace 'integration.WebForm1' I have System.Data defined. Thanks, Tom Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133453
  - 5
    - Working with table adapters and multiple fills Having done all this untyped and with a lot of typing in the past, I'm trying to make efficient use of table adapters instead of data adapters. I understand that it's just a wrapper. But what I don't understand is how providing multiple fill methods gains me anything. For example, I want to pull both the phone number and the fax number for a particular contact. The value of both come from the PhoneNumber column in the PhoneNumbers table and the select differs only in their phone type. So I set up two fill methods to retrieve. However, clearly the datatable now contains ONLY the second fill. I don't see how this gains anything in terms of usability -- I may as well set up a second datatable in the dataset just for the fax numbers, and set up a separate fill there. this.phoneNumbersTableAdapter.FillOfficePhone(this.dsBrands.PhoneNumbers, _intContactID); if (dsBrands.PhoneNumbers.Rows.Count > 0) { dsBrands.PhoneNumbersRow rowPhone = dsBrands.PhoneNumbers[0]; if (!rowPhone.IsPhoneNumberNull()) { txtPhone.Text = rowPhone.PhoneNumber; } } // this fill would now overwrite the contents of the PhoneNumbers datatable this.phoneNumbersTableAdapter.FillFaxNumber(this.dsBrands.PhoneNumbers, _intContactID); ... Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133452
  - 6
    - da.update and Bool --> yes/no type In a data grid using Access as the data source.. The da.update is failing with "object reference not set to an instance of anobject" It looks like everything is ok and I was wondering if there is a problem that I have a bool column mapping to a yes/no type. __________________________________ Here is the code: Private Sub PopulateProductGrid() Dim conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source='C:\Program Files\MyProgramArea\Pricing\Pricing.mdb';Persist Security Info=False") Dim sSQL As String sSQL = "select * from ProductNames" conn.Open() Dim da As New System.Data.OleDb.OleDbDataAdapter(sSQL, conn) Dim cb As New OleDb.OleDbCommandBuilder(da) Try da.Fill(myDS, "ProductNames") DataGrid1.DataSource = myDS DataGrid1.DataMember = "ProductNames" Dim irow As Integer Dim icol As Integer irow = 0 DataGrid1.Select(irow) DataGrid1.SetDataBinding(myDS, "ProductNames") RowCount = DataGrid1.BindingContext(myDS, "ProductNames").Count() Catch ex As Exception MessageBox.Show("Failed to connect to data source") Finally 'conn.Close() End Try End Sub Private Sub ProductTypeSelection_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load PopulateProductGrid() StoreNameOut.Text = StoreName VolumeDiscountOut.Text = VolumeDiscount ' ' Create a Grid Table Style. Map it to the "Product Names" Table. ' Dim aGridTableStyle As New DataGridTableStyle aGridTableStyle.MappingName = "ProductNames" aGridTableStyle.AlternatingBackColor = Color.Lavender ' ' Create GridColumnStyle objects for the grid columns ' Dim aCol1 As New DataGridTextBoxColumn Dim aCol2 As New DataGridTextBoxColumn Dim aCol3 As New DataGridBoolColumn ' With aCol1 .HeaderText = "Code" .MappingName = "CODE_VALUE" .Width = 50 .Alignment = HorizontalAlignment.Center .TextBox.Enabled = False End With ' ' Set column's caption, width and disable editing. ' With aCol2 .MappingName = "CODE_MEANING" .HeaderText = "Prodcut Type" .Width = 515 .Alignment = HorizontalAlignment.Left .TextBox.Enabled = False End With With aCol3 .MappingName = "CODE_Selected" .HeaderText = "Select" .Width = 50 .Alignment = HorizontalAlignment.Center End With ' ' Add the GridColumnStyles to the DataGrid's Column Styles collection. ' With aGridTableStyle.GridColumnStyles .Add(aCol1) .Add(aCol2) .Add(aCol3) End With ' ' Add the GridColumnStyles to the aGridTableStyle. ' DataGrid1.TableStyles.Add(aGridTableStyle) End Sub Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate End Sub Private Sub NextButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextButton.Click DataGrid1.DataSource = myDS DataGrid1.DataMember = "ProductNames" Try da.Update(myDS, "ProductNames") Catch ex As Exception MsgBox(ex.Message) End Try PricingForm.StoreName = StoreName PricingForm.VolumeDiscount = VolumeDiscount PricingForm.Show() End Sub Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133451
  - 7
    - dataview relations hallo i try to fill 3 grids with relational data. customer -> orders -> orderdetail dvcustomer = dvm.createdataview( ds.tables["customer"]);//dvm->dataviewmanager dvorders = dvm.createdataview( ds.tables["orders"]); dvorderdetail= dvm.createdataview( ds.tables["orderdetail"]); foreach( DataRowView drv1 in dvcustomer) { dvcustomer = drv1.CreateChildView(drcustomer_orders); } foreach (DataRowView drv2 in dvorders) { dvorderdetail = drv2.CreateChildView( drorders_orderdetail); } grid1.datasource = dvm; grid1.datamember = "customer"; grid2.datasource = dvm; grid2.datamember = "customer.customer_orders";// relation grid3.datasource = dvm; grid3.datamember = "orders.orders_orderdetail";// relation it works fine , althgough grid3 shows the correct initial rows it is not updated when i change the current row in grid1 or grid2. when i change current row in grid1, grid2 is updated correctly. i have played around with the values for datasources/ datamembers but grid3 will not work as i expected. thanks for help sebastian Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133439
  - 8
    - rowfilter date formats Forgive me if this is not the right place to post this. I've already had it booted from the vb.database forum. ------------------------------ I'm thoroughly confused. The following expression works fine as a table view rowfilter when locale is set to US. dateField <= "#" & Now.ToShortDateString & "#" It fails when you set your locale to German. (Most embarassing for my client on a recent show and tell to his peers in Europe.:-))) Error is string is not recognized as date. Presumably because the day and month are switched in most of the world from what we do in the US. Luckily we "tested" this after the 12th of the month!!!! The following works: Dim noww As String = Now.ToString("MM/dd/yyyy") dateField <= "#" & noww & "#" At first I was puzzled because noww is e.g. 09.30.2006. On further reading the doco I see that the locale date separator is used. So the question is simple. Have I now stumbled across a formatting of the test string that will work regardless of the locale's date seperator, or have I encountered a bug, or what? More to the point where can i find a good definition of the syntax and semantics of rowFilter when dates are involved? Note MS Lurkers - as usual the VS 2005 help is as useless as <substitute your favorite off color expression for these circumstances>. It only shows an example and has nothing on globalization. (And this only after you follow your nose around the barn to "expressions"!) To use my recurring mantra these days - Yet another reason why VSTS 2005 is not ready from prime time:-) -- Regards, Al Christoph Senior Consultant Three Bears Software, LLC just right software @ just right prices @ 3bears.biz Microsoft Certified Partner (ISV) Coming soon: Windows Mail for Vista. Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133431
  - 9
    - A connection pooling error has occurred I am trying to clean an object in its Finalize method regarrding connection pbjects as follows : objConnection.Dispose() objConnection = Nothing shCard = Nothing The connection iscreated in another object as follows objConnection = shCard.CreateConnection I get the following error on the line "objConnection.Dispose() or objConnection.close *************************************** {System.InvalidOperationException} [System.InvalidOperationException]: {System.InvalidOperationException} HelpLink: Nothing InnerException: Nothing Message: "A connection pooling error has occurred." Source: "System.Data" StackTrace: " at System.Data.SqlClient.SqlConnection.Close() at System.Data.SqlClient.SqlConnection.Dispose(Boolean disposing) at System.ComponentModel.Component.Dispose() at ConfigBAL.ShoppingCartDB.Finalize() in C:\Inetpub\wwwroot\Configurator\Components\ShoppingCartDB.vb:line 977" TargetSite: {System.Reflection.RuntimeMethodInfo} ********************************************* Does that mean that I can not close or dispose a connection object because of pooling As I said in a previeou post, I have user connections depassing the default, which I think is 100 by default Thanks Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133424
  - 10
    - Storing data from multiple clients Hi to all. I'm developing a network server application which will be constantly receiving data from mobile GPRS clients and storing them in a centralized database (probably SQL Server or Oracle); from the server's point of view, the clients will just be standard TCP/IP clients, sending data using a custom protocol I'll design. The data will be sent to the server in messages, which will contain very small amounts of data (a bunch of floating point numbers), but will be sent quite often, every second or even faster. The number of client is not pre-determined, but the application needs to be able to scale up to hundreds of clients. The server won't do any processing on the incoming data, just add a timestamp and the client's ID and INSERT a new row in a table. I'm going to use async sockets to handle the connections from the clients, so the data storing actions will be triggered by incoming socket data and will be handled by the socket callback methods; I think this is much better than using worker threads to wait on the sockets, since the number of simultaneous connections can potentially become quite high (am I right on this?). Now, my question is: which is the best way to do an INSERT on the DB every time a data message arrives from a connected socket? I (obviously) don't want to manually open and close a DB connection every time; I could open one when the server starts and do all of my data access using that one, but I don't know how this method works when using a multithreaded application; if I have to use some form of mutual exclusion here, then the single DB connection will become a bottleneck. I've heard about using connection pooling, but I don't actually know how it works and if this is the ideal technique for this scenario. Can someone please help? :-) Thanks Massimo Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133416
  - 11
    - Marginalizing parameter In case I had a parametric query SELECT Name, Phone from MyFriends WHERE MyFriends.city = @CITY is it possible to specify a string value for @CITY in order to have the query to behave exacly like : SELECT Name, Phone from MyFriends ?? In other word, if this is possible, what is the parameter value which would correspond to eliminate the WHERE condition ? Or do I have necessary to remove the "where" part? -P Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133414
  - 12
    - Subselect, TableAdapter and MS-Access 1) I generated a Subselect query in MS-Access which works fine there: SELECT (SELECT Name FROM Players WHERE Players.PlayerID = PlannedGames.PlayerID) AS PlayerName, (SELECT Surname FROM Players WHERE Players.PlayerID = PlannedGames.PlayerID) AS PlayerSurname, * FROM PlannedGames This Subselect is recommended in Scott Mitchel's excellent MSDN Article "Creating a data access layer". Then I cut&paste this select statement into the TableAdapter Configuration Wizard. This generates SQL parsing errors. Does the ADO.NET 2.0 provider for MS-Access not support Subselects? 2) In general is there a comparison table which SQL-Server 2005 features / .NET 2.0 classes are not available/working when using ADO.NET 2.0 and MS-Access 2003 ? thank you very much herbert Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133413
  - 13
    - Timeout expired connection Ihave an application (.Net 1.1, wi2k3 SP1, SQL 2005) that works fine after severalminutes of browsing then I start to get the following error : Servor Error in '/myVirtualServer' Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. Thismay have occured because all pooled connections were in use and max pool size was reached. I have several connections opened/closed that use the same connection string What might be the issue? Is there a parameter to fine tune the number of connection allowed. Thanks Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133412
  - 14
    - Update Access DB from a Data Grid I have a data grid with three columns Text1, Text2, Bool3. I would like to have the user check/uncheck in Bool3 column and automatically update the Access DB. One other thing I have Text1 and Text2 and readonly and the user can only modify the Bool3 column; is there a way to have the user only in the bool3 column and never in the Text1 or Text2 columns. Thanks, Steve Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133406
  - 15
    - Async Ado.Net Operations (Begin/End OpenConnection) We're building high performance database applications, and are trying to use all the new Async ADO.Net 2.0 features. The problem we're having is that we can't find a SqlConnection.BeginOpen/EndOpen method. We can execute everything using the BeginExecuteCommand, but without the BeginOpen on the connection we end up with lots of deadlocked threads, all waiting for SqlConnection.Open. This means things are more-or-less useless. We looked briefly into MARS for doing this, as that might be an option, but we really prefer the more standard Async stuff as it maps much easier into working with other database providers such as Oracle. What's frustrating is that early Whidbey builds, and many old ADO.Net 2.0 articles, refer to these methods so they existed at one point. These methods were obviously removed during the .NET 2.0 RTM process, which seems really strange. Any ideas how to work around this and actually do highly scalable I/O using the Async infrastructure in ADO.NET 2.0? -- Chris Mullins MCSD.Net, MCPD Enterprise http://www.coversant.net/blogs/cmullins Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133397
  - 16
    - ADO.NET 2.0 Asynchronous Command Execution: Independant of Web Session? I have a small ASP.NET 2.0 application that needs to be able to kick off a long stored procedure (for importing and processing tables ) and then allow the user to continue with the session, perform other tasks, and log out without having to wait for the SP to complete. I read the following article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/async2.asp This looks very promising but I could not figure out what would happen to the thread if the user logged out before it completed. The stored procedure could take several hours depending on the parameters selected so I need the command to be independant of the web Session. Will it keep running or die when the user logs out? If it dies, can anyone suggest a way to set it up so it will run until complete? Thanks for your help! Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133388
  - 17
    - Getting Oracle Error Hi I'm trying to run a queries on a schema, and I keep getting ORA-00911: invalid character error. If I copy/paste it in SQL/Plus it runs fine. This is what I'm doing in code: //Set session here to ZGK037_MDB string sqlSession = string.Format("ALTER SESSION SET CURRENT_SCHEMA={0}", oDB.DatabaseName); //Note I don't close the Connection Object in this call. It just gets an OracleCommand Object cmdOracle = MiscDataUtils.GetCommandObj(sqlSession, oDB.Connection); //Execute the sqlSession query cmdOracle.ExecuteNonQuery(); //This is what it shows in my logfile...so it looks like the above worked with no exceptions [Logging-DEBUG]-9/29/2006 10:22:45 AM [GetCommandObj] ALTER SESSION SET CURRENT_SCHEMA=ZGK037_MDB Next I try to run a query on this command obj cmdOracle.CommandText = sql; //Set to my new SQL string rdr = MiscDataUtils.GetDataReaderFromCommandObj(cmdOracle); This is what the log file says: [Logging-DEBUG]-9/29/2006 10:22:45 AM [GetDataReaderFromCommandObj] Select RoFm.SymCount as Occ, csd.oid as SymbolOID, csd.m_progID as ProgID, csd.m_version as ProgVersion From CORESymbolDefinition csd, (Select Count(*) as SymCount, fm.m_defname as RoFmID FROM CORERelationOrigin RO JOIN COREFlavorMgr FM ON RO.oidTarget = FM.oid JOIN CORESymbol S ON RO.oid = S.oid Group By fm.m_defname ) RoFm Where csd.m_progID=RoFm.RoFmID Order by csd.m_progID; [Exception] ORA-00911: invalid character Any help is really appreciated. Sunit Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133385
  - 18
    - TableAdapter vs DataAdapter Hi, I was wondering when to use TableAdapters (TA) and when to use DataAdapters (DA). If I've got a typed dataset containing multiple related tables, I would say use a DA to fill the dataset. Why should I use TA instead? I can imagine situations where may be just one table from the dataset is needed, in that case you might say, use a TA. I agree on that. What about updating a typed dataset containing multiple related tables. In that case I also would use a DA rather than TA's. I presume I will need multiple TA's when updating multiple tables in this typed dataset (i.e. added orderd and orderdetails in the typed dataset). Conclusion: If I want to use just a few tables I would better fill the tables using TA's and if I've got just one table to update I could also use a TA. But when filling a dataset in one operation and when updating multiple tables in a dataset I would better use a DA. Is this correct? P.S.: I will not use SQL in code, just SPROC's Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133383
  - 19
    - Casting - IDataParameter Is there some way to cast a IDataParameter to a SQLParameter? It's not working and I'm not sure why. Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133381
  - 20
    - xmltype oracleclient Hi - Does anyone know if the xmltype oracle column data type is supported in system.data.oracleclient? ---------------- Sal .NET 2003 Oracle 9i Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133379
  - 21
    - Connection pooling parameters not working for Oracle connection I am using OracleConnection to connect to Oracle 9i Database from a VB.NET project the connection string has the parameters "User ID", "password", and "Data Source". my project import System.Data.OracleClient I tried to add connection pooling parameters like "Connection Timeout", "Incr Pool Size" and "Min Pool Size" to the connection string string but for each one I get the error that this parameters is not supported, when I try to open the connection any clue ? Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133378
  - 22
    - Export Table to dBase 3 (.dbf) Hi! Does anybody know the best way how to export a DataTable to a dBase 3 file? Regards Stephan Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133377
  - 23
    - InvalidOperationException when changing value in XmlDataDocument I'm using a XmlDataDocument to interface a DataSet. When trying to change the value of a node (I'm actually using InnerXML) I get an InvalidOperationException saying "Please set DataSet.EnforceConstraints == false before trying to edit XmlDataDocument using XML operations.". Is it not possible to make changes in the DataSet without setting EnforceConstrains to false? If I set the EnforceConstraints to false, all values that are constrained may be overrided. I only want to be able to set values that are not constrained. Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133375
  - 24
    - Remove association between DataSet and XmlDataDocument I have a DataSet that is not cleared untill the application is closed. But I want to associate a XmlDataDocument to the DataSet several times. If I try this I get an ArgumentException saying that a XmlDataDocument cannot be associated with a DataSet that already has an association. How do I remove this association without clearing the DataSet? Another possibility would be to retrieve the reference to the XmlDataDocument from the DataSet, but such a reference doesn't seem to exist? Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133374
  - 25
    - What happened to SqlConnection.BeginOpen? We're building some very high performance database applications, and are trying to use all of the new Async ADO.Net 2.0 features. The problem we're having is that we can't find a SqlConnection.BeginOpen/EndOpen method. We can execute everything using the BeginExecuteCommand, but without the BeginOpen on the connection we end up with lots of deadlocked threads, all waiting for SqlConnection.Open. We looked briefly into MARS for doing this, as that might be an option, but we really prefer the more standard Async stuff as it maps much easier into working with other database provides such as Oracle. To do this with MARS, we would still need this BeginEnd methodology. What's frustrating is that early Whidbey builds, and many old ADO.Net 2.0 articles, refer to these methods so they existed at one point. These methods were obviously removed during the .NET 2.0 RTM process, which seems really strange. Any ideas how to work around this and actually do highly scalable I/O using the Async infrastructure in ADO.NET 2.0? -- Chris Mullins MCSD.Net, MCPD Enterprise http://www.coversant.net/blogs/cmullins Tag: CompareAllSearchableValues creates an update state too long for Access Tag: 133370

My access table has over 100 columns. When I use the CommandBuilder to
create my update statement with this option I can see that it is
creating three criteria per field in the WHERE clause.

example:
AND ((? = 1 AND `WellConfig` IS NULL) OR (`WellConfig` = ?))

When ran the Jet engine throws an error that the statement is too
complex.

Old ADO updating using RecordSets did concurrency checking in a
simlilar fashion (I suspect ) and updates this same table fine. I
assume that is because only a simple...

AND `WellConfig` = ?

...criteria was generated per field.

What am I missing?

Top

Re: CompareAllSearchableValues creates an update state too long for Access by Otis

Otis
Fri Oct 06 05:38:13 CDT 2006

On 5 Oct 2006 14:07:59 -0700, sprockett@gmail.com wrote:

>My access table has over 100 columns. When I use the CommandBuilder to
>create my update statement with this option I can see that it is
>creating three criteria per field in the WHERE clause.
>
>example:
>AND ((? = 1 AND `WellConfig` IS NULL) OR (`WellConfig` = ?))
>
>When ran the Jet engine throws an error that the statement is too
>complex.
>
>Old ADO updating using RecordSets did concurrency checking in a
>simlilar fashion (I suspect ) and updates this same table fine. I
>assume that is because only a simple...
>
>AND `WellConfig` = ?
>
>...criteria was generated per field.
>
>
>What am I missing?

If your Access database will not be used by more than one user at a time you can
stop this by going to the advanced properties of the wizard that generates the
code and turning off concurrency handling. If not, then you will need to try to
figure out a way to reduce the number of columns in the table, or write your own
queries.
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

Top