What is the Best way to fetch the most recent updated row in a database

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
    - Readxml diffgram: returns empty dataset Hello, I'm trying to fill a dataset with the data of a diffgram xml-file. I expect a dataset with datasetname "ComputerSystem" filled with 1 table (tables.count = 1). The only thing i've got is an empty dataset (datasetname = "NewDataSet", tables.count=0). You find a code snippet in attach. Any ideas? Thanks, Mav. Dim XMLSource As String Dim datasetHandler As DataSet Dim srDataSet As StringReader XMLSource = XMLSource & "<diffgr:diffgram xmlns:msdata='urn:schemas-microsoft-com:xml-msdata' xmlns:diffgr='urn:schemas-microsoft-com:xml-diffgram-v1'>" XMLSource = XMLSource & "<ComputerSystem>" XMLSource = XMLSource & "<Win32_ComputerSystem diffgr:id='Win32_ComputerSystem1' msdata:rowOrder='0'>" XMLSource = XMLSource & "<GUID />" XMLSource = XMLSource & "<CMEnvironment>AGENT</CMEnvironment>" XMLSource = XMLSource & "<OEMLogoBitmap />" XMLSource = XMLSource & "<OEMStringArray />" XMLSource = XMLSource & "</Win32_ComputerSystem>" XMLSource = XMLSource & "</ComputerSystem>" XMLSource = XMLSource & "</diffgr:diffgram>" Try datasetHandler = New DataSet srDataSet = New StringReader(XMLSource) datasetHandler.ReadXml(srDataSet, XmlReadMode.DiffGram) Catch gex As Exception Debug.Write(gex.Message) End Try Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104053
  - 2
    - Closing DataSets I have a helper class in my application which manages all my DB Connections. Some of these functions return a OleDbDataReader object while others return a DataSet. When I issue a command.executeReader call, I send as input "CommandBehavior.CloseConnection". For example, public OleDbDataReader ExecuteQuery(string sql) { if (sql != "") { myConnection = new OleDbConnection(connStr); myConnection.Open(); myCommand = new OleDbCommand(sql, myConnection); myReader = myCommand.ExecuteReade(CommandBehavior.CloseConnection); } return myReader; } so when a page uses this function and gets back a reader, that page will implicity close the connection when it closes the reader. How can I implement the same for a DataSet. If this class returns a DataSet to some other page, how can i ensure that the connection is closed with the dataset is cleared? Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104051
  - 3
    - Question about SQLDataReader Would there be any performance gain to calling SqlDataReader.GetSqlXXXX instead of SqlDataReader.GetXXXX ? I assuming that SqlDataReader.GetXXXX makes a call SqlDataReader.GetSqlXXXX (i.e.GetString calls GetSqlString) Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104050
  - 4
    - RowState Hi I have a datable that is linked to a db table. When I delete an unchanged row and then add a new row with the same values my datatable stays with a deleted and an added row identical in values. When I commit the changes into db by Dataadapter.Update is executed a delete and then one update, although the data remains the same. The problem/behaviour is that if the table has a relation to other table and this row has childrows when is deleted it throws an exception and the update is canceled. This is right, but in the user optic he has changed nothing and receives a message that he can't delete that row. What's the best way to deal with this situation? Thanks in advance Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104048
  - 5
    - Question: SqlDataReader.GetSqlXXXX vs. SqlDataReader.GetXXXX Would there be any performance gain to calling SqlDataReader.GetSqlXXXX instead of SqlDataReader.GetXXXX ? Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104047
  - 6
    - Dataset created in Code cannot be used in Crystal Reports designer Hello, I've created a Dataset in code on my form based on an OLEDB provider also created in code. I want to be able to use this dataset in a Crystal Report, but the dataset is not listed in the report designer? I'm not sure how to resolve this problem, any ideas are helpfull. Thank you Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104043
  - 7
    - OleDbParameter doesn't update value correctly This is a multi-part message in MIME format. ------=_NextPart_000_0049_01C5052D.3F21E3E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I use OleDbCommand to insert a new row to Access database. We reuse = instance of OleDbCommand more than one times.=20 =20 example: - The value of the first record is QueryID =3D 1 and = BooleanOperator =3D "And"; Add to database correctly; - The value of the second record is QueryID =3D 1 and = BooleanOperator =3D "Or"; Add to database not correctly; =20 Here is the value in database: QueryID BooleanOperator 1 And 1 Ord =20 =20 The value of BooleanOperator is "Ord". Could you tell me how to = solve this problem, please? Here is the code: OleDbCommand cmdClause =3D new OleDbCommand(); cmdClause.CommandType =3D CommandType.StoredProcedure; cmdClause.CommandText =3D SP_QUERY_SAVEQUERYCLAUSE; =20 cmdClause.Parameters.Add( "@QueryId", OleDbType.Integer); cmdClause.Parameters.Add( "@BooleanOperator", OleDbType.VarWChar, = 50); using (OleDbConnection cn =3D new = OleDbConnection(this.ConnectionString)) { cn.Open(); cmdClause.Connection =3D cn; foreach (QueryClause clause in queryClauses) { cmdClause.Parameters["@QueryId"].Value =3D queryId; cmdClause.Parameters["@BooleanOperator"].Value =3D = clause.BooleanOperator; =20 cmdClause.ExecuteNonQuery(); } } Thanks, Hung ------=_NextPart_000_0049_01C5052D.3F21E3E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1479" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>    I use OleDbCommand = to insert a=20 new row to Access database. We reuse instance of=20 OleDbCommand more than one times. </FONT></DIV> <DIV><FONT face=3DArial size=3D2>    </FONT></DIV> <DIV><FONT face=3DArial size=3D2>    = example:</FONT></DIV> <DIV><FONT face=3DArial = size=3D2>        -=20 The value of the first record is <FONT color=3D#0080ff>QueryID =3D = 1</FONT> =20 and <FONT color=3D#0080ff>BooleanOperator =3D "And"</FONT>; Add=20 to database correctly;</FONT></DIV> <DIV><FONT face=3DArial = size=3D2>        -=20 The value of the second record is <FONT color=3D#0080ff>QueryID =3D = 1</FONT> and=20 <FONT color=3D#0080ff>BooleanOperator =3D "Or"</FONT>; Add to database = not=20 correctly;</FONT></DIV> <DIV><FONT face=3DArial size=3D2>    </FONT></DIV> <DIV><FONT face=3DArial = size=3D2>        Here is=20 the value in database:</FONT></DIV> <DIV><FONT face=3DArial size=3D2>       =20 QueryID    BooleanOperator</FONT></DIV> <DIV><FONT face=3DArial size=3D2>       =20 1            =    =20 And</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>        1  &nb= sp;=20             Or<FONT=20 color=3D#ff0000>d</FONT>    </FONT></DIV> <DIV><FONT face=3DArial size=3D2>    </FONT></DIV> <DIV><FONT face=3DArial = size=3D2>        The=20 value of BooleanOperator is "<FONT color=3D#ff0000>Ord</FONT>". Could = you tell me=20 how to solve this problem, please?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>    Here is the = code:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>    OleDbCommand = cmdClause =3D=20 new OleDbCommand();</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>    = cmdClause.CommandType =3D=20 CommandType.StoredProcedure;</FONT></DIV> <DIV><FONT face=3DArial size=3D2>    = cmdClause.CommandText =3D=20 SP_QUERY_SAVEQUERYCLAUSE;</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>    </FONT></DIV> <DIV><FONT face=3DArial size=3D2>    = cmdClause.Parameters.Add(=20 "@QueryId", OleDbType.Integer);</FONT></DIV> <DIV><FONT face=3DArial size=3D2>    = cmdClause.Parameters.Add(=20 "@BooleanOperator", OleDbType.VarWChar, 50);</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>    using = (OleDbConnection cn =3D new=20 OleDbConnection(this.ConnectionString)) {</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>          &nbs= p;    =20 cn.Open();</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>          &nbs= p;     cmdClause.Connection=20 =3D=20 cn;<BR>           =     =20 foreach (QueryClause clause in queryClauses)=20 {<BR>           &n= bsp;       =20 cmdClause.Parameters["@QueryId"].Value =3D=20 queryId;<BR>          &= nbsp;        =20 cmdClause.Parameters["@BooleanOperator"].Value =3D=20 clause.BooleanOperator;</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>          &nbs= p;        =20 </FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>          &nbs= p;        =20 cmdClause.ExecuteNonQuery();</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>          &nbs= p;    =20 }</FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>          &nbs= p;=20 }</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thanks,</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Hung</FONT></DIV></BODY></HTML> ------=_NextPart_000_0049_01C5052D.3F21E3E0-- Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104030
  - 8
    - Catching ConstraintExceptions on dialogs and allowing the user to keep trying... If I call EndCurrentEdit on a data linked form which contains data that breaks a unique constraint, a ConstraintException is thrown as expected. If I catch that exception and call EndCurrentEdit again, it appears to work but the row is in fact returned to its original state. It is as though the second call was actually CancelCurrentEdit. What is the correct way to handle a ConstraintException such that the user can change what they've entered and try to commit the record again? Here is some code to reproduce my problem. Create a form and add a single TextBox to it. Place the following code into the Form.Load event. DataTable table = new DataTable("TABLE"); table.Columns.Add("COLUMN"); table.Constraints.Add("CONSTRAINT", table.Columns[0], false); table.Rows.Add(new object[] {"first"}); table.Rows.Add(new object[] {"second"}); //binding manager points to the first row which contains "first" textBox1.DataBindings.Add("Text", table, "COLUMN"); //change it to clash with the second row textBox1.Text = "second"; try { this.BindingContext[table].EndCurrentEdit(); Console.WriteLine("success"); } catch(ConstraintException) { Console.WriteLine("failed"); } try { this.BindingContext[table].EndCurrentEdit(); Console.WriteLine("success"); } catch(ConstraintException) { Console.WriteLine("failed"); } You will notice that it failed the first time and succeeded the second. Nathan Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104027
  - 9
    - Continue to develop a old project at a new web path I have developed my web module project at my test machine under a different web path ex. c:\inetput\wwwroot\myProject and tested it. After it work find there I coped it to my real web site under the d:\inetput\wwwroot\script\myProject. How can I continue the develop the project under the new path? I tried to open the project (or solution ) under the new path, .NET will error on this, saying "Unable to openWeb project 'myProject'. The d:\inetput\wwwroot\script\myProject does not correspond to the URL 'http;//localhost/myProject'. Of course. Even with retry to use a different share path (new one) : d:\inetput\wwwroot\script\myProject still won't do? Does Any one how can I continue this? THanks. C Chang Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104026
  - 10
    - # of pooled connections seem too high We have a production web site that's data intensive (save user input to DB and query for displaying) with the ASP.NET app part on one W2K server and SQL 2000 DB on another W2K server. I have set up performance logs to log # of pooled database connections on the web server every 60 seconds (.NET CLR Data(_global_)\SqlClient: Current # pooled connections). The # of connections is constantly higher than the # of active ASP.NET sessions. E.g. right now there are 25 connections and 18 active session. Is this normal? I would think the # of pooled DB connections shouldn't be higher than the # of active sessions since the connections in the pool are shared and user certain pauses between loading pages and entering data. Last year during peak usage when the # of active sessions got around 180 and # of connections peaked at around 150, the SQL Server experienced deadlock as too many processes were waiting for one another. User experience was very bad during that period needless to say. We redesigned the data layer and made very sure that every call to the database closes the connection explicitly. So I'm concerned that the effort didn't resolve anything and am afraid the same problem would happen again. All the calls to the database are straightforward, no transactions involved. Could anyone point out what the potential problems we might have? Thanks a lot Bob Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104024
  - 11
    - infoMessage Truncated I have a large SP that has various print messages throughout. I want to view these print messages from .Net. As such I wire up an infoMessage event handler to the connection. If executed using executeNonQuery it will return all messages fine. Using executeScalar, I only ever get the first message. This does not seem to be desirable behaviour. Is this by design or a bug??? Thanks, - Lachlan Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104023
  - 12
    - Domains Hello, I have a made client application that adds records to a database on a domain. Every now and then the database will create duplicate records. I have closely checked the code and have not seen anyreason for this to be. I am using parameters and using ExecuteNonQuery to update the database with a connection object that is directed to the database on a domain. Any idea why this is happening and if so what is a good work-around? Thanks, Chuck Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104019
  - 13
    - help to nav through DataReader I need to move through some records to get the first and last value of a field. The od days there was movefirst, etc in ADO. I know .ExecuteScalar() can get me the first record. But if I use a DataReader how do I get the last record's information for a specific field in that record? I've looked at DataSets and can't seem to get that going either. Any ideas? Thanx. Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104016
  - 14
    - Concurrency in N tiers Hi, I'm creating the arquitecture of a N tiers application, at the moment I decided to use classes to inherit from Dataset in order to represent the Data Entities. However I hit the concurrency problem and I'm not sure how to prevent it (timestamps, sp, etc.) Can anyone recomend me a good strategy to manage concurrency? TIA Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104014
  - 15
    - Cannot generate DataSet from SQL SP in VS 2003 (Biztalk 2004!!) I have VS.NET 2003 and also have BizTalk 2004 installed on the machine. Usually it is possible to add a DataSet to a project and then drag a stored procedure to the design surface to create a strongly typed dataset. With BizTalk 2004 installed the schema designer comes up as that from BizTalk and this does not give a design surface to drag and drop to. How can I make VS show the default schema designer instead of the BizTalk 2004 one so that I can create datasets as before?? Thanks for your help. -------------------------- Shaun Venus emailid: sunevnuahs domain: hotmail.com -------------------------- Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104010
  - 16
    - preventing new rows in datagrids when using relations hi, i am using 2 datagrids tied together with datarelations in a dataset. That part works great. i need to suppress the new row on the datagrid but cannot use the readonly properties on the datagrid so i can allow the user to check off on bool columns. I know dataviews can prevent it with allowNew set to false but i can't seem to use dataviews with the relationships thanks al Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104002
  - 17
    - How do I filter a child table through its parent? Hi all! I am trying to apply a filter to a table in a dataset using criteria from a parent table, but I have no luck so far. Having a dataset with tables for Customers and Orders, and a relation between them, I wish to find the highest order for customers in a specific region. Creating a view for Customers filtering them to only display Customers in Region='A' is no problem, but as I try accessing Orders, it is not filtered and so I have no way of finding the most expensive order for the region 'A'. Just using the same criteria on the dataview for Orders does not work, neither "Region='A'" nor "Customers.Region='A'" can be applied to the Orders table. I am actually not interested in displaying this information, I need to use values from the filtered version of Orders for extensive calculations and aggregations, so attaching my dataset/tables/views to any userinterface components won't help. Any ideas greatly appreciated! Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104001
  - 18
    - RowNotInTableException Using an XmlDataDocument together with DataSet (typed) Doing some loads and ImportNode/AppendChild So far so good, but when I do some ImportNode/ReplaceChild, the problem starts. Code snippet: : XmlDataDocument destinationDoc; destinationDoc = new XmlDataDocument(myTypedDataset); : destinationDoc.DataSet.EnforceConstraints = false; XmlDocument sourceDoc = new XmlDocument(); sourceDoc.LoadXml(aString); XmlElement importElement = (XmlElement)destinationDoc.ImportNode(sourceDoc.DocumentElement, true); XmlElement destinationElement = (XmlElement)destinationDoc.DocumentElement.SelectSingleNode(path, nsmgr); destinationElement.ParentNode.ReplaceChild(importElement, destinationElement); This last codeline throws the RowNotInTableException with additional info: "This row has been removed from a table and does not have any data. BeginEdit() will allow creation of new data in this row" I understand that a row (representing my destinationElement) has been deleted, but it must be deleted because I want to replace it with another element (and thereby another row (and possibly childrows)). Anyone knows how to fix ? Tag: What is the Best way to fetch the most recent updated row in a database Tag: 104000
  - 19
    - Looking for ideas on how to handle a specific data situation. Hello, I'm looking for options in handling a certain data situation. Data is stored in Oracle. There is a database that contains tables with common information. For example, it contains a table called USER_COMMON. This table has a key field (USER_KEY) and some other fields containing data (login, e-mail, etc) that all applications will need about a user. Then a specific application has its own database and one of the tables there is called USER. This table also has a USER_KEY column with the same value as the column in the USER_COMMON table. I've implemented a layered approach to the data handling. One project has all the data adapter objects, another project has the typed datasets created from the data adapter objects and a third project has the business layer objects which use the data adapter objects and return dataset objects to the GUI layer. This approach has worked great for me when I"m just going against one database. However, now I want to have one object to represent the combination of the USER_COMMON and USER tables. My first thought was "I'll just create a view on Oracle". Then I could create a data adapter from the view and so on. That fell apart because Oracle doesn't allow you to update data thru a view. Next I thought I could put both tables in one typed dataset and do a DataRelation to join them. Again, I can't figure out how to do updates using this approach. Then I saw an article (http://support.microsoft.com/default.aspx?scid=kb;en-us;325688) on creating a dataset JOIN helper class but that didn't seem to have a way to update the backend data either. I noticed this article is quite old so there may be something better out now. I may have missed something but my eyes are glazed over now and I'm looking for some fresh ideas. Any help/tips would be appreciated. Thanks! David. Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103998
  - 20
    - datagrid paging and XML Hi, Just curious. If you use the pagingsystem delivered with ASP.NET datagrid control ... will the system access the database each time you ask the next page ... or will all data be loaded into a hidden XML-file which will be used when going to the next page. If XML is the answer then ... 1) is it stored in memory or on disk 2) when will the file be deleted ... considering IIS doesn't know how long a user will keep the page in his browser. Thanks a lot. -- Kind regards, Perre Van Wilrijk, Remove capitals to get my real email address, Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103993
  - 21
    - SELECT Distinct from DataTable, without using SQL Hi, I have the following query I run using SQL: Select Distinct col1 as D1, col2 as D2, count(col3) as CountOf from MyTable group by col1, col2 order by col1 I need to be able to get the same result without using SQL, by querying an off-line DataSet. XPath might be able to do the job, but it's completely new to me and it appears that the data must be in a pre-defined format (i.e. sorted so that "preceding-sibling" can be used). Does anyone know of an XPath query that could do this, or are there methods on DataSet/Table that can achieve this same result? Thanks in advance, Duncan Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103987
  - 22
    - parent, child, grandchild schema I have a database with tables as follows FILM * filmID FilmTitle FilmLength FILMKEYWORD *filmID *keywordID KEYWORD *keywordID *Keyword * denotes primary key Keywords are simply text descriptors for use by a search engine. Films can have multiple keyword associations. ---------- I am trying to build an interface which would display all three tables in datagrids. As the user moves through the items in the FILM (parent) grid, the FILMKEYWORD grid should track the filmID to filmID relation defined in the dataset schema to display the associated filmID keywordID rows in the FILMKEYWORD (child) grid. (this part is actually working!) The KEYWORD (grandchild) grid should track the keywordID keywordID relation in the schema so that clicks on the FILMKEYWORD grid display the corresponding keywords. ------------ I followed a nice walkthrough I found to get the parent/child part of the system to work but the keyword (grandchild) grid doesn't display the keyword when I click in the filmkeyword grid although it shows the column names in the fixed row at the top. I'm also wondering; when I define the keywordID to KeywordID (grandchild) relation in the dataset schema between FILMKEYWORD and KEYWORD, the relationship dialog box will not enable the OK button unless I choose field associations for both keys in the FILMKEYWORD compound-primary-key though only the keywordID actually relates to the foreign key, keywordID, in the KEYWORD table. To fix this, I removed the primary keys in the FILMKEYWORD schema and redefined just keywordID as the primary. This allowed me to define the relationship but I'm thinking it will cause trouble when update time comes. Also, the FILMKEYWORD/KEYWORD relationship doesn't show up as an option in the grid datamember property like the FILM/FILMKEYWORD relationship did. This is probably why the keyword grid won't track the relationship (doh!) I hate to whine but I can't be the only nerd trying to do this and it shouldn't be this hard! Is there a walthrough tutorial for a parent child grandchild system like this somewhere? Tips and hints are welcome! Thanks, Roger Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103986
  - 23
    - Please help! Setting SelectCommand.Transaction property blocks (locks thread). Hi All, I don't know if I'm missing something, but my code locks (no exception) the third time I set the data adapter's select command Transaction property. The code thats called looks something like: // processing.... transaction = connection.BeginTransaction (); dataAdapter.SelectCommand.Transaction = transaction; // processing.... transaction.Rollback (); The code above hangs the third time its called! Help! Nick Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103985
  - 24
    - Newby. ADO query failing I'm a total newby (except for Borland Delphi database handling). This function is being run on compact framework. MS visual studio 2003 c# .net for pocketpc. When stepping through in debug mode, the program is failing on the "rs.Open(QueryString[nCount],cn" line. The exception.message I'm getting is "InvalidConnection" I'm using the same code elsewhere in the program to open the AdoceConnection object. There I fill a dataset which I bind to a data grid. That works fine so I believe my cn object is fine. (My localdatabase has spaces in the path, does that matter?) public static string LocalDatabase = "\\My Documents\\PocketVibrAPro.cdb"; public static string LocalConnection = "Data Source=" + LocalDatabase; public static int CountReadings(ref int TotalReadings) { int[] result = new int[2]; string[] QueryString = new string[2]; QueryString[0] = "SELECT * FROM tblDataFiles"; QueryString[1] = "SELECT * FROM tblAssets"; if (DatabaseExists) { try { for (int nCount = 0; nCount < 2; nCount++) { AdoceConnection cn = new AdoceConnection(LocalConnection); try { InTheHand.Data.Adoce.Recordset rs = new InTheHand.Data.Adoce.Recordset(); try { rs.Open(QueryString[nCount], cn);// << InvalidConnection error here try { result[nCount] = rs.Count; } finally { rs.Close(); } } finally { rs.Dispose(); } } finally { cn.Close(); cn.Dispose(); } } } catch(Exception e) { SystemHelper.ShowException("Error counting records", e); return 0; } } TotalReadings = result[1]; return result[0]; }//public static int CompletedReadings(ref int TotalReadings) Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103981
  - 25
    - DataRelation in DataGrid Hi, I'm using a Dataset containing two tables, e.g. Person, Customer. Table Person has some Fields like PersonId, LastName, FirstName, Adress, etc. My Table Customer has two fields fk_person and CustomerNo. I have an DataRelation with these tables between field PersonId and fk_person. Further, I have added the columns LastName and FirstName to my DataTable Customer, which is connected to the DataRelation. All I want to do know, is to display the Customer table with the fields of the person table in a datagrid. When I bind this DataTable to a dataGrid instance, I can see all the fields I want to see, but I can't edit the fields of the person table, e.g. LastName. Now, I know that binding to a DataRelation is somehow like binding to a dataview. But how can I make my person tables's field writeable anyway ? Any idea, or is it impossible to edit these fields ? Is there another way to connect these two tables in the Dataset ? I don't want to join these two tables in database, because I want to use the command builder functionality of the dataadapter, which only can be used when data comes from not more of one table. Any tips or other ways to go ? Thanks in advance, Patrick Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103974
- Next
  - 1
    - ASP.NET connection file problem Hello friends, I have some problem in my ASP.NET connection 'Connection file code <% dim connstr connstr=("Provider=Microsoft.Jet.OLEDB.4.0;data source="&server.mappath("database/it.mdb")&"") %> follwoing error message occur when i execute the page Compiler Error Message: BC30451: Name 'connstr' is not declared. 'Below is the file where i am using it  <script language="VB" runat="server"> Public Sub searchsh() Response.Write ("<table align=center width=100% cellspacing=4 cellpadding=0 border=0><tr>") Response.Write ("<td width=30% align=middle valign=top bgcolor=SaddleBrown>") Response.Write ("<table align=center width=100% cellspacing=2 cellpadding=0 border=0><tr>") Response.Write ("<td width=100% align=middle><font size=2 color=Azure face=Arial, Helvetica, sans-serif>") Response.Write ("<b>NAME</b></font></td></tr></table></td>") Response.Write ("<td width=30% align=middle valign=top bgcolor=SaddleBrown>") Response.Write ("<table align=center width=100% cellspacing=2 cellpadding=0 border=0><tr>") Response.Write ("<td width=100% align=middle><font size=2 color=Azure face=Arial, Helvetica, sans-serif>") Response.Write ("<b>ADDRESS</b></font></td></tr></table></td>") Response.Write ("<td width=15% align=middle valign=top bgcolor=SaddleBrown>") Response.Write ("<table align=center width=100% cellspacing=2 cellpadding=0 border=0>") Response.Write ("<tr><td width=100% align=middle>") Response.Write ("<font size=2 color=Azure face=Arial, Helvetica, sans-serif><b>HELLO</b></font></td>") Response.Write ("</tr></table></td>") Response.Write ("<td width=15% align=middle valign=top bgcolor=SaddleBrown>") Response.Write ("<table align=center width=100% cellspacing=2 cellpadding=0 border=0>") Response.Write ("<td width=100% align=middle><font size=2 color=Azure face=Arial, Helvetica, sans-serif><b>Email</b></font></td>") Response.Write ("</table></td></tr>") Dim sqlq="SELECT * from softhouse where cityID Like'"&Request.Form("city")&"'" Dim objdatareader as new OLEDBDataReader Dim cmdobj as new OLEDBCommand(sqlq,objconn) 'objDataReader=cmdobj.Executereader() </script> <%if Request.Form("searchchoice")=1 then searchsh end if %> Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103965
  - 2
    - ADODB reference broken when I take "get latest" from sourcesafe Whenever I tried to "get latest" from VSS, I get the following error message. The referenced component ADODB could not be found. The project file '' has been renamed or no longer in the solution. The reference to ADODB is broken in my windows project. Everytime I need to manually add reference. I tried with different versions of ADODB (found in both .NET tab and COM tab). But no progress. I use vb.net 2003. Thanks and regards J Justin Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103959
  - 3
    - Connection Object Hello, Is there a way to connect to outlook's address book with ADO.net and fill a dataset with it? Any suggestions will be greatly appreciated. Thanks, Chuck Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103952
  - 4
    - Executing SP while data read is open? I execute an SQL stored procedure that returns 5 records ("SqlCmd_SpamUpdate_spfBlackUnprocessed"). I then open a data reader, loop through those five records while calling stored procedures that does updates. The second SP needs information from the 5 records being returned. The problem is that when I try to execute the stored procedure inside of the data reader loop, I get an error that a data reader is already open and should be closed before executing the stored procedure. Any ideas how I can fix this? Thanks, Brett. cn_mydatabase.Open() Dim drspfblacklist As SqlDataReader drspfblacklist = SqlCmd_SpamUpdate_spfBlackUnprocessed.ExecuteReader() While drspfblacklist.Read -- do something --- With SqlCmd_BlacklistUpdateStatus .CommandType = System.Data.CommandType.StoredProcedure .Parameters("@messageid").Value = drspfblacklist.Item("message_id") .Parameters("@subpart").Value = BlackListStatus .ExecuteNonQuery() End With End While drspfblacklist.Close() SqlCmd_SpamUpdate_spfBlackUnprocessed.Dispose() cn_mydatabase.Close() Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103949
  - 5
    - How to check for query field length I have the follow asignment: txtDescription.Text = dtrSlb("slbDesc2") If the field returned is Null i get a run time error. I can check for if (dtrSlb("slbDesc1").GetType.ToString() = "System.DBNull") then .... But there has to be a better way. Aslo what's the best way t oget the length of the returned field from a query? Thanks for help on this simple question kes Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103948
  - 6
    - Async Calls to DB does not help in the efficiency department? Hi Guys, I was recently reading an article: http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/dnservic= e/html/service10012002.asp [Excerpt] "One of the most common types of I/O operations that occur within a Web method is a call to a SQL database. Unfortunately, Microsoft=AE ADO.NET does not have a good asynchronous calling mechanism defined at this time, and simply wrapping a SQL call in an asynchronous delegate call does not help in the efficiency department. Caching results is sometimes an option, but you should also consider using the Microsoft SQL Server 2000 Web Services Toolkit to expose your databases as a Web service. You will then be able to use the support in the .NET Framework for calling Web services asynchronously to query or update your database." It stated that perhaps wrapping an sql call in an async delegate doesn't really provide any performance gains. Is this true? Or does this only apply to webmethods? Maybe I'm misinterpreting what is being said? Regards DotnetShadow Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103944
  - 7
    - SQLHeper ExecuteNonQuery - returning values Hi I have a Stored Proc in SQL server, which creates a record key when a record is created. I want to return the value back to my code, once the record has been created. I am using SQLHelper (Data Application Blocks for SQLServer), and calling the ExecuteNonQuery method in the SQLHelper class... Does anyone have any sample code from their C# code, or T-SQL code to help?? Thanks Paul Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103941
  - 8
    - Oracle Datareader - Datatype I have a table in 9.2 and a field as INT. When I use an OracleDataReader to query the table the field is being returned as Decimal. Being not to familiar with Oracle does anyone have an idea why? CREATE TABLE DWSDLAYERS ( LAYERID NUMBER, LAYERNAME VARCHAR2 (128), LAYERTYPE NUMBER NOT NULL, LAYERDESCRIPTION VARCHAR2 (3000), FEATURETYPE NUMBER DEFAULT 1) TABLESPACE USERS NOLOGGING PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 ) Query = "Select LAYERID, CATEGORY, DESCRIPTION, LAYERTYPE, FEATURETYPE from DWSDLAYERS where LAYERNAME = :LAYERNAME" Debug Ouput: ?OraReader("FEATURETYPE") 1D {Decimal} [Decimal]: 1D Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103939
  - 9
    - Implementin Three-Tier App. Hi, I'm learning about three-tier programing and i want to implement this in a simple windows forms Application (not web), i'm using an Access 2000 Data Base, the objective of my application is to stores data from cd's into the data base, so i have 4 related tables, ("CdCategories", "Cds","Folders","Files"). I want to use Typed Data Classes, so i made one class for each table and one Typed DataSet for each relation (each of this typed datasets contains a pair of datatables with a defined DataRelation, for example a "Folder-Files" dataset). The problem that i have is that i don't know exactly how to implement all this especially when creating the BLL and the DAL layers. I've allready tryed a couple of thing but i allways fall into a death end. I tried to find examples on the web but i've only found web applications related examples. If any of you could give me some ideas i`ll be thanksfull. Thank you. Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103935
  - 10
    - Common SQL connection help Can someone share with me code for the following task please? 1) User clicks a button(button1), to bring up a file location window for the user to select the SQL Server 2000 and Pub database on the network(not onhis/her current machine so the user needs to create the correct path). 2)That user selction will then create the SQL connection. 3) ListBox1 on Form1 will then show the authors in the Pubs database. Thank you for any help with this, I think once I get past this point I will break thru this wall I have hit for connecting to a SQL server that is not my workstation. Mike Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103934
  - 11
    - get record count from stored procedure without passing DATA back? Is it possible in ADO to execute a (select) stored procedure, but just return the number of records, not the actual data. My client has some stored procedures which if executed with certain parameters can return thousands of rows. I would like to execute a check prior to returning these rows across the (very slow) network ** I understand that the SP must execute on the server. It is just the passing of the data back which concerns me. ** I cannot change the stored procedures, only the ADO code. Thanks. Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103930
  - 12
    - Data Controls or Code I'm fairly new to .NET, so bear with me if this question is naive. But do most developers use the VS data controls for connections, adapters, and datasets, or do you write all that code manually? And if you use the controls, how do you wrap your connection and adapter code so that you can easily switch between SQL Server, OLE DB, and ODBC? I've been writing the code manually, and I'm comfortable with that, but I'm wondering whether I'd be more productive using the Visual data controls. Thanks, Steve Murphy Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103927
  - 13
    - need to know the primary key of a table Hi, in order to know the primary key of a table in my dataset, i've the folowing code sql "select * from table1" create a dataadapter with this sql DA fill the dataadapter in a dataset DS DS.Tables(0).PrimaryKey but the expression returns me an empty array, is there any problem? is there another way to get the primary key of a table. I use vb.net and an MSACCESS2000 database. Thanks Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103923
  - 14
    - Copying from DBF into MDB - alternative to ADO method Hi! Currently we are using the following method to copy (or link in this case) a DBF table into an Access database. I'm running into very odd problems with using ADO within .NET and was hoping i could eliminate the need for referencing the ADO libraries by rewriting the code in ADO.NET. Here is a snippet of the original code: connMDB = new ADODB.ConnectionClass(); connMDB.Open( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MDBPathAndFileName_ + ";User ID=Admin;Password=", null, null, 0 ); ADOX.Catalog cat = new ADOX.CatalogClass(); cat.ActiveConnection = connMDB; //Create the new table. ADOX.Table tbl = new ADOX.TableClass(); tbl.Name = NewTableName_; tbl.ParentCatalog = cat; //Set the properties to create the link. tbl.Properties[ "Jet OLEDB:Create Link" ].Value = true; tbl.Properties[ "Jet OLEDB:Link Datasource" ].Value = DBFPath_; tbl.Properties[ "Jet OLEDB:Link Provider String" ].Value = "Dbase IV"; tbl.Properties[ "Jet OLEDB:Remote Table Name" ].Value = DBFName_; //Append the table to the tables collection of the catalog. cat.Tables.Append( tbl ); ---------- Does anyone know of an ADO.NET equivalent? Thanks in advance, Charles Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103920
  - 15
    - ARITHABORT.... I have a stored procedure that is being called from ado.net and it is failing with an exception that is related to the option ARITHABORT - 'UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'. This stored procedure works perfectly fine from query analyzer and from enterprise manager in sql server. Is this a problem with ado.net? The database is sql server 2000 with .net framework version 1.1 Cheers in advance Ollie Riches Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103917
  - 16
    - Execute Query on a TOTALLY disconnected DataSet Hi All Is there a way to Execute a query (possibly including two tables and joins) on a totally disconnected dataset. By 'Totally Disconnted' , I mean there is no DB and there is no DBConnection / DBCommand - all the data and structure is in memory. The query is to be used to databind a grid, so some sort of view is needed ( i.e. its not a one time query ). Its like having a mini in-memory DB on the client side and looking for a way to have a view running stored procedure. ( NOTE: DataView's RowStateFilter and DataTables Select might not be helpful in this case ) Thank You rawCoder Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103915
  - 17
    - db file lock problem, permissions help I'm having a problem with my test server where I get the following message when i run a simple test app created in VisualStudio.net: The Microsoft Jet database engine cannot open the file 'E:\WebSites\DamarStone\db\damarstone.mdb'. It is already opened exclusively by another user, or you need permission to view its data. I know i need to change a permission. The question is which one and wher do i find it? thank oyu kes Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103914
  - 18
    - Timeout error when filling an OleDbDataAdapter Hi, In a ReadCommited transaction I update a row 'A' in a table. Before the transaction ends, another transaction is trying to read ANOTHER row 'B' from the same table and fill a dataset using an OleDbDataAdapter but it seems to be locked and I get a timeot error. Using sp_lock I can see that for row 'A' the table is locked in X mode, type KEY (the table and the page are IX locked), for row 'B' the lock is KEY type and S mode and the status is WAIT. If I try to get data from the row 'B' with an OleDbCommand.ExecuteScalar there is no problem (so, as far as I can understand means that the row itself is not locked), but I cannot get the data with and OleDbDataAdapter.Fill. Is it not possible to do that? Thanks -- Message posted via http://www.dotnetmonster.com Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103912
  - 19
    - Null Exception on OleDbDataAdapter.Fill I am getting Null Exception error when I try to fill a dataset. I am providing a value for my parameter so I'm not sure, what's going on. Please see below..... Thanks Dim cmd1 As OleDbCommand = New OleDbCommand cmd1.Parameters.Add("@adate", OleDbType.DBDate) cmd1.Connection = mycnn cmd1.CommandType = CommandType.Text cmd1.CommandText = "SELECT * FROM Mytable WHERE adate = @adate" MsgBox(cmd1.CommandText.ToString) Dim ds As DataSet = New DataSet Dim da As Data.OleDb.OleDbDataAdapter = New Data.OleDb.OleDbDataAdapter da.SelectCommand = cmd1 cmd1.Parameters("@adate").Value = #1/3/2005# Try da.Fill(ds, "Mytable") Catch ex As OleDbException MsgBox(ex.Message) End Try Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103904
  - 20
    - string name = dataSet.GROUP.FindByID_GROUP(id_group).GROUP_NAME invokes NullReferenceException I vant to receive group name by automaticalyy generated function FindByID_GROUP in VS C#. I know, that mentioned function returns string type value. What is wrong with this line? Why this exception occures, altrought I know that argument id_gropu is properly, and I am sure that the value in column GROUP_NAME for this ID exists ??? Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103898
  - 21
    - update a DataSet object with a GridView hi, it seems, that a lot of people have the problem (including me), that isn't possible, to update a dataSet object with a gridview (without a data objet). does anybody knows something about this problem??? thanks. Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103895
  - 22
    - problem of using ACCESS I've written an asp.net application using ACCESS as the database. It works well first. But after a while, I found the application cound not make response to user's request quickly. It seems dead. You couldn't the ASP.NET page. Why? And how to solve the problem. Someone please help me. Here are something about the application: 1. the debug mode has been turned off in the web.config 2. I close opened connections it after I used. 3. I write a class for handling general operation to the ACCESS database like query, insert, update etc. And the all static methods. Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103885
  - 23
    - Three-Tier Application Examples. Where can i find simple Three-Tier Application Examples? Thank you. Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103879
  - 24
    - How to update dataset with multiple tables in C#? I create 2 tables, ParentTable and ChildTable. The row in ChildTable is referenced to ParentTable. But after updated and saved to database, the row in ChildTable doesn't refer to the correct row in ParentTable. You can see the following example to get the problems. Is there any good way to update multiple tables by using SqlDataAdapter.Update()? Thank you /* New Rows in ParentTable and ChildTable * * ------ParentTable------ * ID Animal * 1 Cat * * * ------ChildTable------ * ID NAME ParentID * "1 small cat 1" */ /* * But after the new rows saved back to the database, the new rows saved wrongly as: * * ------ChildTable------ * ID NAME ParentID * 1 small bird 1 * 2 small dog 2 * 3 small cat 1 =====> The ParentID doesn't dynamically change to 3 but keep the same as 1. * This makes it doen't reference to the correct row in ParentTable */ ------------------------------------------------------------------------------------ I try to use 2 different ways to update tables. But they both result in the same problem. The first way I use is to follow the update sequence recommended by book: adapter1= new SqlDataAdapter("Select * from ChildTable", con); SqlDataAdapter adapter2= new SqlDataAdapter("Select * from ParentTable", con); SqlCommandBuilder sb = new SqlCommandBuilder(adapter1); SqlCommandBuilder sb1 = new SqlCommandBuilder(adapter2); adapter1.Update(NewDataSet.Tables["ChildTable"].Select(null, null, DataViewRowState.Deleted)); adapter2.Update(NewDataSet.Tables["ParentTable"].Select(null, null, DataViewRowState.Deleted)); adapter2.Update(NewDataSet.Tables["ParentTable"].Select(null, null, DataViewRowState.ModifiedCurrent)); adapter2.Update(NewDataSet.Tables["ParentTable"].Select(null, null, DataViewRowState.Added)); adapter1.Update(NewDataSet.Tables["ChildTable"].Select(null, null, DataViewRowState.ModifiedCurrent)); adapter1.Update(NewDataSet.Tables["ChildTable"].Select(null, null, DataViewRowState.Added)); ------------------------------------------------------------------------------------- The 2nd way is to use 2 SqlDataAdapter seperately to update tables, but still get the same problem : The Child table row "3 small cat 1" is wrong adapter1= new SqlDataAdapter("Select * from ChildTable", con); SqlDataAdapter adapter2= new SqlDataAdapter("Select * from ParentTable", con); SqlCommandBuilder sb = new SqlCommandBuilder(adapter1); SqlCommandBuilder sb1 = new SqlCommandBuilder(adapter2); adapter1.Update(NewDataSet.Tables["ChildTable"]); adapter2.Update(NewDataSet.Tables["ParentTable"]); Please refer to the UpdateTableMethod1() and UpdateTableMethod2() in the codes I provide. ------------------------------------------------------------------------------------ I just put some codes on the website. If you are also interested, you can just copy-paste the code on your file and run it. This code would generate 2 tables and insert content to the tables in NorthWindCs database in MsSql server. Please change the connection string to yours. using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace UpdateMultipleTables { /// <summary> /// Summary description for Form1. /// </summary> public class Form1 : System.Windows.Forms.Form { /// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.Container components = null; private System.Windows.Forms.DataGrid dataGrid1; //private conStr = "Server=Local; SSPT = true; "; private string conStr = "server=Local;User ID=sa; PWD=hello;database=NorthWindCs"; SqlDataAdapter adapter1 ; SqlConnection con ; private DataSet NewDataSet; public Form1() { InitializeComponent(); //Create the connection to DataBase CreateConnection(); //Create tables "ParentTable" and "ChildTable". And Insert content to the 2 tables CreateDB(); //Create a new row in both ParentTable and ChildTable CreateNewRow(); //I try 2 ways to update the tables. But both of them gave me the same problem, //The new row in "ChildTable": is WRONG! //UpdateTable1Method1(); UpdateTableMethod2(); } public void CreateConnection() { con = new SqlConnection(conStr); } //Create tables "ParentTable" and "ChildTable". And Insert content to the 2 tables public void CreateDB() { string commandText = "if exists (select * from NorthwindCS.dbo.sysobjects where name like 'ParentTable') "+ "Begin " + "DROP TABLE ParentTable "+ "CREATE TABLE ParentTable( ID INT IDENTITY(1,1) NOT NULL , Animal VARCHAR(40) NOT NULL ) "+ "INSERT INTO ParentTable( Animal) values('Bird') "+ "INSERT INTO ParentTable( Animal) values('Dog') "+ "END "+ "ELSE "+ "Begin "+ "CREATE TABLE ParentTable( ID INT IDENTITY(1,1) NOT NULL , Animal VARCHAR(40) NOT NULL ) "+ "INSERT INTO ParentTable( Animal) values('Bird') "+ "INSERT INTO ParentTable( Animal) values('Dog') "+ "END "+ "IF exists (select * from NorthwindCS.dbo.sysobjects where name like 'ChildTable') "+ "Begin "+ "DROP table ChildTable "+ "CREATE TABLE ChildTable( ID INT IDENTITY(1,1) NOT NULL , Name VARCHAR(40) NOT NULL , ParentID INT Not NULL) "+ "INSERT INTO ChildTable( Name, ParentID) values('small bird', 1) "+ "INSERT INTO ChildTable( Name ,ParentID) values('small dog', 2) "+ "END "+ "ELSE "+ "BEGIN "+ "CREATE TABLE ChildTable( ID INT IDENTITY(1,1) NOT NULL , Name VARCHAR(40) NOT NULL , ParentID INT Not NULL) "+ "INSERT INTO ChildTable( Name, ParentID) values('small bird', 1) "+ "INSERT INTO ChildTable( Name ,ParentID) values('small dog', 2) "+ "END "; adapter1 = new SqlDataAdapter(commandText, con); try { DataSet ds = new DataSet(); adapter1.Fill(ds); dataGrid1.DataSource = ds; } catch(Exception e) { Console.WriteLine(e.Message); } } /* New Rows in ParentTable and ChildTable * * ------ParentTable------ * ID Animal * 1 Cat * * * ------ChildTable------ * ID NAME ParentID * "1 small cat 1" */ /* * But after the new rows saved back to the database, the new rows saved wrongly as: * * ------ChildTable------ * ID NAME ParentID * 1 small bird 1 * 2 small dog 2 * 3 small cat 1 =====> The ParentID doesn't dynamically change to 3 but keep the same as 1. * This makes it doen't reference to the correct row in ParentTable */ //Create a new row in both ParentTable and ChildTable public void CreateNewRow() { //carete data in parent table NewDataSet = new DataSet("NewDataSet"); DataTable ParentTable = new DataTable("ParentTable"); DataColumn dc = new DataColumn("ID", typeof(int)); dc.AutoIncrement = true; dc.AutoIncrementSeed = 1; dc.AutoIncrementStep = 1; ParentTable.Columns.Add(dc); dc = new DataColumn("Animal", typeof(string)); ParentTable.Columns.Add(dc); NewDataSet.Tables.Add(ParentTable); DataTable ChildTable = new DataTable("ChildTable"); dc = new DataColumn("ID", typeof(int)); dc.AutoIncrement = true; dc.AutoIncrementSeed = 1; dc.AutoIncrementStep = 1; ChildTable.Columns.Add(dc); dc = new DataColumn("Name", typeof(string)); ChildTable.Columns.Add(dc); dc = new DataColumn("ParentID", typeof(int)); ChildTable.Columns.Add(dc); NewDataSet.Tables.Add(ChildTable); // DataRelation relation = new DataRelation("ParentChildRelation" , ParentTable.Columns["ID"], ChildTable.Columns["ParentID"], true); // NewDataSet.Relations.Add(relation); DataRow dr = NewDataSet.Tables["ParentTable"].NewRow(); dr["Animal"] = "Cat"; NewDataSet.Tables["ParentTable"].Rows.Add(dr); //carete data in child table dr = NewDataSet.Tables["ChildTable"].NewRow(); dr["Name"] = "small cat"; dr["ParentID"] = NewDataSet.Tables["ParentTable"].Rows[0]["ID"]; NewDataSet.Tables["ChildTable"].Rows.Add(dr); dataGrid1.DataSource = NewDataSet; } //This update method followed by the recommended sequence of updateing tables in Oreilly book. But it doesn't solve //my proble. The Child table row "3 small cat 1" is wrong public void UpdateTableMethod1() { try { adapter1= new SqlDataAdapter("Select * from ChildTable", con); SqlDataAdapter adapter2= new SqlDataAdapter("Select * from ParentTable", con); SqlCommandBuilder sb = new SqlCommandBuilder(adapter1); SqlCommandBuilder sb1 = new SqlCommandBuilder(adapter2); adapter1.Update(NewDataSet.Tables["ChildTable"].Select(null, null, DataViewRowState.Deleted)); adapter2.Update(NewDataSet.Tables["ParentTable"].Select(null, null, DataViewRowState.Deleted)); adapter2.Update(NewDataSet.Tables["ParentTable"].Select(null, null, DataViewRowState.ModifiedCurrent)); adapter2.Update(NewDataSet.Tables["ParentTable"].Select(null, null, DataViewRowState.Added)); adapter1.Update(NewDataSet.Tables["ChildTable"].Select(null, null, DataViewRowState.ModifiedCurrent)); adapter1.Update(NewDataSet.Tables["ChildTable"].Select(null, null, DataViewRowState.Added)); } catch(Exception e) { Console.WriteLine(e.Message); } } //I try to use 2 SqlDataAdapter to update tables, but still get the same problem : The Child table row "3 small cat 1" is wrong public void UpdateTableMethod2() { try { adapter1= new SqlDataAdapter("Select * from ChildTable", con); SqlDataAdapter adapter2= new SqlDataAdapter("Select * from ParentTable", con); SqlCommandBuilder sb = new SqlCommandBuilder(adapter1); SqlCommandBuilder sb1 = new SqlCommandBuilder(adapter2); adapter1.Update(NewDataSet.Tables["ChildTable"]); adapter2.Update(NewDataSet.Tables["ParentTable"]); } catch(Exception e) { Console.WriteLine(e.Message); } } /// <summary> /// Clean up any resources being used. /// </summary> protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows Form Designer generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.dataGrid1 = new System.Windows.Forms.DataGrid(); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit(); this.SuspendLayout(); // // dataGrid1 // this.dataGrid1.DataMember = ""; this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText; this.dataGrid1.Location = new System.Drawing.Point(16, 24); this.dataGrid1.Name = "dataGrid1"; this.dataGrid1.Size = new System.Drawing.Size(496, 216); this.dataGrid1.TabIndex = 0; // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(560, 294); this.Controls.Add(this.dataGrid1); this.Name = "Form1"; this.Text = "Form1"; ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit(); this.ResumeLayout(false); } #endregion /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main() { Application.Run(new Form1()); } } } *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103874
  - 25
    - NullReferenceException in call to DataTable Select I'm trying to use the Select() method on a DataTable but I'm getting a NullReferenceException. Here's the stack trace: at System.Data.Select.Evaluate(Int32 record) at System.Data.Select.FindFirstMatchingRecord() at System.Data.Select.GetBinaryFilteredRecords() at System.Data.Select.SelectRows() at System.Data.DataTable.Select(String filterExpression, String sort) I've found a work-around but I can't explain why it works. The work around is to call the Select() method once first without a filter argument, then to call it again with the actual filter argument you're interested in. Like this: DataRow[] records = ds.Tables["MyTable"].Select("", "C1, C2, C3, C4, C5, ID"); //note the empty string as the filter records = ds.Tables["MyTable"].Select(myFilter, "C1, C2, C3, C4, C5, ID"); //myFilter contains the actual filter value And for some reason the sorting string to the first Select() call has to be the same string that's used in the second Select() call. So doing this wouldn't work: DataRow[] records = ds.Tables["MyTable"].Select(""); records = ds.Tables["MyTable"].Select(myFilter, "C1, C2, C3, C4, C5, ID"); The reason I discovered this work-around is that if you copy and paste the entire call to the Select() method into one of the debugger watch windows, you'll get a message stating the same null reference exception as the value of the expression. But if you replace the filter with "" in the Select() call in the watch window, it will return the correct result as the expression value. Then if you step through the actual code it works (it doesn't throw the exception)! It's like the DataTable needs to be re-initialized before you can call the Select() method, even if you do it in the debugger watch window. Does anyone have any ideas about what's going on here? Thanks, Dave Tag: What is the Best way to fetch the most recent updated row in a database Tag: 103868

I am having an OleDBDataAdapter and a dataset. I fill the dataset with
a row and execute

Adapter.update(dataset,"Table Name");
Adapter.fill(dataset,"Table Name");

For the database table, my primary key is an auto number.

Currently, I am fetching the key using:

dataset.Tables["Table Name"].Rows[0].ItemArray[0]

It is working because this is the only row added into my table.
But I am certain that there should be a better alternative to this.
Waiting for response.

Jag

Top

Re: What is the Best way to fetch the most recent updated row in a database by Miha

Miha
Fri Jan 28 11:17:12 CST 2005

Are you using Access database?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

<jagrat@gmail.com> wrote in message
news:1106931590.480728.88580@c13g2000cwb.googlegroups.com...
>I am having an OleDBDataAdapter and a dataset. I fill the dataset with
> a row and execute
>
> Adapter.update(dataset,"Table Name");
> Adapter.fill(dataset,"Table Name");
>
> For the database table, my primary key is an auto number.
>
> Currently, I am fetching the key using:
>
> dataset.Tables["Table Name"].Rows[0].ItemArray[0]
>
> It is working because this is the only row added into my table.
> But I am certain that there should be a better alternative to this.
> Waiting for response.
>
> Jag
>

Top

Re: What is the Best way to fetch the most recent updated row in a database by W

W
Fri Jan 28 11:16:25 CST 2005

check out www.betav.com - Articles, MSDN - Retrieving the Gozoutas

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
<jagrat@gmail.com> wrote in message
news:1106931590.480728.88580@c13g2000cwb.googlegroups.com...
> I am having an OleDBDataAdapter and a dataset. I fill the dataset with
> a row and execute
>
> Adapter.update(dataset,"Table Name");
> Adapter.fill(dataset,"Table Name");
>
> For the database table, my primary key is an auto number.
>
> Currently, I am fetching the key using:
>
> dataset.Tables["Table Name"].Rows[0].ItemArray[0]
>
> It is working because this is the only row added into my table.
> But I am certain that there should be a better alternative to this.
> Waiting for response.
>
> Jag
>

Top

Re: What is the Best way to fetch the most recent updated row in a database by jagrat

jagrat
Fri Jan 28 11:25:43 CST 2005

Hi Miha,

I am using Access database.

Top

Re: What is the Best way to fetch the most recent updated row in a database by Miha

Miha
Fri Jan 28 15:36:44 CST 2005

Hi,

Check out
Retrieving Identity or Autonumber Values
.net help topic.
MSDN Oct link
(ms-help://MS.VSCC.2003/MS.MSDNQTR.2004OCT.1033/cpguide/html/cpconretrievingidentityorautonumbervalues.htm)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

<jagrat@gmail.com> wrote in message
news:1106933143.116234.202490@c13g2000cwb.googlegroups.com...
> Hi Miha,
>
> I am using Access database.
>

Top