data grid update

[KevinMGore]

The following code:
Try
da.Update(ds.Ta bles(dg1Name))
Catch ex As Exception
Console.WriteLi ne(ex.ToString)
MessageBox.Show (ex.ToString)
End Try

causes this error:

System.InvalidO perationExcepti on: Update requires a valid UpdateCommand when
passed DataRow collection with modified rows.
at System.Data.Com mon.DbDataAdapt er.Update(DataR ow[] dataRows,
DataTableMappin g tableMapping)
at System.Data.Com mon.DbDataAdapt er.Update(DataT able dataTable)
at comments.Form1. Button1_Click(O bject sender, EventArgs e) in
G:\Shared\VBSha re\dotnet\Kevin \comments\Form1 .vb:line 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(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
Dim commands As String = Microsoft.Visua lBasic.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(strD atabase)

'fill the grid
strSQL = "select '" & "Y" & "' as Selected, seqNo, comment from
sql_oe_comments where custNo = '" & gstrCustomer & "'"
cb = New SqlCommandBuild er(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(dg1Na me)
.AllowSorting = True
.AlternatingBac kColor = 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)).E ndCurrentEdit()
Try
da.Update(ds.Ta bles(0))
Catch ex As Exception
Console.WriteLi ne(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(dg1Na me), ts.MappingName = dg1Name I get an error on
this line of code: Me.DG1.TableSty les(0).GridColu mnStyles(2).Hea derText =
"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)).E ndCurrentEdit()
Try
da.Update(ds.Ta bles(0))
Catch ex As Exception
Console.WriteLi ne(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.SqlDa taAdapter
Public cmd As SqlClient.SqlCo mmandBuilder()
Private Sub Form Load
strConn = "Initial Catalog=data_05 ;Data Source=kevin;Us er
ID=test;passwor d=test;"
strSQL = "select * from sql_oe_comments "
da = New SqlClient.SqlDa taAdapter(strSQ L, 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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Try
dg1.Update()
'da.update(ds.t ables(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.SqlDa taAdapter ............ Private Sub Form Load
strConn = "Initial Catalog=data_05 ;Data Source=kevin;Us er
ID=test;passwor d=test;"
strSQL = "select * from sql_oe_comments "
da = New SqlClient.SqlDa taAdapter(strSQ L, strConn)
da.Fill(ds)
dim cmd as new sqlClient.SqlCo mmandBuilder(da )
dg1.DataSource = ds.Tables(0).de faultview
End Sub
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click Me.BindingConte xt(dg1.DataSour ce).EndCurrentE dit()
Try da.update(ds.ta bles(0)) Catch ex As Exception
MessageBox.Show (ex.ToString)
End Try

End Sub

I hope this helps,

Cor