Continuation of issue from 4/16/05

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)

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
ES.AcceptChanges()

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
 
C

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 misunderstandings.

Cor
 
G

Gary Paris

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
 
C

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
 
G

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
 
C

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=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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top