DataAdapter and using .Update to save dataset

[Wokawidget]

Public Sub Update(ByVal CommandText As String, ByRef MyData As DataSet)
        Dim Comm As SqlCommand
        If _Transaction Is Nothing Then
            Comm = New SqlCommand(CommandText, Connection)
        Else
            Comm = New SqlCommand(CommandText, Connection, _Transaction)
        End If
        Dim da As New SqlDataAdapter(Comm)
        da.UpdateCommand = Comm
        da.Update(MyData)
    End Sub

That's what I have.
It doesn't error, but it also doesn't update my DB.
I use (and I know * is bad, but it's testing) the following as the CommandText:

SELECT * FROM Customers

So I fetch a dataset using the above sql, and then I use the above update statement to save it. I know I am doing something REALLY daft.

Woka

[techwizz]

Will you attatch the file? Ill look at if u want?

[Wokawidget]

Do you have 2005 Beta 2?

Woka

[jmcilhinney]

What is the CommandText used for the update command? Also, are you committing the transaction if and when one is used?

[mendhak]

You're using a SELECT statement in your update command?

[RobDog888]

w00f!!! Doh!

[mendhak]

w00f!!! Doh!

[jmcilhinney]

You're using a SELECT statement in your update command?

I don't think he is, although that's what I first thought from the wording. Using a SELECT statement in an UpdateCommand would raise an exception, would it not? I think it's because the transaction is not being committed. If you don't call Commit on a transaction, my guess is that it would get rolled back by default. You would receive no notification of this, as is happening. I'm pretty sure that has happened to me in the past, although it was obviously a long time ago

Edit:
That's assuming that the route that uses a transaction is being followed. If not, forget everything I just said. In fact, I was never here!

[Wokawidget]

I am using "Select * from Customers Where ID = 5", the same sql I use to get the dataset.
It's not the transaction.
I try this with or without starting a transaction, and the same thing happens.
I do no get an exception either.

I am using 2005 Beta 2, but I am assuming the ADO.NET is still the same as 2003.

Woka

[jmcilhinney]

Then mendhak was right. You need to use a SELECT statement to retrieve rows, a DELETE statement to delete rows, an INSERT statement to add new rows and an UPDATE statement to edit rows. You need to use something like:

UPDATE Customers SET Name = @Name WHERE ID = @ID

and then add some parameters to the SqlCommand object to substitute the actual values for the placeholders (@Name and @ID).

[Wokawidget]

Hmmm...I am sure I have done this is the past where the dataset has remembered how to ties into the DB and auto updates the correct field.

Will give that a go though.
Cheers.

Woka

PS Mendhack was right coz he read my 1st post correctly hahaa

[jmcilhinney]

If you want to generate non-query commands automatically, you can use the "Configure Data Adapter..." function at the bottom of the properties window, assuming you have created the adapter in the designer, or you can use an SqlCommandBuilder. The command builder is frowned on in some parts and does have limitations. I never use it myself, but it is quick and easy. The Data Adapter Configuration Wizard is a liitle more involved, so gives you more control, but is still relatively easy to use.