"Update requires a valid UpdateCommand when passed DataRow collection with modified r

[yaniv]

"Update requires a valid UpdateCommand when passed DataRow collection with modified r

 

I"m trying to update the database from my datasetusing this code:

 

Dim oConnection As New SqlConnection("Server=localhost;DataBase=cartisiyot;Integrated Security=SSPI")

oConnection.Open()

 

Dim cmd As New SqlCommand()

Dim strsql As String

strsql = "SELECT * FROM main WHERE musag = '" & musag.Text & "'"

cmd.Connection = oConnection

 

cmd.CommandText = strsql

Dim datareader As SqlDataAdapter

datareader = New SqlDataAdapter(cmd)

 

datareader.Fill(myDS)

 

msg.Visible = False

updat.Visible = False

cance.Visible = False

updatenow.Visible = False

 

myDS.Tables(0).Rows(0).Item(1) = musag.Text

myDS.Tables(0).Rows(0).Item(2) = declaretion.Text

myDS.Tables(0).Rows(0).Item(3) = sources.Text

myDS.Tables(0).Rows(0).Item(4) = moresources.Text

myDS.Tables(0).Rows(0).Item(5) = comments.Text

myDS.Tables(0).Rows(0).Item(6) = autor.Text

 

 

datareader.Update(myDS)

oConnection.Close()

myDS.Dispose()

 

and i"m geting the same error massege again and again:

"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

 

I think i miss understood something about updating using dataset's.

 

can any body help?

[sizer]

When you updating your DB you are doing that with some QUERY, because SQLAdapter don't generate query automaticly you must specify it .

 

datareader.UpdateCommand= New SqlClient.SqlCommand ("your update query", connection)

or, you can declare commandBuilder to do that for you!

 

 

   Dim cmdBuilder As New SqlClient.SqlCommandBuilder(datareader)

CommnadBuilder has some DIsadvantages.

Read in the help of VS or VB !!

[yaniv]

Well, noew it's working. I just can't understand what did i gained from using thedataset, i coult update the old way.

 

thanks a lot

[hemenkap]

look yaniv,

 

what you gain in this new way will occur to you as you prac tice furthur.

just want to explain to you where you were going wrong.

 

look the dataAdapter has 4 commands associated with it.

one for each of the following actions.

Select, Update, Insert & Delete.

 

each of this is a seperate property of the dataadapter.

you were setting the query for the select command only.

so how will the data adapter know what is the delete.update/insert query unless and untill you set that query using

updatecmd.commandtext = " SQL update stmt"

dataadapter.updatecommand = updatecmd

 

so now the dataadapter know how to update it.

 

to save you the hassel of writing the update/insert/delete commands we have something called command builder which is excplained by sizer

what it does is that i creates the sql query for each of the 3 actions.

 

NOTE : you have to first give the select command and sql select stmt for the command builder to work else it wont work.

 

Hemen

[kasyoki]

Re: "Update requires a valid UpdateCommand when passed DataRow collection with modifi

 

Hi, everyone

 

The only solution is to make sure that the table you are updating has one column set as a primary key.

 

then

 

reconfigure the dataset.

 

Important.