Updating table in code

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

Gary

I have been trying to update a record that I edited and can't seem to figure
out what I'm missing. Here is the code that I have to load the record on
the form:

'
****************************************************************************
*****************
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 cmb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(DA)
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.txtFullName.DataBindings.Add("Text", objDS, "Contact.full_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

And here is the code for the update:

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

Try
DirectCast(BindingContext(objDS.Tables(0)),
CurrencyManager).EndCurrentEdit()
Me.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub


What am I missing? Do I need to code the INSERT, UPDATE, and DELETE command
and if so, where do I put them? I have nine fields that I'm working with so
please help me update my record. Any code examples would be appreciated.

Thanks,

Gary
 
Hi Gary,

Where did you let that special code, that should work, although it is not
standard, I did find it very nice to check concurrency errors.

However change it in this what is standard used in this newsgroups and in
samples.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click
BindingContext(objDS.Tables(0)).EndCurrentEdit()
Try
if objDS.haschanges then
Da.update(objDS)
end if
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Cor
 
Hi Cor,

I tried the code you suggested and I get the following error message:

Update unable to find TableMapping['Table'] or Datatable 'Table'

Any suggestions?

Thanks,

Gary
 
Cor,

I just tried the following change (Putting the table name 'Contact' in) and
I received an error saying "Incorrect syntax near the word 'trigger'".
HELP! Do I need to specify UPDATE statements? If so where EXACTLY do I put
them?

Gary



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

BindingContext(objDS.Tables("Contact")).EndCurrentEdit()

Try
If objDS.HasChanges Then
DA.Update(objDS, "Contact")
End If

Me.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub
 
Hi Gary,

As forever I do not want to change to much in peoples code.

This is your code, how can that commandbuilder build commands if he does not
know nothing about what he has to build. Replace the commandbuilder to the
place where it has all information.
(Before the fill)

This did cost time to see this.

And others place it direct for the update, which I messaged you before last
week.

Cor

strSQL = "Select * from Contact WHERE sysID = '" & g_sysID & "'"
objDS.Clear()

Dim cmb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(DA)
Dim selectCommand As New SqlClient.SqlCommand(strSQL)
DA.SelectCommand = selectCommand
DA.SelectCommand.Connection = CN

DA.Fill(objDS, "Contact")
 
Hi Cor,

I did go back and look at the messages from the other day and I just don't
see what else I need to do. I'm sorry but I don't understand. I did place
the CommandBuilder before the fill as you requested. I don't understand the
"Replace the commandbuilder to the place where it has all the information"
line. To me it is already there. Can you elaborate more or give me a
better example of what I have to do? I am lost.

Thanks,

Gary
 
Hi Gary,

It should work now, I do not look anymore to this newsgroup today, (It is
here 20:00 saterday night, however I was curious if you did succeed)


However past in that part of code from the fill and from the update, I
really do not know why it should not work

Before I did send the message some days ago I tested it with your previous
methode completly and now with that new methode I did test it also.

I look to it what is here tomorrow morning, there should be something very
simple what is on the wrong place.

Cor
 
Gary:

1) Do yourself a favor and get rid of the concatenated sql, opt for
Paramaterized queries instead. It's not the problem you are asking about,
but it can introduce some really really bad security problems, not to
mention performance issues.
strSQL = "Select * from Contact WHERE sysID = @g_sysID"
cmd.Parameters.Add("@g_sysID", SqlDbType.WhateverType,
FieldLengthInDb).value = g_sysID

2) The commandbuilder should genereate these for you (Update, Delete etc)
but CB's really suck. The problem is that you aren't calling update that I
can see. In your btn_Save code, add DA.Update(objDS1, "TableName")

That should fix it for you.

Let me know if not.

Bill
 
Apologies for butting in, but just trying to clarify one of Cor's points -

The CommandBuilder cannot function correctly unless it knows what the select
command for the adapter is. The help file example looks like this...

Public Function SelectSqlSrvRows(myDataSet As DataSet, myConnection As
String, mySelectQuery As String, myTableName As String) As DataSet
Dim myConn As New SqlConnection(myConnection)
Dim myDataAdapter As New SqlDataAdapter()
myDataAdapter.SelectCommand = New SqlCommand(mySelectQuery, myConn)
Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(myDataAdapter)
..
..
..
i.e. the select command (and, incidentally, the connection) is set up before
using the CommandBuilder.
 
Thanks for giving me an example, but here is my code and it works bringing
in the record but I'm not sure it's correct

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

Dim cmb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(DA)

DA.Fill(objDS, "Contact")

Me.txtFirstName.DataBindings.Add("Text", objDS,
"Contact.first_name")
Me.txtLastName.DataBindings.Add("Text", objDS, "Contact.Last_name")
Me.txtFullName.DataBindings.Add("Text", objDS, "Contact.full_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

When I want to update, I have the following code:

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

BindingContext(objDS.Tables("Contact")).EndCurrentEdit()

Try
If objDS.HasChanges Then
DA.Update(objDS, "Contact")
End If

Me.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

but the above code doesn't work. HELP
 
Some questions:

Where does objDS get defined?
If you add a breakpoint on the Try line of your btnSave_Click and then step
through the code (use F11) then what happens?
If it gets to the line DA.Update(objDS, "Contact") then what does SQL
profiler show?
 
I made two changes. One was removing the line If objDS.HasChanges
Then

and the other was had to do with the SQL statement. In the table, there was
a field called [trigger]. That was the definition so I changed the name to
trigger1. That seemed to solve the problem. Now everything works and I can
go back to work again.

I was pulling out the remaining hairs on my head over this.

Thanks loads,

Gary
 
Back
Top