Continuation of issue from 4/16/05

  • Thread starter Thread starter Gary Paris
  • Start date Start date
G

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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim strSQL As String = "Select * from Contact where sysid = '" &
g_sysID & "'"
DB = New SqlClient.SqlDataAdapter(strSQL, CN)
Click to expand...

dim cb as new sqlclient.sqlcommandbuilder(db)

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

If ES.Tables("Contact").Rows.Count > 0 Then
txtAddress.Text =
ES.Tables("Contact").Rows(0).Item("con1_02_03")
txtFirst_Name.Text =
ES.Tables("Contact").Rows(0).Item("First_Name")
txtLast_Name.Text =
ES.Tables("Contact").Rows(0).Item("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.EventArgs) Handles btnUpdate.Click

Try
es.Tables(0).Rows(0).Item("First_Name") = txtFirst_Name.Text
es.Tables(0).Rows(0).Item("Last_Name") = txtLast_Name.Text
es.Tables(0).Rows(0).Item("con1_02_03") = txtAddress.Text
Click to expand...
ES.AcceptChanges()
Click to expand...

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
Click to expand...

I hope this helps,

Cor
 
Gary,

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

Cor
 
Cor,

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

dim cb as new sqlclient.sqlcommandbuilder(db)

and in the (update routine), remove the following

ES.AcceptChanges()


One question though, when I create the sqlcommandbuilder, doesn't it have to
be global? Since I have the two routines, they both need to be aware of it?

Thanks,

Gary




i
 
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,

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
 
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=MyServer; DataBase=Northwind; Integrated Security=SSPI")

Private Sub Form2_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim strSQL As String = _
"Select * from Employees where Employeeid = '" & g_sysID & "'"
db = New SqlClient.SqlDataAdapter(strSQL, CN)
db.Fill(ES, "Employees")
Dim cb As New SqlCommandBuilder(db)
If ES.Tables("Employees").Rows.Count > 0 Then
txtFirst_Name.Text = _
ES.Tables("Employees").Rows(0).Item("FirstName").ToString
txtLast_Name.Text = _
ES.Tables("Employees").Rows(0).Item("LastName").ToString
End If
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnUpdate.Click
ES.Tables(0).Rows(0).Item("FirstName") = txtFirst_Name.Text
ES.Tables(0).Rows(0).Item("LastName") = txtLast_Name.Text
Try
db.Update(ES, "Employees")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
///

I hope this helps,

Cor
 
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
 
Back
Top