Data Adapter UpdateCommand

[GatorBait]

Hi all,

I am having a problem with my data adapter update command. I have
generated the data adapter in the IDE and then I built a dataset. The
dataset can get changed in the program and I would like to update the
database. I tried to do so using the update method of the data
adapter, but the table is not getting updated. While debugging I made
sure that the dataset has the correct value prior to calling the update
method. I think the problem is in the CommandText of the UpdateCommand
property of the data adapter. I'm not really sure what all this (see
below) means, and I'm not really sure what is "supposed to go in the
CommandText. One of my fields is called Printed and that is the only
thing I want to update, based on the Letter_Id. Can someone please
help me do what I thought was a simple update to my db?

Thank you very much!!!

This is the code that VS created (I appologize for it being so
long!)....

UPDATE dbo.Letters
SET Old_Letter_ID = @Old_Letter_ID, Old_Client_ID =
@Old_Client_ID, Old_Policy_ID = @Old_Policy_ID, Client_ID = @Client_ID,
Policy_ID = @Policy_ID, Memo1 = @Memo1, Descriptions = @Descriptions,
DateSent = @DateSent, TimeSent = @TimeSent, LetterName = @LetterName,
Printed = @Printed, PrintAgain = @PrintAgain, UserName = @UserName,
Agency = @Agency, rowguid = @rowguid WHERE (Letter_ID =
@Original_Letter_ID) AND (Agency = @Original_Agency) AND (Client_ID =
@Original_Client_ID) AND (DateSent = @Original_DateSent OR
@Original_DateSent IS NULL AND DateSent IS NULL) AND (Descriptions =
@Original_Descriptions OR
@Original_Descriptions IS NULL AND Descriptions IS NULL) AND
(LetterName = @Original_LetterName OR @Original_LetterName IS NULL AND
LetterName IS NULL) AND (Memo1 = @Original_Memo1 OR
@Original_Memo1 IS NULL AND Memo1 IS NULL) AND (Old_Client_ID =
@Original_Old_Client_ID OR @Original_Old_Client_ID IS NULL AND
Old_Client_ID IS NULL) AND (Old_Letter_ID = @Original_Old_Letter_ID OR
@Original_Old_Letter_ID IS NULL AND Old_Letter_ID IS NULL) AND
(Old_Policy_ID = @Original_Old_Policy_ID OR @Original_Old_Policy_ID IS
NULL AND Old_Policy_ID IS NULL) AND (Policy_ID = @Original_Policy_ID OR
@Original_Policy_ID IS NULL AND Policy_ID IS NULL) AND (PrintAgain =
@Original_PrintAgain) AND (Printed = @Original_Printed) AND
(TimeSent = @Original_TimeSent OR @Original_TimeSent IS NULL AND
TimeSent IS NULL) AND (UserName = @Original_UserName OR
@Original_UserName IS NULL AND UserName IS NULL) AND (rowguid =
@Original_rowguid); SELECT Letter_ID, Old_Letter_ID, Old_Client_ID,
Old_Policy_ID, Client_ID, Policy_ID, Memo1, Descriptions, DateSent,
TimeSent, LetterName, Printed, PrintAgain, UserName, Agency, rowguid
FROM dbo.Letters WHERE (Letter_ID = @Letter_ID)

 

[GatorBait]

Hi, my appologies, I forgot to post the code I am using to edit the
dataset and the update command that is not working:

dsLetters.Tables("tLetters").Rows(0)("Printed") = 1
SqlDataAdapter1.Update(dsLetters)

Thanks again in advance for your help!

 

[Cor Ligthert [MVP]]

GatorBait,

I see no error in your code, can you tell us something more what the effect
is from that update?

I assume that you have made the dataset and the dataadapter using the
dataadapterwizard.

Cor

 

[GatorBait]

Hi Cor,

Thanks for your reply. When I step through the code I do not get any
errors, but when I then go and check the table in SQL Server, the
record was not updated. Also, you are correct in your assumption that
I used the dataadapter wizard. There is more information that I think
may be helpful in this problem....

I may not be doing things properly so please tell me if I am incorrect
in how I do this. I created the data adapter and then built the data
set using the wizards. After I did that, I realized that I only wanted
to select those records in the Letters table with Printed = 0. I did
not see a way to have the dataset I just made accept an SQL statement
to change the contents so I just created a new dataset in the code and
that is the one that I am passing into the data adapter to be updated.
I'll post the code I am using:

Dim conn As New SqlConnection()
conn = GetMainConnSQL("username", "password", "server")
Dim sqlStr As String = "SELECT " & _
"* " & _
"FROM " & _
"Letters " & _
"WHERE " & _
"Printed = 0"
Dim rpt As New Letter()
daLetters = New SqlDataAdapter(sqlStr, conn)
dsLetters = New DataSet()
daLetters.Fill(dsLetters, "tLetters")

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

fReportViewer = New ReportViewer()
fReportViewer.crvMain.ReportSource = rpt
fReportViewer.MdiParent = Me.MdiParent
fReportViewer.Show()

Should I be creating the new data adapter and dataset? Also, one other
thing to consider is that I need to be able to access the dataset on 2
different MDI child forms. Thanks for your help!

GB

 

[Cor Ligthert [MVP]]

GatorBait,

I would use the original dataadapter you have created to select. I think
that it can be something as.

SQLConnection1 = GetMainConnSQL("username", "password", "server")
'this one above especially the name is a guess
dsLetters = New DataSet()
SQLDataAdapter1.Fill(dsLetters, "tLetters")

You are now not the dataset that you have generated so that you can remove
than from solution explorer to see if you did that not by mistake in the
rest of your code.

I hope this helps,

Cor

 

[GatorBait]

Cor,

I tried that and I am getting an error on
SQLDataAdapter1.Fill(dsLetters­, "tLetters")

"An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred in system.data.dll
Additional information: System error."

Any ideas?

 

[Cor Ligthert [MVP]]

Gator,

Can you set the fill in a try block like this
try
SQLDataAdapter1.Fill("dsLetters, "tLetters")
catch ex as exception
messagebox.show ex.tostring
end try

Than you see more information about the error.

Cor

 

[GatorBait]

Never mind, I found what the error is....it was an authentication
error, I wasn't using the global connection to my SQL server.
Moreover, when I fixed that error, I was able to get the update to the
table working properly!!! Thanks again Cor for your insight!

GB