Updating a SQL database

[amber]

Hello
I can't get my update command to work..

I've used the sqlDataAdapter wizard to create a dataAdapter sqlDA_CB_LP, and it created the 'UPDATE' code as follows

Me.sqlDA_CB_LP.UpdateCommand = Me.SqlUpdateCommand
Me.SqlUpdateCommand4.Connection = Me.SqlConnection

Me.SqlUpdateCommand4.Parameters.Add(New System.Data.SqlClient.SqlParameter("@STR_COMMENT", System.Data.SqlDbType.NVarChar, 1073741823, "STR_COMMENT")

In my sub that populates the form

cbLP = New SqlCommandBuilder(sqlDA_CB_LP
dsCBLP1.clear(
sqlDA_CB_LP.fill(dsCBLP1
dvCB_LP = New Datavie

with dvCB_L
.table = dsCBLP1.Tables("TDT_CUT_BLOCK_LP"
.RowFilter = "ID_CUT_BLOCK = '" & txtBlockID.Text & "'
End Wit

Me.txtLPComm.DataBindings.Add("text", dvCB_LP, "STR_COMMENT"

Then under my "Save" button
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles btnSave.Clic

Tr
sqlDA_CB_LP.Update(DsCBLP1, "TDT_CUT_BLOCK_LP"
Catch ex As Exceptio
MessageBox.Show(ex.Message
End Tr

End Su

But, it doesn't work..
Any ideas what I'm doing wrong?

Thanks
Ambe

[William Ryan eMVP]

Hi Amber:
"amber" <an*******@discussions.microsoft.com> wrote in message
news:A5**********************************@microsof t.com...

Hello,
I can't get my update command to work...

I've used the sqlDataAdapter wizard to create a dataAdapter sqlDA_CB_LP, and it created the 'UPDATE' code as follows:
Me.sqlDA_CB_LP.UpdateCommand = Me.SqlUpdateCommand4
Me.SqlUpdateCommand4.Connection = Me.SqlConnection1

Me.SqlUpdateCommand4.Parameters.Add(New System.Data.SqlClient.SqlParameter("@STR_COMMENT",
System.Data.SqlDbType.NVarChar, 1073741823, "STR_COMMENT"))

In my sub that populates the form:

cbLP = New SqlCommandBuilder(sqlDA_CB_LP)
dsCBLP1.clear()
sqlDA_CB_LP.fill(dsCBLP1)
dvCB_LP = New Dataview

with dvCB_LP
.table = dsCBLP1.Tables("TDT_CUT_BLOCK_LP")
.RowFilter = "ID_CUT_BLOCK = '" & txtBlockID.Text & "'"
End With

Me.txtLPComm.DataBindings.Add("text", dvCB_LP, "STR_COMMENT")

Then under my "Save" button:
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles btnSave.Click

What exactly do you mean when you say it 'doesn't work'? There can be a few
things. The first thing to do is verify that you have changes in your
dataset, if you don't, calling update 10000000 times won't do a thing. So,
right before the Update command, insert this:

Debug.Assert(DsCBLP1.HasChanges)

Now, if you get a big ugly messagebox popping up, the problem is that you
don't have any changes in the DB. You may need to call EndCurrentEdit or
one of a few other things... So verify the HasChanges first and we'll
eliminate things from there.

Next, are you gettting an exception or is it just not updating the changes
in the DB?

Another thing, if you use the COnfiguration wizard, it should gen the logic
for you for the Update/Delete/Insert statements. you don't need a
commandbuilder and you don't want to use it if you already have your logic
in place. When you ran the configuration wizard, did it tell you that it
succesfully created the Update/Insert/Delete commands? If you don't have a
key for instance, it won't work but neither will the CommandBuilder -
although it should throw an exception.

Let me know about these and we'll take it from there.

Cheers,

Bill
Try
sqlDA_CB_LP.Update(DsCBLP1, "TDT_CUT_BLOCK_LP")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

But, it doesn't work...
Any ideas what I'm doing wrong??

Thanks,
Amber

[amber]

Hi Bill
Thanks for your help :
I added the "debug.assert()" line, and it did come up with an error...so I guess there is nothing to update
I did run the wizard, and it did generate all the sql commands
I've been battling this for a while, and was told I had to add the 'commandbuilder' line..
Does it have anything to do with the fact that I'm using a dataview, of a dataset, then trying to update the dataset
What I have is a form that has text fields populated (by this dataview) from a SQL database. I'm changing the text in them to trigger a 'change' so the update command will work
A 'save' button calls the update method
Any ideas?
Thank
ambe