.Update command hanging up HELP!!!

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi All,

I'm currently trying to run and update using the code below but when I
get to the FIRST oDA.Update call the program just hangs on me. I let it
sit there four about 15 minutes and the call never returned (at most 5
rows would updating for my test). Can anyone offer any ideas? There are
two functions below SaveChanges and UpdateCommand. From comparing
what's going on in them to what I've found in the help files it looks
like everything is OK.

Thanks in advance for any help.

Sincerely,

Mike Gorgone

Private Sub SaveChanges()
Dim cmd As SqlClient.SqlCommand
Dim oDA As SqlClient.SqlDataAdapter = New
SqlClient.SqlDataAdapter
Dim oTrans As SqlClient.SqlTransaction

FUNCTION SHOW BELOW
oDA.UpdateCommand = UpdateCommand()
FUNCTION SHOW BELOW


Dim oCDS As DataSet = mMainDS.GetChanges
oDA.ContinueUpdateOnError = False

Try

FUNCTION THAT IS HANGING
oDA.Update(oCDS, TableNames.AGGREGATEDATA)
FUNCTION THAT IS HANGING


oDA.UpdateCommand = UpdateCommandClientData()
oDA.Update(oCDS, TableNames.CLIENTDATA)

mMainDS.AcceptChanges()

Finally
oDA.Dispose()
End Try

End Sub



Private Function UpdateCommand() As SqlClient.SqlCommand
Dim cmd As SqlClient.SqlCommand

With New System.Text.StringBuilder

..Append(String.Concat("Update ", TableNames.AGGREGATEDATA, " SET "))

..Append(String.Concat("[", AGGDATA.FieldNames.NetWealth, "]=@",
AGGDATA.FieldNames.NetWealth, ", "))

..Append(String.Concat("[", AGGDATA.FieldNames.MajorGivingCapacity,
"]=@", AGGDATA.FieldNames.MajorGivingCapacity, ", "))

..Append(String.Concat("[", AGGDATA.FieldNames.AnnualGivingCapacity,
"]=@", AGGDATA.FieldNames.AnnualGivingCapacity, ", "))

..Append(String.Concat("[", AGGDATA.FieldNames.NWRealEstate, "]=@",
AGGDATA.FieldNames.NWRealEstate, ", "))

..Append(String.Concat("[", AGGDATA.FieldNames.NWStock, "]=@",
AGGDATA.FieldNames.NWStock, ", "))

..Append(String.Concat("[", AGGDATA.FieldNames.NWIncome, "]=@",
AGGDATA.FieldNames.NWIncome, ", "))

..Append(String.Concat("[", AGGDATA.FieldNames.NWOwnership, "]=@",
AGGDATA.FieldNames.NWOwnership, ", "))

..Append(String.Concat("[", AGGDATA.FieldNames.NWFoundation, "]=@",
AGGDATA.FieldNames.NWFoundation, ", "))

..Append(String.Concat("[", AGGDATA.FieldNames.NWOtherAssets, "]=@",
AGGDATA.FieldNames.NWOtherAssets))

..Append(String.Concat(" where [", AGGDATA.FieldNames.ID, "]=@",
AGGDATA.FieldNames.ID))

cmd = New SqlClient.SqlCommand(.ToString, Connection)

End With

cmd.Parameters.Add(String.Concat("@", AGGDATA.FieldNames.NetWealth),
SqlDbType.Decimal, 18, AGGDATA.FieldNames.NetWealth)

cmd.Parameters.Add(String.Concat("@",
AGGDATA.FieldNames.MajorGivingCapacity), SqlDbType.Decimal, 18,
AGGDATA.FieldNames.MajorGivingCapacity)

cmd.Parameters.Add(String.Concat("@",
AGGDATA.FieldNames.AnnualGivingCapacity), SqlDbType.Decimal, 18,
AGGDATA.FieldNames.AnnualGivingCapacity)

cmd.Parameters.Add(String.Concat("@",
AGGDATA.FieldNames.NWRealEstate), SqlDbType.Decimal, 18,
AGGDATA.FieldNames.NWRealEstate)

cmd.Parameters.Add(String.Concat("@", AGGDATA.FieldNames.NWStock),
SqlDbType.Decimal, 18, AGGDATA.FieldNames.NWStock)

cmd.Parameters.Add(String.Concat("@", AGGDATA.FieldNames.NWIncome),
SqlDbType.Decimal, 18, AGGDATA.FieldNames.NWIncome)

cmd.Parameters.Add(String.Concat("@", AGGDATA.FieldNames.NWOwnership),
SqlDbType.Decimal, 18, AGGDATA.FieldNames.NWOwnership)

cmd.Parameters.Add(String.Concat("@",
AGGDATA.FieldNames.NWFoundation), SqlDbType.Decimal, 18,
AGGDATA.FieldNames.NWFoundation)

cmd.Parameters.Add(String.Concat("@",
AGGDATA.FieldNames.NWOtherAssets), SqlDbType.Decimal, 18,
AGGDATA.FieldNames.NWOtherAssets)

cmd.Parameters.Add(String.Concat("@", AGGDATA.FieldNames.ID),
SqlDbType.Int, 4, AGGDATA.FieldNames.ID)
cmd.CommandTimeout = 3600
Return cmd
End Function
 
That's a little bit too much code for me to wade through without
knowing much about your database, so I'll confine myself to suggesting
that you create a SQL Profiler trace to see what is going on under the
covers. One other suggestion -- do your string concatenation in
variable assignments *before* adding the parameters.

--Mary

Hi All,

I'm currently trying to run and update using the code below but when I
get to the FIRST oDA.Update call the program just hangs on me. I let it
sit there four about 15 minutes and the call never returned (at most 5
rows would updating for my test). Can anyone offer any ideas? There are
two functions below SaveChanges and UpdateCommand. From comparing
what's going on in them to what I've found in the help files it looks
like everything is OK.

Thanks in advance for any help.

Sincerely,

Mike Gorgone

Private Sub SaveChanges()
Dim cmd As SqlClient.SqlCommand
Dim oDA As SqlClient.SqlDataAdapter = New
SqlClient.SqlDataAdapter
Dim oTrans As SqlClient.SqlTransaction

FUNCTION SHOW BELOW
oDA.UpdateCommand = UpdateCommand()
FUNCTION SHOW BELOW


Dim oCDS As DataSet = mMainDS.GetChanges
oDA.ContinueUpdateOnError = False

Try

FUNCTION THAT IS HANGING
oDA.Update(oCDS, TableNames.AGGREGATEDATA)
FUNCTION THAT IS HANGING


oDA.UpdateCommand = UpdateCommandClientData()
oDA.Update(oCDS, TableNames.CLIENTDATA)

mMainDS.AcceptChanges()

Finally
oDA.Dispose()
End Try

End Sub



Private Function UpdateCommand() As SqlClient.SqlCommand
Dim cmd As SqlClient.SqlCommand

With New System.Text.StringBuilder

.Append(String.Concat("Update ", TableNames.AGGREGATEDATA, " SET "))

.Append(String.Concat("[", AGGDATA.FieldNames.NetWealth, "]=@",
AGGDATA.FieldNames.NetWealth, ", "))

.Append(String.Concat("[", AGGDATA.FieldNames.MajorGivingCapacity,
"]=@", AGGDATA.FieldNames.MajorGivingCapacity, ", "))

.Append(String.Concat("[", AGGDATA.FieldNames.AnnualGivingCapacity,
"]=@", AGGDATA.FieldNames.AnnualGivingCapacity, ", "))

.Append(String.Concat("[", AGGDATA.FieldNames.NWRealEstate, "]=@",
AGGDATA.FieldNames.NWRealEstate, ", "))

.Append(String.Concat("[", AGGDATA.FieldNames.NWStock, "]=@",
AGGDATA.FieldNames.NWStock, ", "))

.Append(String.Concat("[", AGGDATA.FieldNames.NWIncome, "]=@",
AGGDATA.FieldNames.NWIncome, ", "))

.Append(String.Concat("[", AGGDATA.FieldNames.NWOwnership, "]=@",
AGGDATA.FieldNames.NWOwnership, ", "))

.Append(String.Concat("[", AGGDATA.FieldNames.NWFoundation, "]=@",
AGGDATA.FieldNames.NWFoundation, ", "))

.Append(String.Concat("[", AGGDATA.FieldNames.NWOtherAssets, "]=@",
AGGDATA.FieldNames.NWOtherAssets))

.Append(String.Concat(" where [", AGGDATA.FieldNames.ID, "]=@",
AGGDATA.FieldNames.ID))

cmd = New SqlClient.SqlCommand(.ToString, Connection)

End With

cmd.Parameters.Add(String.Concat("@", AGGDATA.FieldNames.NetWealth),
SqlDbType.Decimal, 18, AGGDATA.FieldNames.NetWealth)

cmd.Parameters.Add(String.Concat("@",
AGGDATA.FieldNames.MajorGivingCapacity), SqlDbType.Decimal, 18,
AGGDATA.FieldNames.MajorGivingCapacity)

cmd.Parameters.Add(String.Concat("@",
AGGDATA.FieldNames.AnnualGivingCapacity), SqlDbType.Decimal, 18,
AGGDATA.FieldNames.AnnualGivingCapacity)

cmd.Parameters.Add(String.Concat("@",
AGGDATA.FieldNames.NWRealEstate), SqlDbType.Decimal, 18,
AGGDATA.FieldNames.NWRealEstate)

cmd.Parameters.Add(String.Concat("@", AGGDATA.FieldNames.NWStock),
SqlDbType.Decimal, 18, AGGDATA.FieldNames.NWStock)

cmd.Parameters.Add(String.Concat("@", AGGDATA.FieldNames.NWIncome),
SqlDbType.Decimal, 18, AGGDATA.FieldNames.NWIncome)

cmd.Parameters.Add(String.Concat("@", AGGDATA.FieldNames.NWOwnership),
SqlDbType.Decimal, 18, AGGDATA.FieldNames.NWOwnership)

cmd.Parameters.Add(String.Concat("@",
AGGDATA.FieldNames.NWFoundation), SqlDbType.Decimal, 18,
AGGDATA.FieldNames.NWFoundation)

cmd.Parameters.Add(String.Concat("@",
AGGDATA.FieldNames.NWOtherAssets), SqlDbType.Decimal, 18,
AGGDATA.FieldNames.NWOtherAssets)

cmd.Parameters.Add(String.Concat("@", AGGDATA.FieldNames.ID),
SqlDbType.Int, 4, AGGDATA.FieldNames.ID)
cmd.CommandTimeout = 3600
Return cmd
End Function
Click to expand...
 
Back
Top