Updating DataAdapter in Subroutines

[Gary]

I have a form that takes one Contact record and edit's it. The routine used
is as follows:

Private Sub ContactMain_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim strSQL As String

strSQL = "Select * from Contact WHERE sysID = '" & g_sysID & "'"
objDS.Clear()
Dim selectCommand As New SqlClient.SqlCommand(strSQL)
DA.SelectCommand = selectCommand
DA.SelectCommand.Connection = CN

DA.Fill(objDS, "Contact")

Me.txtFirstName.DataBindings.Add("Text", objDS,
"Contact.first_name")
Me.txtLastName.DataBindings.Add("Text", objDS, "Contact.Last_name")
Me.txtAddress1.DataBindings.Add("Text", objDS, "Contact.con1_02_03")
Me.txtPhone.DataBindings.Add("Text", objDS, "Contact.Phone1")
Me.txtCity.DataBindings.Add("Text", objDS, "Contact.con1_02_05")
Me.txtState.DataBindings.Add("Text", objDS, "Contact.con1_02_06")
Me.txtZip.DataBindings.Add("Text", objDS, "Contact.con1_02_07")
Me.txtEMail.DataBindings.Add("Text", objDS, "Contact.con1_03_01")

End Sub

This works fine and the fields get populated. When I want to update the
record I hit the "Save" button and execute the following:

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

Try
Dim rows As Integer
DA.ContinueUpdateOnError = True
rows = DA.Update(objDS, "Contact")
If rows > 0 Then
MsgBox("Data updated")
Else
MsgBox("Data Not Updated")
End If

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

I get the message saying that the data is not updated. Any help would be
appreciated.

Thanks,

Gary

 

[Cor Ligthert]

Hi Gary,

Mostly the error is this

DirectCast(BindingContext(objds.Tables(0)),
CurrencyManager).EndCurrentEdit()

Place this as first statement in your update routine, if this does not solve
the problem I will look further, so message it than?

Cor

 

[Gary]

Cor,

I tried the statement and it doesn't work. The table didn't get updated and
no error was generated.

Thanks,

Gary

 

[Cor Ligthert]

Hi Gary,

You probably have nowhere created an insert an update and a delete command.

You can do that in a simple situation simple by adding the code beneath that
in the load after creating the dataadapter

cmb = New Sqlclient.SqlCommandBuilder(DA)

or by building all commands by hand (I would only do that if I had
problems).

Cor

 

[Gary]

Hi Cor,

Thanks for the quick reply. I added a dataadapter by "drag and drop" and
then looked at the generated code. Do I need to put all that code in
because I am doing all the command by hand or can I somehow use the
generated dataadapter code that was generated? Do I need all the parameters
statements generated, the Insert, Update, Delete statements also?

Can you show me the commands that I need to update my database or should I
just copy and rename what was generated by VB?

There must be 10 or more parameter statements associated with the Insert and
Update commands. What do most people do when they are learning VB.NET at
the beginning? Do they use the "wizards" to create everything or do they
dig in and learn it the way I'm trying to? I thought it would be better if
I could create all code by hand but it is an incredible task. I've been
doing VB 6 for a few years, but VB.NET is light years ahead and the learning
curve is pretty steep. I have a couple of books but mostly they have the
very basic (no pun intended) examples, but I guess thats why the newsgroups
are so popular!

Thanks,

Gary

 

[Cor Ligthert]

Hi Gary,

You only needs this

cmb = New Sqlclient.SqlCommandBuilder(DA)

on a proper place, that build it however it is buggy is said when you have
complex insert update and delete commands

Cor

 

[Gary]

Hi Cor,

Can you elaborate a bit more for me please. Where do I put the line? Do I
need to define cmb? Is this a global assign?

Thanks,

Gary

 

[Cor Ligthert]

Hi Gary

I am making a mash with you all errors comes with you

dim cmb as New Sqlclient.SqlCommandBuilder(DA)

I did want to stop however I was looking if it was working with you, message
me when it works or not your problem is so simple for me.

Sorry

Cor

 

[Cor Ligthert]

Hi Gary,

I forgot place it direct before the update than we are sure it works.

Cor