UpdateCommand doesn't work fine.

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
    - DataTable.Select method in .NET 2.0 Hi, I have a problem with DataTable.Select method in Microsoft .NET 2.0. I have a ListBox control, with DataSource pointing to a DataTable "RolePermissions". ListBox has SelectionMode property set to "Multiple". I would like to enable User to Delete Multiple items with just one click. I am almost sure, that this worked in .NET 1.1. I have a DataTable with column "permissionID" of type uniqueidentifier Here is my Code: // We will concatenate selected Guid's into one string StringBuilder sbItemsSelected = new StringBuilder(); // for every Item .... foreach (ListItem item in lbRolePermissions.Items) { // ... which is selected... if (item.Selected == true) { // ...we add selected ID, and space after it sbItemsSelected.Append("'"); sbItemsSelected.Append(item.Value); sbItemsSelected.Append("' "); } } /// We now have list of ID's, separated with spaces (with space at he end) string selectedItems = sbItemsSelected.ToString(); // remove last space selectedItems = selectedItems.Trim(); // insert commas instead of spaces selectedItems = selectedItems.Replace(" ", ","); // get the table, to handle data DataTable dtRolePermissions = Session["RolePermissions"] as DataTable; /// select rows, which are of interest to us ******************************************************************** DataRow[] drsRolePermissions = dtRolePermissions.Select("permissionID IN (" + selectedItems + ")"); ********************************************************************* /// delete them foreach (DataRow drRolePer in drsRolePermissions) { drRolePer.Delete(); } /// save new Table in Session variable Session["RolePermissions"] = dtRolePermissions; // bind the Table again lbRolePermissions.DataSource = dtRolePermissions; lbRolePermissions.DataBind(); The line inside "stars" is line, in which an exception occurs, the exception Message is: "Cannot perform '=' operation on System.Guid and System.String." I thought, that exception can occur because the Items in listBox are filtered with an expression, but that would be stupid of Microsoft not to let such operations. I am confused.... this really worked in .NET 1.1 I used it a lot in previous applications.... Is there some explanation to this fact ? ( apart from saying, that this is a bug ;-) ) Thank You in advance for help, maciu Tag: UpdateCommand doesn't work fine. Tag: 120024
  - 2
    - Detecting detail datagrid rows changing Hi All I need to detect when the row count in a detail grid changes as the user navigates on the Master datagrid I need to change one of the column widths on the detail grid to allow for the scrollbar appearing and disappearing. I can't seem to find an event which fires when the detail grid changes I am using VB 2003 and use a dataset to bind the 2 grids with a relation Regards Steve Tag: UpdateCommand doesn't work fine. Tag: 120023
  - 3
    - Writing back data (Update) Hello, I have created a connection of a Siebel Database (or to be more specific Sybase SQL Anyware), and I am able to retrieve data and view it contents (Tables, Views Stored Procs) using odbc from ADO.NET. The problem I am having is trying to Update a table. It does not appear that I have permission or there is some other type of commands I should be using besides SQL statements. I really need information on writing data back to this database. Any information would be greatly appreciated. If necessary you will be able to VPN onto the computer to accomplish this. I would also be interested in a referral or consultant. Siebel has been no help, especially since Oracle has agreed to purchase them. Thrid party drivers are ok to, providing they do not require individual lisences for each computer that is using it. Thanks, Chuck Tag: UpdateCommand doesn't work fine. Tag: 120021
  - 4
    - How to know a column whether indexed or not in sql2k Tag: UpdateCommand doesn't work fine. Tag: 120011
  - 5
    - Odd Behavior Copying VarBinary Data I'm copying the content from one DataTable to another. Both tables have a SQL column of type VarBinary. The data is actually copied this way. DataRow[Column] = DataRow[Column]; Would this method cause a problem with VarBinary data types? The problem I'm seeing is that the data seems to be duplicated. So, if the both rows contained the value "1 2 3" after the copy I'm seeing a value of "1 1 2 2 3 3" in the row where the data was copied to. Any thoughts ideas are appreciated. Tag: UpdateCommand doesn't work fine. Tag: 120005
  - 6
    - DataTable Adapters and Oracle Provider (2005) Using the GUI tools to have a Table Adapter's Fill& GetData proc point to a SPROC in the database (or a function) works well if the proc is a Function that has no parameters, OR if it is a proc and you are using SQL Server. In my case, I want to use functions inside Oracle, and want to pass parameters. First I tried using SPROC. Note I'm not trying to return a cursor here - my goal is simply to get the SPROC to run. CREATE OR REPLACE PROCEDURE getScreenByApparatus (parmapparatusID in varchar) IS myCursor doe_pkg.TPI_Cursor; BEGIN open myCursor for Select scr.screenID, scr.screenType,scr.Description, scr.userid from screen scr, screenprocessstep sps where sps.apparatusID = 1 -- parmApparatusID and sps.screenID = scr.screenID; end getScreenByApparatus; --- End SQL --- In the DataSet designer, I set the SCREENTableAdapter (attached to the SCREEN object I dragged over) to use these values: Command Text: getScreenbyApparatus CommandType: StoredPrcocedure ExecuteMode: Reader Parameters: Parameter1: DbType: AnsiString Direction: Input ProviderType: VarChar I Right click Preview Data, select the table and procedure, and fill in the Parameter (either Hi There, "Hi There" or "hiThere") I then get back a dialog: Could not be previewed: ORA-6550: line 1 column 7: PLS-00306. wrong number or types of arguments in call to 'GETSCREENBYAPPARATUS' ORA-6550:line 1, column 7 PL/SQL: Statement ignored. (I know I have a select statement that's pointing into a cursor, and I've tried this with an OUT cursor as the second parameter, OUT cursors as return values, but will skip that discussion until I get the basics working). This all works smashingly well in SQL Server. What does it all mean? However, when I create the For example, my Oracle -- Senior Developer Transform Pharmaceuticals Tag: UpdateCommand doesn't work fine. Tag: 120003
  - 7
    - using webrequest Hi I have a requirement to submit form data to a url after collecting the data from the database. For example I need to send firstname and lastname through a post method I can call on the fly. This is the traditional route below , I want to emulate this submit action from a vb.class using request.method="post" <FORM action="http://somesite.com/prog/adduser" method="post"> First name: <INPUT type="text" name="firstname"><BR> Last name: <INPUT type="text" name="lastname"><BR> email: <INPUT type="text" name="email"><BR> <INPUT type="submit" value="Send"> <INPUT type="reset"> </FORM> clear as mud? Anyone have any tips ? Tag: UpdateCommand doesn't work fine. Tag: 120001
  - 8
    - Intermittent SP error - "has too many arguments" Hello, I have an audit table into which I insert information about the use of the application. This works sometimes and other times fails. I cannot find any reason for it failing. It is always given the information. Here are the errors I receive; --------------------------- Exception Message: Procedure or function usp_UseAudit_ins has too many arguments specified. --------------------------- --------------------------- Exception Source: .Net SqlClient Data Provider --------------------------- --------------------------- Exception StackTrace: at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at CLIP.UseageAudit.InsertAuditRecord(String selection, String version) --------------------------- The call to the method \\ InsertAuditRecord("ManageJobs", _version) // The Method \\ Friend Sub InsertAuditRecord(ByVal selection As String, ByVal version As String) Call DAL.InsertUseAudit() Dim row As DataRow = _dataSet1.Tables("tblUseAudit").NewRow row("pkUseAuditId") = Guid.NewGuid.ToString row("ClipUser") = SystemInformation.UserName.ToString row("version") = version row("DateTime") = Now row("Selection") = selection _dataSet1.Tables("tblUseAudit").Rows.Add(row) Dim dsDataChanges As New CLIP.dsTables dsDataChanges = CType(_dataSet1.GetChanges, CLIP.dsTables) If (Not (dsDataChanges) Is Nothing) Then DAL.daUseAudit.Update(dsDataChanges, "tblUseAudit") _dataSet1.Merge(dsDataChanges) _dataSet1.AcceptChanges() // The DataAccess (DAL) \\ Public Sub InsertUseAudit() With cmdUseAudit_Ins .CommandType = CommandType.StoredProcedure .CommandText = "usp_UseAudit_ins" .Connection = sqlConn With cmdUseAudit_Ins.Parameters ' No return required - Does not work even with this incuded '.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, '4, ParameterDirection.ReturnValue, False, CType(0, Byte), 'CType(0, Byte), "", DataRowVersion.Current, Nothing)) .Add(New SqlParameter("@pkUseAuditId", SqlDbType.VarChar, 36, "pkUseAuditId")) .Add(New SqlParameter("@ClipUser", SqlDbType.VarChar, 50, "ClipUser")) .Add(New SqlParameter("@version", SqlDbType.VarChar, 50, "version")) .Add(New SqlParameter("@DateTime", SqlDbType.SmallDateTime, 4, "DateTime")) .Add(New SqlParameter("@Selection", SqlDbType.VarChar, 50, "Selection")) End With End With End Sub // Parts of the DataSet \\ Friend tblUseAudit As New DataTable Me.tblUseAudit = Me.Tables.Add("tblUseAudit") ' tblUseAudit Dim pkUseAuditId As DataColumn = Me.tblUseAudit.Columns.Add("pkUseAuditId", GetType(String)) pkUseAuditId.MaxLength = 36 pkUseAuditId.AllowDBNull = False Dim ClipUser As DataColumn = Me.tblUseAudit.Columns.Add("ClipUser", GetType(String)) ClipUser.MaxLength = 50 ClipUser.AllowDBNull = False Dim version As DataColumn = Me.tblUseAudit.Columns.Add("version", GetType(String)) version.MaxLength = 50 version.AllowDBNull = False Dim DateTime As DataColumn = Me.tblUseAudit.Columns.Add("DateTime", GetType(DateTime)) DateTime.AllowDBNull = False Dim Selection As DataColumn = Me.tblUseAudit.Columns.Add("Selection", GetType(String)) Selection.MaxLength = 50 Selection.AllowDBNull = False pkUseAuditId.ReadOnly = True tblUseAudit.PrimaryKey = New DataColumn() {tblUseAudit.Columns("pkUseAuditId")} The Table \\ CREATE TABLE [tblUseAudit] ( [pkUseAuditId] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ClipUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [version] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateTime] [smalldatetime] NOT NULL , [Selection] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO // The Stored Procedure \\ CREATE PROCEDURE dbo.usp_UseAudit_ins( @pkUseAuditId varchar(36), @ClipUser varchar(50), @version varchar(50), @DateTime smalldatetime, @Selection varchar(50) ) AS SET NOCOUNT OFF; INSERT INTO tblUseAudit( pkUseAuditId, ClipUser, version, DateTime, Selection ) VALUES ( @pkUseAuditId, @ClipUser, @version, @DateTime, @Selection ) /** no return required - doesn't work even with this included. ; SELECT pkUseAuditId, ClipUser, version, DateTime, Selection FROM tblUseAudit WHERE (pkUseAuditId = @pkUseAuditId) **/ GO // What could cause this error? Thank you, dbuchanan Tag: UpdateCommand doesn't work fine. Tag: 119991
  - 9
    - Connection String Problem Hi, I am trying to encrypt my connection string in the app.config file. I am using Base64 encryption, I just want to hide it from casual users. I keep getting file not found error. However when I just use the connection string in plain text I can connect fine. The only difference between the two is the @ sign is used in front of the connection string (plain) when I view them in the command window (see below). Any ideas? > ? this.connectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\WINDOWS\\system32\csdm.dll;Jet OLEDB:Database Password=passwd;" > ? this.connectionString @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\WINDOWS\\system32\csdm.dll;Jet OLEDB:Database Password=passwd;" Tag: UpdateCommand doesn't work fine. Tag: 119984
  - 10
    - retrieve a column value from a row that might be DBNull I have a dataset that I want to update. I pull the row out that I want to update. There is a value in one of the columns that must be analized to determine if other values need to change. This works great as long as there is something in that column. If that DB column is empty, I get this error... "System.Data.StrongTypingException: Cannot get value because it is DBNull. ---> System.InvalidCastException: Cast from type 'DBNull' to type 'Decimal' is not valid. I have tried all sorts of things. I am wanting to get like, row.CCNum THis is great unless CCNumis empty. I've tried the method isDBNull(row.CCNum), but I still get the same error. How do I return a value from a DB and not error out if its empty? I have multiple columns to update, so I don't want to use an execute reader. I would like to use the 'row' type methods to get this done. Tag: UpdateCommand doesn't work fine. Tag: 119979
  - 11
    - Multiple Connections for a Dataset Hi, I have a method in a data access class (all in C#) where I'm trying to put information from different connections into a single dataset to return to the calling class. Any ideas on how I can do this? I only know how to pull data from different tables using ONE connection. I have data in a completely different connection that I need to pull from. This is what I currently have: Connection.Open(); myDataAdapter = new OracleDataAdapter(); myDataAdapter.SelectCommand = new OracleCommand( query, base.Connection ); myDataSet = new DataSet(); myDataAdapter.Fill( myDataSet ); return myDataSet; This fills my dataset using the query and only one connection. I don't want to have to pass connection strings to the method that does this. Any ideas would be greatly appreciated! Christine Tag: UpdateCommand doesn't work fine. Tag: 119977
  - 12
    - Filtering question with related tables Hi guys, I've been struggling with this one for a while. I have a typed dataset. It has two tables inside of it; Resources and EmployeeMaster. Both EmployeeMaster and Resources have a column called "EmpID", an int, which is also both of their primary keys. This is the relationship between the two, and is present in the dataset. EmployeeMaster has detailed data about employees such as full name, address, email, etc. Resources is a table that I created that is used to determine which employees are using a program I am writing right now, and what their security level is. That column is of type int called "UserType". So this is all set up, my dataset is populating nicely with every row from both tables, everything seems to be set. Now, what I want to do is populate a listbox on a C# Windows.Forms app with employee names (stored only in EmployeeMaster) of only those employees who have a UserType (stored only in Resources) of 5. The equivalent SQL for this is simple. "SELECT emp.LastName FROM EmployeeMaster emp, Resources r WHERE r.UserType = 5" I can't figure out how to get the equivalent of this to work in my typed DataSet so I can databind it to my listbox. I looked at DataViews, but couldn't find any decent examples of the RowFilter that was filtering on a child table. I did find one page that said there's a "[child]" keyword that should be put in there, but I couldn't figure that out. I also looked at DataViewManagers, but hit a dead end on that one as well. Can one of you gurus give me a little guidance, please? I feel silly asking what's probably a really simple question, but I've scoured the net for hours and haven't found anything. Thank you VERY much to any and all help! Andy K Tag: UpdateCommand doesn't work fine. Tag: 119973
  - 13
    - creating an xsd through xsd designer - xml format Hi all, I need some help: I have a DataSet object created by the .NET "xsd designer". The DataSet is being filled from a Stored Procedure which returns the results of a few select statements. I have no problem filling the DataSet, but when I use the method GetXml() I get an XML string that is not in the format I wanted. I get somthing like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> When actually I wanted something like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> </Table> I created the xsd by dragging & droping tables in the designer. I put "Table1" into "Table" when I did that, but nothing happened. Is there a way to force a selected format on the XML I generate from the data in such a DataSet, or do I have to create the whole thing by code? Thanks! Lea *** Sent via Developersdex http://www.developersdex.com *** Tag: UpdateCommand doesn't work fine. Tag: 119969
  - 14
    - How to force a specific format on xml generated by xsd Hi all, I need some help: I have a DataSet object created by the .NET "xsd designer". The DataSet is being filled from a Stored Procedure which returns the results of a few select statements. I have no problem filling the DataSet, but when I use the method GetXml() I get an XML string that is not in the format I wanted. I get somthing like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> When actually I wanted something like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> </Table> I created the xsd by dragging & droping tables in the designer. I put "Table1" into "Table" when I did that, but nothing happened. Is there a way to force a selected format on the XML I generate from the data in such a DataSet, or do I have to create the whole thing by code? Thanks! Lea ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: UpdateCommand doesn't work fine. Tag: 119968
  - 15
    - Newbie with a Problem Hi There, Hope Somebody dosn`t mind Helping a Newbie? I have the Follwoing Code: Private Sub frmSystemUsers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'File Datasets Try dsSystemUsers.EnforceConstraints = False Try Me.odcSystemUsers.Open() odaSystemUsers.Fill(dsSystemUsers) cmSystemUsers = DirectCast(BindingContext(dvSystemUsers), CurrencyManager) mlLoading = True Catch fillException As System.Exception Throw fillException Finally Me.odcSystemUsers.Close() End Try Catch eLoad As System.Exception System.Windows.Forms.MessageBox.Show(eLoad.Message) Finally mlLoading = False End Try 'End of Fill Datasets End Sub Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click cmSystemUsers.AddNew() txtFirstname.Enabled = True txtSurname.Enabled = True txtUsername.Enabled = True txtPassword.Enabled = True End Sub Private Sub btnAccept_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAccept.Click If txtFirstname.Text = "" Then ValidDataMessage = "First Name Required" txtFirstname.Focus() ElseIf txtSurname.Text = "" Then ValidDataMessage = "Surname Required" txtSurname.Focus() ElseIf txtUsername.Text = "" Then ValidDataMessage = "Username Required" txtUsername.Focus() ElseIf txtPassword.Text = "" Then ValidDataMessage = "Password Required" txtPassword.Focus() If ValidDataMessage = "" Then If MessageBox.Show("This Will Save All Changes - Are You Sure?", "Warning!", MessageBoxButtons.OKCancel) = DialogResult.OK Then cmSystemUsers.EndCurrentEdit() If dsSystemUsers.HasChanges Then odaSystemUsers.Update(dsSystemUsers) dsSystemUsers.Clear() odaSystemUsers.Fill(dsSystemUsers) End If Else End If MessageBox.Show(ValidDataMessage, "Data Entry Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End If End If End Sub Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click Dim DeleteResult As DialogResult = MessageBox.Show("Delete " & txtUsername.Text & " ?", "Warning!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) If DeleteResult = DialogResult.Yes Then Me.dvSystemUsers(cmSystemUsers.Position).Delete() End If End Sub Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click txtFirstname.Enabled = True txtSurname.Enabled = True txtUsername.Enabled = True txtPassword.Enabled = True End Sub Private Sub btnCancelChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelChanges.Click If MessageBox.Show("This Will Cancel All Changes - Are You Sure?", "Warning!", MessageBoxButtons.OKCancel) = DialogResult.OK Then cmSystemUsers.CancelCurrentEdit() End If txtFirstname.Enabled = False txtSurname.Enabled = False txtUsername.Enabled = False txtPassword.Enabled = False End Sub End Class First Question - Am I writing my code the correct way for a Database Application, or am I going about it totally the Wrong Way? Second Question is - Why I try and click my Update Button, it dosn`t Update the Data to my Database. It Adds a Row but with just Blank Data Third Question - When I delete a Row, It deletes it on Screen but dosn`t Update My Database Again Sorry for been sooooooo Stupid but like I said Im a newbie thats confused, I`ve search the Net but got even more confused because it seems there is so many ways of doing things. Hope omebody dosn`t mind Helping out? Many Thanks NSC Tag: UpdateCommand doesn't work fine. Tag: 119965
  - 16
    - Is there an XMLDataAdapter? I have two, dynamic XML files that contain data. My customer insists on using XML for a number of quite valid reasons. Being new (read naive) to XML, I thought there might be some form of SELECT statement that could be used with XML files. A number of books have referred me to the .NET SDK. However, I can't seem to find what I need. Is there a data adapter for XML? If so, where might it be documented? If not, must I program my own queries against the contents of various dataset tables (read by <data-set>.ReadXML)? I must stay within the Microsoft provided .NET framework and not purchase software from another vender. I also must assume that the user will have neither SQL nor Access available. TIA, Gus Tag: UpdateCommand doesn't work fine. Tag: 119956
  - 17
    - how to know the table is exist in the database Tag: UpdateCommand doesn't work fine. Tag: 119942
  - 18
    - Connect to a password protected MS Access DB After I set the password for MS Access DB , i modify the connection string from connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=System.Environment.SystemDirectory\" & "MyDBName.MDB" to connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=System.Environment.SystemDirectory\" & "MyDBName.MDB" & ";User Id=admin;Password= " & "MyPassWord" & ";" Afterward I open the Access DB, it return error "cannot start your application. the workgroup information file is missing or opened exclusively by another user." Any idea? thanks. Peter Tag: UpdateCommand doesn't work fine. Tag: 119937
  - 19
    - ANN: New VistaDB 2.1 release supports Visual Studio .NET 2005 VistaDB 2.1 embedded SQL database has been released. Free major update features over 60 improvements including improved .NET Provider support for Visual Studio .NET 2005, .NET 2.0 and .NET Compact Framework. 2.1 now includes a free 2-User VistaDB Server that extends the 500KB embedded engine to provide true client/server SQL database support. Embedded and server feature royalty free distribution. Read more: http://www.vistadb.net/Press_051024_VistaDB21Released.asp -- Anthony Carrabino www.vistadb.net Tag: UpdateCommand doesn't work fine. Tag: 119927
  - 20
    - Update Multiple table using Dataset Hi, I have an issue in updating the Primary Key in the Parent table. I have set of three tables (Database SQL2000, VB.NET), where A is related to B and B is related to C. I have written a stored procedure which returns all the records in these tables and fill up a single DataSet. All these table have primary key which is an Identity Column. The dataset then populates the three grids in the client application. The users can add/update/delete the values in all the three tables and there is single save button to do the updates. I am code I have created DataRelation between table A,B and C. Also when I add a new add to the child table, I set the parent row as well. All these is working fine. I am using the example as described in the CodeProject article http://www.codeproject.com/cs/database/relationaladonet.asp?df=100&forumid=14883 But my problem here is when I add a row in the Parent table say A and then add new rows in the child table B. The new generated ID in the Parent table is not propagated to the rows in the child table. I am using RowUpdated event to capture get the latest ID returned from the stored procedure as output parameter. I use the latest ID to update the datatable. When I debug the application, I can retrieve the new ID in the RowUpdated event and set it back to the datarow: e.Row.Item("ColumnName") = newID. e.Row.AcceptChanges() But these changes are not seen in the dataset. It still has same old ID but in the DEBUG mode the e.row has the new value. Moreover, its an issue because the event arguments are passed as Byval and not as ByRef. i.e. I am not getting the actual reference to the DataRow. Has anybody faced this problems before? Please help... Tag: UpdateCommand doesn't work fine. Tag: 119923
  - 21
    - Fill table in dataset with query and then add columns? I'm filling up a dataset table with a database query. Then I'm binding the dataset to an ASP.Net asp:DataGrid. However, some derived columns have values that are too complicated to fill out in the SELECT statement in the query. Can one add additional columns to a dataset after initially creating a dataset with a database query? What I want to do is to fill up the dataset with a query, create additional columns (unless I can/should do that in the SELECT list?), loop thru the dataset and on each row do some logic to decide how to fill out the derived columns. The derived columns can all be text. I do not need to do updates on this table back into the database. Is this a reasonable approach? Tag: UpdateCommand doesn't work fine. Tag: 119919
  - 22
    - Fill related tables in dataset with one query? Thought this would be simple, but apparently its not. I have a strongly-typed dataset with tables that have relations set in the DataSet designer. I've been stymied when attempting to fill this dataset using a join query. Without TableMappings, nothing is filled in the dataset. I can't use table mappings, however, because it won't allow me to split one result set among four different tables. I can create a tablemapping for each table in the dataset, but the "source table" is the same in each one--the result set. No two table mappings can have the same source table, so it fails. If I was grabbing all data, it wouldn't be such a problem, as I could fill each table in turn. However, I'm performing a search at the same time, which means only relevant data is returned. Pulling it all down and then sorting through everything is not feasable, since the database could hypothetically contain millions of rows. What should I do? Use a DataReader and fill the dataset manually? TIA. Tag: UpdateCommand doesn't work fine. Tag: 119916
  - 23
    - Do DataTable's in ASP.NET 2.0 support "blob" datatypes? I have potentially large PDF legal files (up to 10MB) that will be passed to an imaging system via a webservice. Does anyone know which datatype I should use for this? IOW, is there something akin to a "blob", since this is binary data. Tag: UpdateCommand doesn't work fine. Tag: 119912
  - 24
    - Copy a table using ADO.Net OK, I think the answer is no, but I need to ask. I have a table structure defined in my Access database (SQL Server is not available), I would like to take that table and create a new table with a unique name. Can I do this using ADO.NET? Thanks, Dave Tag: UpdateCommand doesn't work fine. Tag: 119902
  - 25
    - Newbie with Database Connection Problem Hi, Wonder if Somebody could help me? I`ve been searching all over for a solution but am totally lost. I have a Class called Connection as below: Public Class Connection Private Shared mConnectionsstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Private Shared mLocation As String Private Shared connArray As ArrayList Public Shared Sub setString(ByVal ind As Integer) Dim connArray As New ArrayList connArray.Add("J:\Database\MasterDB.mdb") connArray.Add("J:\Database\TestDB.mdb") mLocation = connArray(ind).ToString End Sub Public Shared ReadOnly Property [String]() As String Get Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mLocation & ";" End Get End Property End Class On my Main Form I have my Database Connection Setup as: Dim UP As New OleDb.OleDbCommand() Dim Con As New OleDb.OleDbConnection() Connection.setString(Me.cboSelectDB.SelectedIndex) Con.ConnectionString = Connection.String UP.Connection = Con UP.CommandType = CommandType.Text UP.CommandText = "Select * From Passwords Where Users_Name=? and Users_Password=?" End Sub But when I run my App it Errors on line "mLocation = connArray(ind).ToString" with the following error: "Additional information: Index was out of range. Must be non-negative and less than the size of the collection" Anybody point me in the right direction? Many Thanks in Advance Regards Not_So_Clever Tag: UpdateCommand doesn't work fine. Tag: 119899
- Next
  - 1
    - Adding deleted rows back to a DataTable I'm trying to add rows marked as deleted back into their parent DataRowCollection by using DataRowCollection.Add. The behavior I was expecting was having the DataRowState switch back to it's last value before the delete, i.e. Unchanged->Deleted->Unchanged, Modified->Deleted->Modified, etc. Instead I get an ArgumentException with the message "This row already belongs to this table." Obviously the row does belong to the table, however it is not currently part of it's row collection. How would I go about implementing this? I know I can use RejectChanges but that will revert the state back to the last Original, which will undo any field changes as well. All I want to do is undo the last Delete action. I was considering adding a duplicate new row and loading the deleted row's values into it, but that might result in constraint violations when running the related stored procedures against the data source. Thanks in advance. Tag: UpdateCommand doesn't work fine. Tag: 119893
  - 2
    - Hierarchical search of tree flattened to dataset--advise pls I have a search problem that I'm seeking some advice in. I'm dealing with the need to search node names in a tree structure that is flattened into a database table (I have no say over the design, just over how to search). The tree consists of nodes that represent rooms, cabinets in each room, drawers in each cabinet, etc. The flattened representation is as what you would expect--each data row has a column representing the room, cabinet, drawer, etc. When I search this data, I need to do it in a hierarchical fashion--I look at the first room, then the first cabinet in that room, then the first drawer in that cabinet, etc. If I search the dataset linearly, I end up showing the same hit multiple times (if I hit on the room name, once I finish showing hits on that row, I encounter the room again the next row). I could, once a hit is found, move vertically down the column in the dataset, erasing all names that match, but that isn't easily done without performing multiple checks to make sure I'm still in the same branch of the tree. Any hints on a better way to perform this search would be appreciated. I could convert the data to xml and use xpath, or create a normalized dataset and fill it appropriately, but I'm not sure this will ease the search later. Thanks. Tag: UpdateCommand doesn't work fine. Tag: 119890
  - 3
    - SQL Server Raiserror and .NET SqlDataAdapter Running SQL Server 2000 SP4 and .NET Framework 1.1.43322 SP1 I have a trigger that does some work and then, when it decides there is a problem does: if @ErrMsg is not null BEGIN RAISERROR (50001,16,1, 'AssetConfig', 'AssetKey/Site', @ErrMsg) ROLLBACK transaction In my Windows Forms application I have try { SqlTransaction sqlTrans = â?¦.BeginTransaction(); Update several tables, including the one with the trigger sqlTrans.Commit(); } catch (SqlException e) { sqlTrans.Rollback(); } When the trigger raises the error, I do not get the @ErrMsg because the SqlDataAdapter does not trap the error. Instead, I get System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. at System.Data.SqlClient.SqlTransaction.Rollback() at the Rollback statement. This is because the Commit threw this SqlException Message # 3902 "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION." What I WANT is to have the SqlDataAdapter.Update throw the error that I have specified, just as it would do if, for example, a primary or foreign key had been violated. Then the catch block would do the rollback and handle the error. I tried taking the Rollback out of the trigger, but then I got no message, no error, and the database was updated with bad data. What is the secret to making SQL Server Raiserror work with the Framework to return a meaningful error? Tag: UpdateCommand doesn't work fine. Tag: 119889
  - 4
    - Data Grid - Style Hi All! How can i add styles like combo box, checkbox in my datagrid control TIA Viswanathan S Tag: UpdateCommand doesn't work fine. Tag: 119884
  - 5
    - Datagrid - format Hi All! I want to display the currency symbol in the datagrid for all the rows of the salary (currency) field. How to do this? 'This is my sample code Dim cm As New OleDbCommand Dim da1 As New OleDbDataAdapter Dim ds1 As New DataSet Try cm.CommandText = "select distinct d.dept_name as deptname ,sum (e.salary) as salary from department d ,employee e where d.dept_id=e.dept_id group by d.dept_name" cm.Connection = Con da1.SelectCommand = cm da1.Fill(ds1, "employee") dg.DataSource = ds1.Tables("employee") Catch ex As Exception MsgBox(ex.Message) End Try TIA Viswanathan S Tag: UpdateCommand doesn't work fine. Tag: 119883
  - 6
    - build SQL text from SqlCommand Hi, is it possible to retrieve exact sql text which will be executed from a prepared SqlCommand or DBCommand? I need this for logging all activity. Something like this would be helpfull: Command.CommandText = "ExecProc" Command.Paramaters.Add..... SQL = Command.GetInternalSql 'or Command.GetType.InvokeMember ("GetInternalSql") Tag: UpdateCommand doesn't work fine. Tag: 119874
  - 7
    - vs2k5 Beta2 'Code Analysis' returns warnings on generated code I built a simple Data Access Layer with the Visual Studio 2005 DataSet Designer using the wizard. All who I have talked to who use it, articles I haver read by those who use it speak very highly about the quality of code that vs2005 creates. Before adding any code of my own I ran "Code Analysis" (from debug menu) against the code and got (no errors but) 90 warnings! I know nothing about the "Code Analysis" and can find nothing in the help. Some of the warnings are just good suggestions that the user should consider based on best coding practice and the users intension for the code such as; Warning 4 CA2210 : Microsoft.Design : Sign 'NorthwindDataAccess' with a strong name key. 1 Other warnings point out things I think the Wizard should have handled; Warning 42 CA1804 : Microsoft.Performance : EmployeesTableAdapter.GetData(Nullable`1<System.Int32>):EmployeesDataTable declares a local, 'GetData', of type NorthwindDataAccess.EmployeeDataSet+EmployeesDataTable, which is never used or is only assigned to. Use this local or remove it. C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\NorthwindDataAccess\NorthwindDataAccess\EmployeeDataSet.Desig= =ADner.vb 1229 NorthwindDataAccess More Examples: Warning 10 CA1801 : Microsoft.Performance : The 'this' parameter (or 'Me' in VB) of EmployeeDataSet.ShouldSerializeEmployees():Boolean is never used. Mark the method as static (or Shared in VB) or use 'this'/Me in the method body. C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\NorthwindDataAccess\NorthwindDataAccess\EmployeeDataSet.Desig= =ADner.vb 188 NorthwindDataAccess Warning 48 CA1709 : Microsoft.Naming : Correct the casing of parameter name 'LastName'. C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\NorthwindDataAccess\NorthwindDataAccess\EmployeeDataSet.Desig= =ADner.vb 1333 NorthwindDataAccess Warning 89 CA1706 : Microsoft.Naming : 'Id' is an abbreviation and therefore is not subject to acronym casing guidelines. Correct the capitalization of 'ID' in parameter name 'EmployeeID' by changing it to 'Id'. C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\NorthwindDataAccess\NorthwindDataAccess\EmployeeDataSet.Desig= =ADner.vb 1460 NorthwindDataAccess Can anyone who knows more about this comment on this feature and the results I am getting.=20 Thank you,=20 dbuchanan Tag: UpdateCommand doesn't work fine. Tag: 119873
  - 8
    - Concurrency management in XML Files Hi, I was wondering how it is possible to manage optimistic concurrency in XML files. Indeed, i store my data in xml files in an asp.net application. My data access layers accepts custom entities, and then uses dataset.WriteXml() to write the new data. The problem is that this way of doing erases all file content, and writes the content of the dataset. If a user wrote his changes just before we write ours his changes are lost. Is there a simple way to manage optimistic concurrency, like what we can do with a DataAdpter with SQL databases? Thanks a lot for your help PS: I use .net v1.1 ;) Tag: UpdateCommand doesn't work fine. Tag: 119870
  - 9
    - Where namespace have ObjectSpace? Where namespace have ObjectSpace? I need to write application using ObjectSpace, but I cannot find it on VS.NET 2005. Tag: UpdateCommand doesn't work fine. Tag: 119863
  - 10
    - Some newbie questions Hi All Wondering if someone might have a few seconds to clear up some newbie queries I have with VB.NET and ADO.NET. I know their are limitless situations that require different approaches but would love to know some of the fundementals as a place to start. Have been through quite a few examples but are unsure about the following. 1. For a simple database with a couple of tables do you create a dataadapter for each table and then create a dataset that includes each of these tables? 2. Do you use the same adapters and datasets througout the application on different forms? If so how do you make them available to the other forms if you have used the wizard to create them? 3. Is it best to reference rows/records by ID number in the table or the row index? 4. Should you link tables in the database using views/queries in the database or by creating a separate datatable in the dataset that joins the tables from the database? 5. Any other advice on best practices for learing this stuff. Appreciate any assistance. ilr Tag: UpdateCommand doesn't work fine. Tag: 119858
  - 11
    - calling a stored procedure in ADO.NET 2.0 How can I calling a stored procedure (say "sp_example") in ADO.NET 2.0? I used to do this in ADO.NET 1.1, but in 2.0 (beta2 vs2005), after the following ... sqlConnection con = new sqlConnection(strCon) sqlCommand cmd = sqlCommand("sp_example", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.??? under the Parameters property, I only find the AddRange() and AddWithValue() method, but not the Add() method which is in ADO.NET 1.1. Does anyone have an idea how to call a stored procedure in the new 2.0 framwork? Thanks in advance. David Tag: UpdateCommand doesn't work fine. Tag: 119856
  - 12
    - How do I retreive warnings? If I do something like this: SqlConnection connection = new SqlConnection( ... ) SqlCommand command = new SqlCommand( ... ) // initialize command object. command.ExecuteNonQuery(); How do I determine if the sql server generated a warning, and how do I "read" the warning? Thanks in advance, Peter Strøiman Tag: UpdateCommand doesn't work fine. Tag: 119850
  - 13
    - Datagrid forecolor from dataview. Hi, How do I associate a column in the dataview with a color property of datagrid forecolor, more specifically a particular cells forecolor. I'm associating a dataview with the datagrid and the values are showing OK. But I want the fore color of a cell being set on specific condition. I'm thinking of adding a new column to the table which has an expression to determine the color. And if this is associated with the cell fore color, it should work great as I just need to keep on updating the data. I tried but couldn't find a way to do this. Any help would be of great use. Thanks in advance, Gobi. Tag: UpdateCommand doesn't work fine. Tag: 119848
  - 14
    - question i use enterprise services for having transaction management. i use one transactional component that is creating an ado.net connection, and i pass this connection to not enterprise services components to use and have transaction control in a central place in the project. i can commit or rollback the operations from outside those objects. as i tested this works. i need to work this way since my programming model requires the use of friend properties/methods that are not supported by enterprise services. is there some reason why not to work this way? TIA! Tag: UpdateCommand doesn't work fine. Tag: 119847
  - 15
    - System.Data.OleDb.OleDbException: Unspecified error My ASP.NET web page was working just fine connecting to an Access DB and displaying data in a DataGrid. Couldn't be much simpler really. Then, I had to move the page and its underlying database file to a new server. Now it ceases to work and gives me ... Unspecified error Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Unspecified error It is NOT a security issue, I assume, since I gave the ASPNET user full access to the database folder. What else could cause this error? Anyone know? Thanks. Tag: UpdateCommand doesn't work fine. Tag: 119844
  - 16
    - Modeless Form Lose Accelerator and Tabbing Keys All, I am using COM Interop with C# to display a modeless form via a COM call from an application written in Visual studio C++ 6.0. I can display the form as modeless however the accelerator and tab keys do not work. Interestingly, if the form is modal the accelerator and tab keys work properly. Also if the same form is launched from a C# appication (not using COM) the forms accelerator and tab keys work properly. The form is a Class library (DLL) that has a COM interface. There is a COM method that calls .Show() for the form. Does anyone know how to get the accelerator and tab keys to work when calling the form through COM? Thanks in advance, Scott Tag: UpdateCommand doesn't work fine. Tag: 119841
  - 17
    - Find duplicate rows in datatable Hi All I need to detect duplicate rows in a datatable in VB net 2003 using MS Access 2K I can do it at database level with sql e.g sql = "select membernumber from members group by membernumber having count(membernumber) > 1" dt = getdata(sql, "dupes") ' getdata is my sub routine which returns a datatable Can you do a similar thing querying an Ado.net Datatable? i.e can you do advanced querying on a populated datatable? Any tips appreciated Regards Steve Tag: UpdateCommand doesn't work fine. Tag: 119832
  - 18
    - Querying a scalar in DataTable Hi, I have a DataTable with three columns (2 int, and 1 string). Now I would like a query that return the total number of different integers in one of the interger columns. For example Name Age Class A 12 4 B 13 5 C 11 3 D 12 3 Now if I select the total number of different integers in the Age column the answer is 3(because 11, 12 ,13). I hope someone understands my problem. Can some one help me out Thanks in Advance Tag: UpdateCommand doesn't work fine. Tag: 119831
  - 19
    - Help with using DataView to insert Record..Please! I'm new to ado.net/asp.net and have been on a crash course these last few days. I've been stuck on this current problem for over a day and it seems pretty simple, but I can't get it to work. I'd like to create a data view from a data table, insert a record into that data view and have it inserted back into the actual database (Access 2000). Following tutorials online, I've been able to use a dataadapter to connect to the database, then using a datatable, insert new rows and have them inserted in the actual database. But when it comes to doing that same functionality via a dataview, I've had no luck. Here is the code I've been using. It generates no errors, and a new row is added to the underlying datatable, but nothing is ever sent back to the database. I think it might have something to do with the myAdpater.InsertCommand code (are these used the same way in DataViews as in DataTables?). Thanks in advance for any advice. Public Sub editLevelProgressFromView() 'Objects used to connect/read from database Dim cs As ConnectionStringSettings Dim myConnection As OleDbConnection Dim myAdapter As OleDbDataAdapter Dim myTable As DataTable Dim myView As DataView Dim myRow As DataRowView Dim sqlStr As String cs = ConfigurationManager.ConnectionStrings("strConn") myConnection = New OleDbConnection(cs.ConnectionString) sqlStr = "Select * from tblTest" myAdapter = New OleDbDataAdapter(sqlStr, myConnection) 'Set up insert commands for use on myAdapter.Update method myAdapter.InsertCommand = New OleDbCommand("INSERT INTO tblTest (c_name, c_number) VALUES(?, ?)", myConnection) myAdapter.InsertCommand.Parameters.Add("@c_name", OleDbType.VarChar, 15, "c_name") myAdapter.InsertCommand.Parameters.Add("@c_number", OleDbType.VarChar, 30, "c_number") 'Fill Table and create View myTable = New DataTable() myAdapter.Fill(myTable) myView = myTable.DefaultView 'Add Row to myView, insert data via BeginEdit and EndEdit methods myRow = myView.AddNew() myRow.BeginEdit() myRow("c_name") = "testName" myRow("c_number") = "testNumber" myRow.EndEdit() 'Accept changes in table from view myTable.AcceptChanges() 'Commint changes to database (does not work, but no error thrown) myAdapter.Update(myTable) End Sub Tag: UpdateCommand doesn't work fine. Tag: 119827
  - 20
    - DataSet and Grid I want to hook up a DataSet to a grid but control the error display to the user. An exaple is user inputs invalid data and program puts up special message display instead of default eror messge from DataSet/grid combinatio. Thanks, Marc Tag: UpdateCommand doesn't work fine. Tag: 119822
  - 21
    - Confused about the DataAdapter I'm trying to learn/use ADO.Net and am puzzled by the DataAdapter. It has a TableMappings property which is a collection of mappings from the source into the DataSet. This implies that the DataAdapter can retrieve many tables from a source and store each in a single DataSet. But it only has a single SelectCommand so Fill() will only return single result set. I want to read 4 tables out of an access db. Do I need to create 4 DataAdapters to do it? Confused Tag: UpdateCommand doesn't work fine. Tag: 119811
  - 22
    - C# function within DataReader query Hi, I've a parameterised query that is used as the basis for an Excel export. I need to add an additional column that doesn't map to a field in my SQL table. What I have done in the past in my Access apps is to include a function I've created in the query SQL. Is there a way to achive the same with C#/ado.net? Many thanks, Simon Lane Tag: UpdateCommand doesn't work fine. Tag: 119810
  - 23
    - Optimistic Concurrency and TimeStamp Hi, I would like to implement optimistic concurrency where I retrieve the timestamp of a record in a table with a stored procedure and save the timestamp value into a vb.net variable. This represents the original timestamp for the record. When a user updates the record, I would like to pass the original timestamp to a stored procedure that performs the update to ensure the record has not been changed by another user. The problem is, how do I store the original timestamp value from SQL Server 2000 in vb.net? I have tried: Dim OrigTstamp() as Byte I can assign an incoming timestamp to a byte array but I'm not certain if anything is there. I then have great difficulty passing the OrigTstamp() to the update stored procedure. -- Thanks, Scott H. Tag: UpdateCommand doesn't work fine. Tag: 119806
  - 24
    - Parameterized query nightmares I'm having a very strange problem with a parameterized query failing. I grabbed the query in Profiler to see if something weird was going on. Here it is, nonsense stripped out for brevity: exec sp_executesql N'SELECT CabinetID, CabinetName FROM Cabinets WHERE (CabinetName LIKE N''%@SearchText%'') ORDER BY CabinetName', N'@SearchText nvarchar(16)', @SearchText = N'tech' If I manullay change the LIKE statement to replace @SearchText directly with tech (i.e., "LIKE N''%tech%''), the command completes successfully in Query Analyzer and I get back the data I expect. If I run it as is in QA, I get back nothing. I've checked my authentication, and that's not the problem (if I go back to a concatenated select statement in my program it works fine), I've tried different sizes (and no size at all) for the nvarchar, and that doesn't change the result. I'm probably missing something very simple, but I haven't been able to figure it out thorugh research. Help! Tag: UpdateCommand doesn't work fine. Tag: 119799
  - 25
    - SQL Server Parameters I have a typed DataSet with say 50 fields in it. On the form, I only show 40. I have a stored procedure that takes in all 50 parameters. When I call SqlDataAdapter.Update(ds.Tables(0).GetChanges(DataRowState.Modified)), I get a SQL error back that Procedure XYZ is expecting Parameter '@myParameter' which was not supplied. In looking at Profiler, I can see that this parameter is being passed to SQL Server as Default, not as the value that the field has in the Original DataSet. Any suggestions? - I know that I can set the Default values inside of SQL Server, but that isn't going to work for this situation. Thanks in advance Tag: UpdateCommand doesn't work fine. Tag: 119794

Hello,

I've implemented a control that works with a database oracle, with dataset
and dataadapter. It works fine, inserts, delete, and update works fine when
the sentence is like "update table set a=:1,b:1", but when the update is
"update table set a=:1,b=:2 where c=:1", it says "Concurrency Infraction:
Update Command affected 0 register" or "ORA-01722: Incorrect number".

It makes the sentence dynamically the code is:

If txt.isPrimaryKey Or txt.ConcurrencyField Then

If Not primerw Then

where &= " AND "

Else

primerw = False

End If

where &= txt.FieldName & " =:" & txt.FieldName

Else

If Not primerc Then

strupdate &= ","

Else

primerc = False

End If

strupdate &= txt.FieldName & "=:" & txt.FieldName

End If

If param Is Nothing Then

ReDim param(0)

Else

ReDim Preserve param(param.Length)

End If

param(nparams) = New OracleParameter

param(nparams).ParameterName = ":" & txt.FieldName

param(nparams).OracleDbType = txt.FieldType

param(nparams).SourceColumn = txt.FieldName

If txt.isPrimaryKey Or txt.ConcurrencyField Then

param(nparams).SourceVersion = DataRowVersion.Original

Else

param(nparams).SourceVersion = DataRowVersion.Current

End If

nparams += 1

Thanks in advance

Silvia.

Top