data grid update

[KevinMGore]

The following code:
Try
da.Update(ds.Tables(dg1Name))
Catch ex As Exception
Console.WriteLine(ex.ToString)
MessageBox.Show(ex.ToString)
End Try

causes this error:

System.InvalidOperationException: Update requires a valid UpdateCommand when
passed DataRow collection with modified rows.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at comments.Form1.Button1_Click(Object sender, EventArgs e) in
G:\Shared\VBShare\dotnet\Kevin\comments\Form1.vb:l ine 161

The code is behiend a button on the form called Update. If I change the data
in the grid I get the error when I press the update button. Any advice will
be much appreciated.

Kevin

[Cor Ligthert]

Kevin,

How did you create your update command?

You have as far as I know 3 possibilities
- by hand yourself
- using an AdapterWizard
- using an commandbuilder.

Now your error can be everything.

Cor

[KevinMGore]

Cor,
I did not create an update command, I used the following code to fill the
grid, changed one of the records and thought that the dataset and data
adaptor took care of the rest?

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim commands As String = Microsoft.VisualBasic.Command
Dim strDatabase, strSQL As String

strDatabase = Mid(commands, 1, 7)
gstrCustomer = Mid(commands, 9, 12)
gstrOrder = Mid(commands, 22, 8)
gstrOrdType = Mid(commands, 31, 1)

Call connectSQL(strDatabase)

'fill the grid
strSQL = "select '" & "Y" & "' as Selected, seqNo, comment from
sql_oe_comments where custNo = '" & gstrCustomer & "'"
cb = New SqlCommandBuilder(da)

ds = New DataSet()

da = New SqlDataAdapter(strSQL, sqlConn)

da.Fill(ds, dg1Name)

With DG1 'this is the datagrid on the form
.DataSource = ds.Tables(dg1Name)
.AllowSorting = True
.AlternatingBackColor = System.Drawing.Color.Bisque
End With

'format the grid
Call formatGridDG1()
End Sub
"Cor Ligthert" wrote:

Kevin,

How did you create your update command?

You have as far as I know 3 possibilities
- by hand yourself
- using an AdapterWizard
- using an commandbuilder.

Now your error can be everything.

Cor

[Cor Ligthert]

Kevin,

There is a commandbuilder in the code you showed and that I asked for.

By the way, are you sure that this works

da.Fill(ds, dg1Name)

And not just this
da.Fill(ds)

Let us assume that you have a button on your form on what you click and in
that click event you set this code

BindingContext(ds.Tables(0)).EndCurrentEdit()
Try
da.Update(ds.Tables(0))
Catch ex As Exception
Console.WriteLine(ex.ToString)
MessageBox.Show(ex.ToString)
End Try

Than it is bad code, however it will probably work and when you are a newbie
is that one of the first things that is important.

I hope this helps

Cor.

[KevinMGore]

Cor,
Thanks for the reply. dg1Name is a public constant. I found (by trial and
error) that if I don't use it in these three places: da.Fill(ds, dg1Name),
..DataSource = ds.Tables(dg1Name), ts.MappingName = dg1Name I get an error on
this line of code: Me.DG1.TableStyles(0).GridColumnStyles(2).HeaderTe xt =
"COMMENTS"

I took out the grid foramtting and used ds.fill(0) as you suggested adding
the binding code and received the same error originally reported about the
command builder? If there is a better way to do this please let me know, the
book I have suggests that it should work, obviously I must be doing something
wrong.

Thanks,

Kevin

Kevin,

There is a commandbuilder in the code you showed and that I asked for.

By the way, are you sure that this works

da.Fill(ds, dg1Name)

And not just this
da.Fill(ds)

Let us assume that you have a button on your form on what you click and in
that click event you set this code

BindingContext(ds.Tables(0)).EndCurrentEdit()
Try
da.Update(ds.Tables(0))
Catch ex As Exception
Console.WriteLine(ex.ToString)
MessageBox.Show(ex.ToString)
End Try

Than it is bad code, however it will probably work and when you are a newbie
is that one of the first things that is important.

I hope this helps

Cor.

[Cor Ligthert]

Kevin,

Your select statement looks not simple for me to translate for the
commandbuilder, can you not first try it with a simple one as

"Select * from MyTable"

Cor

[KevinMGore]

Cor,
I tried a simple statement and got the error: "update requires a valid
update command..." I have a command builder object but have not structured
any update statements for it? Is there a repository of code samples that show
how this is done? The book I have isn't clear about how the update works.

Thanks,

Kevin

"Cor Ligthert" wrote:

Kevin,

Your select statement looks not simple for me to translate for the
commandbuilder, can you not first try it with a simple one as

"Select * from MyTable"

Cor

[Cor Ligthert]

Kevin,

Can you show us a part of your code as you have it now, the commandbuilder
needs in most cases like yours, nothing extra than that it is called.

Cor

[KevinMGore]

Cor,
Here's the code I'm currently using to fill the grid:
Public ds As New Data.DataSet()
Public da As SqlClient.SqlDataAdapter
Public cmd As SqlClient.SqlCommandBuilder()
Private Sub Form Load
strConn = "Initial Catalog=data_05;Data Source=kevin;User
ID=test;password=test;"
strSQL = "select * from sql_oe_comments"
da = New SqlClient.SqlDataAdapter(strSQL, strConn)
da.Fill(ds)
dg1.DataSource = ds.Tables(0)

End Sub
Then I modify data in the grid and click the update button which has the
following code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Try
dg1.Update()
'da.update(ds.tables(0))

Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

End Sub
With this code I do not get an error when I press the button but the sql
data does not change in the database? If I uncomment the da.update... then I
get an error that a valid update command is required?
"Cor Ligthert" wrote:

Kevin,

Can you show us a part of your code as you have it now, the commandbuilder
needs in most cases like yours, nothing extra than that it is called.

Cor

[Cor Ligthert]

Kevin,

I am glad I asked it to show some code, you don't use a commandbuilder, you
only create a placeholder for it. Can you try the changes I wrote in line. I
did it in this message so watch typos.

Public ds As New Data.DataSet()
Public da As SqlClient.SqlDataAdapter ............ Private Sub Form Load
strConn = "Initial Catalog=data_05;Data Source=kevin;User
ID=test;password=test;"
strSQL = "select * from sql_oe_comments"
da = New SqlClient.SqlDataAdapter(strSQL, strConn)
da.Fill(ds)
dim cmd as new sqlClient.SqlCommandBuilder(da)
dg1.DataSource = ds.Tables(0).defaultview
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click Me.BindingContext(dg1.DataSource).EndCurrentEdit()
Try da.update(ds.tables(0)) Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

End Sub

I hope this helps,

Cor

[KevinMGore]

Thank you very much - it works now.

"Cor Ligthert" wrote:

Kevin,

I am glad I asked it to show some code, you don't use a commandbuilder, you
only create a placeholder for it. Can you try the changes I wrote in line. I
did it in this message so watch typos.

Public ds As New Data.DataSet()
Public da As SqlClient.SqlDataAdapter

............

Private Sub Form Load
strConn = "Initial Catalog=data_05;Data Source=kevin;User
ID=test;password=test;"
strSQL = "select * from sql_oe_comments"
da = New SqlClient.SqlDataAdapter(strSQL, strConn)
da.Fill(ds)

dim cmd as new sqlClient.SqlCommandBuilder(da)
dg1.DataSource = ds.Tables(0).defaultview

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Me.BindingContext(dg1.DataSource).EndCurrentEdit()

Try

da.update(ds.tables(0))

Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

End Sub

I hope this helps,

Cor