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 OLEDB:Database 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:

Add this line of code in place of the line with the MsgBox:
Dim i as Integer = daUser.Update(dsUser, "dtUser")
MessageBox.Show(i.ToString, "Records Updated")

You aren't actually calling update and that's what needs to send the updates
to the db. I responded in ADO.NET too with a more thorough explanation, but
this alone should fix it barring any other errors like bad connection
strings or field names .

HTH,

Bill
"dave" <da**@dave.com> wrote in message
news:_C*********************@wards.force9.net...

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 OLEDB:Database 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:

Add this line of code in place of the line with the MsgBox:
Dim i as Integer = daUser.Update(dsUser, "dtUser")
MessageBox.Show(i.ToString, "Records Updated")

You aren't actually calling update and that's what needs to send the updates
to the db. I responded in ADO.NET too with a more thorough explanation, but
this alone should fix it barring any other errors like bad connection
strings or field names .

HTH,

Bill
"dave" <da**@dave.com> wrote in message
news:_C*********************@wards.force9.net...

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 OLEDB:Database 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