Problem with Data Adapter Update

[max caber]

I have spent the last week getting comfortable with ADO.NET. I think MS has
the right idea with the DataSet, I am predicting that this bridge between
the XML world and the RDBMS world will be the most important component for
years to come. I was able to do everything with it except update a data
source using the Data Adapter.

I used the wizard to configure the select,insert,update,delete commands and
I attempted it with MS SQL Server using the SQL Server components and with
MS Access using OLEDB Components. I got different errors when trying to
update SQL Server and Access even though the Schema was identical. An insert
would work with Access/OLEDB but not an update or delete, and a delete would
work with SQL Server but not an insert or update. The error messages from
Access were completely useless, but the error message from SQL stated that
I had an "unexpected null data source argument". I printed out the number
of rows in the data source, and it had rows so it was not null.

Has anyone had similar problems, or can offer advice.

I read that there is a way to have the data adapter automatically configure
the commands if you set the select command. Does any one know how to do
this?

Any help is appriciated,
Max

[Larry Leach]

Hi Max,

Without seeing your code, I can only guess as to what the problems are (it
could be one of many things that come to mind). Feel free to post the code
if you like.

However, I can provide an easy answer to your question about automatically
configuring SQL statements for a DataAdapter (this is from online help):

To automatically generate SQL statements for a DataAdapter, first set the
SelectCommand property of the DataAdapter. Then create a CommandBuilder
object and specify as an argument the DataAdapter for which the
CommandBuilder will automatically generate SQL statements.
[Visual Basic]
Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers",
nwindConn)
Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(custDA)

Dim custDS As DataSet = New DataSet

nwindConn.Open()
custDA.Fill(custDS, "Customers")

' Code to modify data in the DataSet here.

' Without the SqlCommandBuilder, this line would fail.
custDA.Update(custDS, "Customers")
nwindConn.Close()


"max caber" <maxcaber@yahoo.com> wrote in message
news:3c0f92c2$1@147.208.176.211...
>
> I have spent the last week getting comfortable with ADO.NET. I think MS
has
> the right idea with the DataSet, I am predicting that this bridge between
> the XML world and the RDBMS world will be the most important component for
> years to come. I was able to do everything with it except update a data
> source using the Data Adapter.
>
> I used the wizard to configure the select,insert,update,delete commands
and
> I attempted it with MS SQL Server using the SQL Server components and with
> MS Access using OLEDB Components. I got different errors when trying to
> update SQL Server and Access even though the Schema was identical. An
insert
> would work with Access/OLEDB but not an update or delete, and a delete
would
> work with SQL Server but not an insert or update. The error messages from
> Access were completely useless, but the error message from SQL stated that
> I had an "unexpected null data source argument". I printed out the number
> of rows in the data source, and it had rows so it was not null.
>
> Has anyone had similar problems, or can offer advice.
>
> I read that there is a way to have the data adapter automatically
configure
> the commands if you set the select command. Does any one know how to do
> this?
>
> Any help is appriciated,
> Max
>
>

[max caber]

Thanks Lary,
I will give that a try. I am experimenting with two related tables, invoice
and line item, therefore I must make the updates in the correct order so
I don't get referential integretiry errors.

I created a project with classes that load a DataSet from Code, Load from
XML and from a DataAdapter. All classes use/generate the same Schema. I
am trying to demonstrate the many different ways to work with a DataSet.
Since the code is too long to post, could I zip up the project and email
it to you?
Thanks,
Max

[Larry Leach]

Sure. Send it on.

-ldl-

"max caber" <maxcaber@yahoo.com> wrote in message
news:3c112f7d$1@147.208.176.211...
>
> Thanks Lary,
> I will give that a try. I am experimenting with two related tables,
invoice
> and line item, therefore I must make the updates in the correct order so
> I don't get referential integretiry errors.
>
> I created a project with classes that load a DataSet from Code, Load from
> XML and from a DataAdapter. All classes use/generate the same Schema. I
> am trying to demonstrate the many different ways to work with a DataSet.
> Since the code is too long to post, could I zip up the project and email
> it to you?
> Thanks,
> Max
>

[dotnetfranco]

Public Sub

' Password Variables
Dim password As String = ""
Dim depassword As String = ""
Dim encryptor As RDMencryptor = New RDMencryptor

' ADO Variables
Dim con As SqlConnection = New SqlConnection(Config.conStrDat)
Dim cmd As String = "SELECT id, isnull(pw, '') as pw FROM keymerchants where id = 13415"
Dim ds As DataSet = New DataSet
Dim dr As DataRow = Nothing

' Fill DS
Dim mySqlAdapter As SqlDataAdapter = New SqlDataAdapter(cmd, con)
Dim myCmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(mySqlAdapter)
mySqlAdapter.Fill(ds, "keymerchants")

' Decrypt Paswords in DS
id = ds.Tables(0).Rows(0)("id")
password = ds.Tables(0).Rows(0)("pw").ToString()
depassword = encryptor.DecryptPassword(password)
ds.Tables(0).Rows(0)("pw") = depassword
ds.AcceptChanges()

' Update In Database
Dim cmd2 As String = "update keymerchants set pw = '" & depassword & "' where id = 13415"
mySqlAdapter = New SqlDataAdapter(cmd2, con)
myCmdBuilder = New SqlCommandBuilder(mySqlAdapter)
mySqlAdapter.Fill(ds, "keymerchants")
mySqlAdapter.Update(ds.Tables("keymerchants"))

End Sub