bulk update via gridview

fwstealer
fwstealer used Ask the Experts™
on
I'm trying to perform a bulk update from a gridview but its not working out so well.

so it fails at: SQLCmd.ExecuteNonQuery();

not sure why as it is just a series of update statements

see attached file
gridviewerror.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dear!!!

Inplace of "\n" in your code use "go". Hope it will work.

Author

Commented:
changed to go and it threw an error.

query: {UPDATE dbo.ProjectedSalesUnits SET [newshares] = '1.00', [Lease2YrTerm] = '5.00', [Lease5YrTerm] = '2.00', [AAIROwned] = '1.50', [NonMMFPaying] '1.00', [UnderMMAgreement] '1.00', [TotalUsedShares] '3.50', [Edge25] '11.00', [Edge15] '4.00', [TotalEdge] '15.00', [FraxExpiring] '1', [TargetRenewalSales] '8.00', [DemoHours] '47.00' WHERE [iD] = '1';goUPDATE dbo.ProjectedSalesUnits SET [newshares] = '5.00', [Lease2YrTerm] = '15.00', [Lease5YrTerm] = '3.00', [AAIROwned] = '3.00', [NonMMFPaying] '3.00', [UnderMMAgreement] '3.50', [TotalUsedShares] '9.50', [Edge25] '40.00', [Edge15] '10.00', [TotalEdge] '50.00', [FraxExpiring] '1', [TargetRenewalSales] '6.00', [DemoHours] '43.20' WHERE [iD] = '2';go}


protected void updateData() 002 .Net SqlClient Data ProviderIncorrect syntax near '1.00'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Please try these below ways for this line ".Append(value14).Append("';\n"); "  as

.Append(value14).Append("'; \n " + " go " + " \n" );

or

.Append(value14).Append("'; \r\n " + " go " + " \r\n" );

or

.Append(value14).Append("'; "  + Constants.vbCrLf  + " go " +  Constants.vbCrLf );
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

well since you are passing all the parameters as string, maybe that is the reason.
I suppose some of the fields you are updating are supposed to be int or any other numeric types.

BTW it better to use command parameters to pass the parameters, here see an example:-
http://msdn.microsoft.com/en-us/magazine/cc163799.aspx

Author

Commented:
no luck with those options

Author

Commented:
yes they are decimal
Your missing the "=" signs in your UPDATE statement for some of your values:
UPDATE dbo.ProjectedSalesUnits SET [newshares] = '1.00', [Lease2YrTerm] = '5.00', [Lease5YrTerm] = '2.00', [AAIROwned] = '1.50', [NonMMFPaying] '1.00', [UnderMMAgreement] '1.00', [TotalUsedShares] '3.50', [Edge25] '11.00', [Edge15] '4.00', [TotalEdge] '15.00', [FraxExpiring] '1', [TargetRenewalSales] '8.00', [DemoHours] '47.00' WHERE [iD] = '1';

Open in new window


Starting with [NonMMFPaying].
Dear!!!

if you will see your below code, you are missing the "=" equal to in code

.Append(value4).Append("', [NonMMFPaying] '")
                  .Append(value5).Append("', [UnderMMAgreement] '")
                  .Append(value6).Append("', [TotalUsedShares] '")
                  .Append(value7).Append("', [Edge25] '")
                  .Append(value8).Append("', [Edge15] '")
                  .Append(value9).Append("', [TotalEdge] '")
                  .Append(value10).Append("', [FraxExpiring] '")
                  .Append(value11).Append("', [TargetRenewalSales] '")
                  .Append(value12).Append("', [DemoHours] '")

add equal and try. if it will not work then add equal and try with the option i have provided above.

Author

Commented:
not sure how I missed the = sign.

anyways new error: Error converting data type varchar to numeric.

protected void updateData() 002 .Net SqlClient Data ProviderError converting data type varchar to numeric. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

so i probably need to convert the fields to decimal?

Author

Commented:
i know this will do it for a single row but how for the entire grid and find the row id?

SQLQryString = "update ProjectedSalesUnits set [newshares] = @newshares,[Lease2YrTerm] = @Lease2YrTerm,[Lease5YrTerm] = @Lease5YrTerm,[AAIROwned] = @AAIROwned,[NonMMFPaying] = @NonMMFPaying,
[UnderMMAgreement] = @UnderMMAgreement,[TotalUsedShares] = @TotalUsedShares,[Edge25] = @Edge25,[Edge15] = @Edge15,[TotalEdge] = @TotalEdge,[FraxExpiring] = @FraxExpiring,[TargetRenewalSales] = @TargetRenewalSales,[DemoHours] = @DemoHours where iD ='" + rowid? + "'"

SQLCmd = new SqlCommand(query);

using (SQLCon = new SqlConnection(SQLConnectionString))
{
SQLCmd.Connection = SQLCon;

SQLCmd.Parameters.Add("@newshares", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[0].FindControl("txtbxNewShares")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@Lease2YrTerm", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[1].FindControl("txtbxLease2YrTerm")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@Lease5YrTerm", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[2].FindControl("txtbxLease5YrTerm")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@AAIROwned", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[3].FindControl("txtbxAAIROwned")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@NonMMFPaying", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[4].FindControl("txtbxNonMMFPaying")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@UnderMMAgreement", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[5].FindControl("txtbxUnderMMAgreement")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@TotalUsedShares", SqlDbType.Decimal).Value = Convert.ToDecimal(((Label)row.Cells[6].FindControl("lblTotalUsedShares")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@Edge25", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[7].FindControl("txtbxEdge25")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@Edge15", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[8].FindControl("txtbxEdge15")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@TotalEdge", SqlDbType.Decimal).Value = Convert.ToDecimal(((Label)row.Cells[9].FindControl("lblTotalEdge")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@FraxExpiring", SqlDbType.Decimal).Value = Convert.ToDecimal(((Label)row.Cells[10].FindControl("lblFraxExpiring")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@TargetRenewalSales", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[11].FindControl("txtbxTargetRenewalSales")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@DemoHours", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[12].FindControl("txtbxDemoHours")).Text.Replace("'", "''"));

try
{
SQLCon.Open();
SQLCmd.ExecuteNonQuery();
lblMessage.Visible = true;
lblMessage.Text = "success";
}
catch (Exception ex)
{
lblMessage.Text = "error";
}

}

Author

Commented:
oh - think this did the job:

query.Append("UPDATE dbo.ProjectedSalesUnits SET [newshares] = '")
                  .Append(Convert.ToDecimal((value1)))
                  .Append("', [Lease2YrTerm] = '")
                  .Append(Convert.ToDecimal((value2)))
                  .Append("', [Lease5YrTerm] = '")
                  .Append(Convert.ToDecimal((value3)))
                  .Append("', [AAIROwned] = '")
                  .Append(Convert.ToDecimal((value4)))
                  .Append("', [NonMMFPaying] = '")
                  .Append(Convert.ToDecimal((value5)))
                  .Append("', [UnderMMAgreement] = '")
                  .Append(Convert.ToDecimal((value6)))
                  .Append("', [TotalUsedShares] = '")
                  .Append(Convert.ToDecimal((value7)))
                  .Append("', [Edge25] = '")
                  .Append(Convert.ToDecimal((value8)))
                  .Append("', [Edge15] = '")
                  .Append(Convert.ToDecimal((value9)))
                  .Append("', [TotalEdge] = '")
                  .Append(Convert.ToDecimal((value10)))
                  .Append("', [FraxExpiring] = '")
                  .Append(Convert.ToDecimal((value11)))
                  .Append("', [TargetRenewalSales] = '")
                  .Append(Convert.ToDecimal((value12)))
                  .Append("', [DemoHours]  = '")
                  .Append(Convert.ToDecimal((value13)))
                  .Append("' WHERE [iD] = '")
                  .Append(value14)
                  .Append("';\n");
Dear!!!

please accept the solution if it worked for you.
Um, I was actually the original poster (38009906) that noticed you missing the equal sign (=) in your statements.

Is there another reason pkbugudai was awarded the points for my solution?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial