I am trying to read a value from an Access DB, display it in a text box, allow edit, then when a button is clicked write the updated value back. I have managed to get the code to run without any runtime exceptions, but nothing is updated! What am I missing?

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            myOleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("kpimon.mdb") & ";")
            myOleDbDataAdapter = New OleDbDataAdapter("SELECT optExitLink From Options WHERE optId = 'KPI'", myOleDbConnection)
            myOleDbDataAdapter.FillSchema(myDataTable, SchemaType.Source)
            myOleDbDataAdapter.Fill(myDataTable)
            myDataView = myDataTable.DefaultView
            myDataView.AllowEdit = True
            myDataView(0).BeginEdit()
            myDataView(0).Row("optExitLink") = txtExitLink.Text
            myDataView(0).EndEdit()
        Catch objE As Exception
            status.Text = "Exception on data edit " & objE.Message
        End Try
    End Sub

Also, I am reading the database again as myDataView didn't seem to persist - should it, or do I need to set something in the page load when I create the dataview? No biggie, I would read it again just to be sure it hasn't already be updated (I haven't added the code for buried update checks yet).

Any suggestions welcomed - I'm a .net newbie so please take it easy on me!

: I am trying to read a value from an Access DB, display it in a text box, allow edit, then when a button is clicked write the updated value back. I have managed to get the code to run without any runtime exceptions, but nothing is updated! What am I missing?
:
:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
:         Try
:             myOleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("kpimon.mdb") & ";")
:             myOleDbDataAdapter = New OleDbDataAdapter("SELECT optExitLink From Options WHERE optId = 'KPI'", myOleDbConnection)
:             myOleDbDataAdapter.FillSchema(myDataTable, SchemaType.Source)
:             myOleDbDataAdapter.Fill(myDataTable)
:             myDataView = myDataTable.DefaultView
:             myDataView.AllowEdit = True
:             myDataView(0).BeginEdit()
:             myDataView(0).Row("optExitLink") = txtExitLink.Text
:             myDataView(0).EndEdit()
:         Catch objE As Exception
:             status.Text = "Exception on data edit " & objE.Message
:         End Try
:     End Sub

: Also, I am reading the database again as myDataView didn't seem to persist - should it, or do I need to set something in the page load when I create the dataview? No biggie, I would read it again just to be sure it hasn't already be updated (I haven't added the code for buried update checks yet).
:
: Any suggestions welcomed - I'm a .net newbie so please take it easy on me!
:

I believe the DataAdapter has an Update() function that you must call.

Thanks, I'd stumbled on that (and the AcceptChanges method) but can't work out the syntax for it. I tried:

 myOleDbDataAdapter.Update(myDataTable)

but get this exception:

Operation must use an updateable query.

I suspect I'm pretty close now; I think I need to define an SQL update query for my datasource and set myOleDBDataAdapter.UpdateCommand = whateveritis. Does this sound right?

: Thanks, I'd stumbled on that (and the AcceptChanges method) but can't work out the syntax for it. I tried:

 myOleDbDataAdapter.Update(myDataTable)

but get this exception:

Operation must use an updateable query.

I suspect I'm pretty close now; I think I need to define an SQL update query for my datasource and set myOleDBDataAdapter.UpdateCommand = whateveritis. Does this sound right?
:
Good God! I was warming to .Net until now. How much code do you need to write to get ONE PIECE of data into a database?

I've dad to set up a DataAdapter and a Connection, fine. Now I need a Dataset, and I need to populate it. OK, the Dataset is effectively a runtime image of the data, I can live with that. Now I have to define a DataTable. I'm sure there's a good reason why this can't be inferred from what is already defined - Not!

Now to actually do something with the data I have to start defining SQL update statements, creating commands, linking the commands to the adapter, creating command parameters, adding these to the commands ... blimey, this is a step forward?

I was expecting something like

myDataTable("myField").value = myForm.myControl.text

Surely the arcane mechanics of physically putting the value into the database is something I should care b*gg*r all about; all I want to do is map the value of a control on my form to a field in the database which I have already described (IMHO] in sufficient detail. I'll live with a "commit" if you insist, but other than that I don't want to get my hands dirty. I stopped doing that two decades ago when 4GL's were invented.

Can anyone point me in the direction of a tool which will handle the mechanics for me? I'm too old and weary to be a labourer these days.

This message was edited by iwilld0it at 2005-1-6 7:20:4

---

This message was edited by iwilld0it at 2005-1-6 7:19:4

---

Just to ease your pain or possibly make you more upset, I believe Dataset's and DataAdapter's are overkill for ASP.NET pages. I usually just stick with DataReader's and Command's. It is slightly less work and more efficient.

As for databinding in ASP.NET (at least), it is one way. This means you can bind data to controls, but the data does not automatically update to the database. There is possibly no web developing language that is going to be any better or any less coding. The best thing you can do is spend time writing a class that simulates two-way databinding and then reuse it from then on. ASP.NET 2.0 promises to be much, much better and 70% less coding when it comes out.

If you are sticking with the DataAdapter and do not feel like writing your own Command objects for the UpdateCommand, DeleteCommand, and SelectCommand properties, you can use the CommandBuilder object that is part of .NET. It is used in conjunction with the DataAdapter. The only thing is that it must make a round trip to build the hidden SQL queries.

: This message was edited by iwilld0it at 2005-1-6 7:20:4

---

: This message was edited by iwilld0it at 2005-1-6 7:19:4

---

: Just to ease your pain or possibly make you more upset, I believe Dataset's and DataAdapter's are overkill for ASP.NET pages. I usually just stick with DataReader's and Command's. It is slightly less work and more efficient.
:
: As for databinding in ASP.NET (at least), it is one way. This means you can bind data to controls, but the data does not automatically update to the database. There is possibly no web developing language that is going to be any better or any less coding. The best thing you can do is spend time writing a class that simulates two-way databinding and then reuse it from then on. ASP.NET 2.0 promises to be much, much better and 70% less coding when it comes out.
:
: If you are sticking with the DataAdapter and do not feel like writing your own Command objects for the UpdateCommand, DeleteCommand, and SelectCommand properties, you can use the CommandBuilder object that is part of .NET. It is used in conjunction with the DataAdapter. The only thing is that it must make a round trip to build the hidden SQL queries.
:
:
:
:
:
Ah yes, Whidbey does look more like it!

: Just to ease your pain or possibly make you more upset, I believe Dataset's and DataAdapter's are overkill for ASP.NET pages. I usually just stick with DataReader's and Command's. It is slightly less work and more efficient.
:

OK, I'm game. So what am I doing wrong here?

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            status.Text = txtExitLink.Text
            Dim myConnection As New OleDbConnection(DBCONNECT)
            Dim mySQL As String = "UPDATE Options SET optExitLink = '" & txtExitLink.Text & "' WHERE optId = 'KPI'"
            Dim myCommand As New OleDbCommand(mySQL, myConnection)
            myCommand.Connection.Open()
            myCommand.ExecuteNonQuery()
        Catch objE As Exception
            status.Text = "Exception on data edit " & objE.Message
        Finally
            myConnection.Close()
        End Try
    End Sub

First, the IDE tells me that myConnection in the Finally clause is not declared (?). At runtime I get the error "Operation must use an updateable query.". I practically cut & paste that code from the help on ExecuteNonQuery!!!

Q. So what am I doing wrong here?

A. Nothing!

Sorted it myself. Its the old NTFS security chestnut. I had checked the security tab on the folder the database is in, and made sure IUSR_machinename had write permissions, but still got the error. What I didn't see was the the ASPNET user had no permissions at all (and therefore wasn't visible - easy to mistake the "old" IUSR_machinename for ASPNET I guess, until its bit you on the ass once!!).

Once I added write permissions for ASPNET it works fine.

Looking back from my temporarily smug position of hindsight, it looks pretty easy! Why am I reminded of someone's instructions on how to fly a helicopter: "Put the stick somewhere. Look outside and see whats happening. Next time you want that to happen, that's where you put the stick!"

: : Just to ease your pain or possibly make you more upset, I believe Dataset's and DataAdapter's are overkill for ASP.NET pages. I usually just stick with DataReader's and Command's. It is slightly less work and more efficient.
: :
:
: OK, I'm game. So what am I doing wrong here?

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
:         Try
:             status.Text = txtExitLink.Text
:             Dim myConnection As New OleDbConnection(DBCONNECT)
:             Dim mySQL As String = "UPDATE Options SET optExitLink = '" & txtExitLink.Text & "' WHERE optId = 'KPI'"
:             Dim myCommand As New OleDbCommand(mySQL, myConnection)
:             myCommand.Connection.Open()
:             myCommand.ExecuteNonQuery()
:         Catch objE As Exception
:             status.Text = "Exception on data edit " & objE.Message
:         Finally
:             myConnection.Close()
:         End Try
:     End Sub

First, the IDE tells me that myConnection in the Finally clause is not declared (?). At runtime I get the error "Operation must use an updateable query.". I practically cut & paste that code from the help on ExecuteNonQuery!!!
:

The definition of myConnection is in local scope, which is new for Visual Basic.NET. This means that defining the connection in try part will not make it accessible in the finally part. The solution is to dim mYconnection above the "Try" statement.

That really is "local" scope! Yes, that worked fine thanks.