Database Updating problem

[dave]

Please help! I have spent all weekend trying to solve this and its driving
me mad!

I have a form with a datagrid on. When I click button btnNew, the new row
appears in the datagrid but it does not update the back end access database.
The program continues to the messagebox to say that it has updated it
though. I've attached the relevant code but the main area is the Private
Sub btnNew_Click at the bottom of the posting

Thanks!

Public Class frmUsers

Inherits System.Windows.Forms.Form

Public cnn As New OleDb.OleDbConnection ' database connection string

Public strConn As String

Public cLoc As String

cLoc = "C:\Documents and Settings\dave.PROPENSITY\My Documents\Visual
Studio Projects\test login\login.mdb"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & cLoc & _

"; Jet OLEDBatabase Password=flintstone"

Dim str1 As String = "Select * From Users"

Dim daUser As New OleDb.OleDbDataAdapter(str1, cnn)

Dim dsUser As New DataSet







Private Sub frmUsers_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

daUser.Fill(dsUser, "dtUser")

daUser.FillSchema(dsUser, SchemaType.Source, "Users")

dgUsers.DataSource = dsUser.Tables("dtUser")





Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnNew.Click

Dim drCurrent As DataRow

' Obtain a new DataRow object from the DataTable.

drCurrent = dsUser.Tables("dtuser").NewRow()



' Set the DataRow field values as necessary.

drCurrent("Username") = txtName.Text

drCurrent("Initials") = "George"

drCurrent("password") = "Johnson"

drCurrent("Disabled") = True

drCurrent("Passcode") = "1956 Arlington Pl."


'Pass that new object into the Add method of the DataTable.Rows collection.

dsUser.Tables("dtuser").Rows.Add(drCurrent)

MsgBox("Add was successful.")

'lets update the datasource

Dim strInsert As New OleDb.OleDbCommand

Dim prm As New OleDb.OleDbParameter

strInsert = cnn.CreateCommand

strInsert.CommandText = "Insert into Users (Username, Initials, password,
Disabled, Passcode) Values (@Username, @Initials, @password, @Disabled,
@Passcode)"

strInsert.CommandText = "Insert into Users (Username) Values (@Username)"

prm = strInsert.Parameters.Add("@Username", OleDb.OleDbType.VarWChar, 40,
"Username")

daUser.InsertCommand = strInsert


MsgBox("Updated database")

End Sub

 

[William Ryan eMVP]

Dave:

You aren't calling the Update method of the dataadapter. The messagebox is
just being deceptive here.
Add this line right before the line with the messagebox command
daUser.Update(dsUser, "dtUser")

 

[William Ryan eMVP]

Dave:

The last post I gave you should fix it for you, however, I'd recommend
making a few changes.... just stylistic but I figured they'd be worth
mentioning. In this example, it looks like you've hard coded those values
just to make sure you could add a row. In general though, you probably
don't want to reset the commandtext each time you add a new row. There's
not a big problem if you do, but you dont' get any benefit from it and
unless the commandtext changes, there's not really any reason to put it
there. Instead, load the commandtext for each respective command near the
instantiation of the dataadapter or better yet, you may want to make a
class/layer to handle this for you. Remember that DataAdapters really don't
care where the data they deal with comes from... as long as they have a
valid command for each operation they are asked to perform, they're good to
go. As such, you can update a table with a completely different datadapter
than you used to fill the datatable with.

In this example you are setting the command text for the insert command
twice, you could save some code by cutting this out.

Next, the DataAdapter.Update command returns an integer value representing
the number of rows affected by the update. So you could do this to show
the number of records that were successfully affected by the database:

Dim recsUpdated as Integer = daUser.Update(dsUser, "dtUser")
MessageBox.Show(recsUpdated.ToString, "Records Affected by Update")

Also, when you are starting out, people often call Update and assume that
it's automatically going to work. Provided you have a valid command for each
update operation (insert, delete, update) this is generally true but not
necessarily the case. If you call DataSet.AcceptChanges before the update,
then your local changes won't be sent back to the database. Moreover, if
you call update on a datatable/dataset that doesn't have changes
(DataSet.HasChanges == false) then nothing will be sent back to the db.
When you call update, the dataadapter walks through the dataset with rows
with rowstate or modified/deleted/inserted and fires the respective command
against that row. The columnmappings will map the column value to the given
parameter(s). As such, a common problem is people think the
dataset/datatable haschanges although it doesn't and they call update. The
most common scenario I see this mistake with is when they call AcceptChanges
before update. From their point of view, the datatable/dataset does have
changes, but calling acceptchanges resets and modifies the rowstates of all
of the rows, so HasChanges will be false. And if you call
DataSet.HasChanges or DataTable.HasChanges before update and it returns
False, THEN NOTHING is going to be modified in the db. I personally got
burned by this more than I care to mention when I was first learning
ADO.NET. So if you are just walking through a test scenario..it's often
good to insert a Debug.Assert(myDataSetHasChanges).... that way you'll see a
big ugly assertion box pop up if you don't have any changes and you'll know
not to expect anything to happen on the back end.

Anyway, wasn't sure .. you may already know all of this or not but I figured
it'd be worth mentioning. If you have any questions or concerns (or if
adding the Update call doesn't fix your problem), please let me know.

Cheers,

Bill

 

[dave]

Thanks - Worked first time!

I can now sleep peacefully!

 

[William Ryan eMVP]

Glad it worked!

Thanks - Worked first time!

I can now sleep peacefully!