textbox, datagrid and datatables all not updating

  • Thread starter Thread starter sparkle
  • Start date Start date
S

sparkle

Hi,

I'm having a problem getting data into my db using datatable and
commandbuilder.

The 'cars' table in my sql server holds: employeeID, carID, make,
model, year.

I have a datagrid with car info in it. The grid is bound to a
datatable called dtAutos. The grid includes make, model, year. The
grid updates, adds a new row just fine into the db, but without the
employeeID and/or CarID info. If I manually put an employeeID or
CarID into the table, the row will appear.

I have textboxes with the employeeID and CarID data. They are bound to
a datatable called dtAssignments.

When I add a new car to the datagrid and click the update button, I
want the new row to have the employeeID from the textbox inserted
into the db along with the make, model and year.

Here is how I'm attempting to do this:

Sub GetCommands()
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand
da.DeleteCommand = cb.GetDeleteCommand
da.UpdateCommand = cb.GetUpdateCommand
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click

Dim CurrentRow As DataRow
Dim iPos As Integer

dtAutos = dsAutos.Tables("cars")
CurrentRow = dtAutos.Rows(iPos)

CurrentRow("carid") = txtCarID.Text
CurrentRow.AcceptChanges()

Try
GetCommands()
da.Update(dtAutos)

Catch ex As Exception
MsgBox(ex.ToString)
Clipboard.SetDataObject(ex.ToString, True)
End Try
grdAutos.Refresh()

End Sub

Can anybody help? If you need clarification, post back, I'm very
confused as to why I can't read info straight from a textbox into a
db...
 
If you call AcceptChanges right before Update, then the changes will never
go back to the db. AcceptChanges resets the rowstates to unchanged so
calling Update all year won't do anything.
http://www.knowdotnet.com/articles/efficient_pt4.html

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
sparkle said:
Hi,

I'm having a problem getting data into my db using datatable and
commandbuilder.

The 'cars' table in my sql server holds: employeeID, carID, make,
model, year.

I have a datagrid with car info in it. The grid is bound to a
datatable called dtAutos. The grid includes make, model, year. The
grid updates, adds a new row just fine into the db, but without the
employeeID and/or CarID info. If I manually put an employeeID or
CarID into the table, the row will appear.

I have textboxes with the employeeID and CarID data. They are bound to
a datatable called dtAssignments.

When I add a new car to the datagrid and click the update button, I
want the new row to have the employeeID from the textbox inserted
into the db along with the make, model and year.

Here is how I'm attempting to do this:

Sub GetCommands()
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand
da.DeleteCommand = cb.GetDeleteCommand
da.UpdateCommand = cb.GetUpdateCommand
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click

Dim CurrentRow As DataRow
Dim iPos As Integer

dtAutos = dsAutos.Tables("cars")
CurrentRow = dtAutos.Rows(iPos)

CurrentRow("carid") = txtCarID.Text
CurrentRow.AcceptChanges()

Try
GetCommands()
da.Update(dtAutos)

Catch ex As Exception
MsgBox(ex.ToString)
Clipboard.SetDataObject(ex.ToString, True)
End Try
grdAutos.Refresh()

End Sub

Can anybody help? If you need clarification, post back, I'm very
confused as to why I can't read info straight from a textbox into a
db...
Click to expand...
 
Ya just couldnt resist helping sometone called 'Sparkle' could ya Bill !

Hows things eh ?

OHM



William Ryan eMVP said:
If you call AcceptChanges right before Update, then the changes will never
go back to the db. AcceptChanges resets the rowstates to unchanged so
calling Update all year won't do anything.
http://www.knowdotnet.com/articles/efficient_pt4.html

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Click to expand...
 
Hi Sparkle,

As Bill said the acceptchanges is a bad thing in your code however as well
what I write bellow.

Normaly the code I show you bellow will be enough, and probably is the
EndCurrentEdit your missing link (Because there is no row change, the data
is not entered in the table).

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)

'when there is only one table you can set this above somewhere global and do
this one time after a fill of the dataset.

BindingContext(dtAutos).EndCurrentEdit()

Try
da.Update(dtAutos)
Catch ex As Exception
MsgBox(ex.ToString)
Clipboard.SetDataObject(ex.ToString, True)
End Try
End Sub

I hope this helps?

Cor
 
Thanks for responding,

I know my post was confusing...

I CAN already get data into my db. Just not the ENTIRE row.

I'm missing carID or EmployeeID because the user isn't actually typing
them into the datagrid. I don't want that info to show in the data
grid.

The carID and EmployeeID on the form are from another datatable, so I
want to be able to programmatically say "whatever is currently in
txtCarID.text- go into the current row I've typed in the datagrid"

Is this possible?

I thought something like this would work, (but doesn't)
CurrentRow("carid") = txtCar.Text

Thanks, I hope you understand what I'm trying to do!
 
Back
Top