For Each column In Datarow question.

[qwert231]

Basically, what is the best way to do this?

Here's my goal... I have a table where every Item is a Boolean.
In each iteration I need to get the Column name and whether it is true or not. Here is what I tried:

Dim myColumn As Data.DataColumn
For Each myColumn In objRow.ItemArray
 If myColumn = "True" Then

'This does not work.

  countX += 1
  tmpStr.Append(myColumn.ColumnName & " = YES")
 Else
  tmpStr.Append(myColumn.ColumnName & " = NO")
 End If
Next

Basically, I am building an Update string to go to an Access/Jet database.

 

[Zarcom]

If it is boolean then use the boolean Type True not "True" which is simple a string That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[qwert231]

Thanks Zarcom, but I worked it this way for now.

tmpStr.Append("UPDATE Sport SET ")
For i = 0 To objTable.Columns.Count - 1
 If objRow(i) Then
  countX += 1
  tmpStr.Append(objTable.Columns(i).ColumnName.ToString & " = YES")
 Else
  tmpStr.Append(objTable.Columns(i).ColumnName.ToString & " = NO")
 End If
 If i < objTable.Columns.Count - 1 Then tmpStr.Append(&quot;, &quot;)
Next
Me.daSport.UpdateCommand.CommandText = tmpStr.ToString

 

[Zarcom]

I assume that tmpStr is a stringbuilder. If it is you are counteracting any performance enhancements that it provides when you put the "&" in the append method. The stringbuilder works so well because it builds strings without the method that a "&" does. By putting the "&" back in you just negated any reason for using it. That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[qwert231]

However, at the end of the code I run 4 update commands:

Me.daCustomer.Update(Me.DsGlobal1, "Customer")
Me.daEvent.Update(Me.DsGlobal1, "Event")
Me.daSport.Update(Me.DsGlobal1, "Sport")
Me.daUnderclass.Update(Me.DsGlobal1, "Underclass")

The first command takes, the second 3 do not. I wish I could get an error message or see what Update is trying to do.

(Everything starting with da is a DataAdapter.)

 

[qwert231]

So instead of this:
tmpStr.Append(objTable.Columns(i).ColumnName.ToString & " = NO&quot

Do this?:
tmpStr.Append(objTable.Columns(i).ColumnName.ToString)
tmpStr.Append(" = NO&quot

 

[Zarcom]

I don't know what your first question would be

Yes to the second. That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[qwert231]

K Mark, (I feel as if I am talking to myself) here's the deal.

My iteration through the Datarow looks at each field in a row. For this case this table had, has, and always will have, 1 Row. Therefore I put no primary key in it.

With no PK Commandbuilder will not build the Updatecommand, therefore I am doing it myself. So the iteration does 2 things. Counts how many fields are True (which gives me a number of textboxes to draw on the screen... which is working.)
But also, builds the update command to persist the data back to the Jet database.

So then once I have the Update command I want to do DataAdapter.Update. But I get nothing, no errors, no Database changes... nothing. I want to know why?

Shouldn't this work:

Me.daSport.UpdateCommand.CommandText = tmpStr.ToString
Me.daSport.Update(Me.DsGlobal1, "Sport")

with a String that looks like this:
UPDATE Sport SET Bats = NO, Birthdate = YES, City = YES, Coach = YES, Custom1 = NO, Custom2 = NO, Height = YES, Highlights = NO, PosPlayed = YES, Team = YES, Throws = NO, Weight = NO

 

[Zarcom]

It should/might. I haven't ever taken the time to find out exactly what the .update command does. I do know a bit about it which I will try to convey to you.

First off it accepts a dataset as an argument and optionally which table in the dataset.

Secondly it runs through the dataset and table and calls the correct command depending on what the rowstate is set to. This can be a few options which include but are not exactly: unchanged, added, deleted, modified. The rowstate is a readonly property it is set depending on what you have done previously with the dataset.

My guess is that the dataset your sending in may not have any changes in it. Since you only have one row in your table ever at one time, then I suggest moving away from the dataAdapter and using a datacommand.

A quick and dirty solution to this would be to keep your existing objects and simply replace the following line
Me.daSport.Update(Me.DsGlobal1, "Sport&quot

with this one.
Me.daSport.UpdateCommand.ExecuteNonQuery

Just remember that the update method may depending on what is happening call the DeleteCommand, UpdateCommand, and or InsertCommand. That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[qwert231]

Thankfully in this case, it will always/only be Update. So yeah that will work. (Banging head on wall stops... Flashback to other times of using that begins...)

One thing I also noticed, and this is a funny thing when using datasets with Textboxs, is that when I change to another field it seems to invoke Acceptchanges, so that now the dataset has no RowChanged state (or a false). Which causes problems. Tell me if this makes sense...

Do my DataAdapter to load the data, Textbox data changes, then Textbox focus changes which updates the dataset (this is a Winform by the way). So now original datastate for the dataset is the new data, but when .Update is called I get a Concurrency violation (because the old data in the database doesn't match what the dataset thinks the old data is).

 

[qwert231]

Sorry that didn't work. I'm going to take a look at it tomorrow. The day is over, I'm tired...

 

[Zarcom]

Please stop cross posting!

It is against the rules here and making me angry. That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540