Database

SqlDataAdapter.Update sometimes does not update

7-Oct-03 22:39

I suspect you've still got a BeginTransaction call outstanding on the sqlConnection1 object.

james-cxx2-Oct-03 10:55

james-cxx

2-Oct-03 10:55

As the subject suggests, I'm simply updating the database with SqlDataAdapter.Update and sometimes no update occurs, but usually it does. If it never worked, I'd know it was a problem with my code, but running twice the same steps doesn't always produce the same results.

I've read on the web that others have seen this behavior but haven't seen any fix or workaround. Anyone here experienced this or know a workaround?

-- James --

Re: SqlDataAdapter.Update sometimes does not update

james-cxx14-Oct-03 18:18

james-cxx

14-Oct-03 18:18

So I'm happy to report the problem was with my code and not with the .NET SqlDataAdapter. But I think the circumstances of the problem bear repeating so that others might avoid it.

Form A creates some SqlDataAdapters that fill a dataset and periodically refreshes it. At some point, due to user interaction, an instance of "Form B" is created and passed a reference to the SqlDataAdapters. Form B then finds a DataRow in one of the tables of the dataset, does some work on it, and then calls SqlDataAdapter.Update to put the changed DataRow in the database. And this worked _most_ of the time, but occasionally it would not - no data would be updated to the database.

It turns out that during Form A's periodic update, it would clear the datatable and the refill it. This process would cause Form B's datarow it was working on to get to a state of DataRowState.Detached. Since the DataRow was now detached, when form B would call SqlDataAdapter.Update, the info from the row would not go to the database because the DataRow is not associated with the DataSet that the adapter was working with. And there was no error to be reported in this circumstance.

So the moral of the story is this: Beware what's happening to your DataSet or DataTables while working with a DataRow. And it doesn't hurt to make sure the row hasn't somehow become detatched when you weren't watching.

-- James --

Logic

Anonymous2-Oct-03 3:58

Anonymous

2-Oct-03 3:58

I have 2 tables:Table1 and Table2 where I have ID and Dateentered.
The both tables can have the same id but different date.
If record in table 1 is oldest then in table2 select data in table1.
How do I do this?

Re: Logic

Mike Dimmick2-Oct-03 12:14

Weird problem while inserting a record

2-Oct-03 12:14

Assumption: you mean that you want to select the rows which have the lowest date, whichever table they come from.

On this assumption, I think doing a UNION of the tables is probably about right.

SELECT [ID], MIN( Dateentered ) AS MinDate
FROM Table1 UNION ALL Table2
GROUP BY [ID]

If you actually want to insert rows or update rows in table 2, that's a bit trickier, and would have to be done in two stages:

-- Insert any rows into Table2 that are in Table1
-- but not in Table2
INSERT INTO Table2
SELECT T1.[ID], T1.Dateentered
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.[ID] = T2.[ID]
WHERE T2.[ID] IS NULL
 
-- Set Table2's entries to the date in Table1,
-- if table1's are earlier
UPDATE Table2
SET Dateentered = T1.Dateentered
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.[ID] = T2.[ID]
WHERE
   [ID] = T1.[ID] AND
   T1.Dateentered < T2.Dateentered

2sky1-Oct-03 23:53

2sky

1-Oct-03 23:53

Hello,

I wonder if anyone can help me with this.

I try to insert a record in a DataTable, and everything works absolutely perfect. UNTIL I create a custom DataView object based on the table.

Here is a small sample, when you take the DataView myView = new DataView(table); line out of comment the record isn't at the correct position anymore.

Any help is much appreciated !!!!

DataTable	table = new DataTable("Table");<br />
<br />
			DataColumn colID = table.Columns.Add("ID", typeof(int));<br />
			DataColumn colName = table.Columns.Add("Name", typeof(String));<br />
<br />
			colID.AllowDBNull = false;<br />
<br />
			table.Rows.Add(new object[] {1, "Row 1"});<br />
			table.Rows.Add(new object[] {2, "Row 2"});<br />
			table.Rows.Add(new object[] {4, "Row 4"});<br />
<br />
			table.AcceptChanges();<br />
<br />
			//DataView	myView = new DataView(table);<br />
<br />
			foreach(DataRowView row in table.DefaultView)<br />
			{<br />
				Console.WriteLine(row["Name"]);<br />
			}<br />
<br />
			Console.WriteLine("-------------------------------------------------------");<br />
<br />
			DataRowView cindyView = table.DefaultView.AddNew();<br />
			cindyView["ID"] = 3;<br />
			cindyView["Name"] = "Row 3";<br />
			<br />
			Object[] ItemArray = cindyView.Row.ItemArray;<br />
			cindyView.CancelEdit();<br />
<br />
			DataRow cindy = table.NewRow();<br />
			cindy.ItemArray = ItemArray;<br />
			cindy.EndEdit();<br />
			table.Rows.InsertAt(cindy, 2);<br />
<br />
			table.DefaultView.RowStateFilter = DataViewRowState.None;<br />
			table.DefaultView.RowStateFilter = DataViewRowState.CurrentRows;<br />
<br />
			foreach(DataRowView row in table.DefaultView)<br />
			{<br />
				Console.WriteLine(row["Name"]);<br />
			}

Re: Weird problem while inserting a record

Mike Dimmick2-Oct-03 3:26

Re: Weird problem while inserting a record

2-Oct-03 3:26

It would help if you posted the actual output from this code and what you expected to see.

IIRC, unless you've applied a primary key constraint, or some kind of sort criteria, your view will show the records in the order they were inserted.

2sky2-Oct-03 3:35

2sky

2-Oct-03 3:35

I get the following output if I don't create the additional DataView:

Row 1
Row 2
Row 4
-----
Row 1
Row 2
Row 3
Row 4

And this is the output when the DataView object is created based on the table:

Row 1
Row 2
Row 4
-----
Row 1
Row 2
Row 4
Row 3

I don't see, why the DevaultView of the table changes the order of the DataViewRows, just because I create another DataView object based on this DataTable. Also, when I go in debug mode and check the Rows collection of the DataTable, the rows appear as one would expect (1, 2, 3, 4).

error deleting record in datagrid

Giovanni Bejarasco1-Oct-03 23:09

1-Oct-03 23:09

Hi
I have a datagrid bound to a dataset. I can insert and update thru the grid but a delete returns the following message (through OleDbException.Message)

"Incorrect syntax near the keyword DEFAULT"

This is thown when I call the OleDbDataAdapter.Update() method using my dataset as the target. The following is my code for the delete command of the dataAdapter:

<br />
string sql = "DELETE FROM Product " +<br />
	     "WHERE productID = ? AND desription = ? AND " +<br />
	     "cost = ? AND srp = ?";<br />
<br />
OleDbCommand cmd = new OleDbCommand(sql, cn);<br />
<br />
OleDbParameterCollection pc = cmd.Parameters;<br />
OleDbParameter param;<br />
param = pc.Add("ProductID", OleDbType.Integer, 0, "prouctID");<br />
param.SourceVersion = DataRowVersion.Original;<br />
param = pc.Add("Description", OleDbType.VarChar, 50, "description");<br />
param.SourceVersion = DataRowVersion.Original;<br />
param = pc.Add("Cost", OleDbType.Currency, 0, "cost");<br />
param.SourceVersion = DataRowVersion.Original;<br />
param = pc.Add("SRP", OleDbType.Currency, 0, "srp");<br />
param.SourceVersion = DataRowVersion.Original;<br />

Mike Dimmick2-Oct-03 12:18

2-Oct-03 12:18

swarm wrote:
param = pc.Add("ProductID", OleDbType.Integer, 0, "prouctID");

Could it be that the last parameter to Add should be 'productID'?

Giovanni Bejarasco2-Oct-03 20:07

2-Oct-03 20:07

I'm not sure about that but I thought that the order of the parameters in the parameterCollection should reflect to the order on the SQL statement. I do not find the rationale of adding the product id parameter last. I will try your suggestion though. Thanks

Mike Dimmick2-Oct-03 22:30

2-Oct-03 22:30

That wasn't what I was trying to say. The parameters in the collection must be in the same order as in the statement, for OleDbCommand.

I was assuming that you'd copied and pasted your code when making your post. There are a number of typos or, at least, inconsistencies between the statement, the parameter names, and the column names.

Specifically, in the SQL statement you have 'desription' but in the column name and parameter name you have 'description', and also you have 'productID' in SQL, but the column name parameter has 'prouctID'.

Giovanni Bejarasco3-Oct-03 1:41

3-Oct-03 1:41

Thank you so much and I'm so sorry for the confusion. I should have perused my codes carefully. There were indeed typos.

Cheers

Databases concurrecny issues

Ian Darling3-Oct-03 22:16

Ian Darling

3-Oct-03 22:16

You can help yourself avoid issues like that by declaring const strings for your column names, and using those instead - then if you make a typo the compiler will pick that up, and if the column name changes, you only have to update it in one place.

--
Ian Darling
"The moral of the story is that with a contrived example, you can prove anything." - Joel Spolsky

Jahangir Jamshed1-Oct-03 19:26

Jahangir Jamshed

1-Oct-03 19:26

Dear friends,

I am new to databases and have many questions swinging in my head. The main question is related to the concurrency and consistency issue.

In .NET, Oracle, and Access client server systems, how the DB system manage the different copies of same data to different users. Suppose a client opens a record set to perform some operation on it and write results back in the database. At the same time some other client updates the same rows, then how error is detected and handled by first client. How much control as such do we have and how pure is the object orientation ?

Re: Databases concurrecny issues

Andy Harman1-Oct-03 22:51

Andy Harman

1-Oct-03 22:51

Try searching the MSDN website for "optimistic locking".

On the system I have just developed, we have a "VersionNo" column. Whenever we retreive a resultset that may be modified by the user, we include this column in the column-list.

When we update the SQL-Server database we use stored-procedures that contain:

UPDATE SomeTable SET
    SomeColumn = @SomeValue,
    VersionNo = CASE WHEN VersionNo > 32000 THEN 1 ELSE VersionNo + 1 END,
    LastUpdateUser = @UserId,
    LastUpdateDate = GetDate()
  WHERE SomeKeyColumn = @SomeKeyValue
  AND VersionNo = @OriginalVersionNo
SELECT @intError = @@ERROR, @intRowCount = @@ROWCOUNT
IF @intRowCount <> 1 BEGIN
   RAISERROR('Concurrency problem - someone else has updated record', 16, 1)
   RETURN 1
END

This "VersionNo" value is incremented each time that we update the database. If the number of rows updated (@intRowCount) is not one then someone else has updated your record after you selected it. The case-statement stops the version-number from exceeding the size of the field (smallint in this case).

You can use similar code for Oracle and MS-Access databases - and you don't need to be using stored procedures (although it means that your concurrency control is isolated in a single place in your code).

Hope this helps.
Andy

Re: Databases concurrecny issues

Mike Dimmick2-Oct-03 3:37

Re: Databases concurrecny issues

2-Oct-03 3:37

There's also the SQL Server ROWVERSION datatype, which SQL Server automatically updates whenever the row is changed. However, IIRC, it doesn't write a predictable sequence number, so if you need it to show versions 1, 2, 3, etc in sequence, you'll have to do it as above.

SimonS2-Oct-03 22:46

SimonS

2-Oct-03 22:46

You may find this[^] useful.

Cheers,
Simon

sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.

article :: animation mechanics in SVG picture :: my first abstract photo

DataRowState Problems

Wjousts1-Oct-03 11:33

Wjousts

1-Oct-03 11:33

I'm having a problem with when (or even if) the DataRowState of DataRows are getting changed. Here's the situation:
I have one main table which is bound to a form which displays one record from the main table. If you make changes to the form and then try and update the database, nothing happens. If you move to another record and then try it, it works. I've determined that this is because even though the DataRow has a Proposed version set (HasVersion(DataRowVersion.Proposed)) returns true, the DataRowState is still Unchanged until you move to another record. Does anybody know why this is and if I can change the DataRowState myself?
This situation is a fairly minor problem by itself, but it becomes a major problem because I also have a child table linked to the main table and that never gets changed to DataRowState.Modified so I can never update it.
Please help!!!

Re: DataRowState Problems

Giovanni Bejarasco3-Oct-03 1:46