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!

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!
>

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!!!

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

"GatorBait" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!!!
>