Trying a simple update to Access 2000...

[TheKidd554]

G'day,




Was wondering if someone could look at this simple code and tell me why it isn't updating my Access 2000 database. The form just has a datagrid on it with a Save button. No errors pop up. It fills the datagrid fine and when I hit the Save button it puts '555' in the Address1 area and shows it in the datagrid as well. It's when I hit the Save button that nothing gets updated to the database itself.

Imports System.data.oledb
Imports System.Data
PublicClass Form1
Inherits System.Windows.Forms.Form
Friend connstr AsString
Friend querystr AsString
Friend ds AsNew DataSet
Friend conn As OleDbConnection
Friend da As OleDbDataAdapter

PrivateSub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load

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

"Data Source=c:\documents and settings\bwinkworth\my documents\truck rates.mdb"

querystr = "SELECT * FROM Carrier;"

conn = New OleDbConnection(connstr)

da = New OleDbDataAdapter(querystr, conn)

da.Fill(ds, "Carrier")

dgMain.DataSource = ds.Tables("Carrier")

EndSub

PrivateSub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim cb As OleDbCommandBuilder
Dim table AsNew DataTable
Dim row As DataRow

Try


conn.Open()

cb = New OleDbCommandBuilder(da)
da.UpdateCommand = cb.GetUpdateCommand()
table = ds.Tables("Carrier")
row = table.Rows(0)
row("Address1") = "555"


dgChanges.DataSource = ds.GetChanges

dgMain.DataSource = table

ds.AcceptChanges()



da.Update(ds, "Carrier")

conn.Close()



Catch ex As Exception

MsgBox(ex.Message)



EndTry

'MsgBox(da.UpdateCommand.CommandText, MsgBoxStyle.OKOnly)

EndSub

EndClass



Thanks,

Kidd

 

[PelleH]

Hi,

If you do ds.AcceptChanges() before calling the update method changed rows in the dataset will be marked as unchanged and not included for update.

/Pelle

 

[TheKidd554]

Hi Pelle..thanks for the reply. Ok I omitted the ds.changes() line in the code and I now get the error I'll attach here in a screen shot. Also on my datagrid, The row that was updated has a red exclamation mark over it. When I hold the mouse over the exclamation mark the same error appears. See screenshot for error.

Thanks alot
Kidd

Edit: Ok I simplified the SELECT query thinking that it was bringing in too much as per the screen shot and it works fine. But there's something in the SELECT * FROM Carrier that it can't update. Any ideas?

 

[TheKidd554]

Got it resolved. It was a '%' sign in one of the field names in the database.

Kidd