Continuation of issue from 4/16/05

[Gary Paris]

Cor,

I made the changes you suggested by I get an error message saying "Incorrect
syntax near the keyword 'trigger'.

What is wrong?

------------------------------ Last message from
4/17/05 -----------------------

Gary,

There are at least two things what makes that your update does not work.
You are have no commands in your dataadapter.
You use the acceptchanges wrong.

I have made some corrections typed (so watch typos) inline in this message
so look below to them.

You better create and dispose better as well the connection in those
procedures because now you don't free the connectionpool.

----------------------------------------------------------------------------
Private Sub Form2_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load

Dim strSQL As String = "Select * from Contact where sysid = '" &
g_sysID & "'"
DB = New SqlClient.SqlDa taAdapter(strSQ L, CN)
dim cb as new sqlclient.sqlco mmandbuilder(db )

ES.Clear()
DB.Fill(ES, "Contact")

If ES.Tables("Cont act").Rows.Coun t > 0 Then
txtAddress.Text =
ES.Tables("Cont act").Rows(0).I tem("con1_02_03 ")
txtFirst_Name.T ext =
ES.Tables("Cont act").Rows(0).I tem("First_Name ")
txtLast_Name.Te xt =
ES.Tables("Cont act").Rows(0).I tem("Last_Name" )
End If

End Sub
--------------------------------------------------------------------------
Here is the code to update the table
--------------------------------------------------------------------------
Private Sub btnUpdate_Click (ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnUpdate.Click

Try
es.Tables(0).Ro ws(0).Item("Fir st_Name") = txtFirst_Name.T ext
es.Tables(0).Ro ws(0).Item("Las t_Name") = txtLast_Name.Te xt
es.Tables(0).Ro ws(0).Item("con 1_02_03") = txtAddress.Text ES.AcceptChange s()
delete the row above, this means that all rowstates which are set to a
changed state will be set to unchanged and the changes are accepted, so the
dataadapter has nothing to change. It is implicitly done by the dataadapter
when a change is done.
DB.Update(ES, "Contact")

Catch ex As Exception

MessageBox.Show (ex.Message)

End Try

End Sub

I hope this helps,

Cor

[Cor Ligthert]

Gary,

I don't see it, can you show the corrected code again? It will not be the
first time that there are misunderstandin gs.

Cor

[Gary Paris]

Cor,

The code you asked me to put in was (In the Load routine)

dim cb as new sqlclient.sqlco mmandbuilder(db )

and in the (update routine), remove the following

ES.AcceptChange s()
One question though, when I create the sqlcommandbuild er, doesn't it have to
be global? Since I have the two routines, they both need to be aware of it?

Thanks,

Gary


i
"Cor Ligthert" <no************ @planet.nl> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

Gary,

I don't see it, can you show the corrected code again? It will not be the
first time that there are misunderstandin gs.

Cor

[Cor Ligthert]

Gary,

You don't use the commandbuilder anymore when it has created the commands in
the dataadapter.

However,

I looked what furter in your code.
I don't see a connection.open
I don't see how you fill the g_sysID

Therefore at what moment does that error come.

cor

[Gary Paris]

Cor,

Are you saying that I don't need the line with the commandbuilder? You
suggested that I put it in. g_sysID is global and gets filled when I click
on the datagrid to isolate the record I want to update. When the Load
routine executes the three text fields are populated correctly. When I do
the update, that is when I get the error message regarding the trigger.

Gary

"Cor Ligthert" <no************ @planet.nl> wrote in message
news:Oh******** ******@TK2MSFTN GP14.phx.gbl...

Gary,

You don't use the commandbuilder anymore when it has created the commands
in the dataadapter.

However,

I looked what furter in your code.
I don't see a connection.open
I don't see how you fill the g_sysID

Therefore at what moment does that error come.

cor

[Cor Ligthert]

Gary,

I tested completly your code, however with northwind employee database.
It did completly as it should go.

\\\
Dim db As New SqlDataAdapter
Dim g_sysID As String = "1"
Dim ES As New DataSet
Dim CN As New SqlConnection _
("Server=MyServ er; DataBase=Northw ind; Integrated Security=SSPI")

Private Sub Form2_Load(ByVa l sender As System.Object, _
ByVal e As System.EventArg s) Handles MyBase.Load
Dim strSQL As String = _
"Select * from Employees where Employeeid = '" & g_sysID & "'"
db = New SqlClient.SqlDa taAdapter(strSQ L, CN)
db.Fill(ES, "Employees" )
Dim cb As New SqlCommandBuild er(db)
If ES.Tables("Empl oyees").Rows.Co unt > 0 Then
txtFirst_Name.T ext = _
ES.Tables("Empl oyees").Rows(0) .Item("FirstNam e").ToString
txtLast_Name.Te xt = _
ES.Tables("Empl oyees").Rows(0) .Item("LastName ").ToString
End If
End Sub

Private Sub btnUpdate_Click (ByVal sender As System.Object, _
ByVal e As System.EventArg s) Handles btnUpdate.Click
ES.Tables(0).Ro ws(0).Item("Fir stName") = txtFirst_Name.T ext
ES.Tables(0).Ro ws(0).Item("Las tName") = txtLast_Name.Te xt
Try
db.Update(ES, "Employees" )
Catch ex As Exception
MessageBox.Show (ex.Message)
End Try
End Sub
///

I hope this helps,

Cor

[Gary Paris]

Cor,

I created a new project, did a "cut-and-paste" of this code and it worked on
my machine. Dunno what happened with my other project but I'll check it
out. Thanks for giving me a working codeset.

Gary

"Cor Ligthert" <no************ @planet.nl> wrote in message
news:us******** ******@TK2MSFTN GP09.phx.gbl...

Gary,

I tested completly your code, however with northwind employee database.
It did completly as it should go.

\\\
Dim db As New SqlDataAdapter
Dim g_sysID As String = "1"
Dim ES As New DataSet
Dim CN As New SqlConnection _
("Server=MyServ er; DataBase=Northw ind; Integrated Security=SSPI")

Private Sub Form2_Load(ByVa l sender As System.Object, _
ByVal e As System.EventArg s) Handles MyBase.Load
Dim strSQL As String = _
"Select * from Employees where Employeeid = '" & g_sysID & "'"
db = New SqlClient.SqlDa taAdapter(strSQ L, CN)
db.Fill(ES, "Employees" )
Dim cb As New SqlCommandBuild er(db)
If ES.Tables("Empl oyees").Rows.Co unt > 0 Then
txtFirst_Name.T ext = _
ES.Tables("Empl oyees").Rows(0) .Item("FirstNam e").ToString
txtLast_Name.Te xt = _
ES.Tables("Empl oyees").Rows(0) .Item("LastName ").ToString
End If
End Sub

Private Sub btnUpdate_Click (ByVal sender As System.Object, _
ByVal e As System.EventArg s) Handles btnUpdate.Click
ES.Tables(0).Ro ws(0).Item("Fir stName") = txtFirst_Name.T ext
ES.Tables(0).Ro ws(0).Item("Las tName") = txtLast_Name.Te xt
Try
db.Update(ES, "Employees" )
Catch ex As Exception
MessageBox.Show (ex.Message)
End Try
End Sub
///

I hope this helps,

Cor