Acess Database Update Button **NOT RESOLVED**

[Anjari]

I read the tutorial why is this code not updating my access data?

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
OleDbDataAdapter1.Update(DsCompanyInfo1)
Catch ex As Exception
MsgBox(ex.ToString)
End Try

End Sub

The database is showing my data but will not save the new data.

Can anyone help?

Thanks,

Aaron

[stingrae]

The database is showing my data but will not save the new data

sorry, i'm confused by that. can you elaborate?

[Athley]

Same here, my feeling was: If a database shows new data it sure has been updated......

[Anjari]

I have my Form with SEVERAL textboxes..

On this I have a data connection to my database...

The Form will load and display the data BUT if i make changes to the data.. and click the above coded update button .. shouldnt that save the changes I have made?

Thanks,

Anjari

[Anjari]

one more thing I want it to overwrite this data not apply a new record.

Thanks,

Anjari

[Athley]

If you are displaying the data through databinding I must agree with you that it should update the database (If you are having the dataadapter create updatecommand, that is default I guess), is that the case?

[Anjari]

Yes.. that would be the case.. am i doing something wrong?

Anjari

[Athley]

Do you get an exception or is it just not updating?

[Anjari]

Its not updating/saving the data when I hit the button.

Anjari

[Athley]

just seems that it should work, can't see what you are doing wrong, if you are doing something wrong at all.

I added the employees table of the Northwind database and created a typed dataset. Then I added the following code to the form, wich have a datagrid:

Private Sub Form1_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
    SqlDataAdapter1.Fill(Ds11)
    DataGrid1.DataSource = Ds11
    DataGrid1.DataMember = "Employees"
End Sub

The I remove one row, add another and change a third and hit the button that has the following code:'

Private Sub Button4_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button4.Click
    SqlDataAdapter1.Update(Ds11)
End Sub

Tested this with the oledb thing against a Access database as well, and mine updates in both cases.

[Anjari]

I am not using a "Datagrid" I have a form with nothing but "TextBox's"..... I have

My adapter: OleDbDataAdapter1
My Connection: OldDbConnection1
My DataSet: DsCompanyInformation1

ok here goes:

On my form I have once again "TextBoxes" I have bound the data to the textbox's using the databindings property: (Selecting the datafield from the drop down list.)

When I load my form the DATA WILL display.... No problem there... but lets say the data shows: "Mi Comapnty Name" and I decide I want to change it... So I enter the "TextBox" with the ReadOnly property set to false and I type: My Company Name (Hence changing the data for that record)

Then I have a button on my form with the following code:

OleDbDataAdapter1.Update(DsCompanyInformation1)
DsCompanyInformation1.Clear()
OleDbDataAdapter1.Fill(DsCompanyInformation1)
MessageBox.Show("Database updated!")

Now here is where it ALL goes wrong....

It is NOT saving the data! nothing is changed and it only reverts to the "OLD" data.

Now PLEASE HELP PLEASE PLEASE PLEASE!

Anjari

[Anjari]

Anyone have an answer?



Anjari

[Lunatic3]

First: Experts recommend not using 'Bindings' as you dont know exactly whats happening....

Second: I am not sure about the answer, maybe experts here can answer it better, but I guess the binding property of textboxes are simple bindings and not complex binding like that of datagrid and they only bind to a single value in column and maybe thats the reason you can not update directly from textboxes.

look here and see if it helps.

It would be nice if someone clear it for us.

[Athley]

I've heard the expressions "Databinding of hell" and "Databinding, Devils work". So I guess your right about not knowing exatly what happends.....

Anjari, sorry, I knew it was textboxes, and that was what I used in the Access try, forgot to mention it though.

You should use some kind of BindingManagerBase as well, if you want to be able to scroll through the records.

I have friends over yelling from the kitchen to join again, but I'll try posting a complete sample project as soon as I can, If anyone else doesn't.

[Anjari]

Thanks any help is appreciated... Im going crazy..

Anjari

[Edneeis]

I almost never use typed Datasets or set binding properties at runtime, but using basically the same method as Athley did here. I bound to a textbox and it updated with any changes made to it.

Try doing it through code and see if that changes anything.

[Athley]

Heres a sample project, sorry to keep you waiting. You have to change the path to the db (I hope you have a Northwind copy) in the connection string of course.

[Anjari]

Ok,

Could it possibly be the MS Access DB I am using?

I am using Office XP... Do I need to Export it to another type or earlier version??

All my code appears to be exactly the way the above example is written...

Thanks,

Anjari

[Athley]

I am using Office XP as well, did you try to change the path for the connectionstring in my example to try it on your machine? I ask because maybe its your Jet Engine that is corrupt and that should mean that my example does not work on your machine.

[briancps]

maybe this will help

I dont use bound fields/ this code reads 1 record (using a datareader) and then updates the database for this one record when the user clicks update (using a dataset) -might not be all that pretty but i'm still trying to come to grips with .net after using VB for a number of years.. The table here is very simple - a Union Code and a union Name but I have expanded this for tables that have 40 or so fields -

regards
BH

Private Sub DataLoad()
'this loads the controls in the panel with the data from the selected record
'read id from database & fill controls
Dim nID As Integer
lstSearch.Col = 0
nID = lstSearch.Text
mnCurrentId = nID
Dim nI As Long

Dim sSql As String
Dim cn As New OleDb.OleDbConnection(dbcConnection)
cn.Open()

'find the selected record
sSql = "SELECT * FROM Unions WHERE ID = " & nID & " ORDER BY UNCode"

'open the ADO.NET Datareader
Dim cmd As New OleDb.OleDbCommand(sSql, cn)
Dim drd As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

drd.Read()
lblID.Text = drd!ID
txtName.Text = ToStr(drd!UNName)
txtCode.Text = ToStr(drd!UNCode)
drd.Close()

End Sub

Private Sub DataUpdate()
'this updates the database with details entered
Dim sSql As String

Dim cn As New OleDb.OleDbConnection(dbcConnection)

'find the selected record
sSql = "SELECT ID, UNCode, UnName " _
& "FROM Unions WHERE ID = " & mnCurrentId & " ORDER BY UNCode"

'open the ADO.NET Dataset
Dim cmd As New OleDb.OleDbCommand(sSql, cn)

'open the Data Adapter
Dim adapter As New OleDb.OleDbDataAdapter(cmd)

'create a dataset and fill it with the required information
Dim dsTableTest As New DataSet()
cn.Open()
'the following command fills the default values and constraints such as autonumber fields
adapter.FillSchema(dsTableTest, SchemaType.Mapped, "Unions")
adapter.Fill(dsTableTest, "Unions")

Dim row As DataRow

'if this is add new record then add a blank row
If dsTableTest.Tables("Unions").Rows.Count = 0 Then
row = dsTableTest.Tables("Unions").NewRow
dsTableTest.Tables("Unions").Rows.Add(row)
End If

'move thru the table and update it with the new values - only 1 row
For Each row In dsTableTest.Tables("Unions").Rows
row("UNName") = txtName.Text
row("UNCode") = txtCode.Text

Next

'create the Command Builder
Dim cb As New OleDb.OleDbCommandBuilder(adapter)
adapter = cb.DataAdapter

'update the records
Dim nRowsAffected As Integer
Try

nRowsAffected = adapter.Update(dsTableTest, "Unions")
Catch myerror As Exception
MessageBox.Show(myerror.Message)
'MessageBox.Show(adapter.UpdateCommand.CommandText)

Finally
End Try

cn.Close()



End Sub