Question UPDATE SQL CE Server via DataAdapter

[WaterboyVB]

Hi All,

I have an issue which I have been trying to resolve for a couple of days. I am trying to update the SQL CE Server (which is in my project) with the changes that have been made to my dataset.

Code:

Dim myConn As SqlCeConnection = New SqlCeConnection(My.Settings.db_Database_Application_ConnectionString)

Dim myDataAdaptor As New SqlCeDataAdapter

Dim mySQLCommandUpdate As SqlCeCommand = New SqlCeCommand("UPDATE tblTutors SET Password = 'pass'", myConn)

Dim mySQLCommand As SqlCeCommand = New SqlCeCommand("SELECT * FROM tblTutors", myConn)

Dim myDataset As DataSet = New DataSet

myConn.Open()

myDataset.Clear()

myDataAdaptor.SelectCommand = mySQLCommand
myDataAdaptor.UpdateCommand = myConn.CreateCommand
myDataAdaptor.UpdateCommand.CommandText = ("UPDATE tblTutors SET Password = 'password'")

mySQLCommandUpdate.ExecuteNonQuery()

myDataAdaptor.Fill(myDataset, "tblTutors")

DataGridView1.DataSource = myDataset
DataGridView1.DataMember = "tbltutors"

myDataAdaptor.Update(myDataset, "tbltutors")

myConn.Close()

The changes seem fine when displaying the data in the DataGridView however the changes have not been made to the SQL CE Server itself.

Can anyone spot where I am going wrong here?

Any help is much appreciated.

 

[jmcilhinney]

Is that code all together in your project or are parts of it different places because it doesn't look especially useful if it's that's how it actually appears. Also, do you realise that that ExecuteNonQuery is going to set the Password column of every single record in the tblTutors table to "password"?

 

[cjard]

The changes seem fine when displaying the data in the DataGridView however the changes have not been made to the SQL CE Server itself.

If the database is an attached-every-time file, then read the DNU link in my signature. You might also want to take a read through the DW2 link in my signature, for info on how to do your data access properly