DataAdapter UpdateCommand

[GatorBait]

Hi All,

I have a dataset that has more than one table in it so I am unable to
use the CommandBuilder to automatically generate an update command so I
have to do it by hand. I am a little confused on how to write such a
command and was hoping someone could help me out. Basically, I have 2
tables: 'Customers' and 'Letters' that are joined by a 'Customer_ID'
field. There is a field 'Printed' inside the Letters table.

In my application, the user can generate letters for customers, and I
keep the 'batch' of letters in a dataset. When the user prints the
letter for a particular customer, I set the Printed field to 1. There
can be multiple records that get printed so I'm confused on how I write
the update command for the data adapter.

UPDATE Letters SET Printed = 1 WHERE ??????

Can someone help me? Thanks a lot in advance!

 

[Marina]

Are you only updating columns that belong to one table? In which case, just
change the CommandText of the SelectCommand on the adapter to a query that
only includes only the columns from the one table you want to update, and
that one table.

So, if your original query was:

Select T1.SomeKeyCol, T1.Col1,T2.Col2 FROM T1,T2 WHERE T1.SomeKeyCol=
T2.SomeKeyCol WHERE ...

change it to:

Select SomeKeyCol,Col1 From T1

You don't need a WHERE clause or anything on this new 'fake' query. The
CommandBuilder will just use this new query to read the schema for T1.

 

[GatorBait]

Hi Marina,

Thanks for your reply. I am selecting from 2 tables, and I need to in
this case because the data is used in a Crystal report. All I want to
do is update the table with the changed dataset (I change the value of
the 'Printed' field in the 'Letters' table). Here is the code I am
using:

Cursor = Cursors.WaitCursor

Dim conn As New SqlConnection()
Dim cmdBuilder As SqlCommandBuilder
Dim rpt As New Letter()
Dim strSQL As String

conn = GetMainConnSQL("UserName", "Password", "Server")

strSQL = "SELECT " & _
"Letters.*, CLNMAS.CompanyName, CLNMAS.FNAME,
CLNMAS.LNAME, CLNMAS.ADDRESS1, CLNMAS.ADDRESS2, CLNMAS.CITY,
CLNMAS.STATE, CLNMAS.ZIP, Email " & _
"FROM " & _
"CLNMAS RIGHT JOIN Letters (NOLOCK) ON
CLNMAS.CLIENT_ID = Letters.Client_ID " & _
"WHERE " & _
"Letters.Printed = 0"

daLetters = New SqlDataAdapter(strSQL, conn)
dsLetters = New DataSet()

daLetters.Fill(dsLetters, "tLetters")
cmdBuilder = New SqlCommandBuilder(daLetters)
(of course, this errors out!)
daLetters.UpdateCommand = cmdBuilder.GetUpdateCommand

rpt.SetDataSource(dsLetters.Tables("tLetters"))

fCrystalViewer = New CrystalViewer()
fCrystalViewer.crvMain.ReportSource = rpt
fCrystalViewer.MdiParent = Me.MdiParent
fCrystalViewer.Show()

Cursor = Cursors.Arrow


When the user closes the Crystal Report, the following line is executed
daLetters.Update(dsLetters, "tLetters")

Thanks!!!

 

[Marina]

I already answered what one technique for solving this kind of problem is...