Question on DataAdapter.Update

[bigfoot]

Can someone please tell me how to reset a DataAdapter to update the file a second time?

The first time it works fine, then on an edit I delete the records and call the .Update again and it does nothing that I can see.
Why?

I've searched for an answer but haven't used this object before but it does a great job of saving records passed around in my web site, then I dump it to a SQL Server data table.

My Code

    Dim strSQLAdd As String = "SELECT report_id, user_id, report_name, field_sequence, report_desc, field_name, sort_seq, sort_order, report_public FROM User_Reports WHERE report_id = " & iReportID
    MyDataAdapter = New SqlClient.SqlDataAdapter(strSQLAdd, MyConnection)
    Dim MyCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(MyDataAdapter)

    Try
      MyDataAdapter.Update(tblReportInfoTable)
    Catch ex As Exception
      lblMessage.Text = "Could not save your Report because: " & ex.ToString()
      lblMessage.Visible = True
      Return False
    Finally
      MyConnection.Close()
    End Try

    MyDataAdapter.Dispose()
    MyConnection.Dispose()

Thanks ahead of time.

 

[jmeckley]

I'm going to guess that it's because your are using an injected sql statement rather than a parameterized query statement. change your select statement to use a parameter and see if the results are the same.

move

MyDataAdapter = New SqlClient.SqlDataAdapter(strSQLAdd, MyConnection)
Dim MyCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(MyDataAdapter)

and

MyDataAdapter.Dispose()
MyConnection.Dispose()

into the finally block, otherwise you could end up with side effects when an error is thrown outside of the try block.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[bigfoot]

Thank you Jason.

I moved the code into the Finally block.

What parameters would I use in the sql statement?
Do I want to do them all?

And why do you think it would only work 1 time and not the second if the user pressed save again?

It "feels" like the object thinks there is nothing changed.

 

[jbenson001]

You will have to trace through your code to find the problem. YOu posted code, but not the event that it is fired from. You shoud also use a stored procedure with parameters instead of inline sql as Jason stated.

 

[bigfoot]

Oh thanks.
It's called from a button event that is called from a Save button on a web screen.
the only part I didn't add was the delete code just before this.
Nothing special, just a ExecuteNonQuery statement.

It is the first time I've used this. I usually loop through and write it directly to the table myself using a stored procedure, but this looked faster.

Guess not.

 

[jbenson001]

The update method on the sqldataadaper will only affect updated rows. Since I can't see the flow of your code, I can only assume that on the second time around, no rows have changed (rowstatus of the rows in the ds), and therefore no update is done.

 

[jmeckley]

a parameterized query looks like this

"SELECT report_id, user_id, report_name, field_sequence, report_desc, field_name, sort_seq, sort_order, report_public FROM User_Reports WHERE report_id = @REPORT_ID"

you then define @REPORT_ID in the command.

I'm not advocating stored procs, only parameterized queries.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[jbenson001]

I am advocating stored procedures with parameters.

 

[bigfoot]

Thanks Jason. I'll try that but what jbenson001 wrote makes perfect sense.

Is there a way to just dump this DataTable to a SQL Table without using this command?

I can loop and read then write to a SP or SQL.


(I'm used to writing my own data code and not using these kinds of objects)

 

[jmeckley]

Is there a way to just dump this DataTable to a SQL Table without using this command?

by 'this command' do you mean the data adapter? yes.
but if you mean data table to sql without ADO.Net, no. that is what it does.

depending on the database and number of records a SqlBulkInsert object may be a good choice. if not simply looping through the table and creating a command for each row would work just as well.

when I work with databases i take 1 of 2 approaches. 99.99% of the time I use NHibernate for my data access needs. the other .01% of the time I use raw ADO.Net objects. these cases are so minimal that I don't need an elaborate abstraction atop ado.net.

I have never messed with datasets or table adapters when communicating with a database.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[bigfoot]

I fixed it. Thanks for all the help.
It is working now.