SQLDataAdapter Questions..

[Hanover]

Its funny, I cant find a simple example of the typical way you would
update a table using ADO.NET through bound text boxes on a Winforms
application I can bind my text boxes, I can pull data into my text
boxes, but I am still unclear as to how I get my data back to the
database.

I realize you have to call the UPDATE method of the SQLDataAdapter
passing in the table from the dataset you are using.

I dont understand how you set up the SQLCommand Update for the
SQLDataAdapter. I dont understand if or how it knows which
"@Parameters" need to be passed in from the updated values in the
datatable. Do I need to explicitly pass these in? Or does the data
adapter somehow know from the Changed Rows which fields to change in my
database? Do I need to somehow tell it which Parameters belong with
which fields?

If I have a SQLDataAdapter Update SQLCommand of

"Update Table Set Field1=@Parm1, Set Field2=@Parm2
Where ID = @ParmID"

Does it automatically know what Parm1 and Parm2 and ParmID are from the
updated rows in the datatable? If not, what is the typical way of
taking a row, updating it and passing it back into the database through
text boxes on a winform?

Does a bound textbox even change the values in the datatable or is it
just a one-way thing?

Any enlightenment would be appreciated :)

[Paul]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbcondataadapters.asp

--

Take care,
Paul


Greater love hath no man than this.
That a man lay down his life for his friends.
                        John 15:13

[Hanover]

They talk about Mapped Parameters without telling us how to create
them:

"Mapped parameter values are used during updates. When you call an
adapter's Update method, the method walks through the records in a
dataset table, individually making the appropriate update (update,
insert, delete) for each record. In that case, the parameter values are
already available as columns in the dataset records. For example, when
the update process gets to a new record in the dataset table - a
record for which it must call an INSERT statement in the database -
the values for the INSERT statement's VALUE clause can be read directly
out of the record."

So how do I create a mapped parameter?

Dan

[Paul]

If you have a data adapter on the UI, slect it and select properties. You will see the CRUD commands there. Under those properties, you will have access to the parameters. Drag and drop data adapters prepopulate the param collection with all columns less identity columns. 

 

HTH

 

On 7/26/05, Hanover <dan....@gmail.com> wrote:

[Hanover]

Okay, got that..

However, if I have a bound text box...will the change be sent to the
dataset when I change the value in the text box? Or do I have to
assign the column the value of the textbox myself?

I've created a form, bound a text box to a field in the dataset (all
using the UI).

I then make a change to the text in the text box and call this code:
sdaEstimates.Update(EpmData1.Tables("TBL_ESTIMATES"))

But its still not working. I've checked to see if there are any
changes in the datatable and it turns out there arent any.

Even when I try to do it EXPLICITLY

EpmData1.Tables("TBL_ESTIMATES").Rows(0)("Assumptions") =
txtAssumptions.Text

It's not working! I've been doing this for a week..someone tell me why
this is so hard? I'm about to start using dynamic sql to update my
database and forget about these stupid data adapters.

[Hanover]

The Solution:

To map columns to parameters:

cmdUpdate.Parameters("@PARAMETER").SourceVersion =
DataRowVersion.[Current][Original]
cmdUpdate.Parameters("@PARAMETER").SourceColumn = "TABLECOLUMNNAME"

The reason why my data table wasnt updating:

When using textboxes that are only going to look at a one-row data
table, you have to end the current edit so that the changes are sent to
the datatable. The change usually happens when moving from one record
to the other. Since this isn't going to happen use something like this
before you update:

Me.BindingContext(DataSet.Tables("[Table]")).EndCurrentEdit()