[RESOLVED] Help Updating a Single Column, Multiple Rows in an Access Database?

[dkahn]

I have an access table that I'm loading into a dataset, then I'm binding the dataset to a bindingsource. The bindingsource is the datasource for a datagridview. I can use a command builder to update the entire table, but I need to update all the rows (or changed rows) in specific columns. I have a column called "dPrint" which is Boolean. I'm not sure how to use the bindingsource as my datasource in an Update Command. I've tried this:

' This is at the top of my form, used to add new rows
Dim row As DataRowView

Dim updateCommand As New OleDbCommand("UPDATE Customers SET dPrint = @dPrint", Conn)
updateCommand.Parameters.AddWithValue("@dPrint", row("dPrint"))
updateCommand.Connection = Conn
Conn.Open()

updateCommand.ExecuteNonQuery()

Am I on the right track here? Can someone help me out? Thanks

[jmcilhinney]

You don't use the BindingSource. The data is in the DataTable. That's what you pass to the Update method of your DataAdapter. If you want to edit every row then you would edit every row in the DataTable first, then call Update.

[dkahn]

I may have worded my question wrong, I want to exclude one column from being updated unless the user presses a button to update that column. If I use a command builder, all of the columns will get updated. I'm trying to figure out how to choose which columns get updated.

[jmcilhinney]

Ah, OK. In that case then you wouldn't use a command builder. You'd write your own SQL code. Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. It has code examples of various scenarios, including saving edited data with or without a command builder.

[dkahn]

I actually had looked at that and it's very helpful, but I'm still not sure what the syntax of the value I would assign to @dPrint. In your example:

update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")

Where does "Name" come from? I've used this method before where I'm updating a single row and I would put a textbox.text there. I guess I'm confused as to what to put there when the source is not something like a textbox.

Also in your example:

command.Parameters.AddWithValue("@Name", someName)

How would I use my dPrint in place of someName. Thanks

[jmcilhinney]

What does the relevant documentation say? That is always the first thing you should look at when you aren't sure how to use a particular type or method.

The name of the source column.

The source is the DataTable, so it's the name of the DataColumn from which the data fro that parameter should be drawn. Remember, you're not just saving one row. You're saving a whole DataTable. You can't tell the command the actual value to use for each parameter because the values will be different for each row. You tell the command where to get the value from. Internally, the Update method will essentially loop through the rows in the DataTable and set the values for the parameters by getting data from the columns you specify.

[dkahn]

Isn't this the example I should be following?

Saving changes directly to one or more records in the database.

      Using connection As New SqlConnection("connection string here")
       Using command As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
                                          connection)
                 command.Parameters.AddWithValue("@Name", someName)
                 command.Parameters.AddWithValue("@Quantity", someQuantity)
                 command.Parameters.AddWithValue("@Unit", someUnit)
          
                 connection.Open()
          
                command.ExecuteNonQuery()
  
          End Using
        End Using

Don't you use UPDATE to save changes and INSERT to add new rows? When I try to use this to save changes I get an error "Primary key cannot contain null value."

[jmcilhinney]

No it's not. Is there a DataTable being used in that example? No there isn't, so it obviously isn't the right example.

[dkahn]

It seems to be working using the other example in your post:

Dim da As New OleDbDataAdapter("SELECT Key_No,dPrint from customers", Conn)
Dim update As New OleDbCommand("UPDATE Customers SET dPrint=@dPrint WHERE Key_No=@Key_No", Conn)
         
update.Parameters.Add("@dPrint", OleDbType.Boolean, 1, "dPrint")
update.Parameters.Add("@Key_No", OleDbType.Integer, 5, "Key_No")

'Add the update command to the data adapter.
da.UpdateCommand = update

'Update the database.
da.Update(ds, "dtList")

I still need to test saving the other columns and excluding this one, but I think I'm understanding how it works. Thanks