database update

[AWW]

XP - VB 2005 - Sql Express 2005
I added data to a defined but empty dataset - reflected in a
datagridview.
I then followed the instructions in
http://msdn2.microsoft.com/en-us/library/xzb1zw3x.aspx
to write the code below:
-------------------------------------------------------------------
Me.CBOEdbDataSet.StocksMain.AcceptChanges()
Try
Validate()
' Me.StocksMainBindingSource.EndEdit()
Me.StocksMainTableAdapter.Update(Me.CBOEdbDataSet.StocksMain)
MsgBox("Update Worked")
Catch ex As Exception
MsgBox("Update Failed")
End Try

 

[Kerry Moorman]

AWW,

Don't call AcceptChanges.

Kerry Moorman

 

[AWW]

Removed AcceptChanges and now get "Update Failed"
which makes more sense since it did fail.
Thoughts?
Also printed out the "ex" exception - multiple lines with last one
pointing at "Return Me.Adapter.Update(data table)"

 

[Armin Zingler]

Removed AcceptChanges and now get "Update Failed"
which makes more sense since it did fail.
Thoughts?
Also printed out the "ex" exception - multiple lines with last one
pointing at "Return Me.Adapter.Update(data table)"

Is "Update Failed" all in ex.message? Maybe you have to examine 'ex' and
look at the instance type members (ex might be of type SqlException
which has many additional members compared to System.Exception). Or,
better, add "catch ex as SqlException" *before* "catch ex as exception".
Run again and examine that ex-object.


Armin

 

[AWW]

I did "catch ex as SqlException" and got:
at
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable
dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at
SP500Setup.CBOEdbDataSetTableAdapters.StocksMainTableAdapter.Update(StocksMainDataTable
dataTable) in
C:\MyVB\SP500Setup\SP500Setup\CBOEdbDataSet.Designer.vb:line 713
at SP500Setup.MainForm.SPmove_Click(Object sender, EventArgs e) in
C:\MyVB\SP500Setup\SP500Setup\Form1.vb:line 76

 

[Armin Zingler]

I did "catch ex as SqlException" and got:
at
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable
dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at
SP500Setup.CBOEdbDataSetTableAdapters.StocksMainTableAdapter.Update(StocksMainDataTable
dataTable) in
C:\MyVB\SP500Setup\SP500Setup\CBOEdbDataSet.Designer.vb:line 713
at SP500Setup.MainForm.SPmove_Click(Object sender, EventArgs e) in
C:\MyVB\SP500Setup\SP500Setup\Form1.vb:line 76

Examine the exception object. It has not only a stack trace but several
other properties, like ex.message.


Armin

 

[AWW]

I did "catch ex as SqlException" and got:
at
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable
dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at
SP500Setup.CBOEdbDataSetTableAdapters.StocksMainTableAdapter.Update(StocksMainDataTable
dataTable) in
C:\MyVB\SP500Setup\SP500Setup\CBOEdbDataSet.Designer.vb:line 713
at SP500Setup.MainForm.SPmove_Click(Object sender, EventArgs e) in
C:\MyVB\SP500Setup\SP500Setup\Form1.vb:line 76

Examine the exception object. It has not only a stack trace but several
other properties, like ex.message.


Armin

ex.Message: Update requires a valid InsertCommand when passed DataRow
collection with new rows.
But Help talks about not being able to do an InsertCommand at design
time - and the Help description of InsertCommand says, I think, that
is what I need for adding new rows to the database. So how to do it?
Thanks again.

 

[Armin Zingler]

ex.Message: Update requires a valid InsertCommand when passed
DataRow collection with new rows.
But Help talks about not being able to do an InsertCommand at design
time
Where?

- and the Help description of InsertCommand says, I think, that
is what I need for adding new rows to the database. So how to do it?
Thanks again.

If you used the designer to create the dataadapter, you can select the
"create insert/delete/update commands" option. If you've created it by
code, you can create an OleDbCommandbuilder (or SqlCommandBuilder)
passing the Dataadapter to the constructor.


Armin