SqlCommandBuilder and optimistic concurrency

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
    - SQLHelper ExecuteReader not closing connections Hi There, I am using the SQLHelper class and have implemented runsp, executescalar, executedataset, and executereader. I am using connection pooling between IIS and SQL Server. All these clases except for ExecuteReader are re-using connections properly, ExecuteReader for some reason keeps creating new connections as it is not closing the previous one properly. Any ideas? I do issue a datareader.close command. But as I am using SQLHelper in a data layer, I am passing the datareader back to the business layer but I can't close the connection at this point as I wont be able to use my reader. Is the reader.close suppose to close the connection or just the reader? How can I close the connection outside the data layer. Any help much appreciated. Tag: SqlCommandBuilder and optimistic concurrency Tag: 139540
  - 2
    - ANN: .NET Provider for XML For those who are interested, I've just released a trial version of a .NET Provider for XML. Based on the successful, QueryADataset SQL engine for Datasets and XML documents, this assembly supports the standard .NET provider classes: XmlClientFactory - standard factory classes XmlConnection - connect to a XML file or Dataset XmlConnectionStringBuilder - standard connection string builder XmlCommand - issue SQL commands against a Dataset or XML file, supports all behaviors XmlCommandBuilder - build matching INSERT, UPDATE and DELETE commands for a given SELECT statement XmlTransaction - support a single transaction, commit and rollback XmlParameterCollection - standard parmeter collection XmlParameter - standard parameter XmlDataReader - standard data reader with full schema support XmlDataAdapter - standard data adapter Parameterized SQL statements are over 3000 times faster than inline statements with approx. 25000 statements / second. The underlying query engine supports standard SELECT/UNION, INSERT, UPDATE, DELETE, TRUNCATE, CREATE and DROP commands. Multiple statements can be issued together using ';' to seperate them. The SELECT statement supports all join types, sub-queries, group by, having, order by, built-in and aggregate functions and PIVOT/UNPIVOT table sources. If you are interested, you can download the trial version from http://www.queryadataset.com. Thanks Adrian Moore Microsoft MVP - Windows Networking http://www.queryadataset.com Tag: SqlCommandBuilder and optimistic concurrency Tag: 139538
  - 3
    - Problem in reteriving Data from database Hi EveryBody: I made web site using asp.net 2.0 Vb.Net. The project depends on database in the local machine. The web site has â?¢ Create User Wizard and â?¢ Login form When the user is created the username and password saved in the database. And the code is as follows: Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser Dim husam As String = CreateUserWizard1.UserName Dim password As String = CreateUserWizard1.Password Dim cmd As SqlCommand Dim connectionString As String = "Integrated Security=SSPI;" + "Initial Catalog=Husam;" + "Data Source=localhost;" Dim connection As New SqlConnection(connectionString) Dim sql As String Try connection.Open() sql = "INSERT INTO husam_Tab(user_Name,user_Password)" + "Values('" & husam & "','" & password & "')" cmd = New SqlCommand(sql, connection) cmd.ExecuteNonQuery() connection.Close() Catch ex As Exception End Try End Sub But I face problem when I retrieve the username and password from the database to perform the authentication in the login form. The login form retrieves the data saved in the App_Data File in the solution not from the database. The code for retrieving data from database as follows: Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate Dim husam As String = Login1.UserName Dim password As String = Login1.Password Dim cmd As SqlCommand Dim connectionString As String = "Integrated Security=SSPI;" + "Initial Catalog=Husam;" + "Data Source=localhost;" Dim connection As New SqlConnection(connectionString) Dim sql As String Dim rawan As New List(Of String) Dim rawan1 As New List(Of String) Try connection.Open() sql = "SELECT user_Name,user_Password FROM husam_Tab" cmd = New SqlCommand(sql, connection) cmd.ExecuteNonQuery() Dim myReader As SqlDataReader = cmd.ExecuteReader() While (myReader.Read()) rawan.Add(myReader.GetString(0)) rawan1.Add(myReader.GetString(1)) End While myReader.Close() connection.Close() If rawan.Contains(husam) Then If rawan1.Contains(password) Then 'Login1.DestinationPageUrl = "" Else Login1.FailureText = "Your password is not correct" Login1.FailureAction = LoginFailureAction.Refresh End If Else MsgBox("Your user name is not correct") End If Catch ex As Exception End Try End Sub So any help or redirection to make the authentication from the database not from the App_Data will be appreciated. Regardâ??s Husam Tag: SqlCommandBuilder and optimistic concurrency Tag: 139532
  - 4
    - ASP.NET & MySQL I want to use asp.net & mysql. I am trying to download mysql db and tools from www.mysql.com but i can find everything what I need. Can somebody tell me where I can download DB, Tools, OleDB provider and everything what I need for use mysql & asp.net 2.0? BTW, what is better, use MySQLConnection, MySQLDataAdapter, MySQLDataset etc... or OleDB objects (install oledb provider for mysql)? Tag: SqlCommandBuilder and optimistic concurrency Tag: 139530
  - 5
    - How do I tell if the Oracle client is installed Hi; The class OracleConnection is part of .NET and so is always available. However, it does not work if the Oracle client software is not installed on a user's system. How can I test for that being installed so I don't offer Oracle as a choice if it can't be used? -- thanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm Tag: SqlCommandBuilder and optimistic concurrency Tag: 139525
  - 6
    - AccessDataSource not working I am using MS Visual Web Developer 2005 Express Edition, MS Access DB, XP Pro and IIS 5.1 on my local XP Pro. Why does this work: <asp:DropDownList ID="cmbcboFiltLanguageID" Style="z-index: 201; left: 640px; position: absolute; top: 155px" runat="server" Width="377px" Height="20px" BackColor="8454016" ForeColor="0" AutoPostBack="true" DataSourceID="AccessDataSource4" DataTextField="FilterLanguageID" OnSelectedIndexChanged="cmbcboFiltLanguageID_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose a Language Name --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource4" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT LanguageName & EthnologueCode & EthnologueCodesOld & LanguageID AS FilterLanguageID FROM Languages WHERE (LanguageName <> '' AND LanguageName IS NOT NULL AND LanguageNameAlt <> '' AND LanguageNameAlt IS NOT NULL AND EthnologueCode <> '' AND EthnologueCode IS NOT NULL AND EthnologueCodesOld <> '' AND EthnologueCodesOld IS NOT NULL) ORDER BY LanguageName"> </asp:AccessDataSource> <asp:DropDownList ID="cmbcboFiltEthCode" Style="z-index: 101; left: 641px; position: absolute; top: 202px" runat="server" Width="376px" Height="20px" BackColor="8454016" ForeColor="black" AutoPostBack="true" DataSourceID="AccessDataSource3" DataTextField="FilterEthCode" OnSelectedIndexChanged="cmbcboFiltEthCode_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose an Ethnologue Code --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb"> </asp:AccessDataSource> and this: <asp:DropDownList ID="cmbcboFiltLanguageID" Style="z-index: 201; left: 640px; position: absolute; top: 155px" runat="server" Width="377px" Height="20px" BackColor="8454016" ForeColor="0" AutoPostBack="true" DataSourceID="AccessDataSource4" DataTextField="FilterLanguageID" OnSelectedIndexChanged="cmbcboFiltLanguageID_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose a Language Name --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource4" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb"> </asp:AccessDataSource> <asp:DropDownList ID="cmbcboFiltEthCode" Style="z-index: 101; left: 641px; position: absolute; top: 202px" runat="server" Width="376px" Height="20px" BackColor="8454016" ForeColor="black" AutoPostBack="true" DataSourceID="AccessDataSource3" DataTextField="FilterEthCode" OnSelectedIndexChanged="cmbcboFiltEthCode_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose an Ethnologue Code --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT EthnologueCode & EthnologueCodesOld & LanguageName & LanguageID AS FilterEthCode FROM Languages WHERE (LanguageName <> '' AND LanguageName IS NOT NULL AND LanguageNameAlt <> '' AND LanguageNameAlt IS NOT NULL AND EthnologueCode <> '' AND EthnologueCode IS NOT NULL AND EthnologueCodesOld <> '' AND EthnologueCodesOld IS NOT NULL) ORDER BY EthnologueCode"> </asp:AccessDataSource> But not this: <asp:DropDownList ID="cmbcboFiltLanguageID" Style="z-index: 201; left: 640px; position: absolute; top: 155px" runat="server" Width="377px" Height="20px" BackColor="8454016" ForeColor="0" AutoPostBack="true" DataSourceID="AccessDataSource4" DataTextField="FilterLanguageID" OnSelectedIndexChanged="cmbcboFiltLanguageID_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose a Language Name --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource4" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT LanguageName & EthnologueCode & EthnologueCodesOld & LanguageID AS FilterLanguageID FROM Languages WHERE (LanguageName <> '' AND LanguageName IS NOT NULL AND LanguageNameAlt <> '' AND LanguageNameAlt IS NOT NULL AND EthnologueCode <> '' AND EthnologueCode IS NOT NULL AND EthnologueCodesOld <> '' AND EthnologueCodesOld IS NOT NULL) ORDER BY LanguageName"> </asp:AccessDataSource> <asp:DropDownList ID="cmbcboFiltEthCode" Style="z-index: 101; left: 641px; position: absolute; top: 202px" runat="server" Width="376px" Height="20px" BackColor="8454016" ForeColor="black" AutoPostBack="true" DataSourceID="AccessDataSource3" DataTextField="FilterEthCode" OnSelectedIndexChanged="cmbcboFiltEthCode_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose an Ethnologue Code --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT EthnologueCode & EthnologueCodesOld & LanguageName & LanguageID AS FilterEthCode FROM Languages WHERE (LanguageName <> '' AND LanguageName IS NOT NULL AND LanguageNameAlt <> '' AND LanguageNameAlt IS NOT NULL AND EthnologueCode <> '' AND EthnologueCode IS NOT NULL AND EthnologueCodesOld <> '' AND EthnologueCodesOld IS NOT NULL) ORDER BY EthnologueCode"> </asp:AccessDataSource> The only lines that are different is the SelectCommand's. But they are both the same as version 1 and 2 in the third version. How is this possible? Scott Tag: SqlCommandBuilder and optimistic concurrency Tag: 139524
  - 7
    - "DataSet.Merge()" simple problem Hi there, Can anyone explain the following (very) simple scenario. 1) I make an exact copy of my "DataSet" and delete one record from a given table (in the copy) 2) I invoke "DataSet.GetChanges()" on the above copy and pass the results to "DataSet.Merge()" on the original copy 3) If I now inspect the original copy, it shows that the record has *not* been deleted from the table. However, a call to "GetChanges()" does show my deleted record. Why doesn't the record get deleted from the original table after the merge in step 2? Note that I've inspected my original "DataSet" after step 2 as well as the "DataSet" returned by "DataSet.GetChanges()". Apparently "DataSet.GetChanges()" returns a "DataSet" that's completely empty of data except for the one affected table which contains the single deleted record. That record is then simply added to the table in the original "DataSet" when "DataSet.Merge()" is called. The original table therefore winds up with two copies of the record, the origiinal and the deleted. Does anyone know what's going on here. Thanks in advance. Tag: SqlCommandBuilder and optimistic concurrency Tag: 139517
  - 8
    - Getting next autoincremented id from typed DataTable What's the alternative to the following? void m_BindingSource_AddingNew(object sender, AddingNewEventArgs e) { MyTypedDataSet.TypedTableRow newRow = this.m_DataTable.NewTypedTableRow(); this.m_DataTable = new MyTypedDataSet.VendorsDataTable(); DataRow[] existingRows = this.m_DataTable.Select("ID < 0", "ID ASC"); if (existingRows.Length > 0) { object lowestRowID = existingRows[0]["ID"]; newRow.ID = ((int)lowestRowID) - 1; } else { newRow.ID = -1; } e.NewObject = newRow; } Tag: SqlCommandBuilder and optimistic concurrency Tag: 139500
  - 9
    - DB Design Got a puzzler for anyone wanting to take on a challenge. I have some data, which represents something like an insurance policy. The customer may call up and change the level of coverage at any time. These changes may or may not produce a change in premium but may produce a change in legal obligation for my company. In the event that a customer places a claim on their policy, my company must first verify that there was coverage on the policy for the date of the claim. What this boils down to is that I need to intellegently preserve transactional state of the policy each time a change is made. I am looking for an effiecient way to store this data in the database and relate to it in my business logic. Currently what I am doing is creating a parent table for each of my entities that act as a place holder for each entity and then I create a details table to house the data points of interest. I relate the data via a parent transaction table and a child transaction details table. A record is created in the transaction detail table for each row in a detail table that is inserted. Given the insurance example: Policy PK PolicyID Policy Detail FK PolicyID FK TransactionDetailID Driver PK DriverID FK PolicyID Driver Detail FK DriverID FK TransactionDetailID Vehicle PK VehicleID FK PolicyID Vehicle Detail FK VehicleID FK TransactionDetailID Transaction PK TransactionID FK PolicyID Transaction Detail FK TransactionID In this model there are three types of allowed changes: -- A correction, which allows updates and are not transactional (highly critical of data allowed to be modified.) -- A transactional change in policy coverage, (clone business objects, change desired data points, insert new row) -- A reversal, which allows a transactional change to be reversed. Reversals throw out the biggest challenge, because the transaction it affected is no longer valid, so the one before it becomes the current transaction again. Reversals have the affect of never happened, and will be ignored when checking for coverage. Reverals will display in the transaction log (for Q/A purposes). The only time a reversal is not authorized is: --The original issuance (That requires a cancellation of the policy) While the underwriter can only create a change against the most relevant transaction, reversals place an earlier transaction in that billet, then once a change is made, it too can be reversed again making a prior transaction most relevant. Some of the issues that I am having with this implementation are: -- querying against the tables can be laborious with multiple joins, code table lookups and so forth. -- determining the most recent version of the policy is often convoluted for an active policy. --Transversing the transactional tree for a policy can get confusing. Not all relevant data for a given entity was modified during the target transaction. --Batch processing is complex when using native SQL, and slow when using the business API. Granted batch processing occurs at night, but computers need sleep too. (Currently, activity reports are taking 1.5 hrs to process via business API's and this is still a prototype system). Does anyone have an alternate solution for storing this data? I have been idlely browsing the web for possible solutions, I don't even know how to phrase the question. "Versioned Data", "Transactional Data", Transactional Auditing. (Append methodology, theory, concept). This concept cannot be unique to me alone. My counterpart mainframe group "stacks" the transactional history and deletes the data when a reversal is requested, this is a manual process for them. Our requirment was to preserve this historical change. Thanks in advance Tag: SqlCommandBuilder and optimistic concurrency Tag: 139498
  - 10
    - set a nullable property in object I have a business object (class) with some nullable properties declared like this: Private _MyProp as Nullable(of Int16) public Property MyProp as Nullable(Of Int16) get Return _MyProp end get set(ByVal Value as Nullable(Of Int16)) If not _MyProp.Equals(value) then _MyProp = Value NotifyPropertyChanged("MyProp") ' marks the property changed end if end set I can read from my database with no problem for both MyProp's with or without a value. When MyProp = 1 and I try to set it to Null in a Windows TextBox, the form will not even let me move to another control. If I change rows in a datagridview, the value is set back to 0. So, I added an OnValidate event like this: if String.IsNullOrEmpty(myControl.Text) then _ myBusinessObject.MyProperty = Nothing Now I can set the nullable property to "Nothing" and it works as expected. My question is - Is this the normal procedure to set a nullable value or am I missing some shortcut where I don't have to add the OnValidate event? Rick Tag: SqlCommandBuilder and optimistic concurrency Tag: 139492
  - 11
    - DSN Connection for Microsoft Access in ASP.NET Hi, I want to use a DSN connection to connect to MS Access Database. The ODBC DSN has been configured as MIT. I use the following connection string to connect (using VB.NET): Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; DSN=MIT") This throws up an error: Could not find installable ISAM [OleDbException (0x80004005): Could not find installable ISAM.] System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) +20 System.Data.OleDb.OleDbConnection.InitializeProvider() +57 System.Data.OleDb.OleDbConnection.Open() +203 Can some one please let me know how to make a DSN connection to Microsoft Access 2000 database in ASP.NET 1.1? Thanks in advance, Sandeep Gupta Tag: SqlCommandBuilder and optimistic concurrency Tag: 139489
  - 12
    - ASP.Net, Repeater Control - 3 Columns - Unlimited Rows Hello, I wanna list my stocks in a web page according to search result, this listing must be in a (3 columns and unlimited size) table, what I wanna list in a cell is stock name, stock price and stock picture I tried repeater control but it list data only row by row I think, I couldnt make first 3 datarow in first row, and second three row in second row, Data comes from a table, and paging is a must, Something like this >>> ------------------------------------------------------------------------------ Stocks ------------------------------------------------------------------------------ Stock 1 Stock 2 Stock 3 10,56 USD 20 USD 30,11 USD Picture Picture Picture Stock 4 Stock 5 Stock 6 10,56 USD 20 USD 30,11 USD Picture Picture Picture Stock 7 Stock 8 Stock 9 10,56 USD 20 USD 30,11 USD Picture Picture Picture ------------------------------------------------------------------------------ 1 - 2 - 3 - 4 - 5 - 6 - 7 - Paging - Next.... ------------------------------------------------------------------------------ How can I do this ? Repeater o anoher way ? Thank you Tag: SqlCommandBuilder and optimistic concurrency Tag: 139488
  - 13
    - Read (and Visualize) DB Schema from SQL Server Hi experts, which approaches would you suggest for: - Reading a database schema (tables, fields, relationships) from SQL Server 2005? - Visualizing the DB schema? For developing a DB tool the schema should be read from the server and then be visualized, e.g. like the SQL Server BI Development Studio does when importing the whole schema to a data source view. Any ideas and hints on how to start approaching this (either reading schema and/or visualizing it) using .NET (preferably C#) are welcome! Regards, Max Tag: SqlCommandBuilder and optimistic concurrency Tag: 139484
  - 14
    - create a data provider I would like to create my own data provider in .NET which is able to deliver data to all apps supporting oledb or odbc. Information about this is very rare in the Web. Are there any code samples around or books from which I could learn how to do this? Important, again, is the part which makes the provider visible to Excel etc. - Klemens Tag: SqlCommandBuilder and optimistic concurrency Tag: 139475
  - 15
    - Once more - Pessimistic Locking ADO.NET 2.0 SQL Server 2005 Hello ! I read any articles about locking so far, but I am not able to make a solution for my project. (I really want pessimistic locking - no such optimistic/pessimistic discussion please) This is my idea: (I made some experiencies with a new field "locked" in the orders-table - read committed - this worked, but now I want an other solution): I created a separate locking table. If anyone opens a datarow e.g. in the table orders in editmode, a locking datarow is inserted in the locking table, when storing or canceling, the row is deleted. This is my code: Protected m_objSQLConnection As SqlConnection Protected m_objSQLCommand As SqlCommand Protected m_objSQLTransaction As SqlTransaction Try m_objSQLConnection.Open() m_objSQLConnection.BeginTransaction 'Isolationlevel necessary ???? m_objSQLCommand.Transaction = Me.m_objSQLTransaction Dim strSQL As String strSQL = "SELECT * FROM tblSperrungen " '"WITH (HOLDLOCK, ROWLOCK etc necessary ???) " strSQL &= " WHERE (tablename = @tablename) AND (lineID = @lineID)" m_objSQLCommand.CommandText = strSQL m_objSQLCommand.Parameters.Clear() m_objSQLCommand.Parameters.AddWithValue("@tablename", ...) m_objSQLCommand.Parameters.AddWithValue("@lineID", ...) Dim objRd As SqlDataReader objRd = m_objSQLCommand.ExecuteReader If objRd.Read Then 'Sorry row is locked Else blnErg = True End If objRd.Close() System.Threading.Thread.Sleep(intPause) ' only for testing others try to lock and have success - why ?? If blnErg Then strSQL = "INSERT INTO tblSperrungen(tablename,lineID,GesperrtVon,GesperrtAm) VALUES (@tablename,@lineID,@GesperrtVon,@GesperrtAm)" m_objSQLCommand.CommandText = strSQL m_objSQLCommand.Parameters.Clear() m_objSQLCommand.Parameters.AddWithValue("@tablename", ...) m_objSQLCommand.Parameters.AddWithValue("@lineID", ...) m_objSQLCommand.Parameters.AddWithValue("@GesperrtVon", ...) m_objSQLCommand.Parameters.AddWithValue("@GesperrtAm", ...) m_objSQLCommand.ExecuteNonQuery() 'Locking OK End If TransCommit() CnClose() Catch objE As Exception TransRollback() cWindows.FehlerWin("Fehler bei Sperren-Funktion " & objE.Message, True) End Try My problem now is, that I have to guarantee that between the 2 SQL- Statements (SELECT and UPDATE) no other user can select the row. How can I prevent this row from reading. I played with some isolation levels and sql hints, but with no success. (stored procedures are not allowed in my project) Many Thanks aaapaul Tag: SqlCommandBuilder and optimistic concurrency Tag: 139473
  - 16
    - MS Access DB, XP Pro and IIS 5.1... I am using MS Visual Web Developer 2005 Express Edition, MS Access DB, XP Pro and IIS 5.1 on my local XP Pro. This is a Newbie question with a very simple answer (I think). When I run the page it loads up just fine (with all of DB fields in the dropdown boxes, etc.). The problem is when I go to select an item from any of the dropdown boxes the IE says, "Internet Explorer cannot display the webpage" and FireFox says, "The connection to the server was reset while the page was loading." I believe the error happens right after OnSelectedIndexChanged and before Protected Sub cmbRecordLookup_SelectedIndexChanged(...) because the program isn't getting to the breakpoint I put at the beginning of the Sub. I have programmed four of the pages and three of them work. Strange... Here is a part of my code: <asp:DropDownList ID="cmbRecordLookup" Style="z-index: 201; left: 200px; position: absolute; top: 203px" runat="server" Width="396px" Height="20px" BackColor="Yellow" Font-Names="Doulos SIL" ForeColor="0" DataSourceID="AccessDataSource1" DataTextField="forBookID" OnSelectedIndexChanged="cmbRecordLookup_SelectedIndexChanged" AutoPostBack="true" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose a Book ID --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT Books.BookID & Books.TitleNormalized & Languages.LanguageName & Languages.EthnologueCode AS forBookID FROM Books INNER JOIN Languages ON Languages.LanguageID=Books.LanguageID ORDER BY Books.BookID"> </asp:AccessDataSource> What is happening and how should I do this? I would appreciate any help. TIA Scott Tag: SqlCommandBuilder and optimistic concurrency Tag: 139472
  - 17
    - DataView filter bug? Simple problem. If the company is null, replace it with last name and first name: /* Replace null companies with last and first names */ DataView dv = new DataView((DataTable)this.Invoice, "Company = ''", "InvoiceID", DataViewRowState.CurrentRows); foreach (DataRowView drv in dv) { drv.Row["Company"] = drv.Row["LastName"] + ", " + drv["FirstName"]; } this.AcceptChanges(); Three rows were found in the DataView. While three replacements were made, 1 row received two replacements and 1 row wasn't changed at all. Is this a bug in ADO.NET or can you not change column values included in the filter while iterating through the rows? The following code worked: /* Replace null companies with last and first names */ DataView dv = new DataView((DataTable)this.Invoice, "Company = ''", "InvoiceID", DataViewRowState.CurrentRows); if (dv.Count > 0) { List<int> list = new List<int>(); foreach (DataRowView drv in dv) { list.Add((int)drv.Row["InvoiceID"]); } for (int i = 0; i < list.Count; i++) { InvoiceRow row = this.Invoice.FindByInvoiceID(list[i]); row.Company = row.LastName + ", " + row.FirstName; } } this.AcceptChanges(); Tag: SqlCommandBuilder and optimistic concurrency Tag: 139471
  - 18
    - Typed DataSets in MS Access with Stored Procedures I am using VS 2005 and a MS Access 2003 DB. In VS I use the server explorer to drag and drop tables and existing queries into the dataset designer window. I am having several problems. 1. In the Server Explorer tables are under the Tables folder but queries are under the Views folder or if they have parameters they will be under the Functions folder but never under the Stored Procedures folder. How does the Server explorer classify MS Access queries? 2. For queries (i.e. "Views" or "Functions") in the dataset designer whenever I use the "TableAdapter Configuration WIzard" Stored Procedures is grayed out and only the "Use SQL option is available". How can I make those other options available? 3. The main problem is queries which already exist in my database. I cannot seem to fill the DataTableAdapter with any data. There are no errors but no data either. I tried the same query inside MS Access and it does return data. The datatable adapter for my tables works fine. I did use the dataset properties window for the query to change the command type to "stored procedure" but it still cannot retrieve any data. Why? By the way if I manually code a non-typed dataset for a "stored procedure" it works fine. -- JT Tag: SqlCommandBuilder and optimistic concurrency Tag: 139469
  - 19
    - Problem with Expression in DataColumn Hi, I get errors when I try the following expression in my strongly typed dataset: "Sum(Parent.Child(Relation1).Value)" The error I get is: "Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier." What I want to do is to navigate from the current row to its parent, and then use one of the parents child relations to do a sum. It seems like navigating more than one step is not supported. Can this be true? regards Carl Tag: SqlCommandBuilder and optimistic concurrency Tag: 139461
  - 20
    - How does GetSchema get foreign key constraints? The GetSchema documentation hurts, specifically: "Understanding the Provider-Specific Schema Collections" at http://msdn2.microsoft.com/en-us/library/ms254969(VS.80).aspx I'm trying to create a DataSet that matches an SQL Server database, and I've gotten close. I'm calling SqlConnection's GetSchema( "Tables"), and then for each table name, I'm doing: sqlDataAdapter = new SqlDataAdapter( "SELECT * FROM " + dataTableName, sqlConnection); sqlDataAdapter.FillSchema( dataSet, SchemaType.Mapped, dataTableName); Doing so initializes the data tables of the data set, including primary keys. What remains to be done is to create the foreign key constraints. Calling GetSchema( "ForeignKeys") returns the following columns: CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE IS_DEFERRABLE INITIALLY_DEFERRED What I expected to see was a local table name, the local field of the foreign key, the referenced table name, and the primary key field of the referenced table. Where is that? Thanks in advance. Tag: SqlCommandBuilder and optimistic concurrency Tag: 139457
  - 21
    - XSD generated by the xsd tool vs The Designer Hi I created an xsd file by assign a sample xml file to the xsd.exe tool I opened the xsd file in the vs 2005(designer) and saw that the data entities were represented as an element (E sign on the top, left of each data entity) When I create data entities by my self they represented as datatables 1) Why can't I get the XSD by tool in the same way 2) I thought to workaround that problem by create the diagram from the tool based xsd to the manual created digram, but it's not possible(probably because one is elemnt and the other datatable). Is there any other workaround? 3)If I create the diagram by myself, can I be assure tthat xml serialization will give me same structure? Tag: SqlCommandBuilder and optimistic concurrency Tag: 139456
  - 22
    - TransactionScope Problem Enterprise Library 2.0 We have a web application that calls a business/data layer to do database updates. There is a lot of coordination in the web page to handle id's updated from one class to be used as child id's in other classes. Here's the scenario. Using EnterpriseLibrary 2.0. Surround all class updates with TransactionScope. Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - works correctly Commit Transaction Scope - works correctly. If there are never any problems with the inner update, it works as planned, the problem occurs when there is a problem with the update on ClassB. Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - problem with other data (invalid cast, etc) Dispose Transaction Scope - works correctly. In this case, it works as planned, neither record is saved to db, but when we bring back the page to the user, and they correct the data, on this second attempt is when the problem occurs. Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - problem with other data (invalid cast, etc) Dispose Transaction Scope - works correctly. Display error to user, and they correct bad data Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - Get a FOREIGN KEY constraint violation 'The INSERT statement conflicted with the FOREIGN KEY constraint ...' I'm not sure why the inner update fails the second time because the id generated from the first update is not recognized, like they are in different scopes. fyi, I am using a SCOPE_IDENTITY() to grab the newly inserted id's Tag: SqlCommandBuilder and optimistic concurrency Tag: 139454
  - 23
    - VersionNotFoundException Hello, I have a DataGridView, bound to a typed DataTable (without any TableAdapter etc.). The values of that DataTable are changed in a second thread using the following code: DataSetOrderManager.DbOrderManagerWlanRow row = dtWlan.FindByHardwareID(HardwareID); if (row != null) row.LastVitalSign = DateTime.Now; row.LastIP = FromIpAddress.ToString(); row.LastAccessPoint = AccessPointMac; } Mostly, this is working fine, but sometimes I get an DataGridView.DataError with the VersionNotFoundException. I think, this happans, because the row(s) are changed in a different thread!?? I don't want to "Invoke" to the main thread, because the row(s) will be changed very often, at worst, maybe 100 times per second! My first attempt was just catching the DataError Event and ignoring the error, but then, sometimes I saw wrong values in the datagrid!! So I added a InvalidateCell in the DataError Event. Well, it seems to work, but I think, avoiding that error is better! Now the questions are: 1. Is it OK what I do in the DataError Event, or may this produce problems? 2. The better way: How to avoid the VersionNotFoundException? Thanks in advance, Robert Witter Tag: SqlCommandBuilder and optimistic concurrency Tag: 139453
  - 24
    - Need help with Cinema System I have a form called frmSales which has three datagridviews on it which are linked to a Cinema.mdb database using SQL. The first datagridview (Film) displays the FilmNumber(Unique Key) and FullTitle of the Film, the second datagridview (Date) displays the days the film is on for and the third datagridview (Session) displays SessionNo(Unique Key), Time, Cinema and SeatNumbers. I have two buttons on this form called Sales and the other called Return. What I need help with is the code for the buttons so that when the user has selected the three datagridviews and depending on whether Cinema 1 or Cinema 2 was selected in datagridview Session then when either the Sales or Return button is pressed they will be taken to the corresponding Cinema form (In this case either frmCinema1 or frmCinema2). Any help would be appreciated. Cheers, Kryt0n1t3 Tag: SqlCommandBuilder and optimistic concurrency Tag: 139449
  - 25
    - Need help with Cinema System Hi I am having trouble with a Cinema System I am creating in VB.NET. I have a form called frmSales which has three datagridviews on it which are linked to a Cinema.mdb database using SQL. The first datagridview (Film) displays the FilmNumber(Unique Key) and FullTitle of the Film, the second datagridview (Date) displays the days the film is on for and the third datagridview (Session) displays SessionNo(Unique Key), Time, Cinema and SeatNumbers. I have two buttons on this form called Sales and the other called Return. What I need help with is the code for the buttons so that when the user has selected the three datagridviews and depending on whether Cinema 1 or Cinema 2 was selected in datagridview Session then when either the Sales or Return button is pressed they will be taken to the corresponding Cinema form (In this case either frmCinema1 or frmCinema2). Any help would be appreciated. Cheers, Kryt0n1t3 Tag: SqlCommandBuilder and optimistic concurrency Tag: 139448
- Next
  - 1
    - Problem with Oracle using ADO.NET Hi, I have a problem to call a stored function with a C# application. I have tested the function directly on Oracle Server and the function is well compiled and it runs on SQL*PLUS. I think the problem is due on the RETURN parmeter, because if I create a procedure without a return valule the procedure can be called by my C# application. So the Oracle Function is returning an INT (Oracle). To call it on the console (it runs), I'm using the following code : --------------------------------------------------------------- DECLARE STATUS INT; BEGIN STAUS := MYFUNCTION(); END; ---------------------------------------------------------------- The code inserted in the C# application is the following: ----------------------------------------------------------------- OracleParameter oF = new OracleParameter(); ... oF = cmd.Parameters.Add( "RETOUR", OracleDbType.Int32, ParameterDirection.ReturnValue); cmd.CommandText = procName; cmd.CommandType = CommandType.StoredProcedure; Connection.Open(); cmd.ExecuteNonQuery(); Console.WriteLine(oF.Value); Connection.Close(); ... ----------------------------------------------------------------- When "cmd.ExecuteNonQuery()" is called I obtained the message: ORA-06502: PL/SQL : numeric error or error on a value : conversion error from characters to numbers. If I don't declare the "oF" parameter, I obtain: PLS-00221: 'MYFUNCTION' isn't a procedure or is undefined. If someone had the same problem, thanks to give me more information. Tag: SqlCommandBuilder and optimistic concurrency Tag: 139445
  - 2
    - Get extern changes back into dataset without completely refill Hello, I have a (typed) dataset for some tables in a sql-server express. Well, loading the datas with Fill and sending changes to the sql server with Update works fine. What I like to do is, sending the changes in the sql server (done by an other instance of my application on a different pc) back into the dataset WITHOUT having to fill the whole dataset again with the Fill-command. My sollution at the time is, that every row has a timestamp value and I ceep track of the highest timestamp value of any table. So I can fill with "WHERE timestamp > oldtimestamp". That works good, but what about deleted rows? Saving them in a own table (cause my own application delets them, I can do this), read that table and remove the deleted rows from dataset... Well, since I'm shure, I'm not the only one wanting to do this, I hope, there is a better, easier, elegant way doing this?? Hoping for help and new ideas, Robert Witter Tag: SqlCommandBuilder and optimistic concurrency Tag: 139443
  - 3
    - object-relational mapping Hi, We're looking for recommendations for an object-relational mapping product that will work with Microsoft's Data Access Layer and is either free or very reasonable. We have a very complex database and would need a robust product able to map complex objects. We are currently using a custom framework that turns our object "reads" into hundreds of "select" statements. We'd prefer to use a stored procedure approach which is driving our decision to "shop". We've done a tiny bit of research and plan to check out nHibernate and .NetTiers first. We would love feedback from anyone who has used either of these. We're just beginning the search and will be downloading trials/demos, so please save us some time if we should stay away from any really bad ones! thanks, Deanna Tag: SqlCommandBuilder and optimistic concurrency Tag: 139440
  - 4
    - Renaming files in SSIS Hi, I need to transfer data from a .dat file to different tables in SQL DB. After the transfer i need to rename the .dat file appending current date. I have been successfull in transferfing data. I have problems renaming the file. Can anybody help me with this? Regards, Rocksie *** Sent via Developersdex http://www.developersdex.com *** Tag: SqlCommandBuilder and optimistic concurrency Tag: 139436
  - 5
    - Help, Oledb connection problem Hi,all I met a strange problem, I connected to access database using oledb connection, connection string like "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\Server\Public\QAD\MDB\QADData.mdb;User ID='apiprogram';Password='35msaccessAPI'; Jet OLEDB:Database Password='1';Jet OLEDB:System database=\\Server\Public\QAD\MDB\TFSC_BJ.mdw; Mode=1" In access database, there exist tables linked to antother data server throught ODBC. When exeucting program, I found if there is less records,for example less than 1000, the data read without problem, but if there was large scale records count, the program prompt "odbc error". Why? if I should increase the ODBC Command Time Out or Max Locks Per File properties liking in ADO connection. But in ADO.Net, it does not support this manner, so what shall I do? Tag: SqlCommandBuilder and optimistic concurrency Tag: 139435
  - 6
    - datareader.getstring(0) doesn't return the whole string. Hi, I am using an Access DB and a datareader to retrieve a memo field. When I execute the following code the datareader only ever returns the first 255 characters, even though I have used a string builder (and a regular string) which I believe can hold more than 255 characters. Is there an issue with the datareader or the memo field of an access database. If I use a dataset and an OleDBDataAdapter then all of the characters (i.e. > 255) are read in and can be viewed in a datagrid. Many thanks, Jason. Dim cmd As New OleDbCommand("SELECT tblBOMS.bBOM, Count(tblBOMS.bBOM) AS CountOfbBOM FROM tblBOMS GROUP BY tblBOMS.bBOM HAVING (((Count(tblBOMS.bBOM))>=2)) ORDER BY Count(tblBOMS.bBOM) DESC;", cn4) Dim f As New FileInfo("C:\Duplicate Boms.txt") Dim s As StreamWriter = f.CreateText() If cn4.State = ConnectionState.Closed Then cn4.Open() End If Dim dr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.Default) While dr.Read Dim str As New StringBuilder(dr.GetString(0)) s.WriteLine("=============================================================") s.WriteLine(str) s.WriteLine("=============================================================") ' Find all catalogue number with this BOM Dim dv As New DataView dv.Table = ds2.Tables("BOMS") dv.RowFilter = "bBOM like '" & str.ToString() & "'" Dim drv As DataRowView For Each drv In dv Dim strA As String = drv("bName") s.WriteLine(strA) Next End While s.Close() s.Dispose() Tag: SqlCommandBuilder and optimistic concurrency Tag: 139434
  - 7
    - Prepare yourself for ASP.Net Certification Excellent articles/material for web application (ASP.Net). Complete reference available all in one place. You should visit following URL for vast information about web application. http://www.top-itarticles.com/mcsd/mcsdweb.asp Tag: SqlCommandBuilder and optimistic concurrency Tag: 139431
  - 8
    - partial merge? Hi, I'm using DataTable.Merge() to merge values from a source DT into a destination DT which are in seperate Datasets. The source DT has columns A,B,C and the destination DT has columns A,B,D. column A is primary key for both of them. So, basically, my target is to copy all the values from column B in the source DT to column B in the destination DT. So far so good (Merge with MissingSchemaAction.Ignore does the job). However, The source DT has 1000 rows and the destination DT has 50 rows. The Merge action copy all the 1000 rows into the destination DT, where I'm only interested in updating the existing 50 rows in the destination DT. (Just update, no Insert). From what I've read, if merge doesn't find a corresponding PK it adds the new rows. Is there any way to go around this with merge? any other way, without using merge? (I tried to save looping over all the rows in one DT and locating them in the other one) Thanks in advance Eran Tag: SqlCommandBuilder and optimistic concurrency Tag: 139428
  - 9
    - Changing Database Name and Owner (but not Table Name)in a TypedDat I have a TypedDataSet whose source tables live on a SQL Server. The DataTables in the TypedDataSet have lots of customized TableAdapters to execute special SELECTS. Took a lot of work. I have to change the Database name and Owner (schema actually on SQL 2005) but not the name of the tables on the SQL server. This wreaks havoc as you can imagine with the DataSource Wizard. If I add the tables anew to the TypedDataSet, I loose all the modifications to the original DataTables. I want to just modify the definitions of the existing DataTables in the TypedDataSet to reflect the changes in the underlying SQL database. It's not as simple as changing the Connection properties! It seems that the only way to change the Database Name and Owner is to Find/Replace within the .xsd file itself. A bit scary. Is there a better way? Is it safe just to make the suggested replacement? -- Michael Tag: SqlCommandBuilder and optimistic concurrency Tag: 139425
  - 10
    - OleDb and x64 Hi, Since there *appears* to be no 64-bit support for any data source other than SQL Server (and I wouldn't use OleDb to interface with SQL Server anyway as there's a native .NET data provider for that), what is the "accepted wisdom" for porting 32-bit apps to 64-bit when they require OleDb support...? Can it simply not be done...? E.g. let's say I have an existing 32-bit WinForms app written in VS.NET 2005 (C#) and my client has now asked me to create a 64-bit version, but the existing 32-bit app uses Jet as its back-end database... What are my options...? Maybe the WinForms app is a data consolidation tool which receives remote data feeds in the form of emails with attached Jet databases or Excel spreadsheets...? Jet and Excel remain hugely popular file formats in the corporate world, and that seems unlikely to change in the near future, especially Excel... Do I have to tell my client that, regrettably, it's just not possible to create a 64-bit version of any app which needs to interface with Jet or Excel...? Any assistance gratefully received. Mark -- http://www.markrae.net Tag: SqlCommandBuilder and optimistic concurrency Tag: 139424
  - 11
    - CFront 3.01 Do somebody know how to connect to CFront 3.01 from a Windows Mobile, Using Visual Basic .net 2003 ? or a tool data acces to Cfront? Tag: SqlCommandBuilder and optimistic concurrency Tag: 139422
  - 12
    - Query in memory dataset c# 2 Is there a way to query an in-memory dataset with sql or can you only work with the individual table through DataTable.Select()? How can you go about getting the identity of a new record if you use an autoincrement primary key on the datatable? Thanks Tag: SqlCommandBuilder and optimistic concurrency Tag: 139418
  - 13
    - How to populate an empty table in DB? Hi, I am wondering how I could populate the ampty table in the database from the DataSet or DataTable. So far I am using SqlBulkCopy. Is there any other way, or is it I should be using? I could make Insert row by row, but it is slow, I guess. Thanks, Lubomir Tag: SqlCommandBuilder and optimistic concurrency Tag: 139414
  - 14
    - SQLCLR Hi, I have a stored procedure, for example like the following: Public static void MyProc() { using ( SqlConnection conn = new SqlConnection(â??Context connection = trueâ??)) { String mySql = â??â?¦..â?? SqlCommand cmd = new SqlCommand (mySql, conn); conn.Open(); cmd.ExecuteNonQuery (); } } I can call this procedure from the application : using ( SqlConnection conn = new SqlConnection(MyConnString)) { SqlCommand cmd = â?¦ cmd.CommandType = CommandType.Storedprocedure; cmd.CommandText = â??MyProcâ??; conn.Open(); cmd.ExecuteNonQuery(); } This code is pretty redundant with the code in the stored procedure. Is there any other way how I could call the store procedure? Thanks, Lubomir Tag: SqlCommandBuilder and optimistic concurrency Tag: 139412
  - 15
    - SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange Hi everybody, In last month I'm experiencing a very strange problem and I think it's linked to ado.net. My team and I developed a lot of WebApplications and our environment is on .NET 2.0 and Sql Server 2000. (All Service packs have been installed) Most of time (99%) everything is running well, but sometimes (when exactly, we don't know) the same Web Applications (that worked 100000 times) fail. It seems sp doesn't return right fields or something similar. This happens occasionally and, when it occurs, a lot of components have the same problem. In Event Viewer warning messages appear about our libraries like: System.IndexOutOfRangeException: Site_Uid at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at USNext.CommonUserControls.ContactInfo.GetLoginSites(SqlConnection sqlCnn) DataBinding: 'System.Data.Common.DbDataRecord' does not contain a property with the name 'name'. I'm pretty sure that the code and the stored procedure are fine also because they are old and executed a lot of times (more than 10000) every day. We started to have this type of problem a couple of months ago, initially sometime then every day around same hour (during the morning, when the server load is high). One month ago I increased the Max Pool Size setting of connection string to 500 and now it happens one time every 7-10 days. When it occurs I check the connections opened on SQL and they are not a lot. The only solutions we found: - Wait a few minutes then everything return to work fine - Restart IIS This post has been opened in Nov 2005 on Microsoft MSDN forums http://forums.microsoft.com/msdn/showpost.aspx?postid=137373&SiteID=1 and it's still open. Does someone know how to solve this problem? Does someone experiencing same problem? Thanks, Emanuele Tag: SqlCommandBuilder and optimistic concurrency Tag: 139409
  - 16
    - Help, Connection to Access Database Hi,all I met one problem of connecting Access database in asp.net web program. I use ODBC link to Access, the connection string like "PWD=12345;DSN=QADLinkMDB;DriverId=25;MaxBufferSize=2048;FIL=MS Access;PageTimeout=5;UID=apiprogram" while runing program, system prompt error: the workgoup file is missing or open exculsive by anthor user. My access database located in another server,if I copy the Access database to my local place, the program run without problem, but now always display above error. Then what's the problem? Anyone could give me a hand? Thanks! Tag: SqlCommandBuilder and optimistic concurrency Tag: 139406
  - 17
    - TableAdapter not updating table if the table is empty when loaded? I am having problems with the table adapter not updating (inserting a new record) the sql server table if I open the form with out any records already in the sql server table. If I add on record in sql server management studio, the the table adapter will work correctly, but there has to be at least on record. What is going on?? There are not any exceptions being thrown. I checked the table adapter commands (delete, insert, select, update) and they match the other table adapter that are working correctly. (just different fields and parameters) What else can I check for? Thanks Tag: SqlCommandBuilder and optimistic concurrency Tag: 139404
  - 18
    - Ole DB Jet 4.0 Provider corrupts recipient Excel database predicta VS2005 C# 2.0 I'm using ADO.NET and the Jet 4.0 OLE DB provider to append a row to an Excel spreadsheet. I have a data table of type System.Data.DataTable, and a table adapter of type System.Data.OleDb.OleDbDataAdapter to fill the data table. These are generated by the VS2005 dataset designer. My connection string, created using the Data Links wizard is Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\FootAnalNovel.xls; Extended Properties="Excel 8.0;HDR=Yes";Persist Security Info=False 1. faAdapter.Fill() to fill the table from the worksheet 2. Create a new empty row for the table. 3. Set values for elements of the row. 4. Add the row to the table. 5. Call faAdapter.Update() to send results back to the spreadsheet In the target spreadsheet I observe: 1. The row was added. The first column was autoincremented as desired. 2. All numeric columns in the sheet, including dates, are correct. 3. The header line has not been modified - that is correct. 4. All text columns in the table have been corrupted. But, in a very particular way: they are filled in row by row by text that was originally in the sheet, in the order that it appeared originally in the sheet. Both originally empty cells and filled cells are overwritten. Thanks in advance for your help. Jim Tag: SqlCommandBuilder and optimistic concurrency Tag: 139402
  - 19
    - How do I make lots of changes to a database table using a dataset Hi, I'd like to take a table in a database and modify some rows and delete some others. So I created a dataset, and tried to make modifications to one of its tables, and then I tried to send it back to the database. But I got an error message: "update requires a valid UpdateCommand when passed datarow collection with Modified rows." Here is my code fragment. What's missing? I looked at the Microsoft MSDN pages, but they often seem to explain one method at a time, without showing how to use the methods together to accomplish a task. -- HA Function PlayWithExperimentalTable() As Boolean Dim myOleDbDataAdapter As OleDbDataAdapter Dim myDataSet As DataSet = New DataSet Dim ChangesDataSet As DataSet = New DataSet Dim myDataTable As DataTable = New DataTable Dim myOleDbconnection As OleDbConnection Dim retval As Boolean Dim RowCount As Integer Dim Index As Integer Dim myDisplayRow As DataRow Dim strSQL As String Dim FirstName As String Dim LastName As String Dim Age As Integer strSQL = "Select * from ExperimentalTable" If ConnectToAddinDbase(constStockDatabase, myOleDbconnection) Then Try myOleDbDataAdapter = New OleDbDataAdapter(strSQL, myOleDbconnection) myOleDbDataAdapter.TableMappings.Add("Table", "ExperimentalTable") myOleDbDataAdapter.Fill(myDataSet) myDataTable = myDataSet.Tables("ExperimentalTable") RowCount = myDataTable.Rows.Count If RowCount = 0 Then ErrorBox("No Records found in experimentalTable") Else For Index = 0 To RowCount - 1 myDisplayRow = myDataTable.Rows(Index) FirstName = myDisplayRow("FirstName").ToString LastName = myDisplayRow("lastname").ToString Age = CInt(myDisplayRow("Age")) myDataTable.Rows(Index)("FirstName") = FirstName & "-" & Left(FirstName, 1) & "junior" If Index = 5 Then myDataTable.Rows(Index).Delete() End If Next If myDataSet.HasChanges(DataRowState.Modified Or DataRowState.Added Or DataRowState.Deleted) Then ' Use GetChanges to extract subset. ChangesDataSet = myDataSet.GetChanges(DataRowState.Deleted Or _ DataRowState.Modified Or DataRowState.Added) myOleDbDataAdapter.Update(ChangesDataSet) End If End If retval = True Catch ex As Exception InternalErrorBox("Error Reading From TS2IBSymbol. Internal Message: " & ex.Message & ". routine was 'PlayWithExperimentalTable', sql statement was " & strSQL) retval = False Finally myOleDbconnection.Close() myOleDbconnection = Nothing End Try Return (retval) Else Return (False) End If End Function Tag: SqlCommandBuilder and optimistic concurrency Tag: 139398
  - 20
    - Merge Replication SQL 2000 and SQL Server Compact I have a desktop application using SQL Server Compact Edition and I want to use merge replication with an SQL Server 2000 database (SP4). I have installed all the necessary things (sqlcesa30.dll is available via IIS and displays the message: "Microsoft SQL Server Compact Edition Server Agent" as expected, when I use a web browser from the desktop to navigate to it). I have configured publishing, without any issues arrising, the publisher and subscriber apparently being created without problem and the snapshot data is entered into the snapshot shared folder. My problem is that I cannot get my VB.NET application to synchronize, I catch the exception thrown by: SqlServerCe.SqlCeReplication.Synchronize(), but I am just getting a blank error message. Can anyone suggest what might be wrong? Thanks. Tag: SqlCommandBuilder and optimistic concurrency Tag: 139396
  - 21
    - SELECTing only the first n records from a database I want to select only the first n records from a database using VB.NET. I have declared a DataTable and OleDB.OleDbDataAdapter as follows: Dim linkstable As New DataTable Dim linksadapter As New OleDb.OleDbDataAdapter("SELECT * FROM worldnews ORDER BY updated", System.Configuration.ConfigurationManager.AppSettings("connectionstring")) I want to use the Fill method, and I thought the following overload looked like it might work: Fill(startRecord As Integer,maxRecords As Integer,ParamArray dataTables As DataTable()) The documentation describes the parameters as follows: Parameters startRecord A DataTable to fill with records and, if necessary, schema. maxRecords The maximum number of records to retrieve. dataTables One of the CommandBehavior values. The maxRecords parameter is obvious, and I assumed I would just use the DataTable I wanted to fill for the dataTables parameter (in my case, linkstable). However, I was confused by the startRecord parameter because it is an Integer, but the description says that it is a DataTable. What should I do to Fill the DataTable with only the first n records? I will also need a way to Fill the DataTable with all the records other than the first n records. Any ideas? Thanks. -- Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ Tag: SqlCommandBuilder and optimistic concurrency Tag: 139387
  - 22
    - Concurrency control in updating Access records? I'm using OleDbDataAdapter to update records to the Access database. Refer to the "Introduction to Data Concurrency in ADO.NET" topic in MSDN: "The Saving-All-Values Approach" section ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.VisualStudio.v80. en/dv_raddata/html/d5293098-4a88-4110-abd2-34d9e6661664.htm The Update command text is in the following format: UPDATE Customers SET CustomerID = @currCustomerID, CompanyName = @currCompanyName, ....... WHERE (CustomerID = @origCustomerID) AND ..... (CompanyName = @origCompanyName OR @origCompanyName IS NULL AND CompanyName IS NULL) ....... ; SELECT CustomerID, ...... FROM Customers WHERE (CustomerID = @currCustomerID) Regarding the named parameter @origCompanyName, there are two occurrences in the statement (similarly for all other columns that allow null). However, since OleDb use question marks (?) as parameter placeholders, adding parameters for the above equivalent of OleDbDataAdapter.UpdateCommand would logically cause the parameter mis-position problem (unless we are allowed to add the same parameter twice gracefully). It seems there's no way to correctly add the same parameters or is there any way to achieve that? Thanks for your help... -- Message posted via DotNetMonster.com http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200705/1 Tag: SqlCommandBuilder and optimistic concurrency Tag: 139386
  - 23
    - data-access approach Hi, Your opinion is welcome on choosing a DAL strategy for our .Net web project. We want a single unified DAL approach, but which one? Our new modules (40% of the code) use the DAL created by the XSD designer for calling SQLs and SPs. Our old modules (60% of the code) connect directly to the DB. The query-designer has good dev speed and automation but it has limits (?): 1) doesn't support returning multiple tables from a SP 2) doesn't supoprty (?) BL-level transactions (such as select, update and an update based on the last update's result). It seems that using the DAAB involves manual coding that takes more time and more bug-risk than the automatic code generated by the XSD query designer. Is it worth the time to replace the direct DB access in our old code with DAAB code? Do you know a good SQL code-generation tool? Can it create automatic runtime update SQLs for optimistic-lock checks of all field values? So what do YOU experts think? Which DAL approach and tools do you recommend based on your experience? Thanks for any input, Ofer Tag: SqlCommandBuilder and optimistic concurrency Tag: 139385
  - 24
    - SQL Server Stored Procedure Try Catch and ADO.Net exception handli Hi We have recently moved onto SQL 2005 and our DBA wants us to use Try Catch within our stored procedures. If I have an SP such as: CREATE PROCEDURE NoTry AS BEGIN INSERT INTO MyTable(Id, Description) VALUES (3, 'Toodles'); END and I have a primary key on the Id field and record 3 already exists. When I call it from ADO.Net it will result in a SQLException being thrown with the approrpiate Constraint Violation details shown. Now if I wrap it in a Try Catch block like CREATE PROCEDURE TryCatch AS BEGIN BEGIN TRY INSERT INTO MyTable(Id, Description) VALUES (3, 'Toodles'); END TRY BEGIN CATCH -- Log the error / rollback transaction etc. END CATCH END As you would expect the exception is now not raised to ADO.Net as it is handled by the SP. My question is if I need to re-raise the error how do I do it or alternatively how do I signal to ADO.Net that an error has occurred. My first thought was to use RAISEERROR in the CATCH block however this results in a 50000 error not the original error. Based on this my current approach is concatinating all the error information in the message string. Then have code in my .Net Catch block checking if it is a 50000 error and unpacking the exception. An alternative was to simply set the retun value to the error number. My final thought was to ignore TRY CATCH functionality in SQL totally however that seems extreme and self-defeating. Have I missed something and it is possible to Reraise a SQL error? Has anybody got any best practices on the best approach to take? Thanks in advance Phil Tag: SqlCommandBuilder and optimistic concurrency Tag: 139381
  - 25
    - ReadXML Dataset - Original Rows, RowState = Added (not Unchanged) I realize the original quesiton was dealt with but this appeared to be the most appropriate thread to post my question under. Here is my senario: 1) Connect to DB 2) Fill DataSet with tables, set Relationships, Constraints 3) Save DataSet to file 4) Send DataSet (and additional support files to client from Web Service) 5) Open Dataset (from file) in Client App My problem lies in the fact that once the DataSet is saved to file and then reloaded from the file all RowState values change to "Added" even though no rows have been modified/deleted/added. How can I keep the RowState from changing for the original DataRows that have not been modified by the ClientApp? Tag: SqlCommandBuilder and optimistic concurrency Tag: 139379

Hey ya,

Using .NET 2.0 and SqlCommandBuilder, say I have a customer row, with an
address and name fields. I need 2 different clients to be able to change
1 field each and not get ConcurencyExeption on 2nd Update(). Is it
possible to do?

Thanks

Top

Re: SqlCommandBuilder and optimistic concurrency by Miha

Miha
Mon May 21 05:51:03 CDT 2007

Why don't you craft your SQL statements manually instead of using builder?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"KShvats" <z@z.z> wrote in message
news:udQUmV5mHHA.3520@TK2MSFTNGP04.phx.gbl...
> Hey ya,
>
> Using .NET 2.0 and SqlCommandBuilder, say I have a customer row, with an
> address and name fields. I need 2 different clients to be able to change 1
> field each and not get ConcurencyExeption on 2nd Update(). Is it possible
> to do?
>
> Thanks

Top

Re: SqlCommandBuilder and optimistic concurrency by KShvats

KShvats
Mon May 21 07:16:46 CDT 2007

Becouse it saves a lot of time, I've got absolutely no performance
problems and a lot of things to do.

Miha Markic wrote:
> Why don't you craft your SQL statements manually instead of using builder?
>

Top

Re: SqlCommandBuilder and optimistic concurrency by KerryMoorman

KerryMoorman
Mon May 21 09:19:00 CDT 2007

KShvats,

You can use the SQLCommandBuilder's ConflictOption property to specify how
to deal with concurrency violations.

However, the choices may not be fine-grained enough for your needs. In that
case I think you will need to supply your own SQL statements for Insert,
Update and Delete.

Kerry Moorman

"KShvats" wrote:

> Becouse it saves a lot of time, I've got absolutely no performance
> problems and a lot of things to do.
>
> Miha Markic wrote:
> > Why don't you craft your SQL statements manually instead of using builder?
> >
>

Top