Problem with Data Adapter Update


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Problem with Data Adapter Update

Hybrid View

  1. #1
    max caber Guest

    Problem with Data Adapter Update


    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



  2. #2
    Larry Leach Guest

    Re: Problem with Data Adapter Update

    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
    >
    >




  3. #3
    max caber Guest

    Re: Problem with Data Adapter Update


    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


  4. #4
    Larry Leach Guest

    Re: Problem with Data Adapter Update

    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
    >




  5. #5
    Join Date
    Jul 2007
    Posts
    1

    Cool Here is the Update Fix guys, need to re-fill the adapter, before updating

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center